Custom functions not working in spreadsheet

Question

I've followed the instructions from: https://developers.google.com/apps-script/execution_custom_functions to create a custom function.

Neither my functions or even cut and paste from the tutorial works: the functions aren't available to my spreadsheets. I've tried saving a version and publishing as a web app - with no change. I tried Google Chrome and Firefox, same result. Am I missing something really obvious here?

Ardenardency

have you tried to run it once using the 'run' command in the script editor ? (you don't need to publish nor make version when using custom functions) – Suavity

Did you write the script from within your spreadsheet or as a standalone script ? You should write it within your spreadsheet. Also, as Serge mentioned, you should run it from the script editor once so that any authorization necessary can be provided. – Heterolecithal

When I run the script, it executes top to bottom, going straight into functions without me calling them. This fails because arguments were never passed into the function(s). If the code was: function doStuff(arg1, arg2) { if (typeof arg1 != "number") { throw "arg1 must be a number"; } var out = 1 + 1; return out; } it would fail on the throw line. This doesn't make sense to me because I never actually called the function. – Ardenardency

The lights are coming on (slowly). I found the dropdown which lists the function you want to run or debug. This explains why a function is getting called. It still doesn't give me a chance to pass in arguments (that I can see). It is possible to pass arguments into functions through either the run or debug menus? – Ardenardency

@JustinTilson no. but you can put statements at the top of the function to initialize values for the argument variables. Comment them out once you're done testing. – Hellen

or maybe just refresh the spreadsheet and try again :-) – Eufemiaeugen

Answer 1

You need to have a comment with @customfunction in it above your custom function. Google Sheets will then add you custom function to the autocomplete menu.

Cleghorn

It would be REALLY EXTREMELY NICE if this were in the help page for custom scripts, which currently just says write a function in the script editor, save it, and Bingo you can now use it by entering =myFunction() in a cell. Because no, you can't use it until after you've searched around and found something like this StackOverflow page with this helpful bit of advice. – Aquacade

@DougLeary I'm sure it was and it is in the custom function help page. – Pelayo

Answer 2

Not sure if this is what you ran into...but what happened to me is, I didn't see my custom function in auto-complete and I thought it's not working. But if you just type =double(A1) in a cell (using the official example, assuming there is something in A1...), it will compute!

Phylactery

Same thing happened to me. Did you manage to get it working with autocomplete? – Phyllotaxis

Answer 3

Does your Chrome Popup setting determine whether the customFunction autocompletes when you enter in in a cell? If so how would you add it as an allowed exception in settings? Enable autocomplete is checked under the tools menu in the spreadsheet.

My function works fine if run from the script editor but does not autocomplete when =myCustomFunction or =myCustomFunction( entered in a cell.

Attenweiler

Answer 4

You will need to add JS-Docs before your function to make it work. https://developers.google.com/apps-script/guides/sheets/functions#autocomplete

/**
 * Divides the input value by 2.
 *
 * @param {number} input The value to divide.
 * @return The input divided by 2.
 * @customfunction
 */
function HALF(input) {
  return input / 2;
}

Soke