I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

On his oUseful blog, Tony Hirst  showed how to use Open refine plus various APIs to research which candidates were standing for election in the wards in which they live. This blog has unfortunately disappeared therefore i cannot point you to it anymore.

Here’s a VBA implementation of the same thing, which you can find in the scraperwiki module of cDataSet.xlsm.

Making a google Apps script version

Converting to Google Apps Script is pretty simple since I have the components already migrated from VBA.

The skeleton

In Automatic VBA to GAS skeleton I showed how to convert VBA modules to a skeleton Google Apps Script. I’ve taken the VBA implementation and imported that into the restlibrary examples Google Spreadsheet (see the questionElection sheet) and the restExamples function


I only have one one function to convert, since everything else is in the mcpher shareable library, and since the functions have been implemented in an abstracted way there is really only a little code/syntax tweaking needed. Here’s the result.

What it does

You can read about this in the VBA implementation, or in or in Tony’s original post, but in summary

  • Read electoral candidate results from scraperwiki
  • Get their postcode
  • Use the mysociety API to get electoral boundary information
  • Figure out whether the candidates live in the same wards for which they are standing 

The code

You can find the code by downloading the cDataSet.xlsm workbook. It’s in the scraperWiki module. Here’s a next steps, plotting directly to d3.js

Continue reading about Rest to Excel Library here

For help and more information join our forum, follow the blog or follow me on Twitter .