Question
As background, my goal is to log a timestamp whenever a spreadsheet is opened.
That way, I can hibernate services running via an hourly time-based trigger if the spreadsheet has not been opened after 60 days.
I understand onOpen runs in AuthMode: Limited, but I'm not clear on whether that permits me to addDeveloperMetadata.
I'm getting the following error:
Could not update lastOpened metadata in onOpen: You do not have permission to perform that action.
Is this adding developer metadata via onOpen not achievable?
Is there a pattern for logging a "lastOpened" timestamp that can work in AuthMode Limited?
function stampLastOpened(spreadsheet) {
var now = new Date().toISOString();
var existing = spreadsheet
.createDeveloperMetadataFinder()
.withLocationType(
SpreadsheetApp.DeveloperMetadataLocationType.SPREADSHEET
)
.withKey('lastOpened')
.find();
if (existing.length > 0) {
existing[0].setValue(now);
} else {
spreadsheet.addDeveloperMetadata(
'lastOpened',
now,
SpreadsheetApp.DeveloperMetadataVisibility.PROJECT
);
}
}
function onOpen() {
var ss = SpreadsheetApp;
try {
stampLastOpened(ss.getActiveSpreadsheet());
} catch (e) {
Logger.log('Could not update lastOpened metadata in onOpen: ' + e.message);
}
}
