Merging many spreadsheets into report file exceeds maximum execution time

Question

I am using the following script to add rows of files from a student loop in the Google spreadsheet if credits are less than x. The script was working good but as the data in the spreadsheet is being added daily, now the script is throwing "Exceeded maximum execution time" error (we have more than 2000 files). As I am new to scripting I don't know how to optimize the code.

Could someone help me to optimize the code or any solution so that the execution time take less than 5 min. Every time you compare to an email, it has to be compared to many emails. Please Help!

function updated() {  
  //Final file data (Combined)
  var filecombined = SpreadsheetApp.openById("XXXXXXXXXX");
  var sheet2 = filecombined.getSheets();

  //Folder with all the files 
  var parentFolder = DriveApp.getFolderById("YYYYYYYYYYYY");
  var files = parentFolder.getFiles();

  //Current Date
  var fecha = new Date();

  //Path for each file in the folder
  while (files.hasNext()) {
    var idarchivo = files.next().getId();
    var sps = SpreadsheetApp.openById(idarchivo);

    var sheet = sps.getSheetByName('STUDENT PROFILE');
    var data = sheet.getDataRange().getValues();
    var credits = data[5][1];

    //Flat; bandera:1 (new row), bandera:2 (update row)
    var bandera = 1;

    //Take data from final file (Combined) 
    var data2 = sheet2[0].getDataRange().getValues();

    //If credits are less than X: write
    if (credits < 120) {
      var email = data[2][1];
      var lastrow = filecombined.getLastRow();
      var u = 0;
      //comparison loop by email, if found it, update and exit the loop
      while (u < lastrow) {
        u = u + 1;
        if (email == data2[u - 1][1]) {
          sheet2[0].getRange(u, 3).setValue(credits);
          sheet2[0].getRange(u, 4).setValue(fecha);
          u = lastrow;
          bandera = 2;
        }
      }
      //if that email does not exist, write a new row
      if (bandera == 1) {
        var nombre = data[0][1];
        sheet2[0].getRange(lastrow + 1, 1).setValue(nombre);
        sheet2[0].getRange(lastrow + 1, 2).setValue(email);
        sheet2[0].getRange(lastrow + 1, 3).setValue(credits);
        sheet2[0].getRange(lastrow + 1, 4).setValue(fecha);
      }
    }
  }
  SpreadsheetApp.flush();
}

