Keeping all those Google Apps script ‘shortcuts’ under control

I don’t know about you, but I have trouble keeping all my various Google Apps Script shortcuts and keys (links to workbooks, links to content service queries, library keys and so on) under control and organized. I think that previous posts about using the scriptDB as a lockbox, using Google Apps Script as a jsonP proxy and various others showed the power of ScriptDB for sharing data both inside and outside the Google Apps Script environment.

On the Excel Liberation site I have a lot of examples of Google Apps Script, and use the content service to provide data in jSon format to Excel queries, and it was getting hard to organize. So of course my solution is to create another rest API to describe all the details of scripts and workbooks i have published. This follows along the lines of somewhere to keep those api keys

This will return published scripts and workbooks, along with their public links, links to the source code, and project keys along with a few other useful things. The good thing about this is that we have all the details about all shared workbooks and scripts accessible by a simple name, by anything that can deal with jSon.

It takes the optional parameter ?entry=name. No parameters will give all public scripts

Here’s the response for an example entry, scraperwikiquery.

    “status”: {
        “code”: “good”,
        “reason”: “scraperwikiquery found in your stuff”
    “results”: [
            “siloId”: “scraperwikiquery”,
            “help”: “for details see”,
            “timeStamp”: 1351784852773,
            “myStuff”: {
                “share”: “public”,
                “e”: “shortname=,[limit=0],[fielda=,fieldb=,…]”,
                “source”: “public”,
                “doGet”: true,
                “description”: “will get data associated with a scraperwiki query where all the field tests given are true”,
                “publish”: “”,
                “writeup”: “”,
                “type”: “script”,
                “url”: “”,
                “key”: null,
                “doPost”: false
            “userStamp”: “”

The code for this is rehash of previous posts. As usual you’ll need the mcpher shared library.

First, create a public facing script, and publish it. I’m calling this publicStuff.

 * return the stuff from the db associated with the e.parameter.entry 
 * @param {object} e the event parameters
 * @return {String} the rest response
function doGet(e) {
       return ContentService
            .createTextOutput(getStuffContent (e))
            .setMimeType(ContentService.MimeType.JSON); ;    

 * myStuffDb return the shared DB for myStuff
 * @return {ScriptDbInstance} the db
function publicStuffDb() {
  return ScriptDb.getMyDb();

 * return the stuff from the db associated with the e.parameter.entry value
 * @param {String} siloNamethe the locker name
 * @return {ContentService} the rest response
function getMyStuff (siloName) {
  var q;
  var db = publicStuffDb();
  var response = {status: {code:”bad”, reason:”unknown error”}, results:null};

  q=  siloName ? mcpher.scriptDbSilo(siloName,db).query() : db.query({});
  if (q.hasNext()) {
    response.status.code = “good”;
    response.status.reason = siloName + ” found in your stuff”;
    response.results =  [] ;
    while (q.hasNext()) {
  else {
    response.status.reason = siloName + ” has no data”;
  return response;

/** react to a doGet(e) to return public data
 * @param {object} e as passed to doGet() (show or oauth)
 * @return {String} the json string content
function getStuffContent (e) {
   // e.parameter.entry – optional the lockBox entry name
  var content ;
  var entry =  e.parameter && e.parameter.entry ? decodeURIComponent(e.parameter.entry)  : null;
  try {
          content = JSON.stringify(getMyStuff(entry));
  catch (err) {
        content = JSON.stringify ( { error: err } );

   return content;

function testPublicStuff(){
  Logger.log ( getStuffContent(  { parameter: null} ) );

Next, in  a different script, create a module to populate everything you want to make public and run it once off, using the first script as a library. here’s a snippet…

function createScriptEntry(name, o) {
  return mcpher.createStuff (name, publicStuff.publicStuffDb(), o );

function createScriptLibraries() {

    { url: “”,
      key: null,
      source: “public”,
      publish: “–LHGMnWThd3cizAxw6f/edit”,
      share: “public”,
      e: “[entry=]”,
      type: “script”,
      doGet: true,
      doPost: false,
      description: “rest entry point to get rest library contents”,
      writeup: “”
    { url: “”,
      key: “MEQ3tE5y5_cTOAgUbUKSIAiz3TLx7pV4j”,
      source: “public”,
      publish: null,
      share: null,
      e: null,
      type: “script”,
      doGet: false,
      doPost: false,
      description: “mcpher shared utility library”,
      writeup: “”


(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();

Author: bm082975

Leave a Reply

Your email address will not be published. Required fields are marked *