how to share same app-script between multiple (different) spreadsheets in google spreadsheets

Question

First of all I am after 3hours of reading docs about google cloud, publishing, projects and so on. After many tries i realized that i am missing something so here is my question.

I have two spreadsheets, lets, call it "prices" and "costs". What i want to achieve is that they share same app scripts and if I change one script, since it is shared by both it will automatically change in secons.

So i did create an app script that returns a string (just for simplicity) in a cell and called it STRINGFUNCTION(); Is is created in PRICES spreadsheet. My goal is to have it working in COSTS file without typing it manually. I expected that if i click Resources > Cloud Platform Project and add both app scripts from both spreadsheets to the same project it will work automatically. Well, it wont - if i write in a cell =STRINGFUNCTION() in PRICES it works fine, and in COSTS - it says that function in not known.

How can I achieve that so it works between my both files and they share same function if they are both in same project?

Answer 1

You can do this by putting your code in a standalone script and use it as a Library.

https://developers.google.com/apps-script/guides/libraries

Here's how to do this:

1. Go to https://script.google.com/ and create a new project
2. Replace the code with the code for your custom functions and save
3. Click "Untitled Project" and give it a name to use for accessing the library
4. Click the blue Deploy button and choose New Deployment
5. Click the gear beside Select type and choose Library
6. Enter a description and click Deploy (this is what makes your Library available from other scripts)
7. Go to Project Settings and copy the script ID
8. Go back to your Spreadsheet, open the script for your sheet, click + beside libraries and paste the script ID and click Add
9. Remove the code for the custom functions if necessary and you're going to create functions to pass through the library functions

If your function is called STRINGFUNCTION(), create a function in the local apps script like this:

function STRINGFUNCTION(parameter) {
     return libraryName.STRINGFUNCTION(parameter);
}

Do that for each function you want to use from the Library and save your code. You will need to authorize permission for the script if you haven't already. Now the custom functions should be available in your spreadsheet. Copy and paste this "pass through" script to each sheet where you want to be able to access the custom functions from the Library.

Custom functions inside a Library cannot be called directly from a sheet. You only have to set up this local script to pass through the functions, once. Now if you update the script in the Library, the updated functions will be available to the sheet. You will need to do the deploy set each time you make a change to the Library to publish the changes. If the changes aren't working in the sheet, click on the Library in the local script and make sure the version is Head or the latest version you published.

Answer 2

You can only bind a Google Apps script file to one document at a time. Apps script doesn't allow you to edit the contents of a .gs file on the cloud from inside another Apps script file, as trying to fetch:

var data = DriveApp.getFileById('script-id').getAs('application/vnd.google-apps.script');

will return the error:

No item with the given ID could be found, or you do not have permission to access it.

You could however bind the script to one spreadsheet, for example say the 'COSTS' spreadsheet, and create a second sheet within the spreadsheet for 'PRICES' with all the relevant function calls. In the separate 'PRICES' spreadsheet you could then use the build-in IMPORTRANGE formula to get the data from the 'PRICES' range from the first spreadsheet that has the bound script.