Adding lockservice and SpreadsheetApp.flush to a script (Google App Script / Google Sheets)

Question

Researching how to make the script not continue until IMPORTXML has finished collecting data, I found this function as one of the most used indications:

function testWait(){
  var lock = LockService.getScriptLock(); lock.waitLock(300000); 
  SpreadsheetApp.flush(); lock.releaseLock();
}

Original source:
https://stackoverflow.com/a/43444080/11462274

I would like to know exactly where I should add it and how many times it is necessary, if I should add it below each line of script that calls the IMPORTXML function in the spreadsheet or if I should use it only once at the beginning or at the end of the script.

If possible, please show me an example of what my script would look like after the modifications.

My Script example (it is much bigger, but for the question not to be huge, I will publish only a part):

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  ss.getRange('Monster!A1').setFormula('=IMPORTXML(Gerais!R1,"//*[@class=\'header-label\']"');
  ss.getRange('Monster!A1:A').copyTo(ss.getRange('Page 2!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Monster!A1').clear({contentsOnly: true, skipFilteredRows: true});
  
  ss.getRange('Monster!B1').setFormula('=IMPORTXML(Gerais!R2,"//*[@class=\'header-label\']"');
  ss.getRange('Monster!B1:B').copyTo(ss.getRange('Page 2!B1:B'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Monster!B1').clear({contentsOnly: true, skipFilteredRows: true});
  
  ss.getRange('Monster!C1').setFormula('=IMPORTXML(Gerais!R3,"//*[@class=\'header-label\']"');
  ss.getRange('Monster!C1:C').copyTo(ss.getRange('Page 2!C1:C'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Monster!C1').clear({contentsOnly: true, skipFilteredRows: true});
}

Rubén · Accepted Answer · 2020-09-24T15:14:22.537

There is a misundertanging of the referred answer, the suggested code is not intended to be used several times in a function, it's purpose is to avoid that certain part of the code be excecuted when there is another execution of the same script still running.

The following code reduce the number of calls to Google Apps Script methods include the use of Spreadsheet.flush() and a do... while statement to wait for the spreadsheet recalculation fishish.

NOTE: I didnt' test this as the URL was not provided.

function myFunction() {
  var ss = SpreadsheetApp.getActive();

  // Add formulas

  ss.getRange('Monster!A1').setFormula('=IMPORTXML(Gerais!R1,"//*[@class=\'header-label\']"');
  ss.getRange('Monster!B1').setFormula('=IMPORTXML(Gerais!R2,"//*[@class=\'header-label\']"');
  ss.getRange('Monster!C1').setFormula('=IMPORTXML(Gerais!R3,"//*[@class=\'header-label\']"');

  // Force apply the above changes 

  SpreadsheetApp.flush();

  // Give time for spreadsheet recalculation
  var start = Date.now();
  var limit = 10000;
  do{
    Utilities.sleep(1000);
    var values = ss.getRange('Monster!A2:C2').getValues()[0];
  }
  while( values.every(value => value === '') || Date.now() - start < limit);

  // Copy / paste the formula results
  ss.getRange('Monster!A1:C').copyTo(ss.getRange('Page 2!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  // Clear A1, B1, C1
  ss.getRange('Monster!A1:C1').clear({contentsOnly: true, skipFilteredRows: true});
}

score 1 · Answer 2 · answered Sep 24 '20 at 14:19

Lockservice locks the script execution - it does not lock the access to a spreadsheet - be it by a user or a formula

If what you want is to use make your code synchronous, so that a request (like setting a formula or copying values) won't execute before the previous one is finished - you can use SpreadsheetApp.flush() and Utilities.sleep().

The former waits until the last call to SpreadsheetApp finished
The latter gives the script some extra time - can be useful to wait until a Sheets formula has updated correctly.
You need to use those requests multiple times in your code - wherever applicable.

Sample:

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  ss.getRange('Monster!A1').setFormula('=IMPORTXML(Gerais!R1,"//*[@class=\'header-label\']"');
  //modify the watiting time depending on how long you expect the formula to take to importa data
  Utilities.sleep(1000);
  ss.getRange('Monster!A1:A').copyTo(ss.getRange('Page 2!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush();
  ss.getRange('Monster!A1').clear({contentsOnly: true, skipFilteredRows: true});
  SpreadsheetApp.flush();
  ss.getRange('Monster!B1').setFormula('=IMPORTXML(Gerais!R2,"//*[@class=\'header-label\']"');
  Utilities.sleep(1000);
  ss.getRange('Monster!B1:B').copyTo(ss.getRange('Page 2!B1:B'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush();
  ss.getRange('Monster!B1').clear({contentsOnly: true, skipFilteredRows: true});
  SpreadsheetApp.flush();
  ss.getRange('Monster!C1').setFormula('=IMPORTXML(Gerais!R3,"//*[@class=\'header-label\']"');
  Utilities.sleep(1000);
  ss.getRange('Monster!C1:C').copyTo(ss.getRange('Page 2!C1:C'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush();
  ss.getRange('Monster!C1').clear({contentsOnly: true, skipFilteredRows: true});
// not necessary to flush at the end of the script
}

Thank you very much for the answer, for what you told me so there really is no option that can be faithful in just continuing the script if IMPORTXML has returned the result, the most we can do is try to imagine the maximum time it takes to import and create a pause, am I correct in thinking like that? — Digital Farmer, Sep 24 '20 at 14:24

Adding lockservice and SpreadsheetApp.flush to a script (Google App Script / Google Sheets)

2 Answers2

Lockservice locks the script execution - it does not lock the access to a spreadsheet - be it by a user or a formula