How do I add a custom function to Google Sheets from the Google Workspace add-on environment?

Note that this question is not about adding a custom functions for an Editor Add-on. This question is about building a Google Workspace add-on.

For reference, assume the custom function I want to add is as follows:

Custom function

function GETTAX( price, rate, ) {
  const tax = price * rate;
  const dollarUS = Intl.NumberFormat("en-US", {
    style: "currency",
    currency: "USD",
  });
  const result = dollarUS.format( tax, );
  return result;
}

How can I add a custom function to Sheets when my Google Workspace add-on is open?

CodePudding user response：

Tl;Dr: It's not possible.

Custom functions works only in Editor add-ons, not in Workspace add-ons.

From the Official Docs

Google Developers > Google Apps Script > Guides > Sheets > Custom Functions

Specific page: https://developers.google.com/apps-script/guides/sheets/functions (links not included)

Creating a custom function

To write a custom function:

1. Create or open a spreadsheet in Google Sheets.
2. Select the menu item Extensions > Apps Script.
3. Delete any code in the script editor. For the DOUBLE function above, simply copy and paste the code into the script editor.
4. At the top, click Save save. Now you can use the custom function.

Sharing

Custom functions start out bound to the spreadsheet they were created in. This means that a custom function written in one spreadsheet can't be used in other spreadsheets unless you use one of the following methods:

• Click Extensions > Apps Script to open the script editor, then copy the script text from the original spreadsheet and paste it into the script editor of another spreadsheet.
• Make a copy of the spreadsheet that contains the custom function by clicking File > Make a copy. When a spreadsheet is copied, any scripts attached to it are copied as well. Anyone who has access to the spreadsheet can copy the script. (Collaborators who have only view access cannot open the script editor in the original spreadsheet. However, when they make a copy, they become the owner of the copy and can see the script.)
• Publish the script as a Google Sheets add-on.

Clarifications:

1. Google Sheets add-on refers to Editor add-on, not a Workspace add-on. To learn about Add-ons types see https://developers.google.com/apps-script/add-ons/concepts/types.