Spreadsheet as an DB Admin Interface

Administer your data in an online Spreadsheet, Pull it into your DB with a Ruby one-liner

For many use-cases, using an online spreadsheet as your ‘source of truth’ can give you an immediate (super robust) admin interface for your data, and a Ruby one-liner can import the data into your app into whatever DB format the app is using. This is a huge productivity gain. Let’s just show you how it might work.

1. Publish your data in a Google Doc. (e.g., a list of users.)
2. Grab its JSON link (e.g, this one)
3. Run the following Ruby code.

> require 'json'
> require 'open-uri'
> uri = #YOUR JSON URI (or practice with mine, above)
> rows = JSON.parse(open(uri).read)['feed']['entry'].map {|row| kvs = row.select {|k,v| k.start_with?('gsx$') } }.map {|row| row = row.map {|k,v| [k.sub('gsx$',''),v['$t'] ]; }.to_h }

(The apparently obfuscated one-liner doing the actual grunt-work is just boilerplate functional processing of the ugly JSON format Google Spreadsheets returns into a cleaner representation of the actual data. TBH there might be a cleaner to do this, but a one-liner is easy enough. Also, the Ruby-ness makes me feel happy.)

4. rows is now an array of hashes, each representing a row (in our case, a user).

> rows[0] 
=> {“id”=>”1", “name”=>”Alice”, “age”=>”30", “sex”=>”F”}

We can iterate over rows and upsert each document, by its ID, into our DB. We can optionally precede this by deleting the table; this will make the DB table/collection exactly reflect the spreadsheet. Note this can easily map onto an SQL-, document-oriented, or key-val DB. E.g:

# mongo pseudo-code
> rows.each {|user| MONGO_USERS.insert(user) }
# redis pseudo-code
> rows.each {|user| REDIS.set("users_"+user['_id'], user) }

You get the idea. We now manage our users via the spreadsheet, but can query them from within the app.

This gives us an immediate admin interface for our data, which is edited and viewed in the powerful Google Spreadsheets — giving you admin abilities to query, filter, sort, [etc] your data, a history of every version of the data). By invoking the above one-liner your can pull it into your actual persistence layer, to be queried by your app.

If your data is admin-administered and read-only (such cases might range from your UI text copy to “this week’s recommended articles”), this can be your admin interface + DB. In cases where the data can be modified by the app, you can use the above in an upsert mode, to merely modify selected items.

To recap, to use a Google Spreadsheet as an admin interface:

1. Put your data in a Google Spreadsheet.
2. To pull it into your app, call the following:

def google_doc_to_array_of_rows(uri)
    JSON.parse(open(uri).read)['feed']['entry'].map { |row| kvs = row.select {|k,v| k.start_with?("gsx$") } }.map {|row| row = row.map {|k,v| [k.sub("gsx$",""),v["$t"] ]; }.to_h }
end

3. Congratulate yourself — wasn’t this the best admin interface you’ve ever set up?