Google-sheets – Test Google Spreadsheets Add-On with custom functions

google sheetsgoogle-apps-scriptgoogle-apps-script-add-ongoogle-sheets-custom-function

I have set up a Google Apps Scripts project with the intent of creating a Google Spreadsheets add-on with custom functions. However, when I use the option "Test as add-on", I can't use the functions that I created. When I paste the script code into the script editor opened from a spreadsheet it works fine, however.

Is this a limitation on how custom functions work in Spreadsheets, or am I simply doing something wrong?

Best Answer

Answer

According to Eric Koleda in Code.gs - date_add_and_subtract to make available the custom fuctions in an add-on it's required to include at least one add-on menu, but this is not working at this time.

Remarks

There are a couple of related GAS issues, please star them:

This answer was cross-posted to Stack Overflow

Related Solutions

Google Sheets – Non-Deterministic Parameters to Custom Function

I've made some research and it seems like an old problem, even from old MS Excel versions.

I think the best/easiest way is to create your own randomization function. This way random value won't be regenerated. You use it the same way as before =plusOne(myRandom())

function plusOne(i) {
  return i + 1;
}

function myRandom() {
  # Some function
  return Math.random();
}

You could also shorten it =myRandom()

function myRandom() {
  # Some function
  return Math.random() + 1;
}

Google-sheets – Enabling script to run as author in a shared Google spreadsheet

Scripts are run as the user who is viewing the spreadsheet, unless they are published as web applications, in which case you can choose to run the script as the spreadsheet owner.

So, in order to let your anonymous users modify the spreadsheet via a script, you need to give them edit rights, or publish your scripts as a web application, and instruct your users to visit the web application.