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()); } } }
Improve this question

2 Answers 2

Reset to default
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

Improve this answer
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. Commented Dec 10, 2020 at 16:55
  • @Einstein_1046 Please edit the question to show what you tried to use the change installable trigger Commented 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; }
Improve this answer
2
  • The script gives me the following error: TypeError: Cannot read property 'changeType' of undefined What might be the issue with it? Commented 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. Commented Dec 17, 2020 at 22:35

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.