0

Is there a way you can setup a Google Script project to be accessible from any and all documents in the owner's drive?

For example, I end up writing a few custom functions for most of my spreadsheets that Google Sheets is missing, but I don't want to have to create a Script container attached to each document, copy the code from one project over to this new project, and then use it. Not only is it cumbersome, but it's very difficult to organize if a change needs to be made.

I tried using my "master" project as a library on a new sample sheet I made, but the functions in the library are inaccessible from within the sheet, so that's a no-go. As I understand it, publishing and Add-on or Web-app requires making an interface which is unnecessary for my use-case, as I'm just trying to call functions from within the sheet. Even a rudimentary interface would just slow the process down as I'd have to manually load up the add-on and find whatever function I need to use, and click on it.

Let's say I have a project that just contains:

/**
 * Returns value plus one.
 *
 * @param {num} Input the value to add one to.
 * @return Result.
 * @customfunction
 */

function addOne(input) {
  var result = input + 1;
  return result;
}

I find myself needing to "add 1" to the value of a cell constantly from many different spreadsheets which may or may not be related in any way. I'd like to be able to call this function from my personal script file from any spreadsheet simply by entering =addOne(Sheet1!A1).

Is this possible?

vashp2029
  • 25
  • 5
  • Did you even try to Google? See [add-ons](https://developers.google.com/apps-script/guides/bound#add-ons) and this similar [question](https://stackoverflow.com/questions/50811046/deny-granting-permissions-in-my-own-google-scripts-everytime#comment88634175_50811046). –  Jun 13 '18 at 17:43
  • I did try that initially and I was getting an error asking me to include an HTML script in the project (which I assumed was for a front-end), but after giving it another try, it seems to at least let me get to the publish prompt. However, I see that Google charges a $5 per 20 uploads of web addon, even for private use. That's not really feasible either since I'm constantly adding and changing my custom functions. – vashp2029 Jun 13 '18 at 22:48
  • Use [clasp](https://github.com/google/clasp) then, or turn your code into a [library](https://developers.google.com/apps-script/guides/libraries). –  Jun 13 '18 at 22:57
  • I already did that as I stated in my original post, but library functions are not accessible directly from inside the sheet. There is a [workaround](https://stackoverflow.com/questions/44784024/using-google-apps-script-libraries) of sorts but it runs into the issue that I had initially, which is that each new document needs to have a script container which activates the library to be used from within the document. – vashp2029 Jun 13 '18 at 23:27
  • 1
    You want to put the result from Google Apps Script at all spreadsheet without using the bound scripts and addon. If my understanding is correct, how about using [Web Apps](https://developers.google.com/apps-script/guides/web)? At first, for example, it deploys Web Apps which returns XML values. Then it retrieves the returned value to the spreadsheet using the url query and ``IMPORTXML()``. In this case, this can be used for all spreadsheet you use. If this was not what you want, I'm sorry. – Tanaike Jun 14 '18 at 00:06
  • @Tanaike I ended up publishing it as an add-on and just paying the $5 figuring the one-time fee is worth saving the time to figure out a work-around. Your solution seems like it would likely work too, but maybe someone else can give it a shot and respond with an update. Thanks for the help! – vashp2029 Jun 14 '18 at 23:40

0 Answers0