Timestamp script copying onto same cell of different sheet

Question

I have a strange script issue I was hoping someone could help me with -- I have 2 different sheets with timestamps that update when the neighboring cell is initialed. The scripts for both sheets are exactly the same (with different sheet names of course) and are not intended to interact, but one of them keeps copying over to the other.

Whenever someone initials/timestamps sheet 1, the script runs successfully on sheet 1 but also updates the same cell on sheet 2. If the cell doesn't exist, extra rows get added to create it. This copying doesn't happen the other way around.

Is there anything I can do to stop this?

---

Code for sheet 1:

function onEdit(event)
{ 
  var timezone = "PST";
  var timestamp_format = "MM-dd-yyyy hh:mm:ss"; // Timestamp Format. 
  var updateColName = "Initials";
  var timeStampColName = "Last Updated";
  var sheet = event.source.getSheetByName('WC Labs'); //Name of the sheet where you want to run this script.


  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

---

Code for sheet 2:

function onEdit(event)
{ 
  var timezone = "EST";
  var timestamp_format = "MM-dd-yyyy hh:mm:ss"; // Timestamp Format. 
  var updateColName = "Initials";
  var timeStampColName = "Last Updated";
  var sheet = event.source.getSheetByName('EC Labs'); //Name of the sheet where you want to run this script.


  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

Answer 1

1. Keep things DRY: if there are two functions that do the same thing, you only need one.
2. getSheet method allows you to get the sheet that contains the currently edited Range - use it to acquire the reference to the currently edited sheet.
3. Use strict equality comparison (it will not change anything in your case, but builds a useful habit that will save you debugging time).
4. Exit as early as possible: if statements should handle edge cases, not main logic.
5. Use the newer V8 runtime (the snippet below uses ES6 syntax).

---

/**
 * @param {{
 *  range : GoogleAppsScript.Spreadsheet.Range,
 *  timezone : (string|"PST")
 * }} param0 
 */
function onEdit({ range, timezone = "PST" }) {

    const sheet = range.getSheet();

    const timestamp_format = "MM-dd-yyyy hh:mm:ss";
    const updateColName = "Initials";
    const timeStampColName = "Last Updated";

    const editColumn = range.getColumn();
    const editIndex = range.getRowIndex();

    const [header] = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();

    const dateCol = header.indexOf(timeStampColName);
    const updateCol = header.indexOf(updateColName) + 1;

    if (dateCol < 0 && editIndex <= 1 && editColumn !== updateCol) { return; }
    
    const cell = sheet.getRange(editIndex, dateCol + 1);
    const date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
}

---

References

1. onEdit event object structure

Answer 2

The onEdit() function in both files is getting event.source.getActiveRange(); as the active range. Since you don't specify a particular sheet, whichever cell you are editing becomes active regardless of which sheet/tab it belongs.

---

Try to replace this line:

var actRng = event.source.getActiveRange();

in both files with the following:

var actRng = event.source.getSheetByName('WC Labs').getActiveRange();

and

var actRng = event.source.getSheetByName('EC Labs').getActiveRange();

respectively.

---

Code for sheet 1:

function onEdit(event)
{ 
  var timezone = "PST";
  var timestamp_format = "MM-dd-yyyy hh:mm:ss"; // Timestamp Format. 
  var updateColName = "Initials";
  var timeStampColName = "Last Updated";
  var sheet = event.source.getSheetByName('WC Labs'); //Name of the sheet where you want to run this script.


  var actRng = event.source.getSheetByName('WC Labs').getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

---

Code for sheet 2:

function onEdit(event)
{ 
  var timezone = "EST";
  var timestamp_format = "MM-dd-yyyy hh:mm:ss"; // Timestamp Format. 
  var updateColName = "Initials";
  var timeStampColName = "Last Updated";
  var sheet = event.source.getSheetByName('EC Labs'); //Name of the sheet where you want to run this script.


  var actRng = event.source.getSheetByName('EC Labs').getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}