{"id":1359,"date":"2013-12-04T21:27:27","date_gmt":"2013-12-05T03:27:27","guid":{"rendered":"http:\/\/www.mooreds.com\/wordpress\/?p=1359"},"modified":"2013-11-15T21:42:35","modified_gmt":"2013-11-16T03:42:35","slug":"google-spreadsheets-as-rest-api-sources","status":"publish","type":"post","link":"https:\/\/www.mooreds.com\/wordpress\/archives\/1359","title":{"rendered":"Google Spreadsheets as REST API sources"},"content":{"rendered":"

I recently built out a read only JSON API with a Google spreadsheet as the back end data source.<\/p>\n

Why do such a thing? I didn’t need to modify the back end, but wanted to make the data available to other software. The people who maintain this data are very comfortable using Google spreadsheets. While I could have written a custom CRUD app, this didn’t seem like a good use of time when Google spreadsheets had served us well in the past.<\/p>\n

How did I do this? I first of all created a sanitized spreadsheet, using importrange<\/a> and regexreplace. This level of indirection assures me that if the source data changes, I can adjust fairly easily. If the user managing the spreadsheet wants to rearrange columns, I can adjust my sanitized spreadsheet easily.<\/p>\n

Then I created a Google apps script and used doGet to respond to requests<\/a> and the spreadsheet service<\/a> to retrieve the data. The content service<\/a> lets you serve JSON with the appropriate mime type. I used qunit for Google apps script<\/a> (invaluable when you are working in the loosely typed javascript world and relying on cloud resources). I also worked with the parameters to build the querying needed for our application.<\/p>\n

Then, in order to make it look like a normal API call, I fronted the script with Varnish<\/a> and did some regsub magic in my VCL file (as well as some light authentication). <\/p>\n

This approach has the benefit of keeping everything in Google’s cloud, and allowing you to access the spreadsheet data easily.<\/p>\n

This approach has significant limitations, however.<\/p>\n