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?

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) — Serge insas
– Serge insas, Commented Aug 3, 2012 at 21:36
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. — Srik
– Srik, Commented Aug 4, 2012 at 16:09
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. — Justin Tilson
– Justin Tilson, Commented Aug 9, 2012 at 20:16
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? — Justin Tilson
– Justin Tilson, Commented Aug 9, 2012 at 21:06

Scott S · Accepted Answer · 2017-04-20 00:18:30Z

17

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.

answered Apr 20, 2017 at 0:18

Scott S

1791 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Doug Leary Over a year ago

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.

TheMaster Over a year ago

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

jtlai · Accepted Answer · 2015-01-31 02:14:58Z

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!

edited Jan 31, 2015 at 2:14

answered Jan 31, 2015 at 2:04

jtlai

7897 silver badges7 bronze badges

1 Comment

Kechit Goyal Over a year ago

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

double-beep · Accepted Answer · 2020-10-10 12:46:04Z

0

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.

edited Oct 10, 2020 at 12:46

double-beep

5,67019 gold badges43 silver badges50 bronze badges

answered Oct 8, 2020 at 19:26

aNewb

3082 silver badges12 bronze badges

Comments

Ajay Mathew · Accepted Answer · 2022-06-25 10:13:32Z

0

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

answered Jun 25, 2022 at 10:13

Ajay Mathew

213 bronze badges

Collectives™ on Stack Overflow

Custom functions not working in spreadsheet

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related