0

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());

}
}
}

2 Answers 2

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

Related

2
  • Ruben, I have tried manipulating it with some of the scripts I have come across; however, it's not working. Could you please share with me a script applicable to the question? I am still new to Google Scripts. Dec 10, 2020 at 16:55
  • @Einstein_1046 Please edit the question to show what you tried to use the change installable trigger Dec 10, 2020 at 17:37
1

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;
 
}
2
  • The script gives me the following error: TypeError: Cannot read property 'changeType' of undefined What might be the issue with it? Dec 17, 2020 at 22:16
  • This means suggests that "e" is undefined. This is possibly/probably because 1) you haven't installed the trigger (it's an Installable trigger, don't forget), and/or 2) you have run the function from the Apps Editor instead of from the spreadsheet (that is, selecting a dropdown in Column I with a value of "Complete"). On a trouble-shooting tangent, you could "uncomment the line Logger.log(JSON.stringify(e)); in order to see what the system is reporting as the events.
    – Tedinoz
    Dec 17, 2020 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.