I want to set a "Last Updated" cell to the current time in cell B2 of any sheet in a spreadsheet. This code seems to work for changes I type into a given sheet:
function onEdit(e) {
const sh = e.source.getActiveSheet();
sh.getRange('B2').setValue(new Date()).setNumberFormat('MM/dd/yy HH:MMam/pm');
}
But if I do a Find and Replace operation that alters several sheets, my "Last Update" cell is updated only in the rightmost sheet or tab of the collection of sheets that are affected by the change. So for example, if a change alters sheets 2, 3, and 4, only sheet 4's "Last Update" cell is updated with the current time. I guess the getActiveSheet() function only returns the last sheet visited during the event.
Is there any way I can get a list of all the sheets affected by the change and set my timestamp cell in all the affected sheets?
Thanks