Google Apps Script Iterating over Spreadsheets very slow

Question

First time posting, long time reading :)

I've just written my first google apps script to collate information from 14 spreadsheets, each sheet with 2-30 worksheets into a single report spreadsheet.

The script runs beautifully, it checks a single column for data and if found grabs the spreadsheet name, worksheet name, first column data for the row and the data from the check column and adds it to an array as a sub-array of data.

It then calculates the area of the array of sub-arrays and writes the data into the report file (which is where the script is run from).

My only issue is that it takes the script around 2 minutes to run.

I wonder if I've been inefficient in my method and was hoping someone could review the script and let me know if I've made some mistakes?

here goes:

/**
* Retrieves all the rows in the active spreadsheet that contain data and logs the
* values for each row.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/

function getFaults() {
/** opens each spreadsheet for Liddon and examines the "Report/Replace" column "F"
if there is data there then  get the 
[Sheetname], [fault area (column "A" row relative to the "F" field found)] and the ["F" field  data]
 **/
var reportsheet = SpreadsheetApp.getActiveSheet();
var reportdata = []
var reportrow = 0

var liddonblocks = [ 
"1APshQevK7iZxhP7--zmtuM3K6dPTgTZjmNarQ6CEsV4", "1riCQMOa38jo4nCD4qjW1BFZKk5xpXFZiCXHzXpiYKIU",   "1NTKXmted1-U12MiqvCGRuYBdhPy1_eLiPn7v8_oVKFE", "1RKOJUNNi5TAg5dETZDtLjZOkUSheuguzmtdPelMclMI",
"1b5-fzCp0wzW8llpUc_6xi1iTFzsapZh9ASSFgDYt4WU", "1qJtY37K0zwoJcz7LdyHhWgkypRMP9LabBchNLM4Fgow",   "1yvf4W8-SkfTH-n-PdDNQeyEDEz-shzTe-Id57S_YB2M", "1ETZc1xeNGXU6ipb1XQiD8SiIyRXzZtiJfS4AClKroJk",
"1tJ5u3Hv0uz-n2cdw-QYixKnuMG9skvrUbz1UROhIm34", "1DjhmIdD0GrPxR-fv7pCPkIwIyfai5BHsK9GhT-Hcs3k", "15w39NZZIacD1OfiTWG1E3HmOhV0B_e2Jsuan_ySwf2Q" , "1cK2HBLEftYOZEkCcxs1TX1PxcJRiKTZpQrcsOfE4B1s",
"16W_bfMKk98wkLpEmm2Q68Ta_SrCA8EBarQyGF2yfm18","1_Z_tgF5UAfq3fxPsDEe40z2GZSehhL-u4hEuVszrbn8" ]

// loop through the spreadsheets
for (block = 0; block < liddonblocks.length; block++) { 
  //open the spreadsheet using the index from the liddonblocks list
  var ss = SpreadsheetApp.openById(liddonblocks[block]);
  //get all of the sheets within the spreadsheet
  var sheets = ss.getSheets();

//loop through each sheet in each spreadsheet using the length of the number of sheets in the     spreadsheet as the index
for (var sheetnum = 0; sheetnum < sheets.length; sheetnum++) {
  //get an array of all data in the sheet
  //assigns array in the form of: [[area, fault], [Bedroom, Broken Bed], [Bathroom, ]] 
  //where each sub-array is a row of data starting at row 1 eg: [[row1-col1, row1-col2...],[row2-col1, row2-col2...]...]
  data = sheets[sheetnum].getDataRange().getValues();
  //get the text name of the sheet
  name = sheets[sheetnum].getSheetName();

  // iterate over the data set and look for values in the  5th column, starting at row 7 to exclude the headers.
  // this is the column named "Report / Replace "
  for (var count = 7; count < data.length; count++) {
    if (data[count][5] != "" && data[count][5] != 0) {
      //if there is data in the 5th column of the row then append the following data to the reportdata array and a sub-array
      // [ sheetname, columnA, columnF ]
      reportdata[reportrow] = [ ss.getName(), name, data[count][0], data[count][5]]
      //increment the reportcount variable so any further hits on data in column 5 are created as sequentail sub-arrays in the reportdata array.
      reportrow++
    }
  }
}
}
//write the contents of reportdata to the console
var range = reportsheet.getRange(2,1,reportrow,reportdata[0].length);
range.setValues(reportdata);
}

/**
* Adds a custom menu to the active spreadsheet, containing a single menu item
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
  name : "Update Report",
  functionName : "getFaults"
 }];
 spreadsheet.addMenu("Keble Scripts", entries);
};

Answer 1

First of all, review the information on this page.

Three things I've experienced that can really slow you down are:

1. Calling external service multiple times
2. Calling SpreadsheetApp.flush() in a loop to force updating the sheet.
3. Calling Range.getValue() a lot.

For the first part, try batching your calls. For instance if you are requesting exchange rates for your local currency and foreign ones, try sending a bunch of queries in one request.

For the second part, just don't call this method unless its really necessary.

for the third part, try modifying your code so you can call this method once and move around with its result instead of calling it in multiple methods for the same result.

Hint: To avoid modifying many methods parameters, just pass a single object at the beginning of your method-tree call and fill it with your parameters. This way you don't have to modify each method you pass by to add\remove a parameter. That's a concept that's applied to all programming languages with functions.

Answer 2

I agree with Serge's comment that this code is already well optimized, opening that many spreadsheets is going to take some time.

I see one opportunity to improve but it will probably have a very minimal impact on speed if any. You could move the ss.getName() call out of the inner loop, instead assign it to a variable right after you open the spreadsheet, then reference that variable in the inner most loop.

Note that in my experience the speed of the Google service calls tends to vary pretty widely, so sometimes this may run faster or slower. You can see how long each call is taking by looking in the Execution Transcript of the script editor, under the View menu.