Google app script exceeded execution time limit

2
votes

I have a Google sheet with almost 160 sub sheets. All the questions are in "All" sheet and B column is the actual spreadsheet name where they should be. The following code is reading data from "All" spreadsheet and sending them perfectly to the last row of desired spreadsheet perfectly but it is taking very long time! Probably because it has a lot of subsheets and is using getSheetByName again and again. Now I've stored all the sub sheets' name and ID in 'sheets' and 'sheetID' arrays at once. I'm thinking to compare between rangeValues[j][1] and sheetNames[k][0]. Below is the code and screenshot of the spreadsheet.

Is this a suitable way? Please help me to run the script faster!

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("All");
var rangeData = sheet.getDataRange();
var lastRow = rangeData.getLastRow();
var searchRange = sheet.getRange(1,1, lastRow, 8);
var curr_sheet;

function send() {
  var rangeValues = searchRange.getValues();
  var sheetNames = new Array();
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) sheetNames.push( [ sheets[i].getName() ] );
  //Logger.log (sheetNames.length);
  
  var sheetID = new Array();
  var sheetIDs = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheetIDs.length ; i++) sheetID.push( [ sheetIDs[i].getSheetId() ] );
  //Logger.log (sheetID.length);

  for ( j = 0 ; j < lastRow; j++)
  {
      for ( k = 0 ; k < sheetNames.length ; k++) //
        {
          if (rangeValues[j][1] === sheetNames[k][0])
            {
              
              var targetSheet = ss.getSheetByName(sheetNames[k][0]); // This line is working but taking very long to complete
              var targetSheet = ss.getSheetByID(sheetIDs[k][0]); // This line is not code just to show what I'm thinking to do.
              
              targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, 8).setValues([rangeValues[j]]);
            }
         }
   }
}

enter image description here

2
javascriptfor-loopgoogle-apps-scriptoptimizationgoogle-sheets

2 Answers

1
votes

I believe your goal as follows.

  • You want to reduce the process cost of your script.

Modification points:

  • There is no method of getSheetByID in Class Spreadsheet. By this, I think that in your case, the following script can be removed.

      var sheetID = new Array();
  var sheetIDs = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheetIDs.length ; i++) sheetID.push( [ sheetIDs[i].getSheetId() ] );

  • In your script, getSheetByName and getRange(###).setValues(###) are used in the loop. And also, even when the sheet is the same sheet, each row is put using setValues in the loop every row. In this case, the process cost will be high. I think that these situation might be the reason of your issue.

  • Fortunately, in your situation, all sheets are in the same Spreadsheet. So for reducing the process cost of your script, here, I would like to propose to put the values to each sheet using Sheets API. The flow of modified script is as follows.

    1. Retrieve Spreadsheet and values.
      • This script is from your script.
    2. Retrieve all sheets in the Spreadsheet.
    3. Check whether the sheet names from the column "B" are existing.
    4. Create the object for putting to each sheet using Sheets API.
    5. Request the method of spreadsheets.values.batchUpdate of Sheets API using the created object.

When above points are reflected to your script, it becomes as follows.

Modified script:

Please copy and paste the following script. And, please enable Sheets API at Advanced Google services. And then, please run the script.

function send() {
  // 1. Retrieve Spreadsheet and values. This script is from your script.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("All");
  var rangeData = sheet.getDataRange();
  var lastRow = rangeData.getLastRow();
  var searchRange = sheet.getRange(1,1, lastRow, 8);
  var curr_sheet;
  var rangeValues = searchRange.getValues();
  
  // 2. Retrieve all sheets in the Spreadsheet.
  var sheets = ss.getSheets().reduce((o, e) => Object.assign(o, {[e.getSheetName()]: e}), {});
  
  // 3. Check whether the sheet names from the column "B" are existing.
  // 4. Create the object for putting to each sheet using Sheets API.
  var data = rangeValues.reduce((o, e) => {
    if (sheets[e[1]]) {
      if (o[e[1]]) {
        o[e[1]].values.push(e);
      } else {
        o[e[1]] = {range: `'${e[1]}'!A${sheets[e[1]].getLastRow() + 1}`, values: [e]};
      }
    }
    return o;
  }, {});
  
  // 5. Request the method of spreadsheets.values.batchUpdate of Sheets API using the created object.
  Sheets.Spreadsheets.Values.batchUpdate({data: Object.values(data), valueInputOption: "USER_ENTERED"}, ss.getId());
}

Note:

  • In this case, when you run the script, one API call is used. Because the method of spreadsheets.values.batchUpdate is used.
  • In my environment, when I tested above script and your script using a sample Spreadsheet, I could confirm the reduction of the process cost of about 70 % from your script.

References:

0
votes

Try this:

Should be a lot faster

function send() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("All");
  var rg=sh.getRange(1,1,sh.getLastRow(),8);
  var v=rg.getValues();
  var names=[];
  var sheets=ss.getSheets();
  sheets.forEach(function(sh,i){names.push(sh.getName());});
  v.forEach(function(r){
    let idx=names.indexOf(r[1]);//column2
    if(idx!=-1) {
      sheets[idx].getRange(sheets[idx].getLastRow()+1,1,1,8).setValues([r]);
    }   
  });
}