0

I'm using a script to add a timestamp when a value on a given row is changed. My issue I'm running into is that the getSheetByName isn't returning the sheet I want, rather the script is running across all 15+ sheets on the file. What am I doing wrong?

function onEdit() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LeadTracker");
  var r = s.getActiveCell();
  if( r.getColumn() != 2 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-06:00", "MM-dd-yy, hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange('R' + row.toString()).setValue(time);
  };
 };
2

You'll want to check the event's sheet and cell, not necessarily a specific sheet.

So, receive the event as a parameter to your event handler:

function onEdit(event) ...

then check the event's source for which sheet it occured on:

if (event.source.getActiveSheet().getName() == "LeadTracker") ...

So:

function onEdit(event) {
  var s = event.source.getActiveSheet();
  var activeCell = s.getActiveCell();
  if (s.getName() == "LeadTracker" && activeCell.getColumn() != 2) {
    var row = activeCell.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-06:00", "MM-dd-yy, hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange('R' + row.toString()).setValue(time);
  };
 };

See the example spreadsheet I've set up, and feel free to copy it.

  • Ok, so I implement the script and get: TypeError: Cannot read property "source" from undefined. (line 2, file "Code") – Kyle Walker Mar 7 '17 at 16:21
  • 1
    @KyleWalker Are you trying to run it by pressing the Play button? That won't work, as there is no event object in that case. Instead, trigger the script by doing an edit in the spreadsheet. – Vidar S. Ramdal Mar 7 '17 at 17:33
  • That worked perfectly. Thank you so much for your help! – Kyle Walker Mar 9 '17 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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