events - How can I run a script only when someone adds new rows

Question

Situation:

I have the following script, that runs in OnEdit, but I want to tune this script to run only when someone adds new rows to the sheet.

Script:

function DataValidation2(e)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  if (s.getName() == "Sheet1"){
  var cell = s.getRange('C3:C');
  var rule = SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireValueInList(['0:15:00', '0:20:00']).build();
  cell.setDataValidation(rule);
  }
}

If possible execute this script only when someone add new row using the boton add include at the end of the sheet?

Best Regards,

Answer 1

There is a special trigger to detect such changes : onChange

You can even create it programmatically as described in the documentation :

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("myFunction")
   .forSpreadsheet(sheet)
   .onChange()
   .create();

and then a function like this that will be called on every change that looks at the number of rows and do something if it changed...and does nothing if the change was something else.

You can add more conditions to handle other cases, for example what to do id rows where deleted

function myFunction(){
  var numRows = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getMaxRows();
  if(ScriptProperties.getProperty('numberOfRows')){
    var nRows = Number(ScriptProperties.getProperty('numberOfRows'));
    if(nRows<numRows){
      //do something because rows have been added in this sheet
      ScriptProperties.setProperty('numberOfRows',numRows);// update value with current value
    }
  }
  ScriptProperties.setProperty('numberOfRows',numRows);// create a start value with current value of simply update if the trigger was called for another reason
}

---

EDIT following Wchiquito 's comment

here is another (simpler) version that works pretty well too :

function initializeTrigger(){ // run this only once to create a trigger if necessary
  var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("myFunction")
   .forSpreadsheet(sheet)
   .onChange()
   .create();
}

function myFunction(e){
  Logger.log(e.changeType);
  if(e.changeType=='INSERT_ROW'){
    // do Something
    Browser.msgBox('New row(s) added');
  }
}