Google Script – Opting out of a loop before it’s finished, returns later to finish the loop

I have a bit of a weird problem. I built a script that will essentially take variable information from Spreadsheet 1, it will then create a copy of EmptySheet, fill out all the information row by row (let’s refer to it as FilledInSheet after).

After that it takes FilledInSheet and deletes the requested tabs. This all works perfectly, except that the last row to be filled in, that row is skipped, the copyfile for 4 is created, and AFTER that the FilledInSheet is updated with the right information.

I have spent hours and hours trying to fix this, any feedback is much appreciated. Again, everything works except the code seems to skip one row loop, and then comes back to it later?

I have tried to boil the code down as much as possible, but please let me know if anyone has additional questions.

function ingestProduct() {
  var ss = SpreadsheetApp.getActive();
  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var selectionRange = ss.getRange("Overview!A:A").getValues();
  var destinationId = id_of_the_folder
  var variable2 = ["1", "2", "3", "4"];

  //cut off 2 headers
  var headers = rows.shift();
  var headers2 = rows.shift();
  //loop through each row and check if it's been selected. If so, continue
  for (var r = 0; r < rows.length; r++) {
    var row = rows[r];
    if (row[0] === true) {
      //overview of all information fields available
      var unimportantvariable = row[1];

      //toggle between the different variable2's and execute the fillDistroOut code
      for (var x = 0; x < variable2.length; x++) {
        fillDistroOut(variable2[x]);
      }
    }
  } //this should close the loop

  for (var e = 0; e < variable2.length; e++) {
    var destFile = ss.openById("1234");
    if (destFile.hasNext()) {
      //check if file already exists
      Logger.log("File exists");
    } else {
      //splitting up files
      var newFolder = DriveApp.getFolderById(destinationId);
      var ingestionFile = DriveApp.getFileById(fileid).makeCopy(
        newName,
        newFolder
      );
      var ingestionFileID = ingestionFile.getId();
      var ingestionFile2 = DriveApp.getFileById(ingestionFileID);
      ingestionFile2.moveTo(newFolder);
      var fileOpenned = SpreadsheetApp.openById(ingestionFileID);

      switch (variable2[e]) {
        case "1":
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("2")
          );
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("3")
          );
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("4")
          );
          break;
        case "2":
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("1")
          );
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("3")
          );
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("4")
          );
          break;
        case "3":
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("1")
          );
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("2")
          );
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("4")
          );
          break;
        case "4":
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("1")
          );
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("2")
          );
          fileOpenned.deleteSheet(
            SpreadsheetApp.openById(ingestionFileID).getSheetByName("3")
          );
          break;
        default:
          break;
      }
    }
  }
}

function fillDistroOut(variable2){
  var destRange = SpreadsheetApp.openById(copyID).getSheetByName(name);
  var Avals = destRange.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length+1;
  var headerRange = destRange.getRange(1,1,2,30).getValues();
    for(var x=1; x < headerRange[0].length; x++){
    destRange.getRange(Alast,x).setValue(variabel4[x]);}
    }

Answer

Use SpreadsheetApp.flush()

• This will ensure that the script execution will pause and wait for the request within one loop to be completed before the next loop iteration starts.

• Without this the script runs asynchronously and a request execution could start before the previous one(s) is completely finished.

• SpreadsheetApp.flush() only works for calls to the SpreadsheetApp, so a good position to place it is after calling a SpreadsheetApp method that you expect to be slow.

Sample:

  for(var x=1; x < headerRange[0].length; x++){
    destRange.getRange(Alast,x).setValue(variabel4[x]);
    SpreadsheetApp.flush()
  }