Punchcard charts in Google Sheets/Spreadsheets (querying Google’s plan to drop gadgets)

A couple of days ago Mat Morrison posted:

I’ve been messing around with Excel; trying to create a “punchcard chart” that will let me visualise data by hour and day at the same time. This is where I’ve got to so far, using nothing more than COUNTIFS and SUMIFS.

It works, more or less, although there are some bits to tidy up; notably that y-axis, which should really read Sunday through Saturday or the like.

Here’s a link to the Excel workbook if you want to try this out: https://docs.google.com/file/d/0B26bhH2SxecqcDN4c3JvYTlfYTA/edit?usp=sharing

Punchcard example in Excel by Mat Morrison

Knowing that Google Sheets/Spreadsheets permits the QUERY formula which allows SQL like data manipulation I thought I’d give it a try. The result is mixed. As thought reshaping the data was relatively straight forward using a query along the lines of:

Query data in google sheets

 =QUERY(raw!A:A,"SELECT COUNT(A) WHERE HOUR(A) = "&C2&" AND DAYOFWEEK(A) = "&D2&" LABEL COUNT(A) ''")

where raw!A:A is some datetime data (in this case imported from TAGS).  Here’s the source spreadsheet if you want a closer look at the formula.

So far so good. Next to graph the data. This is where the trouble starts. First here’s the result (click for the interactive version):

punchcard - bubble plot in google sheets

For reference: I’ve had problems embedding interactive Google charts in blog posts. On self-hosted WordPress sites the data source url gets mangled by the editor. My current fix is to use the Raw HTML plugin which preserves the url. Worth also considering that interactive charts may not appear in RSS feed readers. You can try to get around this with the noscript tag (see this gist for example) [Also Cloudflare users need to note this]

Looking at the bubble chart option it says:

The first column in the table should be text, and represents the label of that bubble. The numbers in the second column are plotted on the x axis. The numbers in the third column are plotted on the y axis. The optional fourth column should be text, and determines the bubble colour. The optional fifth column is numeric, and determines the size of the bubble.

Okay … so in the first image you’ll see I’ve had to start spoofing columns. In B:B I’ve had to create a column of unique labels. In E:E I needed to create a dataset for results where the count is zero. This is because the bubble chart will render bubbles even if the size is zero. To get around this I’ve tried removed the fill on zero values. Another aspect I’m not entirely convinced with is the bubble scaling, but I’ll let you decide.

The really worrying aspect of all this is given the recent announcement of Deprecation of Gadgets in Google Spreadsheets which allowed you to embed your own charts in Google Sheets (here’s an example gadget I wrote to include a d3.js force layout graph), come July/August  if I want to visualise data in a spreadsheet all I’m going to be left with is Google’s crappy charts … sigh :(

Comments are closed.