Many backends assign unique keys and other control information to data objects. In order to keep the results for a query compatible between backends, the driver has the responsibility to remove these. However there are some occasions when you might want those, and in fact you need key information for the datahandler get() and the update() methods. The query() method will return this control information along with the value that the get() and update() method consider to be the key for a particular object, but separated from the main data. By default, these properties will not be returned. You need to tell .query() you want them as below – where true is the keepIds parameter.

var result = handle.query ( {some query}, {some parameters}, 0, true);


By default, a query will return an object that looks like this:

After checking for a non-negative handleCode, (see  How to write a driver for the possible error codes), you would access the data store in the array.
If I do a query to, I get the same result. However, actually inserts a bunch of stuff in your data which the driver strips out. The real looks like this:

The unique Id for is obviously objectId, but for other databases it will be something else. In order to keep consistency between back ends, here’s the full story of what you would get back from the driver.
Note the 2 properties 
driverIds – contains the additional fields that parse addshandleKeys – the unique keys for each object for which there is data results with keepIds

The handleKeys array can then be used along with get() and update().

scriptDB results

Function results results

Sometimes the .count() function for orchestrate does not reflect the true count of matching records if you execute it immediately after saving some records. I suspect there is some kind of internal delay in the back end. 

Drive results

A note on Sheets

All the backends except sheets use a unique key either generated by the underlying API or by the driver, that will stick with the object for its lifetime in that database. However, the key for sheets is actually the row number which is transient and should be used with caution. I did not want to store additional key data in a sheet since it would distort its usability as a normal spreadsheet. Its probably better to do a remove() and save() operation with a Sheets backend rather than an update(). Also note that the row number is the data row number, and starts at 0. The first item of data would actually be on physical row 2 of the spreadsheet, but would be considered row 0 of data.
Here is the result from a sheets query, with keepIds set to true.

See Database abstraction with google apps script for more on this.


For help and information join the forum, follow the blog or follow me on Twitter.