Google Sheets – getSheetByName Not Returning Specific Sheet

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

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

Best Answer

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.