Set SpreadsheetApp.flush(); and Utilities.sleep(5000); lines location in script (Google App Script)

Question

The script below is activated every 5 minutes, sometimes the importxml function takes a long time to return the result, causing the script not to wait for the query to finish and to move on to the next steps, causing an error in copying the data.

I would like to know where I should put the SpreadsheetApp.flush(); so that it only goes to the next step when the importxml loads completely and if only it does not solve, how should I add the Utilities.sleep(5000);?

When I put only the Utilities.sleep(5000); the script does not wait for the indicated milliseconds and passes directly, it only works when I add the SpreadsheetApp.flush();. So I would like to know if I need to use both or if only SpreadsheetApp.flush(); solves my problem and where it should be positioned.

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-23T04:00:44.520

2

You should add

SpreadsheetApp.flush();
Utilities.sleep(milliseconds); // milliseconds should be a number big enough to allow the spreasheet finish to recalculate

on each line above each copyTo

NOTE: Instead of using Utilities.sleep(milliseconds) you could poll the spreadsheet to check if the formula results were added to it. See How to pause App Scripts until spreadsheet finishes calculation

Why?

SpreadsheetApp.flush() forces Google Apps Script to apply the changes made so far. This should be done before coping the range that is intended to hold the result of the formula otherwise it will be empty. Utilities.sleep(milliseconds) is necessary (or somethin equivalent) to wait for the spreasheet recalculation finish before copying the range, otherwise the only value to copy might be Loading...

edited Sep 23 '20 at 04:00

answered Sep 23 '20 at 03:04

Rubén

34,714
9
70
166

Thank you very much for the answer, could you tell me why to use below ```copyto``` instead of being below ```setformula```? because thinking fast if the fear is that ```imortxml``` will not fully load, i should put it below it, but seeing your answer and other comments would be wrong if i did that. – Digital Farmer Sep 23 '20 at 03:49
1

@BrondbyIF Add an explanation – Rubén Sep 23 '20 at 04:00

Set SpreadsheetApp.flush(); and Utilities.sleep(5000); lines location in script (Google App Script)

1 Answers1

Linked