… if you enjoy this post you might want to register for the online SheetsCon conference 11th – 12th March 2020. Registration is free and there is a great lineup of speakers … and me ;)
On Friday I had the honour of speaking at a @HDINework workshop in Edinburgh for ALT with my boss Maren Deepwell. The purpose of the workshop was to answer the question:
How do we support and empower all aspects of society to function in a world where almost every aspect of our lives results in data trails? This might include questions around literacy, power, access to resources, and responses to surveillance capitalismHDI Network
For this I wanted to share my own personal journey interacting with data. As part of this I wanted to share my personal journey interacting with data using spreadsheets and Google Sheets.
You can view a modified version of my slides referenced in this post and also here is the full deck with Maren’s part at the beginning and Maren has also posted her own reflections on this and another talk, which is well worth a read.
Below I’ve broken down the main points of what I talked about:
Let’s start with the ubiquity of spreadsheets. I’ve long felt that spreadsheets were everywhere but until a post from Hjalmar Gislason I had never seen it in numbers and it made for interesting reading:
- 1.2 billion people use Microsoft Office (WindowsCentral, March 2016), odds are most of them have at least access to Microsoft Excel.
- Microsoft believes that 1 in 5 adults in the world use Excel (“What’s new in Microsoft Excel”, Sept 2017)
- Excel is the number one skill mentioned in job ads, mentioned in approximately 1 in 3 job ads! (Indeed.com Job Trends, June 2017).
- In 2010, RescueTime found that about 25% of computer users used Excel on a daily basis and that about 2% of all time spent on a computer anywhere was spent using Microsoft Excel.
Whilst spreadsheets have a high level of ubiquity I also wanted to acknowledge ‘spreadsheet addiction’, a post I regularly revisit from Patrick Burns, which in summary highlights the limitations of spreadsheets:
- Ambiguity of value and formula
- Data extent – e.g. new data might not be included in formula range
- Data structure
- Command history
- Computational volatility
But… let’s remember the usage numbers PLUS when you look at Google Sheets there are some wonderful affordances of a cloud based spreadsheet tool that shouldn’t be overlooked, particularly for the citizen developer, and one of my favourites, the IMPORT family of formula:
I’ve previously shared a post with lessons in IMPORT published in 2012 which has some examples of how you can use the IMPORT formula. The first one looking at IMPORTHTML to generate an Olympic Medal Table map needed some fixing as the HTML table moved in the Wikipedia page. So there are situations where IMPORT formulas are great but it also highlights the fragility of referencing external data, small changes can lead to broken sheets.
Feeding spreadsheets with services (Zapier and IFTTT)
An alternative to relying on referenced data is to write the data to a sheet. This is where services like Zapier and IFTTT provide a possible solution. Zapier and IFTTT integrate with thousands of other services allowing simple no code automations including adding data from supported services to spreadsheets.
For example, using IFTTT I’ve been able to backup my Wemo smart switch data for the last 3 years:
Also for the last 3 years I’ve also automatically been backing up my Withing smart scale data to a Google Sheet:
No code applications with Sheets
Data generation and interaction
In terms of data collection you also don’t have to rely on automations shown in the examples above. Another no-code solution which has recently been acquired by Google is AppSheet. AppSheet enables users to create native mobile and browser based applications incorporating the ability to use features like: image capture, location logging, barcode scanning, signature capture as well as working offline. You can also use various data sources including reading/writing data to Google Sheets and Drive.
Google Sheet Add-ons
Add-ons exist for various G Suite tools including Google Sheets. Add-ons let anyone extend the functionality of Google Sheets and there are already a number of add-ons that let you integrate and interact with third party data sources.
For example, Supermetrics provides add-ons for Google Sheets (as well as Excel) which let you interact with data from various sources including Google Analytics, Google Ads, Facebook, Bing, Twitter and Linkedin.
Interfacing Google Sheets with APIs
You are not just restricted to the add-ons other people have created. If you aren’t familiar with APIs they essentially provide a way to interact with third part services and post/retrieve data in a machine format. Not all services provide APIs and there are usually restrictions on accessing data in this way, but when you can there are many powerful things you can do.
For developers Google Apps Script provides a cloud based scripting language that you can use to write your own integrations with APIs and other data sources, which can also easily be shared. Some examples of this include the work Ben Collins has published interacting with the fitness tracking app Strava and recording data in Google Sheets. For almost the last decade I’ve published a maintained TAGS, which is a similar solution for archiving data from Twitter.
Thinking voice first data interaction
In a presentation about human data interaction I also wanted to highlight that the use of spreadsheets is no longer limited to your sheet ninja skills in terms of formating, reshaping and generally interacting with data. Integrating with services like Dialogflow allows you to potentially use people’s voice as a way to interface with data. In many ways this is already happening for example when you ask your smart speaker what the weather is going to be like, or what’s your next appointment, these are all data driven. In the context of a spreadsheet voice enabled interfaces provide an opportunity to have a conversation with your data allowing you to explore, format and gain deeper insight without having to dig deep into various spreadsheet menus. Experiments like the Highlighter/Dialogflow Add-on demo hopefully give an indication of what is possible now and what might become mainstream in the future (if you are interested in this area here is also a recording of a talk I gave on Google Sheet voice interfaces at DevFest London).
So that was a whistle stop tour of some of the ways I have retaking ownership of my data using spreadsheets. A question that this perhaps immediately raises is by passing my personal data through a range of services rather than retaking ownership am I not just shotgunning it across the internet letting others benefit and ultimately monetize it? My answer in the case of Google Sheets is no. For Google the income doesn’t come from the data I put in, it’s the fact that I’ve logged in to my Google account and then continue to still be logged in whilst I browse the internet, perform searches and click on ad content …but I happy to listen if you know something different.