Function works but nothing appears in cell

Question

So I have a function called shift that returns what shift the user is in based on the time.

When I run the program the time and dates go into their cells in my spreadsheet however my shift value does not. I know my shift function returns a value because I've tested it using the test function and the correct value appears in my logs. Here is my code:

const timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();

/** @returns {string} */
function timestamp() {
  var timestamp_format = "HH:mm:ss";
  return Utilities.formatDate(new Date(), timezone, timestamp_format);
}

/** @returns {string} */
function datestamp() {
  var datestamp_format = "yyyy-MM-dd";
  return Utilities.formatDate(new Date(), timezone, datestamp_format);
}

function shift() {
  var shift;
  const dt = timestamp();
  if(dt > "08:00:00" && dt < "13:59:00"){
    shift = "1";
  }
  return shift;
} 

function test(){

  shifts = shift();
  console.log(shifts);
}

/* @Process Form */
function processFormHood(formObject) {
  var url = "GOOGLE DOCS URL";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("SHEETNAME");
  
  ws.appendRow([
                datestamp(),
                timestamp(),
                shift()])
}

The last function takes values from my HTML form and writes it into my spreadsheet. An example would be "formObject.value," I have attempted to do this with my shift function but it did not work. The cell it is supposed to be in gets skipped and everything after it gets filled.

I think that in your script in your question, when `processFormHood` is run, the values of `[datestamp(),timestamp(),shift()]` are appended to the Spreadsheet. So, in order to correctly understand about your issue of `Function works but nothing appears in cell`, can I ask you about the detail flow for replicating your issue? By the way, I couldn't understand about `An example would be "formObject.value," I have attempted to do this with my shift function but it did not work. The cell it is supposed to be in gets skipped and everything after it gets filled.`. — Tanaike, Oct 08 '20 at 23:56
@Tanaike When processFormHood runs it places the results from my functions into a row which has no data, for example say row 6 has no data in it, then cell A6 receives datestamp()'s value, B6 receives timestamp()'s value, and then C6 is supposed to receive shift()'s value however this is the problem as nothing gets inserted into the cell. — Ramy CoreMT, Oct 12 '20 at 12:30
@Tanaike This function also has a formObject parameter. This parameter retrieves the answers submitted from an HTML form and then inserts its value into my cell. For example say I have in my HTML form then formObject.task gets the value. — Ramy CoreMT, Oct 12 '20 at 12:30
Thank you for replying. About `C6 is supposed to receive shift()'s value however this is the problem as nothing gets inserted into the cell.`, in your script, the string values are compared as the date. I think that this is the reason of your issue. In this case, [this thread](https://stackoverflow.com/q/492994/7108653) might be useful. And about `This function also has a formObject parameter.`, in your script, it seems that `formObject` is not used. How about this? — Tanaike, Oct 12 '20 at 22:10

score 0 · Accepted Answer · answered Oct 13 '20 at 12:09

It seems all I had to do was instead of having

function shift() {
 var shift;
  const dt = timestamp();
  if(dt > "08:00:00" && dt < "13:59:00"){
    shift = "1";
  }
  return shift;
}

I needed to do was make it return without the use of varables:

function shift() {
  const dt = timestamp();
  if(dt > "06:00:00" && dt < "13:59:99"){
    return 1;
  }
  else if(dt > "14:00:00" && dt < "21:59:59"){
    return "2,3";
  }
}

Function works but nothing appears in cell

1 Answers1