I'm working on an Add-On for Sheets called LastEdit. The problem I'm having is that onOpen() only runs for the first time the Add-On is installed:
- The code works fine running as a bound script to the original Sheet where I write the Add-on; issues only arrive with the published Add-on (installed to a different Google Drive account)
- If I innstall the Add-on from the Chrome Web Store:
- A new untitled Sheet is automatically opened with the Add-On installed.
- Everything works just fine this first time: help tip pops up, AddonMenu populates, etc.
- If I refresh the page onOpen() stops working. I still see the Add-on menu called LastEdit but it's empty except for the default Help button; all my custom menu items are missing.
- If I open a new Sheet I have the same issue.
- If I go to Add-ons -> Manage add-ons and click Manage -> Use in this document there is no change. I can check, uncheck, and refresh the page in any combination and no change.
- The script is otherwise working fine: onEdit() is getting called no problem.
- I snuck a ui.alert() at the start of onOpen(); I only see this alert once with the automatically opened first Sheet.
- I tried removing all code from onOpen() except for ui.alert() and it can't even manage that.
So, why won't onOpen() run anymore??
Here's the code BTW:
var documentProperties = PropertiesService.getDocumentProperties();
var ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActive();
var editCell;
// This function runs whenever cells are edited
function onEdit() {
updateLastEdit();
}
// This function updates the editCell contents
function updateLastEdit() {
// Fetch the coordinate of the designated LastEdit cell
editCell = documentProperties.getProperty('editCell');
// If our docProp editCell is 0 then we don't have a LastEdit to update
if (editCell != 0.0) {
sheet.getRange(editCell).setValue(new Date() + '');
}
}
// This function will be used to designate the new LastEdit cell
function lastEdit() {
editCell = sheet.getActiveCell()
SpreadsheetApp.getUi().alert("LastEdit cell added at: " + editCell.getA1Notation());
documentProperties.setProperty('editCell', editCell.getA1Notation());
// Once we've stashed the location of the LastEdit cell we move to update the LastEdit cell contents
updateLastEdit();
}
// When the Sheet opens add a new custom menu
function onOpen(e) {
// This is the alert for testing purposes
ui.alert("onOpen() has run!");
// Contingency strategy; set an Installable Trigger to perform the onOpen tasks
//ScriptApp.newTrigger("openTrigger").forSpreadsheet(sheet).onOpen().create();
// Creating custom menu for this app
newMenu = ui.createAddonMenu();
newMenu.addItem('Insert LastEdit Cell', 'insertLastEdit');
newMenu.addItem('Disable LastEdit Cell', 'disableLastEdit');
newMenu.addItem('LastEdit Cell Location', 'locateLastEdit');
newMenu.addSeparator();
newMenu.addItem('About', 'aboutLastEdit');
newMenu.addToUi();
// I know this can be shortened, but I removed/tested each individual
// item to see if any of these were derailing onOpen()
}
// Insert LastEdit Cell
function insertLastEdit() {
lastEdit();
}
// Disable LastEdit Cell
function disableLastEdit() {
documentProperties.setProperty('editCell', 0);
SpreadsheetApp.getUi().alert("LastEdit cell disabled");
}
// Fetch and display the LastEdit cell location via popup
function locateLastEdit() {
editCell = documentProperties.getProperty('editCell');
if (editCell != 0) {
SpreadsheetApp.getUi().alert("LastEdit cell is located at: " + editCell);
} else {
SpreadsheetApp.getUi().alert("No LastEdit cell is active ");
}
}
// About!
function aboutLastEdit() {
ui.alert("About LastEdit", SpreadsheetApp.getUi().ButtonSet.OK);
}
// After installation just run the onOpen function
function onInstall(e) {
onOpen(e);
}
// If an Installable Trigger is required...
function openTrigger() {
// I had a duplicate of onOpen() in here, but have abandoned this strategy
}
What am I missing here? There seem to be a lot of moving parts to the Add-on authorization (Bound scripts, Simple Triggers, Enabled in Document, authMode.LIMITED vs. FULL, etc.) I reviewed the Add-on Authorization Lifecycle page but it seems to point to this process being handled largely on an automatic basis.
Thanks in advance for all your help!