Get Last Modified Date When Cell is changed

Ask for Help
4

This is how I do it.

I have a function in my script associated with “On change” event. I handle EDIT, INSERT_ROW with it.

function changeTriggerProcessing(e) {
  // Logger.log(e.changeType);

  let sheetName = e.source.getActiveSheet().getName();

  // in case of EDIT, the way to get active sheet is different
  if (e.changeType == "EDIT") {
    sheetName = SpreadsheetApp.getActiveSheet().getName();
  }

  if (
    e.changeType == "EDIT" &&
    sheetName == "SHEET_1" &&
    SpreadsheetApp.getActiveSheet().getActiveCell().getHeight() == 1 &&
    SpreadsheetApp.getActiveSheet().getActiveCell().getWidth() == 1
  ) {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sh = ss.getSheetByName("Audit Log");
    let rowIndex = SpreadsheetApp.getActiveSheet()
      .getActiveCell()
      .getA1Notation()
      .replace(/\D/g, "")
      .trim();

    // detect delete from the App (anchor cells become blank)
    if (
      !SpreadsheetApp.getActiveSheet()
        .getRange("A" + rowIndex)
        .getValue() &&
      !SpreadsheetApp.getActiveSheet()
        .getRange("D" + rowIndex)
        .getValue()
    ) {
      // whole row deleted case
      // your code here
    } else {
      // update case
      // your code here
    }
  } else if (sheetName == "App: Logins" && e.changeType == "INSERT_ROW") {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sh = ss.getSheetByName("Audit Log");
    let rowIndex = SpreadsheetApp.getActiveSheet().getLastRow();

    // INSERT_ROW processing here
    // your code here
  }
}
1 Like