How to stop or limit Google spreadsheet app script from running parallelly?

user_17933467 0
Viewed 35

I have a spreadsheet table like below.

A B
0 RJ162718 =FetchScript(A0)
1 RJ258445 =FetchScript(A1)
2 RJ228027 =FetchScript(A2)
3 RJ258362 =FetchScript(A3)
... ... =FetchScript(...)

The FetchScript function use UrlFetchApp.fetch(url) to fetch a website where is not allow massive api calls.

How to stop script run parallelly or limit the number of executions at a time?

Thanks.

Edit:

Column A is part of url in UrlFetchApp.fetch(url), for example：

functtion FetchScript(ColunmA) {
url = "https://example.com/" + ColumnA;
response = UrlFetchApp.fetch(url);
//do something
}
custom-function urlfetch google-apps-script google-sheets
1 Answers

Your current situation:

Although I'm not sure about the detail of your function of FetchScript, when I saw your showing sample table, I guessed that your FetchScript might be as follows.

function FetchScript(value) {

  // do something.

  var res = UrlFetchApp.fetch(url);

  // do something.

  return output;
}

Workaround:

In this case, when =FetchScript(A1), =FetchScript(A2),,, are put, each function is run with the asynchronous process. If you want to run the function with the synchronous process, how about the following modification?

function FetchScript(values) {
  var results = values.map(e => {
    // do something.

    var url = "###";
    var res = UrlFetchApp.fetch(url);

    // do something.

    return output; // In this case, please return 1 dimensional array for putting values to the row direction.
  });
  return results;
}

  • In this case, please put a custom function like =FetchScript(A1:A10) to a cell. By this, the cell values are used in this modified function.

  • In this modification, UrlFetchApp.fetch is run with the synchronous process.

  • If you want to run UrlFetchApp.fetch in the specific number, you can also use the following script.

      function FetchScript(values) {
    var limit = 5; // Please set the number of run of UrlFetchApp.fetch. In this case, 5 values are used.

    var results = values.splice(0, limit).map(e => {
      // do something.

      var url = "###";
      var res = UrlFetchApp.fetch(url);

      // do something.

      return output; // In this case, please return a 1-dimensional array for putting values to the row direction.
    });
    return results;
  }

Note:

  • From your question, I couldn't understand your actual script. So I proposed a sample script for explaining the method for achieving your goal using // do something. So, when you test this method, please reflect this in your actual script.

Reference:

user_71086530
Related