javascript - Need help to rewrite Apps Script so it doesn't need to loop

Question

I have written a script that loops through each URL in a long list of URLs. Unfortunately, because it goes through each one individually it exceeds the maximum execution time (6 minutes). Is there a faster way to get through my list of almost 28,000 URLs?

function getData() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName('URL2');

 var firstURL = 1
 var lastURL = 27886


 var i
 for (i=firstURL; i<lastURL; i++) {
  var data = sheet.getRange(i,1).getValue();
  var response = UrlFetchApp.fetch(data, {muteHttpExceptions: true}).getContentText();

  //Logger.log(data)
  //Logger.log(response)

  if (response.toLowerCase().indexOf("cast</tt></a> <tt>area")>-1) {
    //Logger.log(1)
    sheet.getRange(i,2).setValue(1);
  }
  else{
    sheet.getRange(i,2).setValue(0);
  }
 }
}

question from:https://stackoverflow.com/questions/65868803/need-help-to-rewrite-apps-script-so-it-doesnt-need-to-loop

Answer 1

Please read the Best Practices to learn how to use batch operations.

This should be way faster:

function getData() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('URL2'); 
 const urls = sheet.getRange('A1:A2000').getValues().flat(); // Run this in batches
 const data = urls.map(url=>{
   let response = UrlFetchApp.fetch(url, {muteHttpExceptions: true}).getContentText();
   return [response.includes("cast</tt></a> <tt>area") ? 1: 0];
 })
 const brow=sheet.getRange('B1:B').getValues().filter(String).length+1;
 sheet.getRange(brow,2,data.length,data[0].length).setValues(data);
}

Important info:

Note that consumer accounts and business accounts have a limit of 20k and 100k of URL fetch calls respectively on a daily basis. Since you can only run scripts for 6 minutes it means that you don't have a business account and therefore you can fetch only 20k urls per day. Assuming you already fetched some URLs today, you might have already consumed some of your daily quota, so be careful with this.

Please Note!

Run your code in batches: from A1:A2000 then from A2001:A4000 etc, so you make sure the script is not terminated in the middle of the process and then you don't get anything back in the sheet. I adjusted the code so you can continue pasting from the last entry in column B.

Also UrlFetchApp.fetch is a slow method, so it will still take some time. However, from GAS or JavaScript point of view, the code is very efficient.