Google Apps Script inserts a date to GSheets but changes to undefined

Question

This code will insert the correct dates in the correct cells, but seems like after each loop (before moving to the next row) the date changes to undefined. This only happens for var = expDelivery all other dates are fine.

I am not sure why it inserts the date, correctly, then changes to undefined.

function doPost(e){
  //append the ID to your spreadsheet like this:
  var sheetID = e.parameter.sheetid;  
  var ss = SpreadsheetApp.openById(sheetID);
  var sh = ss.getSheetByName("Sheet1");
  var headers = sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  
  var uninstalledCol = headers.indexOf("uninstalledDate");
  
  //get all variables passed from app
  var ft = e.parameter.ftNumber;  
  var measuredDate = new Date(e.parameter.measuredDate);
  var installedDate = new Date(e.parameter.installedDate);
  var uninstalledDate = new Date(e.parameter.uninstalledDate);
  //add 21 days to measuredDate 
  var dayInMs = 24*60*60*1000;    //one day in Milliseconds
  var daysToAdd = 21;  
  var expDelivery = new Date(measuredDate.getTime()+(daysToAdd*dayInMs));  
  
  var shtData = sh.getDataRange();
  var shtVals = shtData.getValues();

  var updatedCols = [];
  
  for(var j = 1; j < shtVals.length; j++) //Loop through rows
  {
    for (var i = 6; i < uninstalledCol; i++)  //Loop through columns
    {
      // IF col 1 of current row = ft AND current cell is blank AND current col title = measuredDate AND value passed with same name as col title IS NOT blank
      if(shtVals[j][0] == ft && shtVals[j][i] == "" && headers[i] == "measuredDate" && e.parameter[headers[i]] != "") 
      {
        shtVals[j][i] = e.parameter[headers[i]];
        shtVals[j][i+1] = expDelivery; //Will set the correct date in spreadsheet but changes to "undefined" before moving to next row
        shtData.setValues(shtVals);
        updatedCols.push([headers[i]]);
      } 
      // IF col 1 of current row = ft AND current cell is blank AND current col title IS NOT "DELIVERED DATE" or "measuredDate" AND value passed with same name as col title IS NOT blank
      else if(shtVals[j][0] == ft && shtVals[j][i] == "" && headers[i] != "DELIVERED DATE" && headers[i] != "measuredDate" && e.parameter[headers[i]] != "")
      {
        shtVals[j][i] = e.parameter[headers[i]];
        shtData.setValue(shtVals);
        updatedCols.push([headers[i]]);
      }
    }
  }

 return message(updatedCols);

}


function message(msg) {
  return ContentService.createTextOutput(JSON.stringify({Result: msg })).setMimeType(ContentService.MimeType.JSON);
}

Answer 1

Your code is so inefficient. You're reading sheet values onece here var shtVals = sh.getDataRange().getValues();. Then for each row, for each col, you're reading and writing sheet values again and again.

for(var j = 1; j < shtVals.length; j++) //Loop through rows
  {
    for (var i = 6; i < uninstalledCol; i++)  //Loop through columns
    {
      {
        // do stuff
      }
      {
        // do stuff
      }
    }
  }
shtData.setValues(data);

Reading and writing takes time, that's why you should minimize them as possible. Best way is to read once, do all operation on the values then write once.

There can always be special cases and maybe you don't need speed.

About the issue -

It's writing here data[j][i+1] = expDelivery;. This is inside the inner loop which is running a few times, as suggested by the code. The code seems modifying col by col of a row, so it is unlikely for a row to mess with previous row. This is probably inner loop issue where cols are getting read, manipulated and written.

The loop code is not easy to understand, so, fixing bug is harder. You might wanna simplify it first if you haven't fixed the issue already.

Answer 2

I need the values to go to specific cells, is it possible to do this with a single write function?

Yes.

Lets say this is your sheet -

    -----------------------------
    | A            | B          |
    -----------------------------
1   | Name         | Age        |
    -----------------------------
2   | Alice        | 25         |
    -----------------------------
3   | Bob          | 30         |
    -----------------------------

We do var values = sheet.getDataRange().getValues(). We get -

values = [ [ Name,  Age ],
           [ Alice,  25 ],
           [ Bob,    30 ] ]

Let's say you want to change A2 into Jane. Which indices do you use to access it inside values array ?

You can see from sheet, its 2nd row, 1st col. But values is a 2-dimensional array, the indices will be values[1][0] because indices in arrays start with 0.

So, the actual indices to access A2 inside values will be values[1][0], row and col each 1 less than sheet row, col indices.

Lets change A2 to Jane. values[1][0] = 'Jane'

Once you change values array, this change is live, in memory, ready to be accessed in other operation or calculation. But, it is not written to sheet yet.

Lets say you need to change B2 as Jane is 26. So, do you make that change now ? Or, write previous change into sheet first ? It is preferred to make that change now and write both changes in one operation.

Now, what is the indices in values for B2?. B2 is 2nd row, 2nd col in sheet. In values array it is values[1][1].

Lets change B2 to 26. values[1][1] = 26. Now both changes are live. If we write values into sheet, both changes will be saved.

The part that is giving me trouble when it comes to this, is specifying the range to set values, how do I pass the [j] and [I] values of each entry.

Now you know how sheet indices and values indices work. Use values indices to change/set values on it then write values into sheet.