CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamprow
BuckEyeTree
BuckEyeTree

Reputation: 65

Adding A Timestamp When New Row Inserted or Row Edited in Google Sheets

I have:

I want:

I pieced the below code together from this post "Want to create timestamp for time added and create unique incremental user ID. And also include a last changed column". However I have an issue -- when I try to run this I am getting the following error message:

"TypeError: Cannot read property 'source' of undefined (line 6, file "Code")"

function onChange(e) {

var lastChangedTime = 276 //column JP

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = e.source.getActiveSheet

var now = new Date();

if (e.changeType == "INSERT_ROW") {


//set lastChangedTime when the row is created 
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), lastChangedTime, 1, 1).setValue(now);
   } 

  if (e.changeType == "EDIT") {

//set date in same row as edit happens, at fixed column  
ss.getActiveSheet().getRange(ss.getActiveRange().getLastRow(), lastChangedTime, 1, 1).setValue(new Date());

   }  
}

Can anyone tell what the error is by looking at this code? Any help would be very much appreciated!

EDIT 1:

I wanted to follow-up and tell everyone how this issue resolved for me. I thought I had one problem, but I actually had two.

  1. The first problem was that I was trying to "Run" the OnEdit(e) function from the script editor to test it, which was throwing out the above error code. As Chemi Adel correctly notes, the code needs to be altered to enable this type of test.

  2. The second problem, as correctly identified by Mateo Randwolf in the comments, was that I had not correctly installed the trigger. To install the trigger, start by opening the google sheet containing the script. Then go to Tools > Script Editor to open the script editor. The script editor should open in a separate tab in your web browser. Then, within the script editor tab go to Edit > Current Project's Triggers which will open a new tab showing the triggers for the current project. Then press the "Add Trigger" button in the lower right, and set the parameters of the function as Function: OnChange, Deployment: Head (I don't know what this means but is only option for me), Select Event Source: From Spreadsheet, and Select Event Type: On Edit. I was able to find how to install the trigger in the documentation that Mateo Randwolf linked to.

Upvotes: 3

Views: 2045

Answers (2)

Chemi Adel
Chemi Adel

Reputation: 2165

Edit triggers give to onChange an object as parameter event, click run without defining event cause error create object similar structure as for testing event

function onChange(event) {

...Code

//Overrid e just for testing
event = {
  source:ss,
  changeType:'INSERT_ROW'
}

...Code
    
}

Upvotes: 4

BuckEyeTree
BuckEyeTree

Reputation: 65

I wanted to follow-up and tell everyone how this issue resolved for me. I thought I had one problem, but I actually had two.

  • The first problem was that I was trying to "Run" the OnEdit(e) function from the script editor to test it, which was throwing out the above error code. As Chemi Adel correctly notes, the code needs to be altered to enable this type of test.
  • The second problem, as correctly identified by Mateo Randwolf in the comments, was that I had not correctly installed the trigger. To install the trigger, start by opening the google sheet containing the script. Then go to Tools > Script Editor to open the script editor. The script editor should open in a separate tab in your web browser. Then, within the script editor tab go to Edit > Current Project's Triggers which will open a new tab showing the triggers for the current project. Then press the "Add Trigger" button in the lower right, and set the parameters of the function as Function: OnChange, Deployment: Head (I don't know what this means but is only option for me), Select Event Source: From Spreadsheet, and Select Event Type: On Edit. I was able to find how to install the trigger in the documentation that Mateo Randwolf linked to.

Upvotes: 2

Related Questions