Importing A Google Spreadsheet Into A Rails Application

Recently at work I have been updating an application that provides a listing service for Extension professional development opportunities in order to facilitate some of the application’s features to be used for our upcoming conference (the application is typically geared toward interactive online sessions).

I work best in applications when I have real data to work with, and not a bunch of automatically generated “Lorem Ipsum” data. That’s why, as part of my development, I operate on copies of live data for pretty much every application I work with (which fortunately, other than a few transmogrifications like dumping passwords, we can do with our data). I even added an option to the tool a colleague and I wrote to post our deploys to a central deployment monitor to facilitate the data download.

So, I wanted real data to work with for our sessions. Fortunately, a few days ago, one of my colleagues sent out the sessions in a spreadsheet, which I spent a few hours reformatting and dumping to a csv — and then importing that data into the application.

But after doing all that work, on data that wasn’t yet fully complete (I didn’t yet have descriptions, or all the presenters in the data) — I realized that was going to be a continued pain in the ass to keep updating my translation of that data, dumping to csv, adding to the seeds, or wherever I would do a on it — to keep it updated to when this actually gets deployed in production (after which it can just be updated in the application).

To be sure, there has to be a better way™ (I’m hereby trademarking this phrase because it is the muse for every podunk Dev/Ops/DevOps innovation ever, and should be a repeated mantra for all of us).

And it turns out there is. Publishing Google Spreadsheets to the web.

By publishing my import spreadsheet to the web — and using the csv output:


I can then import it at will (if you look close, you’ll see it’s only a one time thing, there’s no provision for a unique key in this particular data set to do a find_or_create on import — but the source data on this is only 4MB — so I’m constantly doing a capatross getdata rake db:migrate ruby importer.rb nexc_import) as needed.

In theory, updating the spreadsheet can now be crowdsourced among our staff. In practicality — probably not, but at least I can stop exporting, committing, and then running scripts on it any time a change is made.