时间戳

首页 > 解决方案 > Javascript 中的循环从 Google Apps 脚本中的错误索引开始

javascript - Javascript 中的循环从 Google Apps 脚本中的错误索引开始

问题描述

我有一个表格，第四行是学生姓名，根据班上有多少学生，我需要不同数量的页数。根据表格的类型，每张表格可容纳 8 或 9 名学生。

我有一个外循环来处理每张纸，还有一个内循环来处理该行上的每个名称。只要学生人数是 8 或 9 的倍数（取决于表格），姓氏就会被跳过。当我开始研究问题并检查计数器的值时，我注意到

  1. 记录器输出中打印的第一件事是行“i value:”及其值，1
  2. 索引 i 从 1 开始，h 从 7 开始，尽管被初始化为 0

这是非常令人困惑的（更不用说令人沮丧了）。

这是代码：

function insertNames(mealcountDoc, className, namesInTheClass, constructionDetails){
Logger.log("\nEntered 'insertNames' function\n");
  namesInTheClass = namesInTheClass.sort();
  var newlyCreatedSheet = mealcountDoc.getSheetByName(className);

// loop for each row in the tab
  var numOfRowsOfMealcounts = Math.ceil(namesInTheClass.length/constructionDetails.namesPerRow);
  for(var i = 0; i < numOfRowsOfMealcounts; i++){
  Logger.log("i value just after entering outer loop: " + i);
//  Logger.log("h value just after entering outer loop: " + h);
    //    var countOfStudentsEntered = 0;
    var classDetailsInfoRow = (i * 41 + 3);
    var footerRow = (i * 41 + 41);
    newlyCreatedSheet.getRange(classDetailsInfoRow - 2, mealcountDataPlacement.className).setValue(className).setHorizontalAlignment('Left');
    newlyCreatedSheet.getRange(classDetailsInfoRow, mealcountDataPlacement.nameOfInstitution).setValue(mealcountDoc.getName().slice(0, mealcountDoc.getName().indexOf("-")-1)).setHorizontalAlignment('Left');
    newlyCreatedSheet.getRange(classDetailsInfoRow, mealcountDataPlacement.agreementNum).setValue(constructionDetails.agreementNumber).setHorizontalAlignment('Left');
    newlyCreatedSheet.getRange(classDetailsInfoRow, mealcountDataPlacement.facilityNameAndNum).setValue(mealcountDoc.getName().slice(0, mealcountDoc.getName().indexOf("-")-1)).setHorizontalAlignment('Left');
    newlyCreatedSheet.getRange(classDetailsInfoRow, mealcountDataPlacement.monthYear).setValue(constructionDetails.monthAsAWord + " " + constructionDetails.currentYear).setHorizontalAlignment('Left');
    newlyCreatedSheet.getRange(classDetailsInfoRow, mealcountDataPlacement.serviceDays).setValue(constructionDetails.numOfServiceDays).setHorizontalAlignment('Left');
    newlyCreatedSheet.getRange(footerRow, mealcountDataPlacement.pageNumber).setValue("Page " + (i + 1) + " of " + numOfRowsOfMealcounts).setHorizontalAlignment('Left');
    
    var startingRow = (i * 41 + 4);
    var loopLimit = constructionDetails.namesPerRow
    
    // loop for each name
    // if-else statement deals with the final row containing less than a full row of names (8 or 9, dep on type of sheet)
    if(numOfRowsOfMealcounts - i <= 1 && namesInTheClass.length % constructionDetails.namesPerRow != 0){
      loopLimit = namesInTheClass.length % constructionDetails.namesPerRow;
    }
    for(var h = 0; h < loopLimit; h++){
      Logger.log("\n");
      Logger.log(constructionDetails);
      Logger.log(namesInTheClass);
      Logger.log("i value: " + i );
      Logger.log("h value: " + h );
      Logger.log(namesInTheClass[i * constructionDetails.namesPerRow + h][1]+ "\n" + namesInTheClass[i * constructionDetails.namesPerRow + h][0]);
      newlyCreatedSheet.getRange(startingRow,h * constructionDetails.numOfColsPerEntry + 2,1,1).setValue(namesInTheClass[i * constructionDetails.namesPerRow + h][1]+ "\n" + namesInTheClass[i * constructionDetails.namesPerRow + h][0]);
    }    
  }

日志：


Stackdriver logs
Aug 19, 2020, 4:12:44 PM
Info
i value here: 1
Aug 19, 2020, 4:12:44 PM
Info
h value here: 7
Aug 19, 2020, 4:12:44 PM
Info
Cayden
Jimenez
Aug 19, 2020, 4:12:44 PM
Info
i value just after entering outer loop: 2
Aug 19, 2020, 4:12:44 PM
Info

Aug 19, 2020, 4:12:44 PM
Info
{currentYear=2020, initNumberOfCols=5.0, agreementNumber=0.0, firstSaturday=5.0, numOfColsPerEntry=4.0, numOfServiceDays=22.0, lastDateOfMonth=30.0, trackingColumnsLength=33.0, namesPerRow=8.0, mealcountType=Attendance, monthAsAWord=September}
Aug 19, 2020, 4:12:44 PM
Info
[[Barr, Serena, , , Fri Jun 19 00:00:00 GMT-07:00 2020, First Day of School, Shelly's Infants], [Bonilla, Klay, , , Wed Jan 18 00:00:00 GMT-08:00 2017, First Day of School, Shelly's Infants], [Brookes, River, , , Tue Feb 07 00:00:00 GMT-08:00 2017, First Day of School, Shelly's Infants], [Burnett, Kaden, , , Sun Sep 11 00:00:00 GMT-07:00 2016, First Day of School, Shelly's Infants], [Coulson, Camille, , , Mon Mar 23 00:00:00 GMT-07:00 2020, First Day of School, Shelly's Infants], [Cruz, Abdulahi, , , Wed Jan 10 00:00:00 GMT-08:00 2018, First Day of School, Shelly's Infants], [Duarte, Ben, , , Wed Jan 04 00:00:00 GMT-08:00 2017, First Day of School, Shelly's Infants], [Franco, Shaunie, , , Tue Feb 07 00:00:00 GMT-08:00 2017, First Day of School, Shelly's Infants], [Fuller, Roscoe, , , Wed Jan 10 00:00:00 GMT-08:00 2018, First Day of School, Shelly's Infants], [Giles, Gwen, , , Fri Jun 23 00:00:00 GMT-07:00 2017, First Day of School, Shelly's Infants], [Goodman, Jeremiah, , , Thu Aug 31 00:00:00 GMT-07:00 2017, First Day of School, Shelly's Infants],

它继续。

请注意，日志中的第一个条目不是代码首先调用的打印内容。

标签： javascriptloopsgoogle-apps-script

解决方案

Apps 脚本调用SpreadsheetApp异步运行，这会使您的调用顺序无序。

  1. 您应该setValue()通过单个setValues()请求替换多个请求，请参阅最佳实践。

  2. 您应该在每个请求之后放置SpreadsheetApp一个SpreadsheetApp.flush()调用，以确保循环迭代仅在所有调用SpreadsheetApp成功完成后继续。

推荐阅读