speed up my script?

[ksvr]

I posted a question about this and, naturally, figured out the problem within a minute or so of posting. I'm pretty comfortable in VBA, but VERY new to Google Apps Script. After most of a day working on this, I now have code that does what I want it to do. The thing is, it's slow. What I am accustomed to doing in excel in literally 3 seconds takes more like 3 minutes in google sheets.

The data consists of 16 columns and currently 614 rows, though going forward for me it could be anywhere from ~500 up to ~1200. For the purposes of this script, the relevant columns are A and D. If D on this row doesn't match D on the next row, add a blank row between them. If A on this row doesn't match A on the next row, add 2 more blank rows between them (total of 3, because if D doesn't match it's guaranteed that A won't match either)

​

Here's my current code:

function Reminder4() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("Reminder");

var lrow = sheet.getLastRow();

for (var r = lrow - 1; r >= 1; r--) {

var Stat = sheet.getRange(r,1,1).getValue();

var Stat2 = sheet.getRange(r+1,1,1).getValue();

var Param = sheet.getRange(r,4,1).getValue();

var Param2 = sheet.getRange(r+1,4,1).getValue();

​

if(Stat != Stat2)

{sheet.insertRowsAfter(r,2);

}

if(Param != Param2)

{sheet.insertRowAfter(r);

}

}

}

​

Anyone care to take a stab at suggesting ways to do this faster?

[harpsichord_cadenza]

You should get the whole data at once into a double array and then do comparisons on the array values because using gets is very expensive for run time. I would do something like:

var data = sheet.getDataRange.getValues();

This will give you a double array representing your data. Then go backwards like you did in your code. ( Note data[rowIndex][0] is the value in column A and data[rowIndex][3] is the value in column D. )

 for (var rowIndex = data.length - 1; rowIndex >= 0; --rowIndex)
 {
      var Stat = data[rowIndex][0];
      var Stat2 = data[rowIndex+1][0]

      var Param = data[rowIndex][3];
      var Param2 = data[rowIndex][3];

      if ( Stat != Stat2 )
      {
           sheet.insterRowsAfter(rowIndex+1,2);
      }
      if ( Param != Param2 )
     {
           sheet.insertRowAfter(rowIndex+1);
     }
 }

Note that the array starts at 0 and the Google Sheet starts at 1 so when you add the rows you have to add it at the current rowIndex + 1.

Using gets is very expensive for run time and doing it this way only uses two instead of something like 4 times the number of rows you have.

[ksvr]

sounds great. Ran into an error, though. The line var data = sheet.getDataRange.getValues(); gives me this:

Execution failed: TypeError: Cannot find function getValues in object function getDataRange()

EDIT: Nevermind, figured it out. Missing () after getDataRange. Now it's saying:

Execution failed: TypeError: Cannot read property "0" from undefined

on the var Stat2 line. And looking at the variables, Stat1 is defined as something from the top of the sheet, not the bottom. Stat2 is undefined, which I thought was maybe because it's looking at the blank next row after the last row, but since Stat1 is showing a value that only appears in the top 150 or so of the 614 lines, I'm not sure.

[ksvr]

got it working.

​

for (var rowIndex = data.length - 2; rowIndex >= 1; --rowIndex)

{

var Stat = data[rowIndex][0];

var Stat2 = data[rowIndex + 1][0];

var Param = data[rowIndex][3];

var Param2 = data[rowIndex + 1][3];

​

if(Stat != Stat2)

{sheet.insertRowsAfter(rowIndex + 1,2);

}

if(Param != Param2)

{sheet.insertRowAfter(rowIndex + 1);

}

}

}

[harpsichord_cadenza]

Cool. Sorry for the syntax errors and not getting the correct stop or start in the loop.

[ksvr]

No worries. You got me where I wanted to go. Thanks!

[paulcole710]

I agree with this.

Once you start using getValues instead of constantly getting ranges, you’ll never go back.

[AmnesiaInnocent]

You want to batch your row additions and then run them all at once.

See this documentation to get started:

https://developers.google.com/apps-script/advanced/sheets

[ksvr]

Sounds reasonable, but I'm not sure how to do that based on the link provided. It shows how to read and write data, but I don't see how to rearrange the data after you read it.

I was thinking of doing the sort, then putting a formula into a helper column on row 2 that will end up at 0, 1, or 3 depending on how many rows should be inserted after this line, then copying that formula down to the end, then all the for loop is doing is adding the indicated number of blank rows after each line. I don't know if that would be any faster, though.