1 Comment

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:

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


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


Update 16/06/2014: This idea has been revisited by the Elevate team at University Campus Suffolk. You can read more about and get a copy of the code here

Update 18/02/2015: I've revisited the idea of issuing Open Badges with Google Apps Script and embedding in Google Sites which improves this idea further. You can read more about this here.

 Open Badges Issuer Gadget for Google SitesWant to issue badges in Google Sites? That was the challenge I set myself. My solution is the Open Badges Issuer Gadget for Google Sites. This gadget wraps the Mozilla Issuer API to allow you to issue badges from a Google Site. To use the gadget is insert into a Google Site and prefix (base url) is set for your Assertion JSON. To allow users to collect their badges direct them to the web address of your Site page containing the gadget adding ?claim_code={assertion url post fix}.

For example, if my Site page with the Issuer Gadget is found at and by Assertion files are all found in the directory this would be my base url. If one of my Assertion files in this directory was v1mhaws.json, to claim the badge for that person I’d send them a link to

Get the Open Badges Issuer Gadget for Google Sites

The Open Badges Issuer Gadget for Google Sites is located here:

To add to your own site

  1. Open/create a page on your Google Site
  2. Selecting Insert > More gadgets,
  3. Add gadget by URL inserting the address
  4. Insert a prefix (base url) for your Assertion JSON files (you can leave this blank if the host site varies) and click ‘Ok’
  5. For each or collections of Assertions direct users to visit the page your gadget is hosted on adding ?claim_code= followed by a comma separated list of the remainder of you Assertion file locations

Try it

If you’d like to try the gadget complete this form and you’ll be issued with a Gadget Issuer User Badge. Get the question right and you’ll also get the Gadget Issuer Gold Badge.

How the gadget works

For those of you unfamiliar with gadgets/widgets they are an easy way to embed content in other gadget/widget compatible sites. The majority of gadgets are simply XML wrappers for HTML content. The great thing is that gadgets can include JavaScript that doesn’t get sanitized/stripped out. If you want more information about making gadgets see My First Google Gadget. The source code for the is linked to above but can also be viewed on GitHub. Essentially it’s a wrapper for Mozilla’s Issuer API

The Issuer API is a [java]script that can be dropped-in to any badge issuer's website to provide a way for users to add an issuer's badges to their backpack.

Feel free to modify the gadget code to handle the success and error callbacks.

Yep I’m issuing badges from a Google Form/Spreadsheet, here’s how

If you tried the demo you might be wondering how I went from a Google Form to issuing some badges. Here’s how. Google Spreadsheets includes Google Apps Script, a cloud scripting language with uses the JavaScript Syntax to automate processes across Google products and third party services and deploy/publish custom applications and data. Apps Script includes a Content Service, which amongst other things lets you publish JSON data. As the metadata blobs behind open badges are JSON based we can use Apps Script to process the form responses, email the recipient and create the JSON … well almost.

An issue with JSON files generated by App Script  is security measures put in place by Google prevent the cross-domain use when called by AJAX as used by the Issuer API. So currently I have to proxy the JSON via my webhost (Mozilla could fix this by also permitting JSONP, which can also be produced by Apps Script. I imagine this is however low priority. If you have any thoughts on other ways leave a comment).

Here’s a copy of the code running behind my Google Form (you’ll also need to include a rollup of the CryptoJS sha256 library to hash and salt the recipient’s email address).

[A pointer if you want to extend this work is you might want to use the native NoSQL style ScriptDb Service part of Google Apps Script to prepare and serve assertions. Also I found the Open Badges Validator is very useful for testing your Assertions.]

Related work

Some recent posts/upcoming events that have influenced this post are:

So what do you think? Inspired to issue badges from Google Apps? Have I missed anything? What isn’t clear? I look forward to your thoughts ;)


I'm returning to university and wonder if you could recommend a gadget for note taking? I'd like to use my MacBook - typing would be easier than writing - but I'm not sure it would be practical. Is there a device designed for note taking?

Guardian Gadget Clinic, 23rd August 2008

This question appeared in the Guardian Gadget Clinic recently and the recommendation by Bobbie Johnson was to stick with the MacBook because of it's larger keyboard and screen or alternatively go for a low cost PDA or a smartphone. An alternative family of devices not mentioned in the article are Netbooks.

These devices are defined by Wikipedia as "small-sized, low-cost, light weight, lean function subnotebooks optimized for Internet access and core computing functions (e.g. word processing)". The first modern Netbook* hit the UK market in November 2007 was the ASUS eeePC 701. I was fortunate to be one of the first to hand over £230 and get my hands on a 701 and it has been close to my side ever since. So 10 months on if I was looking for a Netbook what would I be looking for?

Operating system - go for Windows

The majority of Netbooks have the option of Linux or Windows operating systems. When I got the 701 the only option was Linux which for me was a great opportunity to learn a new operating system. I've found that Linux does need a lot of behind the scenes tweaking to get it to work with some wireless networks and Bluetooth devices.  My advice would be either to buy with Windows or go for the cheaper Linux version and install Windows (if your campus agreement allows it).

Connectivity - wireless + Bluetooth

There are a number of manufacturers making very small Bluetooth adapters