Any chance it runs faster if you hide the sheet with `SpreadsheetApp.getActive().getSheetByName("STUDENT PROFILE").hideSheet();`? — Matt Cremeens, Oct 25 '18 at 17:29
Are you perhaps showing all the 2000 files? Perhaps you could hide them, too. That way the screen doesn't have to continue updating and the end result will be the same. Not certain about this, though. — Matt Cremeens, Oct 25 '18 at 17:31
Possible duplicate of [Google app script timeout ~ 5 minutes?](https://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes) — TheMaster, Oct 25 '18 at 19:06
Instead of calling `setValue` in `while (u < lastrow)` loop, store those data into a 2 dimension array and call `setValues` outside the loop. — Thum Choon Tat, Oct 26 '18 at 07:46
You need to read the emails once, and create a data structure that supports faster lookup than array traversal. E.g. review my solutions [here](https://stackoverflow.com/a/50286994/9337071), [here](https://stackoverflow.com/a/49519839/9337071), and [here](https://stackoverflow.com/a/51875692/9337071). Also read this question: https://stackoverflow.com/questions/18706085/is-checking-an-object-for-a-key-more-efficient-than-searching-an-array-for-a-str — tehhowch, Nov 04 '18 at 15:54

score 2 · Answer 1 · answered Nov 04 '18 at 02:38

The questioner's code is taking taking more than 4-6 minutes to run and is getting an error Exceeded maximum execution time.

The following answer is based solely on the code provided by the questioner. We don't have any information about the 'filecombined' spreadsheet, its size and triggers. We are also in the dark about the various student spreadsheets, their size, etc, except that we know that there are 2,000 of these files. We don't know how often this routine is run, nor how many students have credits <120.

getvalues and setvalues statements are very costly; typically 0.2 seconds each. The questioners code includes a variety of such statements - some are unavoidable but others are not.

In looking at optimising this code, I made two major changes.
1 - I moved line 27 var data2 = sheet2[0].getDataRange().getValues();
This line need only be executed once and I relocated it at the top of the code just after the various "filecombined" commands. As it stood, this line was being executed once for every student spreadsheet; this along may have contributed to several minutes of execution time.

2) I converted certain setvalue commands to an array, and then updated the "filecombined" spreadsheet from the array once only, at the end of the processing. Depending on the number of students with low credits and who are not already on the "filecombined" sheet, this may represent a substantial saving. The code affected was lines 47 to 50.

line47:         sheet2[0].getRange(lastrow+1, 1).setValue(nombre);
line48:         sheet2[0].getRange(lastrow+1, 2).setValue(email);
line49:         sheet2[0].getRange(lastrow+1, 3).setValue(credits);
line50:         sheet2[0].getRange(lastrow+1, 4).setValue(fecha);

There are setvalue commands also executed at lines 38 and 39 (if the student is already on the "filecombined" spreadsheet), but I chose to leave these as-is. As noted above, we don't know how many such students there might be, and the cost of these setvalue commands may be minor or not. Until this is clear, and in the light of other time savings, I chose to leave them as-is.

function updated() {  

  //Final file data (Combined)
  var filecombined = SpreadsheetApp.openById("XXXXXXXXXX");
  var sheet2 = filecombined.getSheets();

    //Take data from final file (Combined) 
    var data2 = sheet2[0].getDataRange().getValues();

    // create some arrays
    var Newdataarray = [];
    var Masterarray = [];

  //Folder with all the files 
  var parentFolder = DriveApp.getFolderById("YYYYYYYYYYYY");
  var files = parentFolder.getFiles();

  //Current Date
  var fecha = new Date();

  //Path for each file in the folder
  while (files.hasNext()) {
  var idarchivo = files.next().getId();
  var sps = SpreadsheetApp.openById(idarchivo);

  var sheet = sps.getSheetByName('STUDENT PROFILE');
  var data = sheet.getDataRange().getValues();
  var credits = data[5][1];

  //Flat; bandera:1 (new row), bandera:2 (update row)
  var bandera = 1;

    //If credits are less than X: write
    if (credits < 120){
        var email = data[2][1];
        var lastrow = filecombined.getLastRow();
        var u = 0;
        //comparison loop by email, if found it, update and exit the loop
        while (u < lastrow) {
         u = u + 1;
         if (email == data2[u-1][1]){
           sheet2[0].getRange(u, 3).setValue(credits);
           sheet2[0].getRange(u, 4).setValue(fecha);
           u = lastrow;
           bandera = 2;
         }
        }
        //if that email does not exist, write a new row
        if(bandera == 1){
         var nombre = data[0][1];
          Newdataarray = [];
          Newdataarray.push(nombre);
          Newdataarray.push(email);
          Newdataarray.push(credits);
          Newdataarray.push(fecha);
          Masterarray.push(Newdataarray);
        }
    }
  }
  // update the target sheet with the contents of the array
  // these are all adding new rows
  lastrow = filecombined.getLastRow();
  sheet2[0].getRange(lastrow+1, 1, Masterarray.length, 4);
  sheet2[0].setValues(Masterarray); 

  SpreadsheetApp.flush();
    }

score 0 · Answer 2 · answered Nov 05 '18 at 18:08

As I mentioned in my comment, the biggest issue you have is that you repeatedly search an array for a value, when you could use a much faster lookup function.

// Create an object that maps an email address to the (last) array
// index of that email in the `data2` array.
const knownEmails = data2.reduce(function (acc, row, index) {
  var email = row[1]; // email is the 2nd element of the inner array (Column B on a spreadsheet)
  acc[email] = index;
  return acc;
}, {});

Then you can determine if an email existed in data2 by trying to obtain the value for it:

// Get this email's index in `data2`:
var index = knownEmails[email];
if (index === undefined) {
  // This is a new email we didn't know about before
  ...
} else {
  // This is an email we knew about already.
  var u = ++index; // Convert the array index into a worksheet row (assumes `data2` is from a range that started at Row 1)
  ...
}

To understand how we are constructing knownEmails from data2, you may find the documentation on Array#reduce helpful.

Merging many spreadsheets into report file exceeds maximum execution time

2 Answers2