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:

Solution

  • I believe your situation is as follows.

    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:

    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");
}

    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:

    References: