Google Script for a Sheet - Maximum Execution time Exceeded

Question

I'm writing a script that's going to look through a monthly report and create sheets for each store for a company we do work for and copy data for each to the new sheets. Currently the issue I'm running into is that we have two days of data and 171 lines is taking my script 369.261 seconds to run and it is failing to finish.

function menuItem1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("All Stores");
  var data = sheet1.getDataRange().getValues();
  var CurStore;
  var stores = [];
  var target_sheet;
  var last_row;
  var source_range
  var target_range;
  var first_row = sheet1.getRange("A" + 1 +":I" + 1);

  //assign first store number into initial index of array
  CurStore = data[1][6].toString();
  //add 0 to the string so that all store numbers are four digits.
  while (CurStore.length < 4) {CurStore = "0" + CurStore;}
  stores[0] = CurStore;

  // traverse through every row and add all unique store numbers to the array
  for (var row = 2; row <= data.length; row++) {
    CurStore = data[row-1][6].toString(); 

    while (CurStore.length < 4) {
      CurStore = "0" + CurStore;
    }

    if (stores.indexOf(CurStore) == -1) {
      stores.push(CurStore.toString());
    }
  }

  // sort the store numbers into numerical order
  stores.sort();

  // traverse through the stores array, creating a sheet for each store, set the master sheet as the active so we can copy values, insert first row (this is for column labels), traverse though every row and when the unique store is found, 
  // we take the whole row and paste it onto it's newly created sheet
  // at the end push a notification to the user letting them know the report is finished.
  for (var i = stores.length -1; i >= 0; i--) {
    ss.insertSheet(stores[i].toString());
    ss.setActiveSheet(sheet1);
    target_sheet = ss.getSheetByName(stores[i].toString());
    last_row = target_sheet.getLastRow();
    target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));
    first_row.copyTo(target_range);

    for (var row = 2; row <= data.length; row++) { 
      CurStore = data[row-1][6].toString();

      while (CurStore.length < 4) {
        CurStore = "0" + CurStore;
      }

      if (stores[i] == CurStore) {
        source_range = sheet1.getRange("A" + row +":I" + row);
        last_row = target_sheet.getLastRow();
        target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));
        source_range.copyTo(target_range);
      }
    }

    for (var j = 1; j <= 9; j++) {
       target_sheet.autoResizeColumn(j);
    }
  }

  Browser.msgBox("The report has been finished.");
}

Any help would be greatly appreciated as I'm still relatively new at using this, and I'm sure there are plenty of ways to speed this up, if not, I'll end up finding a way to break down the function to divide up the execution. If need be, I can also provide some sample data if need be.

Thanks in advance.

How large is the data range? You might be able to considerably speed up the process if you splice the row out of the data for every store so the next store won't have to search through those anymore. You might also be able to get a faster execution when running it scheduled by itself rather than by your manual trigger. — Robin Gertenbach
Once all of the data has come in at the end of the month, I would think that the Data range is going to at least be 3000 rows and up to 9 rows. The only thing about splicing the rows is I want to have a master sheet with all of the data intact. Would you mind elaborating further on the scheduled trigger as to why that might achieve a faster execution? — AustynM
You wouldn't splice the data from the actual sheet, just from the data variable (which you either get from the master or could write to first). The range you'd have to scan to find an entry would continuously shrink, which, in my experience, offsets the cost of deleting the elements. In my experience having the process run while the sheet is closed makes it execute much faster probably because it isn't queried at the same time by the user and doesn't have to continuously update the view. — Robin Gertenbach

Adelin Adelin · Accepted Answer · 2016-02-05T13:58:09

The problem is calling SpreadsheepApp lib related methods like getRange() in each iteration. As stated here:

Using JavaScript operations within your script is considerably faster than calling other services. Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google's servers or an external server, such as requests to Spreadsheets, Docs, Sites, Translate, UrlFetch, and so on. Your scripts will run faster if you can find ways to minimize the calls the scripts make to those services.

I ran into the same situation and, instead of doing something like for(i=0;i<data.length;i++), I ended up dividing the data.length into 3 separate functions and ran them manually each time one of them ended. Same as you, I had a complex report to automate and this was the only solution.

Google Script for a Sheet - Maximum Execution time Exceeded

1 Answers