Trigger when user insert row in google sheet and return the list of new created row numbers

Question

I use google script to capture event when user insert rows (in google sheet) and fetch the list of row number that's newly created.

I tried onChange :

function onInsertRow(e){
  if (e.changeType == "INSERT_ROW") { 
    SpreadsheetApp.getUi().alert("insert");
  }
}

Trigger works but the event object of onChange() doesn't have range property. So how can i get the list of row numbers that has been inserted ?

liquidkat · Accepted Answer · 2021-07-28 21:45:50Z

3

Below is the code:

you have to set up a new trigger.

you can use the getRow or geRowIndex to find out which row has changged.

Hope this will help.

function setUpTrigger(){
  ScriptApp.newTrigger('on_insert').forSpreadsheet('id').onChange().create();
}

function on_insert(e){
  const sheet = SpreadsheetApp.getActiveSheet(); 
  sheet.appendRow([e.changeType,e.source.getActiveSheet().getActiveRange().getRowIndex()])
}

edited Jul 28, 2021 at 21:45

answered Jul 28, 2021 at 4:29

liquidkat

5864 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

andio Over a year ago

I use onChange() because I tried using onEdit() and it can't be triggered when i insert rows. It works only when i edit the cells. How do you make it work ?

liquidkat Over a year ago

I updated the code above. You have to set up a trigger for this sheet. Then each time you made changes on this sheet, trigger will be fired.

liquidkat Over a year ago

So happy to help!

Collectives™ on Stack Overflow

Trigger when user insert row in google sheet and return the list of new created row numbers

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related