Reducing Execution time of UrlFetch in google AppScripts

1
votes

I am having a spreadsheet which contains a list of URLs. I am trying to search if an URL contains a particular class or not and based on response I will set 0 or 1 in the cell next to it. I have made a script and it does the purpose but I'm experiencing execution timeout error so just want to is there any way I can reduce it's execution time.

here is the code

function ulrFetch(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var class = ss.getRange(1, 5).getValue();
  var lr = ss.getLastRow();
  var urlList = ss.getRange(3, 8, lr-1).getValues();
  var length = urlList.length
  for (var i = 0;i<length;i++){
    var url = urlList[i];
    var response = UrlFetchApp.fetch(url.toString());
    var result = response.getContentText();
    var index = result.indexOf(class);
    if (index > -1){
      
      ss.getRange(i+3, 5).setValue('1');
    }
    else {
      ss.getRange(i+3, 5).setValue('0');
    }
  }
}

Thanks in Advance!!

1
javascriptgoogle-apps-scriptgoogle-sheets
Can I ask you about the value of length of var length = urlList.length? I think that in your situation, you can use fetchAll(). fetchAll() can be worked by the asynchronous processing. But because also I think that when length is large, it might not be able to use it. – Tanaike
@Tanaike Thank you for your response and length of urlList is 720 – Amit Singh
Thank you for replying. I posted an answer, because I thought that 720 requests might be able to be used by fetchAll(). Could you please confirm it? – Tanaike

1 Answers

1
votes

How about this modification? I thought that 720 requests might be able to be used by fetchAll(). So I would like to propose to use fetchAll(). The flow of this modified script is as follows.

  1. Create requests using urlList.
  2. Retrieve values from URL using fetchAll().
  3. Create values for putting to Spreadsheet.
  4. Put the created values to "E3:E".

Modified script :

Please modify as follows.

var urlList = ss.getRange(3, 8, lr-1).getValues();

var urlList = ss.getRange(3, 8, lr - 1 - 1).getValues();

By this modification, the values from row 3 to last row can be retrieved.

And

for (var i = 0;i<length;i++){
  var url = urlList[i];
  var response = UrlFetchApp.fetch(url.toString());
  var result = response.getContentText();
  var index = result.indexOf(class);
  if (index > -1){

    ss.getRange(i+3, 5).setValue('1');
  }
  else {
    ss.getRange(i+3, 5).setValue('0');
  }
}

var requests = urlList.map(function(e) {return {url: e[0]}});
var res = UrlFetchApp.fetchAll(requests);
var values = res.map(function(e) {return e.getContentText().indexOf(class) > -1 ? ["1"] : ["0"]});
ss.getRange(3, 5, values.length, 1).setValues(values);

Note :

  • If an error due to the limitation of fetchAll() occurs, please tell me. I would like to modify the script.

Reference :

If I misunderstand your question, I'm sorry.