I have several Google Sheets doing several things on their own through Google Apps Script. I’ve started to make it a habit that each action is logged to a separate, isolated spreadsheet so I can pop in and look for error messages in one places rather than several.
This poses a small problem. I have to actually remember to open that sheet. Usually, something goes wrong, and then I remember to check the logs. I wanted to have something more up to date that I could glance at without too much effort.
You can get Google Sheet data as JSON which is handy in a number of contexts (here and here are two examples from my own work). It’s not as straightforward as tagging
.json on the end of the URL (though that would be sweet) but the process isn’t hard. To get the data, this post details how to publish your sheet and find the feed.
Once the dataset was live online and updating regularly, I needed to decide how to get it. I use GeekTool on my desktop so I decided to use a Python script and the Responses library to gather and process the feed.
I put this into a Geeklet on my desktop and voila!
Give it a try with your own sheet. You can run it in your terminal to get a printout of the last 5 entries of the sheet. The JSON output from Google is really weird, so it helps to put it into a prettifier to make it more readable before parsing keys.
What did I miss? What would you do differently?
—Written on October 7th, 2017 by Brian Bennett Categorized in: Projects Technology