Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
325 views
in Technique[技术] by (71.8m points)

javascript - Reducing Execution time of UrlFetch in google AppScripts

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');
    }
  }
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

From :
var urlList = ss.getRange(3, 8, lr-1).getValues();
To :
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

From :
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');
  }
}
To :
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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...