Use app script to insert more than one row into a sheet on button click-CodePudding

I have a code written to insert a single row into a google sheet below a keyword (a little clunky but I don't mind, unless anyone has a fix for it off the top of their head). Problem is, I want to add 250 rows beneath that and, being new to app scripts, I'm not sure where to specify the number of rows I want it adding for me.

Here's what the code I've got looks like:

function addRows() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var EditSheet = ss.getSheetByName("Testing"); //source sheet

  var LastRowEdit = ss.getLastRow();

  for(var i = 1; i <= LastRowEdit; i  )
  {

    if(EditSheet.getRange(i,1).getValue() == 'add') //keyword
    {
 
      EditSheet.insertRowAfter(i);
    
    }

  }

}

I would love to hear your suggestions for how I can adjust this to match my goals.

Thank you, guys!

CodePudding user response：

In your situation, how about using insertRowsAfter(afterPosition, howMany) instead of insertRowAfter(afterPosition)? So, how about the following modifcation?

From:

EditSheet.insertRowAfter(i);

To:

EditSheet.insertRowsAfter(i, 250);

By the way, in your script, getValue() is used in a loop. In this case, the process cost becomes high. In order to reduce the cost, how about the following modification?

Modified script:

function addRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var EditSheet = ss.getSheetByName("Testing");
  var LastRowEdit = ss.getLastRow();
  var row = EditSheet.getRange("A1:A"   LastRowEdit).getValues().map(([a]) => a).indexOf("add");
  if (row != -1) {
    EditSheet.insertRowsAfter(row   1, 250);
  }
}

Reference:

insertRowsAfter(afterPosition, howMany)