Search code examples
google-sheetsgoogle-apps-scripttriggersmenu

How to create menu items on Google Sheets through non-bound scripts?

Is there any way we can create menu items in Google Sheets through "non-bound" (standalone) scripts? I read some official documentation on getUI, but found nothing so far in regards to non-bound scripts. The following was my initial attempt, but no menu item is generating on my Google Sheet...


function onOpen() {

  const id = "1AHHRp0YNltB...";
  const sheet = SpreadsheetApp.openById(id);
  const tab = sheet.getSheetByName("results");
  const menu = sheet.getUi();
  menu.createMenu("send email").addItem("Approve", "approver").addToUi();

  function appover() {
    const id = "1AHHRp0YNltB...";
    const sheet = SpreadsheetApp.openById(id);
    const menu = sheet.getUi();
    const res = menu.alert("Okay?", menu.ButtonSet.YES_NO);
  }

}

Update

With feedback/resources provided so far (thanks!), I'm trying my best to craft an installable open trigger programmatically with little documentation support on the how-to. Here is my latest effort...but no menu item displaying in my Google Sheet yet. How far off am I?

function mTrigger() {
  var ssid = "1AHHRp0YNltB_TOJftIX9...";
  ScriptApp.newTrigger("mSheetMenu").forSpreadsheet(ssid)
  .onOpen()
  .create();
}

function mSheetMenu(event) {
  event.createMenu("send email").addItem("Approve", "approver").addToUi();
  function approver() {
    console.log("show me something");
  }
}

As Tanaike mentioned below, I'm coming into this question with the following background:

  • I have a Google Apps Script project of the standalone type and a Google Spreadsheet as the independent files, respectively.

  • I want to create a custom menu from the Google Apps Script project of the standalone type to the Google Spreadsheet.

  • Also, I want to make users execute the function in the Google Apps Script project of the standalone type from the created custom menu on the Spreadsheet.

Solution

  • I believe your situation is as follows.

    • You have a Google Apps Script project of the standalone type and a Google Spreadsheet as the independent files, respectively.
    • You want to create a custom menu from the Google Apps Script project of the standalone type to the Google Spreadsheet.
    • Also, you want to make users execute the function in the Google Apps Script project of the standalone type from the created custom menu on the Spreadsheet.

    First, I think that Wicket's answer is useful. Ref In this answer, I would like to explain the modification points of your showing script. When I saw your showing script, I thought of the following modification points.

    Modification points:

    • In your showing script, there is no method createMenu in the event object event. By this, the custom menu is not created. I guess that an error like event.createMenu is not a function occurs.
    • Even when the custom menu is created, the function approver is not run. Because function approver() {,,,} is included in the function mSheetMenu.

    When these points are reflected in your script, how about the following modification?

    Modified script:

    function mTrigger() {
  var ssid = "###"; // Please set your SpreadsheetID.
  var functionName = "mSheetMenu";

  ScriptApp.getProjectTriggers().forEach(t => {
    if (t.getHandlerFunction() == functionName) {
      ScriptApp.deleteTrigger(t);
    }
  });
  ScriptApp.newTrigger(functionName).forSpreadsheet(ssid).onOpen().create();
}

function mSheetMenu(event) {
  event.source.addMenu("send email", [{ name: "Approve", functionName: "approver" }]);
}

function approver() {
  console.log("show me something");
  Browser.msgBox("ok");
}

    • If you want to use the event object event, please use the above-modified script.

    • If you want to use createMenu, please modify event.source.addMenu("send email", [{ name: "Approve", functionName: "approver" }]); to SpreadsheetApp.getUi().createMenu("send email").addItem("Approve", "approver").addToUi();. Both results are the same.

    • In order to avoid the duplicated installable triggers, I updated the function mTrigger.

    Testing:

    1. Run mTrigger.
    2. Open Spreadsheet.
    3. When you open the Spreadsheet, you can see the created custom menu "mSheetMenu".
    4. When you run "Approve" from the custom menu, you can see an opened dialog on the Spreadsheet. By this, you can confirm that the function approver in the Google Apps Script project of the standalone type is run.

    Note:

    • In the current stage, when a custom menu is created by the trigger installed from the outside of Google Docs (Spreadsheet, Document, and so on), the function in the Google Apps Script project that the trigger is run.

    References: