Google Sheet Script cycling through a sheet and adding new rows and text

Question

I created my first function in google sheets. Basically I got it to work the way I want it to but it hits a max execution time limit. I posted the code below, I'm sure there are a few ways to optimize it so it can run faster.

The code basically cycles through records in a sheet to add image filenames. There are six image filenames per product and sometimes I need to add new rows to list all the filenames, like in the following screenshot:

file structure

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  var c = 1;

  for (var i=1; i<values.length; i++) {

    c = 1;

    do {
    c = c + 1;
    i = i + 1;
    } while (values[i+1][13] == values[i][13]);

    while (c < 6) {
    sheet.insertRowsAfter(i+1,1);
    c = c + 1;
    i = i + 1;
    sheet.getRange(i+1,1).setValue(values[i-1][0]);
    }

    range = sheet.getDataRange();
    values = range.getValues();


    if(c <= 6) {
      sheet.getRange(i-4,25).setValue(values[i-5][13] + "_main.jpg");
      sheet.getRange(i-3,25).setValue(values[i-5][13] + "_detail.jpg");
      sheet.getRange(i-2,25).setValue(values[i-5][13] + "_detail1.jpg");
      sheet.getRange(i-1,25).setValue(values[i-5][13] + "_detail2.jpg");
      sheet.getRange(i,25).setValue(values[i-5][13] + "_side.jpg");
      sheet.getRange(i+1,25).setValue(values[i-5][13] + "_back.jpg");
    }

    if(c > 6) {
      sheet.getRange(i-c+2,25).setValue(values[i-c+2][13] + "_main.jpg");
      sheet.getRange(i-c+3,25).setValue(values[i-c+2][13] + "_detail.jpg");
      sheet.getRange(i-c+4,25).setValue(values[i-c+2][13] + "_detail1.jpg");
      sheet.getRange(i-c+5,25).setValue(values[i-c+2][13] + "_detail2.jpg");
      sheet.getRange(i-c+6,25).setValue(values[i-c+2][13] + "_side.jpg");
      sheet.getRange(i-c+7,25).setValue(values[i-c+2][13] + "_back.jpg");
    }

}

Joshua Dawson Joshua Dawson · Accepted Answer · 2016-07-28T05:01:29

One Big Batch Update instead of a bunch of small ones

Your problem is that each call to sheet.getRange take quite a lot of time (~100 ms) relative to average run of the mill code. When you do it 10 times per loop with the loop running 100 times, your code will run a few minutes when it could be taking only a few seconds.

The way you speed it up is by taking all those little updates (each sheet.getRange call) and combining them into one big batch update, as recommended in this article by google about best practices with google scripts.

For example, instead of doing this:

sheet.getRange(1, 1).setValue(1);
sheet.getRange(2, 1).setValue(2);
sheet.getRange(1, 2).setValue(3);
sheet.getRange(2, 2).setValue(4);

Do this:

sheet.getRange(1, 1, 2, 2).setValue([[1, 2], [3, 4]]);

How to do it with your code

First load all the data you need in as few I/O calls as possible which you do with sheet.getDataRange().getValues().

Next make all the necessary calculations without reading and writing to the spreadsheet. If you need, you can use a two-dimensional array to act in place of the spreadsheet:

data = [["A", "tire.jpg"], ["B", "fire.jpg"]];
data[i+2][c-1] = values[3][8] + "_main.jpg";

When you're finished you can write the data to the spreadsheet all at once:

sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

Google Sheet Script cycling through a sheet and adding new rows and text

1 Answers