Google Apps Script Table iteration is very slow

Question

First post, a long time :)

I just wrote my first google script applications to compare information from 14 tables, each sheet with 2-30 worksheets into one report table.

The script works beautifully, it checks one column for data and, if it finds it, captures the name of the spreadsheet, the name of the worksheet, the data of the first column for the row and the data from the validation column and adds it to the array as a subdirectory of the data matrix.

Then it calculates the area of ​​the array of auxiliary arrays and writes the data to the report file (from which the script is executed).

My only problem is that it takes about 2 minutes to run the script.

I wonder if I was ineffective in my method and hoped that someone would be able to view the script and let me know if I 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

I agree with Serge’s remark that this code is already well optimized by discovering that many spreadsheets will take some time.

I see one opportunity to improve, but it will probably have minimal impact on speed, if any. You can move the ss.getName () call from the inner loop, instead assign it to a variable immediately after opening the spreadsheet, and then refer to that variable in the inner majority of loops.

Please note that in my experience, Google’s call speed tends to vary greatly, so sometimes it can work faster or slower. You can see how long each call takes by looking at the Execution Transcript script editor in the View menu.

Answer 2

First of all, view the information on this page .

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

• Calling an external service several times
• Call SpreadsheetApp.flush () in a loop to force the sheet to be updated.
• Calling Range.getValue () a lot.

For the first part, try making batch calls. For example, if you are requesting exchange rates for your local currency and foreign, try sending a bunch of requests in one request.

For the second part, just do not call this method if it is really necessary.

for the third part, try changing your code to call this method once and move it with the result, rather than calling it in several ways to get the same result.

Tip. To avoid changing many method parameters, just pass one object at the beginning of your method call to the tree and fill it with your parameters. Thus, you do not need to change every method you pass to add / remove a parameter. This is a concept that applies to all programming languages ​​with features.