Google Sheets - Retrieve contents of the last modified cell in a workbook-CodePudding

I'm building a dynamic validation list, the missing ingredient is the following

I need to populate a cell (A1) with the contents of the last modified cell in a workbook.

I dont know the column/row/sheet of the cell i want to retrieve its content. So basically it is the last entry, and it can be anywhere in the workbook

Any chance of doing that?

CodePudding user response：

Assuming that data will be stored in sheet named lastModifiedRange

function onEdit(e) {
  const sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('lastModifiedRange')
  sheet.getRange('A1').setValue(e.value);
  sheet.getRange('A2').setValue(e.source.getActiveSheet().getName());
  sheet.getRange('A3').setValue(e.source.getActiveRange().getA1Notation());
  sheet.getRange('A4').setValue(e.oldValue);
}

edit : if you want to write it in the same sheet

function onEdit(e) {
  const sheet=e.source.getActiveSheet()
  sheet.getRange('A1').setValue(e.value);
}