Tag: google appscript

Whilst preparing for an open data training session for New Economy in Manchester earlier this week, I was introduced to the DWP tabulation tool that provides a quick way of analysing various benefits and allowances related datasets, including bereavement benefits, incapacity benefit and employment and support allowance.

The tool supports the construction of various data views as well as providing 1-click link views over “canned” datasets for each category of data.

The data is made available in the form on an HTML data table via a static URL (example):

To simplify working with data, we can import the data table directly into Google spreadsheets using the importHTML() formula, which allows you to specify a URL, and then import a specified HTML data table from that page. In the following example, the first table from a results page – that contains the description of the table – is imported into cell A1, and the actual datatable (table 2) is imported via an importhtml() formula specified in cell A2.

Note that the first data row does not appear to import cleanly – inspection of the original HTML table shows why – the presence of what is presumably a split cell that declares the name of the timeseries index column along with the first time index value.

To simplify the import of these data tables into a Google Spreadsheet, we can make use of a small script to add an additional custom menu into Google spreadsheets that will import a particular dataset.

The following script shows one way of starting to construct such a set of menus:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('DWP Tabs')
      .addSubMenu(ui.createMenu('Bereavement Benefits')
          .addItem('1-click BW/BB timeseries', 'mi_bb_b')
          .addItem('1-click Region timeseries', 'mi_bb_r')
          .addItem('1-click Gender timeseries', 'mi_bb_g')
          .addItem('1-click Age timeseries', 'mi_bb_a')
      )
      .addSubMenu(ui.createMenu('Incapacity Benefit/Disablement')
          .addItem('1-click Region timeseries', 'mi_ic_r')
      )
      .addToUi();
}

function menuActionImportTable(url){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var cell = sheet.getRange("A1");
  cell.setFormula('=importhtml("'+url+'","table",1)');
  cell = sheet.getRange("A2");
  cell.setFormula('=importhtml("'+url+'","table",2)');
}

//--Incapacity Benefit/Disablement
function mi_ic_r() {
  var url='http://tabulation-tool.dwp.gov.uk/flows/flows_on/ibsda/cdquarter/ccgor/a_carate_r_cdquarter_c_ccgor.html';
  menuActionImportTable(url)
}


//-- Bereavement Benefits
function mi_bb_r() {
  var url='http://tabulation-tool.dwp.gov.uk/flows/flows_on/bb/cdquarter/ccgor/a_carate_r_cdquarter_c_ccgor.html';
  menuActionImportTable(url)
}

function mi_bb_g() {
  var url='http://tabulation-tool.dwp.gov.uk/flows/flows_on/bb/cdquarter/ccsex/a_carate_r_cdquarter_c_ccsex.html';
  menuActionImportTable(url)
}

function mi_bb_a() {
  var url='http://tabulation-tool.dwp.gov.uk/flows/flows_on/bb/cdquarter/cnage/a_carate_r_cdquarter_c_cnage.html';
  menuActionImportTable(url)
}

function mi_bb_b() {
  var url='http://tabulation-tool.dwp.gov.uk/flows/flows_on/bb/cdquarter/ccbbtype/a_carate_r_cdquarter_c_ccbbtype.html';
  menuActionImportTable(url)
}

Copying the above script into the script editor associated with a spreadsheet, and then reloading the spreadsheet (permissions may need to be granted to the script the first time it is run), provides a custom menu that allows the direct import of a particular dataset:

Duplicating the spreadsheet carries the script along with it (I think) and can presumably also be shared… (It’s been some time since I played with apps script – I’m not sure how permissioning works or how easy it is to convert scripts to add-ons, though I note from the documentation that top-level app menus aren’t supported by add-ons.