CODEWITHSUNDEEP
google-apps-scripttriggers
user1114
user1114

Reputation: 1169

Google Apps Script Run Installable Trigger on Custom Menu Item Click

Is it possible to set an installable trigger that runs when users select an option from a custom menu in Google Sheets? I need it to be an installable trigger in order to run with the permissions of the creator versus with those of the user behind the keyboard.

Edit:

ScriptApp.newTrigger() documentation lists the following example

// Creates an edit trigger for a spreadsheet identified by ID.
ScriptApp.newTrigger('myFunction')
    .forSpreadsheet('1234567890abcdefghijklmnopqrstuvwxyz_a1b2c3')
    .onEdit()
    .create();

Here it seems that it is set to call a custom function whenever the spreadsheet is edited. This does not seem to address how to create a trigger that fires when a user selects a custom menu item.

Upvotes: 1

Views: 1596

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Answer:

You can not make a trigger fire by clicking on a custom menu item in Sheets. You can however utilise a Web App to execute a function as another person from a custom menu item.

NB: This answer was edited after more information was provided, the original answer is below the break.

More Information:

As a preface, I'd just like to go over what triggers actually are. Google Apps Script has two kinds of triggers, Simple and Installable. From the documentation on Simple Triggers:

Triggers let Apps Script run a function automatically when a certain event, like opening a document, occurs.

and from the Installable Triggers documentation:

Like simple triggers, installable triggers let Apps Script run a function automatically when a certain event, such as opening a document, occurs. Installable triggers, however, offer more flexibility than simple triggers: they can call services that require authorization, they offer several additional types of events including time-driven (clock) triggers, and they can be controlled programmatically.

Triggers are exactly that - they work similarly to cron jobs or event listeners to execute code when a very specific type of action happens, like a Sheet is edited or the time hits 2pm. It isn't possible to force a trigger to fire under any circumstance other than the one it was set up to do.

The Workaround:

The Apps Script environment provides a way of executing code as yourself regardless of the person that calls it - Web Apps.

Firstly, you will need to create a second project which contains the code that you wish to execute on button click, inside a function called doGet() (this is another kind of simple trigger):

function doGet(e) {
  console.log("Menu item clicked!");
}

From here, you will need to publish the script as a Web App. Follow the Publish > Deploy as web app... menu item, and set up the configutation as follows:

  • Project version: New
  • Execute the app as: Me ([email protected])
  • Who has access to the app: Anyone, even anonymous

And Hit Deploy. This will give you a Web App URL in a text box which you can copy.

Now going back to your Sheet with the custom menu, you can make a call to your Web App URL using UrlFetch to execute the code inside the doGet(e) function that you set up:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  UrlFetchApp.fetch("https://script.google.com/a/somedomain.com/macros/s/some-web-app-id/exec");
}

Rundown of this code:

  • When the Spreadsheet is opened, the onOpen() method is called, creating a new menu item in the UI's toolbar.
    • This onOpen() method adds the menu item Custom Menu > First item. The function menuItem1 is assigned to run when this menu item is clicked.
  • The function menuItem1 will then make a fetch call to the web app you created in the first step, executing the code in its doGet(e) function as you - the person that set up the Web App, as per the Web App's configuration.

Things to remember:

  • You will need to first authorise your code so that it runs when the menu item is selected.
  • As per this bug, when you authenticate the script by clicking on the menu item, it will not run the first time the menu item is clicked. You will have to invoke it a second time after you have authorised the script.

References:

Answer:

This is absolutely possible!

More Information:

You're most of the way there with the code snippet you have - what you need to do from here is call this script when a menu item in the UI is called. You can see an example of using custom menus in G Suite here.

Code Example:

You will need to use the newTrigger() method as you have shown in your question, in conjunction with the custom menu item functions:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  ScriptApp.newTrigger('myFunction')
    .forSpreadsheet('your-spreadsheet-id')
    .onEdit()
    .create();
}

function myFunction() {
  // do some stuff
}

Rundown of this code:

  • When the Spreadsheet is opened, the onOpen() method is called, creating a new menu item in the UI's toolbar.
    • This onOpen() method adds the menu item Custom Menu > First item. The function menuItem1 is assigned to run when this menu item is clicked.
  • The function menuItem1 creates an installable onEdit() trigger when called. This trigger will fire the function myFunction() whenever the Spreadsheet is edited.
  • The function myFunction() is whatever you want your installable onEdit to do.

References:

Upvotes: 4

Related Questions