CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Digital Farmer
Digital Farmer

Reputation: 2147

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

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

Upvotes: 0

Views: 1344

Answers (2)

Wicket
Wicket

Reputation: 38435

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

Upvotes: 1

ziganotschka
ziganotschka

Reputation: 26836

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
}

Upvotes: 1

Related Questions