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