Using as a database feed

Martin Hawksey described how you could populate a Google Spreadsheet with regular updates from data that had been prepared by some scraping process in

Getting it into a spreadsheet is a good start, but what if you want to get the data into some other kind of format? A fusion table, an Orchestrate database or some other medium for example. Let’s say you are creating a mobile application that accesses for its data, and for this application we need some data processed by, perhaps merged with some data from some other source. Here’s how we can use Google Apps Script to achieve that.

Database abstraction

One good approach when writing an application like this is to abstract the database handling. This means that we can easily change back end from one database to another, or even mix them up, by using the same code in the application, and having it translated to the native database by the use of Google Apps Script libraries, of which there are a number freely available. In this way, we can make just another database provider, without needing to worry about the details of how to access it.

An example

Let’s take Martin’s dataset, which he accessed in google sheets like this.

Here’s how to get that data into Apps Script using a data abstraction library

The key for Martin’s data is 11fd9a49-e25b-47f0-9e49-317225ae5eaa, and I’ve used my own credentials to access it. You’ll notice that I look up some data in the Google Apps Script PropertyStore. This is the usual place to keep things like credentials.

Here’s what I get back – the table as a JSON object

Next I’ll write it to a few different places, only changing a few parameters. This gives me multiple access opportunities, and also automatically gives me sorting and querying capabilities on the original data.

Fusion table

This gives me this in Fusion. I won’t repeat it for each of the following examples, since the result is the same, even though the back end database is completely different. database

Orchestrate database

A spreadsheet

A file on Google Drive.


Bruce McPherson, Head of Architecture and Engineering IT at Motorola Solutions Bruce McPherson, Head of Architecture and Engineering IT at Motorola Solutions

You can combine this approach with the timed trigger that Martin demonstrated, and easily update one or more database targets for use in other applications.

From the examples above, a selection of the databases supported, you can see that your data can be widely accessed by using the power of Google Apps Script. For more on this topic see the Desktop Liberation site.

by Bruce McPherson

Turn the web into data for free

Create your own datasets in minutes, no coding required

Powerful data extraction platform

Point and click interface

Export your data in any format

Unlimited queries and APIs

Sign me up!