Search code examples
javascriptgoogle-sheetsgoogle-apps-script

How would I want to inform users, if the script is not authorized?

My Google Sheet uses an App Script with an installed trigger to extend some functionalities.

  • As an owner I can deploy my App Script and run it under my account for other users, so they don't have to install the trigger themselves.
  • However, I expect users to make a copy (...of a copy) of the spreadsheet, which makes them the new owner of a new spreadsheet. This renders parts of the script out-of-bounds, requiring users to install the trigger themselves.

To make it easy for the new owner of the new spreadsheet to install a trigger, a custom menu item has been added.

I would like to inform the user about the necessity to click the custom menu item:

  1. IF the script is NOT authorized THEN a warning message should be displayed onOpen
  2. IF the script is authorized THEN a success message should be displayed.
  3. IF the script's permission have been revoked (e.g. due to another copy) THEN a warning message should be displayed again, see 1.

Preferably this message should be non-intrusive, e.g. inside a specific cell.

Things I have tried:

How would I want to inform users, if the script is not authorized?

Solution

  • You should create a "user manual". It could be included in a sheet that you might name README or use a more appealing name for your spreadsheet audience. If the complexity or the detail required by the spreadsheet audience makes a sheet unsuitable, you might still use it to include a link to a user manual.

    The previous answers by doubleunary and TheMaster have explained what the OP might need to know. I am including an implementation example in this answer. Each function includes a brief description as JSDoc comments.

    As an example of a function that requires authorization to run, I'm including a function calling SpreadsheetApp.Spreadsheet.show(HtmlService.HtmlOutput) which shows a dialog created using the HtmlService.

    /**
 * When the spreadsheet is opened, checks if the installable trigger was  
 * created, otherwise run authorizeCustomMenu.
 */
function onOpen() {
  const hasTrigger = PropertiesService.getScriptProperties()
     .getProperty('Trigger');
  if (hasTrigger) return;
  authorizeCustomMenu();
}

/**
 * Creates a custom menu intended to provoke the authorization of the script.  
 * This creates a Script Property to flag the script as having the On open 
 * installable trigger.
 */
function authorizeCustomMenu(){
  SpreadsheetApp.getUi().createMenu('Authorize My App')
    .addItem('Run', 'authorize')
    .addToUi();
}
/**
 * This function is intended to be run just after a copy of the original 
 * spreadsheet is created. Before it finishes, it replaces the  Authorize 
 * My App custom menu by My App custom menu.
 */
function authorize() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('customMenu')
    .forSpreadsheet(spreadsheet)
    .onOpen()
    .create();
  PropertiesService.getScriptProperties()
    .setProperty('Trigger', "TRUE");
  customMenu();
}

/**
 * Creates the My App custom menu. This is intended to be run when by the 
 * On open installable trigger.
 */
function customMenu() {
  SpreadsheetApp.getUi().createMenu('My App')
    .addItem('Show instructions', 'showDialog')
    .addToUi();
}

/**
 * Shows a dialog created using the HtmlService. This is intended to be run  
 * from the My App custom menu.
 */
function showDialog() {
  const html =
  `<!DOCTYPE html>
  <html>
    <head>
      <base target="_top">
    </head>
    <body>
      <h1>My App</h1>
      <h2>Usage Overview</h2>
      The purpose of this spreadsheet is...
    </body>
  </html>`;
  const dialog = HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getActiveSpreadsheet().show(dialog);
}

    You might want to extend the above implementation to handle cases like the On open trigger being disabled, deleted, or not working because the trigger's total daily execution time has exceeded