A long time back now, I showed how to publish and prettify source code and scriptdb contents using Google Apps Script. This allows you to embed code or data samples in blog posts and so on.
For example, below is the Google Apps Script code to do it, being shown live from the GAS IDE, using this link
ScriptDB as a non-Google Apps Script noSQL database
It can take a number of sources aside from Google Apps Script (such as gist, any url) and can return various formats, and select specific functions. One of the things it can do is to publish data inside a scriptDB. It occurred to me that with a minor tweak I could use it to query scriptDB (using the regular scriptDB query by example) and return JSON selected data to Excel, or any app that would know what to do with JSON – meaning that we’d have a ready made noSQL database.
Test database
For testing I’ll use the scriptDB in this example showing how to use multiple databases. I’ve created a merged database and embedded it using the link below
Query by example
It would be useful if we could selectively take results from that scriptDB. That would give us a noSQL database to be used as reference outside of Google Apps Script. Well – all I have to do is pass the JSON that describes the query I want to make, In this case I want all records for customer named john, so I can use scriptDB query by example language and pass it as a parameter
Prettification
https://storage.googleapis.com/toasty/p/gaspubcontainer is just a prettification wrapper for the Google Apps Script webapp that actually does the work. Below is that wrapper code (it’s being used to display itself)
Altogether now
So if you want to get this data, unadorned, into a web app you can simply call the underlying Google Apps Script directly giving it a type of ‘jsonp’ – adding a callback if you need one.
and here’s the source code
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <title>scriptDB data input - ramblings.mcpher.com</title> <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" /> <link rel="stylesheet" type="text/css" href="http://xliberation.com/cdn/css/d3direct.css"> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> (function () { google.load("jquery", "1"); google.setOnLoadCallback(function() { initialize().then ( function (control) { renderScriptDb(control); }); }); function renderScriptDb(control) { $('#chart').text(JSON.stringify(control)); } function initialize() { var s = []; s.push ("https://script.google.com/a/macros/mcpher.com/s/AKfycbzhzIDmgY9BNeBu87puxMVUlMkJ4UkD_Yvjdt5MhOxR1R6RG88/exec") s.push ("?type=jsonp"); s.push ("&source=scriptdb"); s.push ("&module=accounts"); s.push ("&library=useMultipleDB"); var query = {data:{customer:{name:"john"}}}; s.push ("&query=" + encodeURIComponent(JSON.stringify(query))); return getPromiseData (s.join('')); } function getPromiseData(url,proxyUrl){ var deferred = $.Deferred(); var u = proxyUrl ? proxyUrl + "?url="+encodeURIComponent(url) : url + "&callback=?"; $.getJSON(u, null, function (data) { deferred.resolve(data); }) .error(function(res, status, err) { deferred.reject("error " + err + " for " + url); }); return deferred.promise(); } })(); </script> </head> <body> <div><h2>Test scriptdb query access</h2> <div id="chart"> </div> </body> </html>
For more stuff like this see Excel Liberation