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,

Serge insas · Accepted Answer · 2013-11-28T16:13:08.527

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');
  }
}

Have you considered using `INSERT_ROW` available in parameter `changeType` from the `onChange()` event? — wchiquito, Nov 28 '13 at 13:49
You're right, didn't think about it... feel free to post another answer, your idea is better as it makes use of GAS direct method. — Serge insas, Nov 28 '13 at 13:53
Hello, I'm trying to integrate the script to my funtion when somone add or insert new rows to sheet but didn't work... I don't know where I needs to put the my script.. Still working.. — LAD Service Desk, Nov 28 '13 at 15:14
You have to insert the functions alone in the script editor (ie 2 separate function) and just run the forst one once to create the trigger. After that it will be fully automatic. If you want it to take some action, call a third function (the one you probably already have) where I put "do something" — Serge insas, Nov 28 '13 at 15:21
Serge, Now is working... The OnChange() value exist in Resurce > Proyect Trigger... Note: When I use the part of create the trigger, the system create it 10 time the same trigger — LAD Service Desk, Nov 28 '13 at 15:51

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

Situation:

Script:

1 Answers1

Linked