I'm writing a code for google sheet and im using OnEdit function to update a column with the update date. (if the cell under column called NAME is edited,time stamp will be created under the cell "time stamp A". that works fine. the problem is when I'm trying to apply the same rule for another two column. I'm trying to make a time stamp under the cell called "time stamp B" whenever cell under the cell names PROGRESS is edited.

Under is the code that works fine for me with the original option and the code that I tried to write that doesn't work. thank you!!

function onEdit(event) {
    var timezone = 'GMT';
    var timestamp_format = "MM-dd-yyyy HH:mm:ss"; // Timestamp Format. var updateColName = "NAME"; var timeStampColName = "time stamp A"; var sheet = SpreadsheetApp.getActive().getSheetByName('work_sheet'); var updateColName2 = "PROGRESS"; var timeStampColName2 = "time stamp B";

    var actRng = event.source.getActiveRange();
    var editColumn = actRng.getColumn();
    var index = actRng.getRowIndex();
    var headers = sheet.getRange(4, 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(), "GMT+2", timestamp_format); cell.setValue(date); }

    }

The code I tried to write:

function onEdit(event) {
    var timezone = 'GMT';
    var timestamp_format = "MM-dd-yyyy HH:mm:ss"; // Timestamp Format. var updateColName = "NAME"; var timeStampColName = "time stamp A"; var sheet = SpreadsheetApp.getActive().getSheetByName('work_sheet'); var updateColName2 = "PROGRESS"; var timeStampColName2 = "time stamp B";

    var actRng = event.source.getActiveRange();
    var editColumn = actRng.getColumn();
    var index = actRng.getRowIndex();
    var headers = sheet.getRange(4, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf(timeStampColName);
    var updateCol = headers[0].indexOf(updateColName);
    updateCol = updateCol + 1;
    var dateCol2 = headers[0].indexOf(timeStampColName2);
    var updateCol2 = headers[0].indexOf(updateColName2);
    updateCol2 = updateCol2 + 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(), "GMT+2", timestamp_format); cell.setValue(date); }

        if (dateCol2 > -1 && index > 1 && editColumn == updateCol2) { // 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(), "GMT+2", timestamp_format); cell.setValue(date); }
    }

0 Answers