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

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(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):

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 :(