I was talking to some cockroachDB developers at an event lately and discovered that many of them were, like me, using ElasticSearch for full text and complex querying in the cases where SQL was not […]
Here’s how to set up a service account to access your firebase data base in admin mode. It’s quite disjointed in the docs, so here it is from start to finish. The console Just as […]
Like most Google Services, there are various quotas that apply to scriptDb. One of these is the maximum object size. Here’s how to split up large arrays to help get round that. In this case, I’m not […]
The JavaScript client is very similar to A VBA API for scriptDB, so reading that will give you a more detailed flavor of what can be done. The implementation is very different though, and there are […]
The tests that are referred to in scriptDB API for JavaScript are implemented in https://storage.googleapis.com/toasty/t/gas/scriptdbapi.html. Code is below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
<!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <title>scriptDB data input - ramblings.mcpher.com</title> <link rel="stylesheet" type="text/css" href="https://storage.googleapis.com/toasty/cdn/css/d3flights.css"> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript" src="https://apis.google.com/js/client.js"></script> <script type="text/javascript" src="js/scriptdbapi.js"></script> <script type="text/javascript" src="js/firsttime.js"></script> <script type="text/javascript" src="https://storage.googleapis.com/toasty/cdn/js/ccookie.js"></script> <script type="text/javascript"> /* v1.0 */ // do various test excercises on cScrptDbCom class (function () { google.load("jquery", "1"); google.setOnLoadCallback(function() { // these create cookies with credentials for scriptdbAPI, // They only need to be run once for a pc, so they should be in some different script firstTimeReadonly(); firstTimeTest(); // copy a couple of classes from the primer database to the test one testCopy() .done (function() { // do a query------ var dbCustomer = getScriptDb("VBAParseCustomers","dbTest"); dbCustomer.getObjectsByQuery({country:"United States"}) .done (function (data,cob) { renderScriptDb(data.results,'#countryquery'); }) // look up date in another table based on data in another var dbData = getScriptDb("VBAParseData","dbTest"); // restrict to paricular customers dbCustomer.getObjectsByQuery({country:"United States"}) .done (function (data,cob) { $('#matchquery').html(''); for ( var i = 0 ; i < data.results.length;i++) { printTransaction (dbData,data.results[i]); } }) }); function printTransaction (dbData,result) { dbData.getObjectsByQuery({customerid:result.customerid}) .done (function (transaction,cob) { if (transaction.results.length) { $('#matchquery').append(JSON.stringify( { country:result.country, name:result.name, transaction:transaction.results }) + '<br>'); } }); } }); })(); function testCopy() { // do the copies simulatneously var dd = dbCopy ("primer", "dbTest", "VBAParseData"); var dc = dbCopy ("primer", "dbTest", "VBAParseCustomers"); // report when done showCount (dd,'#countcopydata'); showCount (dc,'#countcopycustomers'); // we can return a promise for them all being done return $.when(dd,dc); // show counts function showCount (promise, elemID ) { promise .done (function(pData,cob) { cob.getCount() .done (function (data) { renderScriptDb(data.count,elemID); }) .fail(function (error) { renderScriptDb(JSON.stringify(error),elemID); }); }) .fail(function(error) { alert (JSON.stringify(error)); }); } function dbCopy( source, target , cls) { var dbSource = getScriptDb(cls,source); var dbTarget = getScriptDb(cls,target); var d= $.Deferred(); //delete everything in source db of this class if (!(dbTarget && dbSource)) { d.reject ("scriptdbs not opened"); } else { dbTarget.deleteObjects() .done( function() { // get the whole thing dbSource.getAllMatchingQueries() .done (function (data){ // copy to target dbTarget.createObjects(data); // clear anything in batch not yet done. dbTarget.finalFlush() .done(function(data,cob){ d.resolve(data,cob); }) .fail(function(error,cob){ d.reject(error,cob); }); }) .fail ( function (error,cob){ // failed to do a query alert (JSON.stringify(error)); }); }) .fail( function (error,cob){ // didnt manage to delete it alert (JSON.stringify(error)); }); } return d.promise(); } } /* display contents of some object at given element * @param {object} control some object * @param {string} id the id of the element * @return {null} */ function renderScriptDb(control,id) { $(id).text(JSON.stringify(control)); } </script> </head> <body> <div> <h2>Test scriptdb query access</h2> </div> <div><h2>after copy<br></h2> <div><b>Count on customers</b><div id="countcopycustomers"></div></div> <div><b>Count on data</b><div id="countcopydata"></div></div> <div><b>object query</b><div id="countryquery"></div></div> <div><b>match query</b><div id="matchquery"></div></div> </div> </div> </body> </html> |
These are the results you should see, once the copy has completed
|
1 2 3 4 5 6 7 8 9 10 11 |
Test scriptdb query access after copy Count on customers 100 Count on data 308 object query [{"region":"Castilla y León","zip":"B9L 8T4","city":"Segovia","country":"United States","customerid":97,"email":"nascetur@orciUt.net","address":"P.O. Box 271, 4700 Tellus St.","objectId":"S333422122829","name":"Nash","company":"Nascetur Ridiculus Institute","coordinates":"53.60934, 6.94959"},{"region":"GO","zip":"D51 4LF","city":"Luziânia","country":"United States","customerid":85,"email":"sit.amet@congueturpis.com","address":"2195 Scelerisque Road","objectId":"S333422122822","name":"Hammond","company":"Consectetuer Rhoncus Nullam Industries","coordinates":"-49.77372, 82.53103"},{"region":"Principado de Asturias","zip":"11713","city":"Oviedo","country":"United States","customerid":37,"email":"quis@anteipsum.org","address":"Ap #770-5598 Faucibus Rd.","objectId":"S333421005208","name":"Lyons","company":"Sed Dolor Fusce Foundation","coordinates":"-36.59556, -133.78963"}] match query {"country":"United States","name":"Hammond","transaction":[{"customerid":85,"objectId":"S333421434339","value":2575,"quantity":5,"date":{"__type":"Date","iso":"2013-07-03T00:00:00.000Z"},"invoiceid":161}]} {"country":"United States","name":"Nash","transaction":[{"customerid":97,"objectId":"S333422178785","value":4800,"quantity":10,"date":{"__type":"Date","iso":"2013-06-02T00:00:00.000Z"},"invoiceid":282},{"customerid":97,"objectId":"S333422758773","value":2800,"quantity":5,"date":{"__type":"Date","iso":"2013-06-30T00:00:00.000Z"},"invoiceid":391}]} |
For help and more information join our […]
Here is the javascript implementation for the scriptDB API. It needs jQuery.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
* methodType = "GET"; */ var dataType = "json"; if(!postData) { u += "&callback=?"; dataType = "jsonp"; } var d = $.Deferred(); var headers = oa2 ? { Authentication: 'Bearer ' + oa2.getAccessToken() } : {}; // application headers /* headers["X-scriptDb-Application-Id"] = pPackage.clientKey; headers["X-scriptDb-REST-API-Key"] = pPackage.restAPIKey; */ $.ajax({ type: methodType, dataType: dataType, url: u, success: function (data) { d.resolve(data,defers); }, error:function(res, status, err) { d.reject("error " + err + " for " + url,defers); }, headers: headers, data : postData }); return d.promise(); }; } |
For help and more information join our forum, follow the blog or follow me on Twitter
As per this blog post, Google Apps Script does not support CORS (cross origin resource sharing) to allow access from cross domain client apps. The API uses JSONP for GET requests to get over this, but […]
In parse.com – nosql database for VBA, I described how to use cParseCom to be able to use parse.com directly from VBA. You can find the implementation details here parse.com api class for VBA Here’s a few […]
I use scriptDB a lot , both with Google Apps Script and from other sources, including VBA, and in this blog post, I compare parse.com performance with script DB. One of the things that Parse.com has going […]
Because the GAS API is so similar to parse.com – nosql database for VBA, you may want to read that too, which has similar examples. Note that virtually all methods can be chained leading to pretty […]
Just as in parse.com – nosql database for VBA, you’ll probably want to load up some test data from a Google Spreadsheet to a Parse.com class. You’ll find some test data in this workbook – it’s same data […]
In Getting cockroachDB running with Kubernetes I covered how to get cockroachdb going on Kubernetes, but that was in insecure mode, which is fine for playing around inside the Kubernetes cluster, but not good enough […]
Cockroachdb is a PostgreSQL compatible SQL database. It’s architecture is somewhat like Google Spanner, but you need to install it on your own infrastructure. This article will give a brief overview of how this stuff is […]
The first problem we hit is the 1000 object limit on parse queries. Here’s how to get round it using jQuery promises.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
<!DOCTYPE HTML> <html> <head> <title></title> <link rel="stylesheet" type="text/css" href="css/colortable.css"> <script src="http://www.parsecdn.com/js/parse-1.2.2.min.js"></script> <script src="js/colortable.js"></script> <script type="text/javascript" src="http://www.google.com/jsapi"></script> <script type="text/javascript"> google.load('visualization', '1'); google.load("jquery", '1'); </script> <script type="text/javascript"> google.setOnLoadCallback(function () { //populateParse(); var scheme = ""; var parseKeys = getParseKeys(); Parse.initialize(parseKeys.appId, parseKeys.jsKey); var ColorTable = Parse.Object.extend("ColorTable"); schemePromise(ColorTable, scheme).done(function (all) { console.log("scheme " + scheme + " returned " + all.length); }) .fail(function (error) { console.log("error getting scheme " + scheme + JSON.stringify(error)); }); }); function findChunk(model, scheme, allData) { // we have to find in chunks since there is a limit on query size // will return a promise var limit = 1000; var skip = allData.length; var findPromise = $.Deferred(); var query = new Parse.Query(model); if (scheme) query.equalTo("scheme", scheme); query .limit(limit) .skip(skip) .find() .then(function (results) { findPromise.resolve(allData.concat(results), !results.length); }, function (results) { findPromise.reject(error); }); return findPromise.promise(); } function schemePromise(model, scheme, allResults, allPromise) { // find a scheme at a time var promise = allPromise || $.Deferred(); findChunk(model, scheme, allResults || []) .done(function (results, allOver) { if (allOver) { // we are done promise.resolve(results); } else { // may be more schemePromise(model, scheme, results, promise); } }) .fail(function (error) { promise.reject(error); }); return promise.promise(); } </script> </head> <body></body> </html> |
How it works Parse allows only 1000 results max per query, but […]
Writing to parse.com was fairly complex, with all the asynchronicity and limiting. Writing the workbook to scriptDB should be much easier. We’ll use exactly the same approach as for Updating parse.com , namely get the data, get the existing […]
We’ve looked at Query Limits on parse.com and Google Visualization API data to prepare for loading the color table from Playing around with GAS color into parse.com. Now to load the data Using parse.save One of the constraints of parse is that it’s […]
Here’s how to set up a service account to access your firebase data base in admin mode. It’s quite disjointed in the docs, so here it is from start to finish. The console Just as […]
Firebase authentication is replacing the Google Identity toolkit. I’m not sure if I really understand the ‘firebasification’ of many of Google’s APIS. I think it’s confusing whether you should use the Firebase version of things, or the underlying […]
This’ll be quite a long post, or maybe a series of them, as auth is always complicated. Lets first of all set the scene. This is a VueJs client that needs access, both authenticated and […]
This is one of a series of articles about Going serverless with Firebase. You many want to read about Firebase cloud functions and Custom domains and ssl with Firebase hosting before this article. Rate limiting So you’ve created your cloud […]
This is one of a series of articles about Going serverless with Firebase. You many want to read about Firebase cloud functions before this article. Using custom domains 2 of the great things you get with Firebase hosting […]
This is one of a series of articles about Going serverless with Firebase What are Cloud functions There are both Google and Firebase branded cloud functions. They are probably the same thing under the hood, but […]
This is a new section to collate various snippets related to GraphQL both from and back ends. For the purposes of these articles I’ll be concentrating on the integration of this collection of platforms with […]
First up we’ll query the colortable created in Updating parse.com, using the parse REST API. We can’t use the javaScript API in dome way from GAS, because it’s not caja compliant – if it were, we […]
NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. Sometimes you need to create a unique identification for each object. ScriptDB automatically assigns a unique objectId for each object, so […]
Here’s the arguments to the cScriptDbCom class of A VBA API for scriptDB Here’s the VBA client code for A VBA API for scriptDB And the code for many of the examples mentioned for this topic For […]
If you’ve followed Creating your own scriptDB environment we are ready to start accessing it from VBA. Registering authentication There are 2 things you need to do, both of which are one off things. You may have […]
Here’s what you need. This is written from the perspective of A VBA API for scriptDB, but most of it also applies to scriptDB API for JavaScript Work your way through the material on A VBA API for […]
The VBA API generates REST API requests from your VBA function calls, and needs a Google Apps Script handler to service those requests by communicating with the required scriptDB instance. You can have multiple handlers, […]
First of all you need to Download cDataSet.xlsm and go to the scriptDBCom module, where you’ll find various examples. Readonly, no oAuth2. To get used to the API we’ll first of all use some of my test […]
There are a number of examples on this site of one library being used to provide data to another script. You may want to do this for a number of reasons. The limit for a […]
One of the great strengths of scriptDB is also one of its weaknesses. It is easy to share amongst projects, but it’s hard to detect who is using it since Session.getActiveUser() only works within domains, […]
In Ephemeral Exchange I wanted to create a push notification capability so that subscribing apps could be informed immediately a cache item had changed or expired without having to poll for changes. The back end for cache […]
NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. I leave this article here for interest.In Using scriptDB, I covered some usages of scriptDB,along with a method of siloing data […]
You will need a script to host the scriptdb that contains your public data. In the step by step guide, Delegation to Google Apps Script, you should have created a script, pubstuff and added the mcpher library. […]
If you followed Do something useful with GAS in 5 minutes, you’ll already know how to use a spreadsheet as a database. You may want to move on to using a real database as a back […]
Google Apps Script already has a noSQL database – scriptDB. I use it a lot, and have plenty of examples on this site. When I was developing parse.com api class for VBA , part of the motivation was […]
We’ve dealt with parse.com – nosql database for VBA, cParseCom examples and shown the code at parse.com api class for VBA. Here’s a few selected topics to help you understand a lottle more about how […]
Following on from Query parse.com from GAS, and Query scriptDB from GAS here’s the common code they each need to construct and compile the queries and results. colorschemer created by GitJsonApi https://github.com/brucemcpherson/colorschemer 0 forks. 3 stars. 0 open […]
The BigQuiz app uses Firebase for keep track of the question, category and game scores of individual players. In Firebase custom authentication with goa I showed how to use Goa to manage authentication for Firebase. This firebase access library works with […]
The BigQuiz app uses Firebase for keep track of the question, category and game scores of individual players. In JSON web tokens I showed an example of how to authenticate and authorize when making a call to the Firebase JSON API. […]
Deprecated Parse.com has now become parseplatform.org and i will be investigating it at a later stage. I am however leaving the page as if as you may still find it useful. Contact me if you […]
In this section, we’ll build an application that pulls together many concepts already covered elsewhere on this site. You can run it standalone at xliberation.com, or below, embedded in this page The target application Pulling together Playing […]
I use scriptDB a lot , both with Google Apps Script and from other sources, including VBA, and in this blog post, I compare parse.com performance with script DB. One of the things that Parse.com […]
parse.com – nosql database for VBA, describes how to use this class to be able to use parse.com directly from VBA. Here’s the details of the implementation. List of functions and properties Can be found […]
UPDATE parse.com as now been closed and moved to parseplatform.org. I will update the content of this page an move the back-end to this platformis text. I like scriptDB, but in many ways I prefer […]
NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. The versatile scriptDB scriptDB is a javaScript object database for Google Apps Script project. It is a very useful feature to […]
We have CRUD natively to scriptDB from Google Apps Script, A VBA API for scriptDB gave us one for Excel – now here’s one for JavaScript. That means you can use the same noSQL database for all […]
This tutorial will get you started with Database abstraction with google apps script and show you how to write a small app that uses a sheet as a database. Open do something in 5 with gas-1-use spreadsheet as […]
Ephemeral Exchange is a cross platform cache that allows you to share transient data across a range of platforms without the need for authentication, but by sharing access keys between participants. The code is open source, […]
As you will know by now, ScriptDB is now deprecated. A year or so ago, I published some articles on alternatives to ScriptDB on the desktop liberation site, one of which was Parse.com. This has turned […]
If you are a regular on this blog or on the Excel Liberation site you’ll know that I’m a great fan of scriptDb – the cloud base noSql database for Google Apps Script – and like […]
I figured it would be pretty nice to be able use one of the cloud based noSQL databases directly from VBA, so that I could share data easily between various platforms. On Excel Liberation , I […]
When the scriptable data validation feature in Google Apps Script came out recently, I started thinking about how we don’t really use the cloud based nature of Google Docs to its potential in a number […]
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 […]
oAuth arghhh.. When I was looking around for a way to easily implement oAuth, and be able to use it from multiple scripts, or even languages (I was originally trying to figure out how to […]
In various posts and articles I’ve been exploring uses of the Google Apps scriptDB datastore. One of the things I’ve been mulling over is how to simplify the whole oAuth experience when dealing with REST queries that need […]
In messing around with scriptDB, I covered some usages of scriptDB when it first came out, along with a method of siloing data to keep it organized. Here’s a usage that might be useful for […]
What no server? That’s right – you can serve up data from your Google Apps Script directly. In my post the other day, I showed how to use the Google Apps ScriptDB as a place to store […]
I’m taking a break from d3.js and playing around with the new Apps Script DB. Those of you who read these posts or follow the rambling site may know that I have been building up a library […]
Abstracting an Excel data source. In almost of all of the downloadable examples on the ramblings web site, I use a set of data abstraction classes to separate workbook data from its workbook location, and […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines