13

I fear this may have an obvious answer, but I've been knocking my head over this and can't find it!

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 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!

Any insights?

9

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:

Screenshot of the Manage Versions dialog

Close this dialog, and go to File → Project Properties. Here, find the Project key, which is a seamingly random combination of characters:

Screenshot of the Project Proerties dialog

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 click Resources → 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):

Screenshot of the Manage Libraries dialog

This will populate the list with your Master script. Give it a more friendly name (3), and turn on Development 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

So it really is quite simple after the initial setup. See this folder on my Google Drive for a real example.

Also see the Google documentation for libraries.

  • 2
    I think I wasn't clear enough in my question (I had a link showing how to do the libraries, I've read all my links and lots more. I think I've read all the docs), and I omitted a crucial info, sorry about this. I know it isn't "all that complicated" to do it, but my main goal here is to share the script and this is complicated enough to whom I'd share with. Simple has to be simple. I'll edit the question. But thanks for all the consideration! Also, I can't see anything on your folder. Just 2 empty files. Even after opening them. – cregox Mar 7 '14 at 15:07
  • Ah, I totally missed your link to @JacobJanTuinstra's already excellent answer on how to create libraries. My bad. You have read the docs, no doubt. But I'm not sure I understand what you mean by share, then. Do you want to share your script with another user? – Vidar S. Ramdal Mar 7 '14 at 23:27
  • As for the files in my folder, yes, they seem to be blank. But that's because the spreadsheets are blank - there's nothing to them except the scripts. In order to use my spreadsheets, you must click the link above, click on one of the (blank) files, then Open, then File -> Make a copy, so that you have a copy in your own Google Drive. This should also copy along the attached script files. – Vidar S. Ramdal Mar 7 '14 at 23:29
  • Ok, after making a copy I finally could see the script. Still there's nothing simple there. Yeah, share with another user. Copy & Paste works fine, and it's simple enough, but it's too cranky. I still have to paste into a file and the other user copy from it! I hardly can understand why we can't use files... – cregox Mar 7 '14 at 23:58
2
+50

Short Answer

Publish your script as an add-on. Bear in mind that you could make it public, unlisted or private, shared with a Google Group.

Explanation

The Script Gallery was replaced by Add-ons that were added on 2014.

In order to publish an add-on you should paid a 5 dollars fee to be able to publish an add-on to the Chrome Web Store. If you will make the add-on public it should be reviewed by Google before making available to the public but if you make it private restricted to a Google Group it will be available after few minutes.

An alternative to publish your add-on is to use the add-on test feature but this has some limitations like that it's not possible "to test" triggers which depending on your script could be a significant limitation or not.

Using an add-on is better than using a library because the libraries could slow down your spreadsheet but specially because, from the end-user point of view, it's easier to use add and use it than adding a adding a script project, add the code required to call the library and adding the library itself.

An advantage for the add-on creator could be the fact that the add-on users will not able to view the add-on code from the add-on itself.

By the other hand, it requires some images with specific format and size requirements that could be considered too complicated for certain uses cases and we could finally consider that doing a copy/paste of our script or making a copy of the spreadsheet (document/form/presentation) is the straightforward solution for those use cases.

References

2

You should try Google Clasp which is talking about

The Drive ID of a parent file that the created script project is bound to

Did not tried but looks like there are commands for retrieving project script code, create and update :

  • Pull: Fetches a project from either a provided or saved script id. Updates local files with Apps Script project
  • Push: Force writes all local files to the script management server
  • Create: Creates a new script project (with optional project parent Id. The Drive ID of a parent file that the created script project is bound to)
  • 2
    thanks for this contribution, Masson! i find it quite interesting, even if i won't be able to test it any time soon. :) in any case, could you elaborate more on specifically how to resolve the question with clasp? it's a good idea to keep answers self-contained for future reference, in case links get broken or change in significant ways. – cregox Aug 30 '18 at 13:49
1

Halfway is to download the files from your project.

Solution #1

I found a tool to achieve this:

http://googleappsscriptexport.trototype.com/ by kerem tiryaki

  • You have to give access to your drive
  • to find the script, you'll have to copy the key of a script:

enter image description here

Disadvantage

GoogleAppsScriptExporterForAll now has access to your Google Account (have to delete it manually)

Solution #2

I also use this add-on to integrate Git-Hub and Google Sheets Scripts: https://chrome.google.com/webstore/detail/google-apps-script-github/lfjcgcmkmjjlieihflfhjopckgpelofo

After this addon is installed, you need to:

  1. Log In & Push your code to GitHub (the button will appear in editor)
  2. Download your code from GitHub if needed.
  3. You also can pull the project from GitHub (average user won't do it).

Disadvantage

The user still has to open Script Editor and copy the code.

Advantages

  1. Quique implementation. Do not have to play with add-ons
  2. Easy for a user to copy one script, instead of multiple files.

Next Steps

The next steps for me will be:

  1. Merge my *.js files into single line file. Successfully used https://jscompress.com/ =)
  2. Sharing the one file with users (posting the script to GitHub or e-mailing them directly)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.