Google Apps Script custom functions for Google Sheets outside of Code.gs?

Question

I'm building an add-on. I've noticed I can have multiple html files. Can I also have multiple code files, that is, more than just Code.gs?

If so, how to I cause them to be loaded? If not, how does Sheets know whether a function defined in Code.gs can be used in a formula or not?

LATER

So it has been suggested, seeing that the script editor allows you to create extra gs files, that simply having the custom function in another script file within the add-on is sufficient. However, I'm not having a lot of success with that.

So apart from my Code.gs, I have Medical.gs with the following in it:

/**
 * Gargling function for digital sore throat
 *
 * @param {object} input The thing to gargle
 * @return the gargled parameter
 * @customfunction
 */
function GARGLE(input) {
  console.log("GARGLE");
  return "GARGLE";
}

Even with the JSDoc header, the custom function is invisible when, from the Run menu in the Script Editor, I select the "Test as add-on" and run the prepared test spreadsheet. In the spreadsheet I can do =GARGLE() as much as I like, but I still get the standard #NAME? error.

LATER STILL

So I put a similar custom function into a fresh standalone script project's Code.gs. When tested as an add-on the target spreadsheet can't even see this one. Going slightly nuts here.

You can have any number of script files in your Spreadsheet file Project. You just go to code editor, File -> New -> Script file. Each function is defined by a name. You can't have more than one function with the same name. Then you can execute a function by name(...) where the ... may be parameters. You could execute the same function as a custom funtion in a spreadsheet cell by entering =name(...). Its the same for a web app, the Project knows the names off all the function in the multiple script pages. — TheWizEd
@beano (the kids loved that comic back in the day) Yeah the function works fine in certain contexts but not in others. If I have an AddOn with a single Code.gs file and that function in it and then test it as an addon from the script editor, it doesn't get seen by Google Sheets. Maybe I should reboot my laptop ... the usual response when things go anomalous. — bugmagnet
@TheWizEd I was rather wanting to develop a standalone rather than a bound. — bugmagnet

Alan Wells Alan Wells · Accepted Answer · 2019-04-02T13:23:17

There is an open issue concerning custom functions not working with the menu item

test-as-add-on

https://issuetracker.google.com/36763437

Currently you must use a script bound to your Sheet in order to test your functions.

Google Apps Script custom functions for Google Sheets outside of Code.gs?

1 Answers