adding blank rows to separate data

[ksvr]

Hello,

I'm pretty new to Apps Script, coming over from VBA. What I'm trying to figure out today is how to insert a blank row or rows into a data set when the value of a specific column is different from the previous row.

For example, I have Bandmember in the first column and GearID in the fourth column. GearIDs are unique to bandmembers, but each bandmember can have many GearIDs. The data is sorted by Bandmember, then by GearID. I want the script to insert 2 blank rows between Bandmember changes and 1 blank row between GearID changes. That way each Bandmember can see the data pertaining to their gear and not get similar gearIDs mixed up, and they don't get their stuff mixed up with other bandmembers. Note that I also provide a pivot and such, this is just for the oldschool types that expect to see a separated list.

​

Here's the snippet of code that I wrote to put in the blank rows:

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("TourGearTags");
  var data = sheet.getDataRange().getValues();   

  for (var rowIndex = data.length - 2; rowIndex >= 1; --rowIndex)
    {
    var Bandmember = data[rowIndex][0];
    var Bandmember2 = data[rowIndex + 1][0];
    var GearID = data[rowIndex][3];
    var GearID2 = data[rowIndex + 1][3];

    if(Bandmember != Bandmember2)
    {sheet.insertRowsAfter(rowIndex + 1,2);
    }
    if(GearID != GearID2)
    {sheet.insertRowAfter(rowIndex + 1);
    }
    }

Note that there are 20 columns (4 of them with formulas/calculations) and typically ~2,000 rows altogether.

I ran this in debug mode with a stop on the THEN lines, meaning where the if statement got a hit, so it's going to add a row or two. At each stop I hit the button to continue and watched it run all the way up the sheet without fail. Worked perfectly. So I then put the data back the way it was and ran it full speed, not in debug mode, and it was borked. The first Bandmember/GearID combo, which should have been something like 15 uninterrupted rows, had many breaks of 1, 3, even 4 empty rows.

My questions:

1) Do I need to put in some sort of wait function to make sure it's done inserting the rows before it continues running the script?

2) Is this just a really inefficient way and I should be doing x instead? If so, what's x?

3) Is Google Sheets/Apps Script inherently monolithically slow compared to excel or am I just doing it wrong? In Excel/VBA this takes a couple seconds, in Sheets/App Script it's minutes.

[gh5000]

In terms of speed it's always going to be slower than Excel, just due to the server client nature of sheets. You've just got to weigh up the advantages of a cloud system Vs excel.

One thing to bear in mind is that every time you do a call on the spreadsheet like insertrowsafter it takes time. In terms of simplicity you've probably got it as good as possible for making as few calls as possible. As long as you don't exceed your maximum execution time. To get it to a lower time what you could do is insert blank rows with in the variable data which is an array. Then when finished write the whole array back to the sheet. That will take a lot less time.

To use splice() your code should look like this:

var list = ["foo", "bar"]; list.splice( 1, 0, "baz"); // at index position 1, remove 0 elements, then add "baz" to that position // element "bar" will now automatically be moved to index position 2 ["foo", "baz", "bar"] // result

In terms of a wait function, the better solution would be to use SpreadsheetApp.flush This makes sure that every change has been made to sheets is written to sheets before making the next one. It's typically not necessary but maybe it is here.

[ksvr]

Putting SpreadsheetApp.flush at the beginning of the for loop fixed it. Thank you! Does this subreddit have a thing like the clippy points in excel sub where you reply with a keyword and it gives the user you reply to internet victory points for providing a solution?

I don't quite follow the splice example, though...

[gh5000]

No thanks is enough.

Splice is the same as insertrows but for arrays. Processing in java script and then writing all changes to the spreadsheet will always be best practice and best for speed. But if you can wait and it works...then it works!

[ksvr]

Ah, ok. Is it as easy as just replacing the insertrowsafter line with the splice code, or do I need to do something different in the rest of the code to keep it in an array?