Google Script Function Timing-CodePudding

I am struggling to find the cause of the "mis-timing" of the following two function calls. I am attempting to have google look for a csv file in a particular gmail folder and insert the data into a google sheet. That works fine. Then, once the data is in the sheet, I call "UpdateComplete" whose sole purpose is to sort the sheet by column K (where there is a vlookup function that looks at a sheet with completed rows that have been moved there) so that rows that have the vlookup function already are sort to the top, and it then copies the formula into the rows that are new and don't already have it. However, if the google sheet has, say, 2000 rows, and the csv file contains 2100, for some reason the new 100 rows are being added after the call to UpdateComplete. So the new 100 rows are added, but they do not get the vlookup like all of the other rows. This issue only happens when the google sheet does not have enough rows, initially, for the csv data.

If, however, I comment out the call to "UpdateComplete" from within "RetrieveAwardData", and manually run that first, and then manually run "UpdateComplete", it works perfectly. I have tried adding a Utilities.Sleep call before the call to "UpdateComplete" (but after the csv setvalues line), in case it was a timing thing, but when I do that, the system waits that amount of time before adding the 100 new rows, even though the line for sleep comes after the line to add the csv data. I also tried creating a new function that calls "RetrieveAwardData" first (with the UpdateComplete call commented out) and then calls UpdateComplete 2nd, but the same issue happens. Why does it work properly if I run them, separately, manually, but not one after the other programmatically?

function RetrieveAwardData(){
  var threads = GmailApp.search('is:unread subject:VA Benefit Aid');
  var message = GmailApp.getMessagesForThreads(threads); //retrieve all messages in the specified threads.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AwardData');
  
  if(message[0] != null){
    Logger.log(message[0]);
    for (var i = 0 ; i < message.length; i  ) {
      for (var j = 0; j < message[i].length; j  ) {
        var attachment = message[i][j].getAttachments()[0];
        var csvData = Utilities.parseCsv(attachment.getDataAsString('ISO-8859-1'), ",");
        
        sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
        
        UpdateComplete();
        
        GmailApp.markMessageRead(message[i][j]);
      }
    }
  }
  else{Logger.log("No file available.");}
}

function UpdateComplete(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('AwardData');
  sheet.sort(11);
  var LastAwardRow = sheet.getLastRow();
  var Avals = ss.getRange("K1:K").getValues();
  var LastCompleteRow = Avals.filter(String).length;
  
  if(LastAwardRow != LastCompleteRow){  
    sheet.getRange("K" (LastCompleteRow 1)   ":K"   LastAwardRow).setFormulaR1C1(sheet.getRange("K" LastCompleteRow).getFormulaR1C1());
  }

}

CodePudding user response：

Posting this for documentation purposes.

As mentioned by Cooper, use SpreadsheetApp.flush().

This method ensures that later parts of the script work with updated data, since changes made by previous parts are applied to the spreadsheet.