Add a date stamp when a new row is added from another tab

Question

I have been trying this for a few hours. I would like a date stamp inserted on column K when a new row is added (my data is on another sheet). I currently have the script below, however, it only works when I edit column 4 manually and not when the row is moved. I want the date stamp to be put when a new row is added. Could you please help me around this?

function onEdit() {

  var s = SpreadsheetApp.getActiveSheet();

    if( s.getName() == "Marketing Dept" ) { //checks that we're on Sheet1 or not

  var r = s.getActiveCell();

    if( r.getColumn() == 4 ) { //checks that the cell being edited is in column D

  var nextCell = r.offset(0, 7);

    if( nextCell.getValue() === '' ) //checks if the adjacent cell is empty or not?

nextCell.setValue(new Date());

}
}
}

Answer 1

Edit triggers (simple and installable) only are triggered by editing a cell or range by using the Google Sheets user interface. "by editing" we means changing the cell or range content.

There is another trigger that migth be what are you looking for, the change installable trigger.

I suggest you to study the event objects. In the case change event object it has the changeType property that could help your script to identify if the change made was to insert a new row. If you use e as the variable name for the event object the you could use

if(e.changeType === 'INSERT_ROW'){
  // Add here what you want to be done when a row is inserted
} else {
  // Add here what you want to be done in any other case
}

Resources

• https://developers.google.com/apps-script/guides/triggers
• https://developers.google.com/apps-script/guides/triggers/installable
• https://developers.google.com/apps-script/guides/triggers/events

Related

• Need Help With a Script to Automatically Insert Rows in a Sheet Based on Other Rows/ Data Inserted in Another Sheet
• How to receive a notification when changes are made to a specific tab in Google Sheets?

Answer 2

Taking the answer provided by @Rubén, and the code included in the question, and the thrust of the question itself, a script like this is likely to meet your needs.

Notes:

• The name of the script is not important but it MUST be installed as a Installable Change trigger.
• it is essential that the (e) attribute be included in the script since this will enable the Event Objects.
• I included the handy code line: Logger.log(JSON.stringify(e)); if you ever wanted to know what events are generated by the trigger, this will identify them.

---

function onChange(e) {

  var s = SpreadsheetApp.getActiveSheet();
  
  var sheetname = "Marketing Dept";
 
  var datestampOffset = 11; // Column K
  
  // Logger.log(JSON.stringify(e));
 
 
  if ((e.changeType === 'INSERT_ROW') && (s.getName() == "Marketing Dept")){
 
    var rng = SpreadsheetApp.getActiveRange();
    var changedRow = rng.getRow();//Row where insert row is done
    // Logger.log("DEBUG: the row = "+changedRow);

    // define the cell for the date stamp
    var nextCell = s.getRange(changedRow-1, datestampOffset);

    // insert the datestamp
    nextCell.setValue(new Date());
  }
 
 return false;
 
}