2
votes

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!

1

1 Answers

1
votes

"If an add-on is installed for a user but not enabled in the current document, onOpen(e) runs in AuthMode.NONE; if the add-on is enabled in the current document, onOpen(e) runs in AuthMode.LIMITED. If the add-on is both installed and enabled, the enabled state takes precedence, since LIMITED allows access to more Apps Script services."

When the script is on AuthMode.NONE it does not have access to the Properties Service. Since you have global code that calls this service, the add-on if failing the execution of onOpen().

You need to move this global code to within a function.

See more here https://developers.google.com/apps-script/add-ons/lifecycle#authorization_modes