Recreating Tufte’s famous weather chart with Google Sheets and Charts API

My Twitter feed is mostly populated with data-related resources and people, so I almost always find something new and shiny going on there to distract me.

Sometimes this can be a good thing though (and sometimes, it can be the best thing ever).

Take last Friday. I found myself procrastinating, browsing my home feed when I saw this tweet for the upcoming Microsoft Data Science summit:

Tufte temp chart tweet

What caught my eye was not the actual tweet, but the sumptuous weather visualization that I recognized as one of Edward Tufte’s famous creations.

I clicked through to the article, which linked to Tufte’s original chart (here) and a fantastic tutorial about re-creating the chart using R, from Brad Boehmke (which is actually the chart shown in the tweet above).

It looks at daily average temperature over a period of time and captures the historic min/max range, the normal range (95% confidence interval), the 2014 temperature line and picks out the highs and lows. It renders all of this information in a single, beautiful column/line combination chart.

Curiousity piqued, I wondered whether this would be possible with Google Sheets?

The short answer is….. Continue reading Recreating Tufte’s famous weather chart with Google Sheets and Charts API

Build time-based triggers in Apps Script projects to automate programs

Ever find yourself needing to repeat an action in your Google Sheets?

For example, on a recent client project I wanted to cycle through my spreadsheet data, compare it to another dataset and apply a matching algorithm. To do this and stay within the 6 minute execution limit for Apps Script, I split my data into “blocks” of 10 rows, counted how many blocks I had and then ran the matching algorithm once for each block until they’d all been processed.

By adding time-based triggers, I setup my Google sheet to do this automatically, rather than having me click a button each time.

Using Apps Script (read my getting started guide here), you can add these time-based triggers programmatically, so they can run and stop automatically.

In this post I’m going to show a skeleton example of this time-based architecture, to which you can add your own functionality.

The entire code file is here on GitHub and the spreadsheet is available here (feel free to make a copy: File > Make a copy… and then you can open the script editor to modify the code).

Time-based triggers with Google Apps Script

In this simple example, I’m going to output a random number in a new line of my spreadsheet every minute until I’ve looped through a set number of times (2 in this case). It’s deliberately basic as the focus is on the trigger/timing architecture.

Here’s a screencast of this simple program in action (speeded up):

Auto trigger loop

Behind the scenes, everything is running on autopilot once I’ve clicked that first Run button.

Continue reading Build time-based triggers in Apps Script projects to automate programs

Friday reading: resources and inspiration for spreadsheet buffs

Here’s a collection of resources for you: a strange mix of advanced, esoteric and inspirational spreadsheet uses, showing how far some (mad)men and women push their spreadsheets. The list gets more light-hearted towards the bottom of the post!

Somehow I doubt Dan Bricklin would have envisioned all this when he invented the spreadsheet…

Spreadsheet programming

Functional programming in Excel from Felienne Hermans all about her research into treating Excel as programming language. Well worth a watch.

Continue reading Friday reading: resources and inspiration for spreadsheet buffs

6 advanced techniques to master in Google Data Studio

Google launched a new business intelligence tool called Data Studio in May 2016. It’s a really smart reporting tool for quickly creating powerful, stunning dashboards from multiple Google data sources.

It’s a great option for small/medium businesses already using Google tools, who want to build bespoke dashboards for that 40,000ft view of their business.

Google Data Studio Example Reports

Here are two example reports for a mid-size website (~500k pageviews a month).

Firstly, a mobile performance dashboard:

Data Studio Mobile Performance Dashboard

Continue reading 6 advanced techniques to master in Google Data Studio

Makeover Monday Tableau Challenge: Theft in Japan

Chart showing the number of reported thefts in Japan in 2012, part of the Makeover Monday data visualization series, run by Andy Kreibel and Andy Cotgreave.

Design inspired by Japanese hand fans like this one. I had some fun with this one and took some liberties to create a piece more in the data-art camp than a serious data visualization.

At heart, it’s a radial bar chart with a background image. I’m indebted to this useful blog post, which showed me how to do all the hard stuff: How-to create radial bar charts from Interworks.

Vlookup in Google Sheets using wildcards for partial matches

VLOOKUP with a single wildcard

Imagine we have this table of employee information and we’ve been given a partial name (in this example: “Mye“) to find and retrieve information about.

To do this we use a standard VLOOKUP formula with the wildcard asterisk character: *

Continue reading Vlookup in Google Sheets using wildcards for partial matches

Introduction to Google Data Studio: a free BI tool for small businesses

Regular readers will know of my enthusiasm for building dashboards, especially using Google apps (like this one or this how-to article).

So I was super excited in May of this year when Google launched Data Studio, a free data visualization and dashboard tool to compete against incumbent dashboard vendors Microsoft PowerBI, Tableau and Qlickview.

Here, I’m excited to share my initial impressions and show you some of the basics steps to build dashboard reports using this tool.

Continue reading Introduction to Google Data Studio: a free BI tool for small businesses

Web traffic chart with dynamic banding in Google Sheets

This is a simple but effective technique for adding dynamic bands to your charts, which are useful to highlight specific parts of your chart.

For example, in this chart of website pageviews, I’ve added bands to show weekdays or weekends and make it easier to see the changing trends.

Chart with dynamic banding

Continue reading Web traffic chart with dynamic banding in Google Sheets

Creating a d3 chart with data from Google Sheets

In the following image, I have two browser windows open. On the left, a Google Sheet containing a simple table of data. In the right browser window, a web page with a d3 chart that displays the data from the Google Sheet and can be refreshed dynamically when the data changes.

d3 + Google Sheets

Continue reading Creating a d3 chart with data from Google Sheets