Get Last Modified Date When Cell is changed

Ask for Help
1

Hey, could some help having “Last Modified” date appear and update when a particular cell is modified.

I have a very simple Scripts formula in Sheets (see below). It works fine when I manually edit the sheet, but doesn’t work when Glide updates the sheet.

I also tried onChange and onFormSubmission — none of these work :frowning:

————

function onEdit(e) {

var row = e.range.getRow();
var col = e.range.getColumn();

if (col === 1 && row > 1 && e.source.getActiveSheet().getName() === “Tasks” ) {
e.source.getActiveSheet().getRange(row, 8).setValue(new Date())
}
}

———

2

The onChange and onEdit is only the name of the function. Go to Edit > Current project’s triggers and add a trigger for your function with the event type On change.

3

Oh I just realized if you enable the edit option for the item, you can capture the current datetime value into a field of your choice, so that’s the better option for you I suppose.

1 Like
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
5

Hi,

Would it be more understandable to replace :

with :

    let rowIndex = SpreadsheetApp.getActiveSheet()
      .getActiveCell()
      .getRowIndex();