How to export / import a Google Apps Script as a file into a new spreadsheet?
Question?
When creating a new spreadsheet, we can easily create a script within it. I like to call it a macro. But when I create an even newer spreadsheet, I can’t find a good way to get my macro!.I actually can see we could probably publish / submit it to the gallery, but that ain’t private.
And then there’s the library “solution”, but that is even more complicated than publishing. Plus I’m not sure this enables sharing with other accounts.
I just wish to save a file and re-use it at will, without needing to copy & paste & rename. My goal here is sharing the script and whoever is going to use it isn’t a programmer. This got to be really simple!
Answer!..
The “library solution” is actually your best bet. It is not as complicated as it sounds, but it takes a little bit of one-time work.
First you create your script in one spreadsheet (let’s call it the Master spreadsheet).
Save a version of the script by clicking File → Manage versions in the script editor, and give your version a name:
Close this dialog, and go to File → Project Properties. Here, find the Project key, which is a seamingly random combination of characters:
Copy or take note of the project key.
When you have created a new spreadsheet and want to reuse the script, go to the script editor and clickResources → Libraries (if you haven’t saved your new script, you will be asked to so now).
In the Find a Library field, paste the project key from the Master project (1) and click Select (2):
This will populate the list with your Master script. Give it a more friendly name (3), and turn onDevelopment Mode (4) (this will allow you to debug the library). Select the most recent version of your script (5).
Now you’re able to use functions from your included library in your new script. Just prepend any function names with the name you specified in (3), so that if you identified your library as MyLibrary
, and your library has a function myFunction
that you want to call, you call MyLibrary.myFunction()
.
When you want to update your library, you simply do so, and save the file. Any scripts that depend on the library will see the updated library code.
I just tried this excercise as a proof-of-concept:
- Set up a library with a function:
function myFunction() {return "Foo"}
- Include it in another script, identifying the library as
MyLibrary
- Called the library function with
Logger.log(MyLibrary.myFunction)
- The log displays
Foo
- Edited the library:
function myFunction() {return "Bar"}
- Re-ran the including script, the log now displays
Bar