How can I give my programmatically created Google Spreadsheets a Sidebar?

Question

Background:

1. I have a Script Project, tooled up using node-google-apps-script
2. I have a Rails app that uses the Script Project to create Spreadsheets using the REST Execution API. It does this by cloning a template Spreadsheet.
3. My Rails app also occasionally pushes data to the Spreadsheet, using the REST Execution API
4. The Script Project is published privately using Publish>Deploy as API Executable..., and the Rails App uses the API Executable ID to talk to it.

The above all works reasonably well, and development is not too onerous.

Now I want the generated spreadsheets to have a sidebar. The alternatives I've discovered so far each seem problematic:

Alternative 1: Publish private Add-On

I'm aware that I can get my client to make me an admin of their google apps domain, and then I can publish the Script Project as a domain-restricted add-on. This alternative seems like it will be onerous both for development & usage:

• add-ons take up to an hour to publish. Publishing a version of the Script Project is now two manual routines which can't be automated
• as best I can tell, I can't programmatically add the add-on to the spreadsheet. It has to be installed in each spreadsheet before use.

Alternative 2: Give the Template Spreadsheet a Bound Script

I could give the Template Spreadsheet a Bound Script. Apparently, that will be copied. Bound Scripts can't be tooled with node-google-apps-script, so I will be stuck using copy & paste updating. Updates to the script would require manually updating each existing spreadsheet! However possibly I could make the sidebar a skeleton that pulls its actual content from the Script Project.

Is there a better way?

Answer 1

Essentially what you can do is tie all the sidebar creation part of the programming to the library that you have created. In the templated spreadsheet that you use, you should only need to do the following:

1. Add your library to the container bound script using the Project Key of the library script.
2. Add a simple onOpen function to create a menu that then calls the library create sidebar function.
3. Since all of the server side functions that might be called by the sidebar will be contained in the library, you will need to add a function handler that calls the correct function in your library to pass off to the sidebar when using google.script.run For example:

Library Functions:

function getSomeNum() {
  return 3;
}

function doSomething(e) {
  return e.num * 2;
}

var runFunction = {
  getSomeNum : getSomeNum,
  doSomething : doSomething
};

function runHandler(functionName, parameters) {
  return runFunction[functionName](parameters);
}

Container Bound Handler:

function runHandler(functionName, parameters) {
  return MyLibraryIdentifier.runHandler(functionName, parameters);
}

Example google.script.run call from sidebar:

function onLoad() {
  google.script.run
    .withSuccessHandler(processNum)
    .runHandler("getSomeNum");
}

function processNum(num) {
  var event = {
    num : num
  };

  google.script.run
    .withSuccessHandler( function(retNum) { alert(retNum); } )
    .runHandler("doSomething", event);
}

Keep in mind that any user that needs to utilize this sidebar must at least have view access to the library. What I ended up doing was deploying as an API Executable for anyone and then setting the sharing of the script to public-view only, but must have link. It's the only thing that seemed to work to get it to multiple users.

Another downside is when updating the code of the library, the container bound scripts will not be updated unless you change the library version they are utilizing. Meaning the user would have to actively look for newer library versions. When Updating the API executable, the menu gives you the impression that you can just update the current version, but I haven't had any luck in to actually getting it to reflect the changes (perhaps I just wasn't patient enough).

This should at least get you a base line of where to begin your testing.