Insert Row After Specific Rows

March 18, 2023

I Want To Use Google Apps Script To Insert One Row After Specific Rows, If The Row In Column C Is "Y" The Script Will Add Row After It

I Tried To Write Code Like This But It Didn’t Work

const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
 const data = sheet.getDataRange().getValues();

 function insertRowsAfter() {
   for(var i = 2; i < data.length; i++) {
        if(data[i][1] === "Y") {
      sheet.insertRowsAfter(i + 1);
    }
  }
}

This is a sample sheet

>Solution :

Your primary issue is in [1] as that would be requesting the value for the B Column.

if(data[i][1] === "Y") { ...

Try adjusting your code to the following to target Column C:

if(data[i][2] === "Y") { ...

Additionally, your for loop is only targeting data from the 3rd row onward. I recommend adjusting your code to the following to start in Row 2:

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

It’s important to know that Arrays are 0-indexed, meaning their first element is at index 0.

Another issue is you are not defining how many rows to insert after the index.

See: https://developers.google.com/apps-script/reference/spreadsheet/sheet#insertrowsafterafterposition,-howmany

sheet.insertRowsAfter(i + 1);

Should be:

sheet.insertRowsAfter(i + 1, 1);

Another issue is that you must track the amount of new rows you are adding, as we must keep the array of row values accurate. Each time a new row is added, the data becomes offset by 1.

This is solved by adding an offset value:

   let offset = 1

   for(let i = 1; i < data.length; i++) {
      if(data[i][2] === "Y") {
        sheet.insertNewRow(i + offset, 1);
        offset += 1;
    }
  }

Lastly, it’s good to practice to avoid naming your functions, variables and beyond as existing functions, classes or methods.

Try renaming your function to something else.

function insertNewRow() { ...

Result:

 const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
 const data = sheet.getDataRange().getValues();

 function insertNewRow() {

   let offset = 1;

   for(let i = 1; i < data.length; i++) {
      if(data[i][2] === "Y") {
        sheet.insertNewRow(i + offset, 1);
        offset += 1;
      }
   }
}