javascript - Google Apps 脚本将日期插入 GSheets 但更改为未定义

Question

此代码将在正确的单元格中插入正确的日期，但似乎在每次循环之后(移到下一行之前)日期都会更改为未定义。这只发生在 var = expDelivery 所有其他日期都可以。

我不确定为什么它会正确插入日期，然后更改为未定义。

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

你的代码效率太低了。您正在此处读取工作表值 var shtVals = sh.getDataRange().getValues();。然后，对于每一行、每一列，您都会一次又一次地读取和写入工作表值。

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);

阅读和写作需要时间，这就是为什么你应该尽可能减少它们。最好的方法是读取一次，对值进行所有操作，然后写入一次。

总会有特殊情况，也许您不需要速度。

关于这个问题-

这里写的是data[j][i+1] = expDelivery;。正如代码所建议的，这是在运行几次的内部循环内。该代码似乎逐行修改列，因此一行不太可能与前一行混淆。这可能是内部循环问题，其中列被读取、操作和写入。

循环代码不容易理解，所以修复bug比较困难。如果您还没有解决问题，您可能想先简化它。