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

https://script.google.com/a/macros/mcpher.com/s/AKfycbzLXr1aQKQVK2imlIJp9C6m_HEBAmLBiYM28mfnLn_3oIe3c2kN/exec

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 ramblings.mcpher.com",
            "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": "https://script.google.com/a/macros/mcpher.com/s/AKfycbwY-jmsNFu4tori8pxu_J4qmSu467yFLzlzKgG-Vfv7jOOLa5A/exec",
                "writeup": "https://ramblings.mcpher.com/from-vba-to-google-apps-script/scraperwiki-data-to-google-docs/",
                "type": "script",
                "url": "https://script.google.com/d/18AvCG0-keshNutz2leRArTmTM45fGAsxrrqkJLD600Xrq7bLTvzCS6qg/edit",
                "key": null,
                "doPost": false
            },
            "userStamp": "bruce@mcpher.com"
        }
    ]
}

 

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()) {
      response.results.push(q.next());
    }
  }
  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) {
   //parameters
   // 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() {


 createScriptEntry("restserver",
    { url: "https://script.google.com/a/mcpher.com/d/11ElSkBvx4DMqGncy8sxZKNXZrnlrpLhTuyVL9N0tNm95nGwkGVoflVD8/edit",
      key: null,
      source: "public",
      publish: "https://script.google.com/d/18mwQcRdgeVXKguRJ2F-eTCm2tzunCzhzS9RJ--LHGMnWThd3cizAxw6f/edit",
      share: "public",
      e: "[entry=]",
      type: "script",
      doGet: true,
      doPost: false,
      description: "rest entry point to get rest library contents",
      writeup: "https://ramblings.mcpher.com/from-vba-to-google-apps-script/google-apps-script-rest-library-migration/"
	  }
  );
  createScriptEntry("mcpher",
    { url: "https://script.google.com/d/1hhJ8M6z99XccL8WRq2d24-pWGwhq8EfYNaQIQV0CEe5gE1HbBoF4X9W_/edit",
      key: "MEQ3tE5y5_cTOAgUbUKSIAiz3TLx7pV4j",
      source: "public",
      publish: null,
      share: null,
      e: null,
      type: "script",
      doGet: false,
      doPost: false,
      description: "mcpher shared utility library", 
      writeup: "https://ramblings.mcpher.com/from-vba-to-google-apps-script/creating-a-google-apps-script-library/"
	  
     }
  );




//etc.....
}

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.