The majority of Netbooks come with your standard wireless 801.1b/g connection. Having a Ethernet connection has come in handy when in the office. Built-in Bluetooth is a bonus but if it doesn't come as standard you can get a small plug-in dongle for less than £10. For a data connection when your in a wireless blackspot you can use a USB modem dongle. The latest Netbooks are now including built-in sim cards to give you a data connection over 3G networks. Personally, I don't like the idea of been locked into a contract  and the monthly tariffs still seem very high. My solution is to share the data connection on my mobile phone. Windows Mobile 6 is particularly good at sharing an Internet connection via Bluetooth. Here are other ways to connect to the Internet via a mobile phone (I personally use a PAYG account with the '3' network who have a £5/month fair-use Internet add-on).

Screen resolution - at least 1024×600

As the majority of web pages are designed for a screen width of 1024 pixels I would recommend that this is the minimum resolution you should go for (Note: there is a difference between screen resolution and viewable image size. It's possible to have a small screen with a high resolution, so check the devices specification). Screen height is often an issue with Netbooks because of the widescreen format. Space can also be quickly eaten up by toolbars and drop down menus. In Firefox this can be overcome with carefully selected themes and add-ons. I recommend Compact Classic theme and Glazoom zoom extension.

Storage - 8Gb SSD

If you are prepared to do some windows maintenance, removing temporary files, 8Gb is enough to install windows and office applications. I would recommend going for a solid state drive (SSD) because it has no moving parts which should make it more reliable.

Size - keep it compact (225x165mm)

If you have nimble typing fingers I find a width of 225mm is the most you need for a decent size keyboard. I recently got a chance to see some of the new Netbooks from HP and Acer and felt that there portability was compromised by a larger keyboard.

Cost - less than £250

I don't see a Netbook as a replacement for my home PC or laptop, but as a device I'm happy to chuck in my bag for when I'm out and about. Consequently, its a device I don't want to spend too much money on. My original attraction to the ASUS eeePC 701 was its portability but it was it's price which made it a justifiable purchase.

Which Netbook would I buy?

So if I was going to buy a Netbook today which one would I buy (new Netbooks are being announced regulatly so the list will quickly become obsolete).

  • ASUS eeePC 701 - Screen too small and not enough disk space.
  • ASUS eeePC 900 - Enough screen resolution, disk space and a similar compact form of the 701. At around £260 I might be tempted but the batery life is supposed to be poor.
  • ASUS eeePC 901 - Again similar compact form of the 701 and 900. More performance from the Intel Atom processor and integrated Bluetooth. This issue for me with the 901 is price.
  • Acer Aspire One - This ticks all the boxes and with a Linux version with 12Gb SSD you can pick one up for £200. I was almost tempted to buy as a replacement to my 701 but when I went to see it in the shop it immedaitely looked too bulky for me.
  • MSI Wind - It has a 10" screen but the maximum resolution is only 1024x600. The bigger screen just adds bulk and drains the battery quicker.
  • HP Mininote - While having a 8.9" screen it boasts a screen resolution of 1280 X 768. At 1.3kg its too heavy and bulky for me. Your also paying a premium for the HP branding.
  • Elonex One - Hmm, at £100 the price makes it very appealing but the screen is too small and the spec reflects the price. One of the worst keybaords I've ever used. Not for me.
  • Dell Inspiron Mini - Released 2 days ago. Currently Dell are only offering the higher spec Windows XP version with 1Gb RAM at £300. Potentially this device has everting I want but the price isn't right yet.

In summary my ideal Netbook isn't available for the right price yet but I'm sure with such a competive market I may be retiring my 701 soon (possibly for the Dell Mini).

1 Comment

I came across this post by Zaid Ali Alsagoff the other day. Basically Zaid has compiled a list of almost 250 repositories containing free OpenCourseWare and Open Educational Resources. The list is billed as being for higher education including OCWs from MIT and OU's OpenLearn, but there are plenty of resources which would be suitable for FE and schools (e.g. Jorum, Intute, TeacherTube, HowStuffWorks).

Tony Hirst had the brilliant idea of making the list of repositories searchable by creating a Google custom search (basically instead of individually searching 250 sites you can search them all from Tony's custom Google search page). This has now evolved to Scott Leslie's version which contains a wiki for people to add more OER/OCW links to another Google custom search.

This is definitely a great resource for staff and students!!!


I was recently looking for an accessible way to generate chart data when I came across Chris Heilmann example for Generating charts from accessible data tables and vice versa using the Google Charts API. One of the limitations of Chris's solution was it only generated pie charts. Having some time to kill over the weekend I've made some additions to Chris's original script. The biggest change has been the inclusion of line charts.

The changes are probably best illustrated by this demo page. On this page I've copied Chris's original pie charts and included the new charts generated by the script. Most of the changes reflect suggestions from the comments on Chris's blog (i.e. alt tag [Ben  Millard], fixed 3 digit limit [Robin Winslow]).

The big addition has been the inclusion of support for line charts. This uses the same principle of reading data from a table but unlike the original script which only read the first two columns the new script now reads in the entire table. Table headings are read as the data legends and the y-axis automatically scales to fit the entire data range (so far I've only got it working for absolute numbers).

One feature I've removed from Chris's original script is the function to create a table from a chart.

You can download the script with the demo page and have a play around yourself. If anyone else is interested in developing this further leave a comment.

[I should also point out that I'm a 'hack' programmer so if anyone would like to tidy up my revised code please feel free].