How to create a function sleep(miliseconds) in a google sheet to delay or pause a formula in a range

Question

In a google sheet I have:

Range B1:B260

I have the Function:

(ImportXML(A1;"//a/@href")
(ImportXML(A2;"//a/@href")
(ImportXML(A3;"//a/@href")
.
.
.
(ImportXML(A260;"//a/@href")

I Need to delay the time between the calculation because If I perform this simultaneously, I Will Get #REF! because limits in APIs and =ImportXML()

It will be fine if I delay 10 seconds per cell calculation

I tried creating a =LEN() function to only perform the calculation in a cell just if the number of characters is bigger than X... But It delays just a tiny portion of time.

Using sleep(milliseconds) in a google script could I get a formula sleep(B1:B260,5000)? To Delay The ImportXML, Importdata, Import.... Process?

score 3 · Answer 1 · answered Oct 17 '19 at 13:47

You can use the Utilities.sleep(milliseconds) function for sleeping between two separate remote API calls. See an example below:

function runScraper() {
  var resultRange = SpreadsheetApp.getActive().getRange("B1:B260");
  var urlRange = SpreadsheetApp.getActive().getRange("A1:A260");
  for (var i=1; i<=urlRange.getHeight(); i++) {
    var currentValue = resultRange.getCell(i, 1).getValue();
    if (currentValue !== "") continue;

    var url = urlRange.getCell(i, 1).getValue();
    var result = scrape(url);
    resultRange.getCell(i, 1).setValue(result);
    Utilities.sleep(10000);
  }
}

As for implementing the scrape(url) function, the implementation depends on which API are you using and what is the data transport format used (JSON, XML...). For that I recommend you check the following links:

URLFetchApp.fetch(): https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetch(String,Object)
XMLService: https://developers.google.com/apps-script/reference/xml-service
Working with external APIs: https://developers.google.com/apps-script/guides/services/external

Disclaimer

Google Apps Scripts have a maximum runtime of 6 minutes. If you wait 10 seconds for each call and issue a total of 260 calls, the maximum runtime will be exceeded. For that, in the code above I implemented a check that will verify that the row that is working on has not been scraped yet. If it has already been scraped, it will just go straight to the next one. That will allow you to easily re-run the script and continue from the point it last stopped (due to timeout).

How to create a function sleep(miliseconds) in a google sheet to delay or pause a formula in a range

1 Answers1

Disclaimer