Inserting Rows Automatically... ?

[Aitch]

I'm working with a long list which is a bunch of names in one column - they are in teams of a maximum of 5, but usually less so I need to create blanks rows to keep things in order:

Column A/B is what I need to achieve - from the basic list I get in column E...

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TeamA[/TD]
[TD]1[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Jill[/TD]
[TD][/TD]
[TD]Jill[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]TeamB[/TD]
[TD]1[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD]Harry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Harry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Phil[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rob[/TD]
[/TR]
[TR]
[TD]TeamC[/TD]
[TD]1[/TD]
[TD]Bill[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Phil[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]Rob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Is there any way to Insert (Blank) Rows automatically to the list I get in column E ?

 

[Aitch]

Seems like you're doing it correctly. Can you post more sample data so I can try to see what is happening?

I'll try - hopefully screenshots will make it clearer... also, I said 25 rows in total before - sorry, it should be 26!



This is the list I receive - there are about 100 "groups" of between 1 to 25 all in a long list... column C is how they are numbered etc

And this is the format it needs to be in - all the groups separated consistently with 25 rows, plus 1 extra row marked in orange etc... so there is always 25 rows between each orange row

When I run your code - it seems to work on the first group fine, but then carries on inserting rows afterwards, so pushing the rest of the data continuously down...


Thank you again!

 

[lrobbo314]

Of course. Easy fix. Just move from bottom to top instead of top to bottom. Here's the updated code. Worked on bigger data set over here.

function InsertRowsFX() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  for(var i=lastRow; i >=3; i--) {
    if(sheet.getRange(i, 3).getValue()==1) {
      sheet.insertRowsBefore(i, 26 - sheet.getRange(i, 3).getValue());
    }
  }
}

 

[Aitch]

Awesome! Thank you so much!

But there's another problem now lol - this error message keeps coming up ... "Exceeded maximum execution time"

Is there any way to increase the execution time or speed up the script?

Or can I re-run the script somehow so it carries on from where it left off?

 

[Aitch]

Of course. Easy fix. Just move from bottom to top instead of top to bottom. Here's the updated code. Worked on bigger data set over here.

function InsertRowsFX() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  for(var i=lastRow; i >=3; i--) {
    if(sheet.getRange(i, 3).getValue()==1) {
      sheet.insertRowsBefore(i, 26 - sheet.getRange(i, 3).getValue());
    }
  }
}

Is there no fix or work-around for this?



Awesome! Thank you so much!

But there's another problem now lol - this error message keeps coming up ... "Exceeded maximum execution time"

Is there any way to increase the execution time or speed up the script?

Or can I re-run the script somehow so it carries on from where it left off?