10

I have created a standalone Apps Script from Google Drive, but when I try to access it from a Google Spreadsheet, I don't see a way to access the script (even though when I created the script, I created it as a spreadsheet script).

In the spreadsheet, the "Tools->Script manager..." menu item doesn't show my script, nor does the "Tools->Script editor...". The latter has a section for 'recent projects', but it doesn't list my newly created script, nor its associated project.

If I create a new script from inside the spreadsheet (i.e .Tools->Script editor...) and cut and paste the code from the standalone script, it works fine. However, this can only be used in the single spreadsheet -- to use it from another one, I have to cut and paste / go through auth again.

Is there a way to do this without publishing the script to the gallery, which I assume makes it public (I don't have a problem making it public, but it seems like there must be a better way)? The script is here -- it is world readable with the link. It adds a menu to a google spreadsheet that lets you run a query in BigQuery and dumps the results into your spreadsheet.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63

3 Answers3

6

The behavior you describe is exactly how it is intended to work... the script you wrote can only work if it is bounded to a spreadsheet.

The fact that you wrote it in an independent script makes it indeed a standalone app but it can only be executed from within the script editor and in this context an onOpen function like the one you wrote doesn't make sense (it runs on opening a document/spreadsheet, not the script).

For now a script can only be bounded to a spreadsheet by copy/pasting the code, at least a script that you want to use as you do (with a menu and direct function calls and activeSheet calls), maybe one day Google will expand the concept of libraries to make your script function available from different document directly (read also the doc about libraries).

There are actually 2 types of standalone Apps Scripts, the one you tried that runs only from within the editor and webapps that are deployed and run in a browser window by themselves. The latter are always build around a doGet function that represents an entry point for the application. You should read the documentation on the subject to get all the details.

To summarize : if you want to access your spreadsheet using getActiveSpreadsheet() and similar methods then you have to write (or paste) the script in the script editor bounded to the spreadsheet.

If you want to have a standalone script you can also interact with spreadsheets (and other document as well) but you have to access them by their ID and the spreadsheet will only be viewable from another browser window, without link between them, and there will be no "active spreadsheet" nor "active sheet"...

A webapp can of course also do that with the advantage of a user interface that you can adapt to your needs but this is becoming a bit too far from the scope of your question.

Hoping I've made it (a bit) more clear, at least I tried to make it simple ;-)

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • 3
    Thanks for the informative answer. It seems strange, then, that from "Drive->Create->Script" it gives you the option of creating a script for a spreadsheet, but then there is no way of using that script inside a spreadsheet. – Jordan Tigani Dec 17 '13 at 21:32
  • I guess your concern comes from the template in the script editor, these templates are generic , meaning they should be useable in any context (which is practically not possible I'm afraid) To be honest I disabled that feature long ago so I'm not even sure how they look like.(I should have a look from time to time for my personal info :-) btw, thx for accepting. – Serge insas Dec 17 '13 at 21:38
3

I worked this out with Jordan, following the instructions here:

https://developers.google.com/apps-script/guide_libraries

  • First, he had to share his standalone script with me, so I had read access to it (probably setting the script as public read would work too).
  • Then he told me his project key.
  • I took his project key and inserted it in "Find a Library", in the "Manage Libraries" menu.
  • For this to work, Jordan had to export "a version" of his library - so third parties can rely on stable code and upgrade at their own rhythm.
  • As Jordan's script runs automatically on onOpen(), my code had to define a function onOpen() that called his script onOpen(). To do this, the library manager gave me an identifier for the imported library.

Sample code once the library is imported and the library manager gives you an identifier for the library:

   function onOpen() {
     GivenLibraryIdentifier.onOpen();
   }
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • 3
    Thanks, Fh. This is closer to what I was looking for, although there are still a lot of non-intuitive steps, at least it lets me use the code I've written once without cut and pasting it everywhere. – Jordan Tigani Dec 18 '13 at 02:43
  • 1
    maybe closer to what you expected but it still needs a a code in your spreadsheet bounded script. Libraries (and I suggested to read the doc about it) are useful when developing complex functions or to handle code versioning but it doesn't mean your standalone script is useable "as it is", you still need a script to actually "call" the library functions. The sample code shown in this answer shows clearly what I mean ;-) – Serge insas Dec 18 '13 at 06:55
3

You can also use installable triggers. If you install an installable trigger (such as onOpen) from your standalone script into the document, it gets similar privileges as if it were a simple trigger.

And better yet, when the installable trigger calls its function from your standalone script, it executes WITHIN your standalone script! So it can call any other function in your standalone script. Its Logger.log() entries can even be viewed from your standalone script, even though it was executed from your sheets document.

So basically, if your script installs an onOpen() trigger tied to one of its functions, it's as good as if your Sheets document had required() your whole standalone script file.

(Assuming the owner of the script has edit privileges to the file.)

Amaroq
  • 31
  • 1