Question 211553 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Editing One Sheet is Timestamping Another

I’m using this code to timestamp sheet named "Verify"’s Col11 based on the first time (only) Col9 is edited. It will be cleared and reset manually, daily.

The issue i’m having is editing Col9 on any sheet makes Col11 on the "Verify" tab timestamped, on the corresponding row that was edited on another sheet. Example, i’ll edit Col9 on "Example Sheet"’s row 89, and Col11 row 89 on sheet "Verify" is timestamped. I’ll attach the code i’m using below – thank you!

function onEdit(e) {
  var sh = e.source.getSheetByName("Verify");
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var stampCol = sh.getRange(row, 11);
    
  if (col == 9 && !stampCol.getValue()) {
    var tz = e.source.getSpreadsheetTimeZone();
    var date = Utilities.formatDate(new Date(), tz, 'dd-MM-yyyy hh:mm:ss');
    stampCol.setValue(date);
  }
}
Tags:

2

Answers


  1. 0

    I think the issue is you are "hard coding" the sh var to reference the ‘Verify’ sheet. Combined with your IF statement that doesn’t specifically include a sheet name reference.

    Try replacing

    var sh = e.source.getSheetByName("Verify");

    with:

    var sh = e.source.getActiveSheet();

    And replacing:

    if (col == 9 && !stampCol.getValue()) {

    with

    if (col == 9 && sh.getName() == 'Verify' && !stampCol.getValue()) {
    Login or Signup to reply.
  2. 0

    Try this:

    function onEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  if(sh.getName() == "Verify" && e.range.columnStart == 9 && e.range.rowStart > 1) {
    //e.source.toast("Gate1");
    if(e.range.offset(0,2).isBlank()) {
      //e.source.toast("Gate2");
      e.range.offset(0,2).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm:ss")
    }
  }
}

function resetCol11() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Verify");
  sh.getRange(2,11,sh.getLastRow() - 1).clearContent();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search