I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

This is the dbab JSON API for  Database abstraction with google apps script, which is Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j.   One great thing about Google Apps Script is that you can expose an app as a web app, so that whatever you’ve written can be enjoyed by other apps that know how to talk to web apps. In this case, we’ll take Database abstraction with google apps script, expose it as a web app that can serve up JSON and JSONP, and we’ll be able to use all those back ends from any client that can interact with it. In a later post, I’ll show you how to write a VBA native API that uses the dbab JSON API – meaning that with this you can access all these backends directly from VBA with a simple noSQL client. Remember that a Google Sheet can be a backend – so now you can have live interaction between Excel and Sheets. This builds on, and largely supersedes DataHandler REST API Here’s a primer to introduce the concepts

 Starting off You should take a copy of a webapp template as it has all the patterns you’ll need to get started. Since you’ll likely be protecting your webapp with oAuth2, you should head off to the Cloud Console and create an app, and some client credentials. You’ll use these when connecting to your webapp – which you should publish to be ‘run as user accessing webapp’. Next you should set up any APIKeys in the script or userproperties of your webapp as appropriate. Look at the details of the driver(s) you plan to use in  Database abstraction with google apps script and store your apikeys as described. If you are using datastore, you’ll need to set up so that your webapp can authenticate to Datastore with  oAuth2 as described in Datastore driver. This involves setting up an app in the cloud console and getting some credentials. (Note these are different than the ones you create for for authentication to your webapp – those are about allowing a client that will use the JSON API to connect to your webapp). These new credentials are about allowing your webapp to access datastore. Using your webapp Any client that can deal with JSON/JSONP can now use your published webapp to access any of the supported backends. For convenience all parameters and data can be send using GET or POST, but normally you’d use POST for update/save/remove and GET for count/query/get. Here’s the parameters

 parameter  values  purpose
 driver  any supported driver – for example sheet or mongolab  will select the backend driver to write to
 siloid  the tablename  filters the data in the database to the given type
 dbid  the database name  this varies by database. For example for sheets its the spreadsheet ID, for mongolab it’s the database name
 nocache  0 or 1  1 means dont use cache, 0 means use cache if available
 keepid  0 or 1  1 means you want any special keys that the driver adds to make an object unique to be returned in addition to the data
 query  a json string  a data abstraction nosql query such as {“name”:”fred”}. The object can be flattened or deep as you prefer ({“stuff”:{“sex”:”male”}} is equivalent to {“stuff.sex”:”male”} and either is acceptable)
 params  a json string  parameters such as {“sort”:”name”,”limit”:10,”skip”:3}
 action  query|remove|update| count|get|save  what to do
 postdata  the data to be saved/updated to  a json array with the data to be saved. Usually sent in the post body, but can be passed to a get using postdata (subject to maximum url length)
 keys  the unique keys that identify the data to get or update  a json array of the handleKeys returned by a query with keepid=1

 Of course you can create an API that uses this API to further abstract your cline access. That’s exactly what I’m going to do in  VBA API using the Google Apps Script dbab library