CODEWITHSUNDEEP
google-sheetsgoogle-apps-scripttriggers
JeroenSyncro
JeroenSyncro

Reputation: 29

How to get new row number after user inserted row

I'm trying to do some stuff after a user added a new row in Google SpreadSheet.
I've created a trigger that runs my function "nieuweregel" and I want get the row number of the new row. The object 'event' send by google apps, only contains the Type of change and the user (so event.source.getActiveRange() doesn't work because source isn't there).
Understanding Events

I tried to get it through the active SpreadSheet object, but no luck, it always returns 0.
I thought probably a timing thing because after you insert the row that new row is selected. So I even tried to use setTimeout(),1000); to try to fire the get row after the new row is added, but it seems that Google Apps doesn't accept setTimout.

Anyone ideas to get that row number?

function nieuweregel(event){
  if(event.changeType == "INSERT_ROW")
    var ss = SpreadsheetApp.getActiveSpreadsheet();  
  // var r = event.source.getActiveRange() 
  var r = ss.getActiveRange();
  ss.toast('new row nr = '+r.getRow());
}

Upvotes: 2

Views: 2498

Answers (1)

Bence Kaulics
Bence Kaulics

Reputation: 7291

The event that is passed to the onChange does not seem to have any information apart from the type and some other attributes.

You could inspect the active sheet and active range instead. The following function shows the newly inserted row's number.

function onChange(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  var row = range.getLastRow();

  SpreadsheetApp.getActiveSpreadsheet().toast(">" + e.changeType + " " + row + " " + sheet.getName()) ;
} // Added: Please install OnChange event trigger to this function.

Upvotes: 1

Related Questions