Google Apps Script Iterating over Spreadsheets very slow -

first time posting, long time reading :)

i've written first google apps script collate information 14 spreadsheets, each sheet 2-30 worksheets single report spreadsheet.

the script runs beautifully, checks single column data , if found grabs spreadsheet name, worksheet name, first column data row , data check column , adds array sub-array of data.

it calculates area of array of sub-arrays , writes data report file (which script run from).

my issue takes script around 2 minutes run.

i wonder if i've been inefficient in method , hoping review script , let me know if i've made mistakes?

here goes:

/** * retrieves rows in active spreadsheet contain data , logs * values each row. * more information on using spreadsheet api, see * https://developers.google.com/apps-script/service_spreadsheet */  function getfaults() { /** opens each spreadsheet liddon , examines "report/replace" column "f" if there data there   [sheetname], [fault area (column "a" row relative "f" field found)] , ["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 spreadsheets (block = 0; block < liddonblocks.length; block++) {    //open spreadsheet using index liddonblocks list   var ss = spreadsheetapp.openbyid(liddonblocks[block]);   //get of sheets within spreadsheet   var sheets = ss.getsheets();  //loop through each sheet in each spreadsheet using length of number of sheets in     spreadsheet index (var sheetnum = 0; sheetnum < sheets.length; sheetnum++) {   //get array of data in sheet   //assigns array in form of: [[area, fault], [bedroom, broken bed], [bathroom, ]]    //where each sub-array row of data starting @ row 1 eg: [[row1-col1, row1-col2...],[row2-col1, row2-col2...]...]   data = sheets[sheetnum].getdatarange().getvalues();   //get text name of sheet   name = sheets[sheetnum].getsheetname();    // iterate on data set , values in  5th column, starting @ row 7 exclude headers.   // column named "report / replace "   (var count = 7; count < data.length; count++) {     if (data[count][5] != "" && data[count][5] != 0) {       //if there data in 5th column of row append following data reportdata array , sub-array       // [ sheetname, columna, columnf ]       reportdata[reportrow] = [ ss.getname(), name, data[count][0], data[count][5]]       //increment reportcount variable further hits on data in column 5 created sequentail sub-arrays in reportdata array.       reportrow++     }   } } } //write contents of reportdata console var range = reportsheet.getrange(2,1,reportrow,reportdata[0].length); range.setvalues(reportdata); }  /** * adds custom menu active spreadsheet, containing single menu item * invoking readrows() function specified above. * onopen() function, when defined, automatically invoked whenever * spreadsheet opened. * more information on using 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); }; 

i agree serge's comment code optimized, opening many spreadsheets going take time.

i see 1 opportunity improve have minimal impact on speed if any. move ss.getname() call out of inner loop, instead assign variable right after open spreadsheet, reference variable in inner loop.

note in experience speed of google service calls tends vary pretty widely, may run faster or slower. can see how long each call taking looking in execution transcript of script editor, under view menu.