Optimizing Script run time gathering large volume of spreadsheet tab information

Question

I'm hoping that someone might have an alternative suggestion for a strategy for the Google Apps Script I wrote below to help it finish running before it times out. I have a lengthy list of Spreadsheets (about 200) that each have anywhere from 20-100 individual sheets/tabs in them. I'm trying to loop through the entire list of spreadsheets to gather information about each individual sheet (about 8,000 in total) but unfortunately it's running to slow to complete in one run.

Thanks in advance for any suggestions

function getListofTabs(urlName) {
 var ssRecipeTable = SpreadsheetApp.openByUrl(urlName); 
 
 
 var tabs = ssRecipeTable.getSheets(); 
   for(var i=0;i<tabs.length;i++){
     sheetName = tabs[i].getName();
     sheetID = tabs[i].getSheetId(); 
     sheetURL = tabs[i].getParent().getId(); 
     //Logger.log(sheetName,sheetID,sheetURL); 
     dataList.appendRow(['=HYPERLINK("https://docs.google.com/spreadsheets/d/'+tabs[i].getParent().getId()+'/edit#gid='+tabs[i].getSheetId()+'","'+tabs[i].getName()+'")',tabs[i].getName(),tabs[i].getParent().getId(),tabs[i].getSheetId()]); 
 }
}

//Function to feed getListofTabs function with list of all recipe workbooks
function getArray(){
  
  //Temp location for active tests
  var recipeDataSS = SpreadsheetApp.getActiveSpreadsheet(); 
  
  //List of all Recipe workbooks from master Tested Recipe folder
  var listofWorkbooks = recipeDataSS.getSheetByName('ListofWorkbooks'); 
  
  //Location for data input sheet for Plx Upload
  var dataList = recipeDataSS.getSheetByName('Spreadsheets2');
  
  //gets list of workbooks urls, loops through until the end
  var dataRecipe = listofWorkbooks.getDataRange().getValues(); 
  for(var x=0; x<dataRecipe.length; x++)
  {
    var urlName = dataRecipe[x][0]; 
    getListofTabs(urlName); 
  }}```

Answer 1

You should get rid of appendRow() in the for loop.

In order to do that you need to create an empty array before the for loop and then push() the new values into this array. Then after the for loop, use setValues() to add the values to the sheet. In this way, you call setValues() once instead of calling appendRow() tabs.length number of times.

For example the function getListofTabs(urlName) could be replaced by that:

function getListofTabs(urlName) {

 var ssRecipeTable = SpreadsheetApp.openByUrl(urlName); 
 
 var dat = []

 var tabs = ssRecipeTable.getSheets(); 
   for(var i=0;i<tabs.length;i++){
     sheetName = tabs[i].getName();
     sheetID = tabs[i].getSheetId(); 
     sheetURL = tabs[i].getParent().getId(); 
     //Logger.log(sheetName,sheetID,sheetURL); 
     dat.push(['=HYPERLINK("https://docs.google.com/spreadsheets/d/'+tabs[i].getParent().getId()+'/edit#gid='+tabs[i].getSheetId()+'","'+tabs[i].getName()+'")',tabs[i].getName(),tabs[i].getParent().getId(),tabs[i].getSheetId()]); 
 }

dataList.getRange(dataList.getLastRow()+1,1,dat.length,dat[0].length).setValues(dat);

}

Please read carefully the best practices when writing GAS.