UK Postcode Geocoder

UK Postcode Geocoder is a Google Sheets addon to convert UK postcodes into geographic information, including latitude/longitude coordinates, country, region and constituency.

Installation

You can install the addon to Google Sheets via the Google Chrome store.

Source code on Github.

How do I use it?

Select cells containing postcode data and click “Add-ons > UK Postcode Geocoder > Geocode selection” to convert the data into latitude/longitude.

Results will be displayed in a new sheet.

What do you mean by ‘UK postcodes’?

The application currently will only provide latitude and longitude coordinates for active (i.e. currently in use and no discontinued) UK postcodes according to the ONS’ Postcode Directory for November 2014 via postcodes.io: http://postcodes.io/about

Postcodes can be upper or lower case and contain no spaces or 1 space before the last 3 characters (see examples below). Other formatting is likely to return an error.

  • wc1e 6bt
  • wc1e6bt
  • SW4 0BY
  • SW40BY

Postcodes or zip codes from other countries will return a ‘not a valid postcode’ error.

Where is your data from?

This application uses UK postcode data under the Open Government Licence for public sector information.

The data is pulled from the postcodes.io API.

Twitter Follower Count

This is a Google Script to get the Twitter follower numbers for selected Twitter handles in a Google Sheet. The follower numbers are outputted in a new tab of the same Google Sheet.

View source code on Github.

How do I use it?

You’ll need to set this up as a standalone Google Script in Google Drive as then test it as an add-on to actually run the script:

  1. Create new blank Google script in Google Drive (not as part of a spreadsheet but standalone)
  2. Paste the above code into the script and save it
  3. Replace ‘##############’ in the config object with your consumer key and consumer secret (lines 13-14) from https://apps.twitter.com/
  4. Add the OAuth1 library as explained in the “Setup” section here https://github.com/googlesamples/apps-script-oauth1

Test as addon:

  1. click Publish >> Test as addon
  2. Create a new test: select “Test with latest code”, “Installed and enabled” and then for “Select doc” choose a Google Sheet file
  3. Click Save and then select the test you just created and click “Test”
  4. You’ll be redirected to the selected Google Sheet where you can run the addon.

Select the cells with the Twitter handles in

  1. Click Addons >> [name you gave the code file] >> Get Twitter Followers
  2. Authorise the app with Google
  3. Login to Twitter when redirected to authorise there
  4. … finally, go back and select the cells with the Twitter handles in again
  5. Click Addons >> [name you gave the code file] >> Get Twitter Followers
  6. The data for the Twitter followers will be published in a new tab in the same sheet!

Google Script snippets

A selection of simple javascript snippets and functions that I’ve used in Google Scripts.

I’ll likely add to these over time but, at present, the list includes:

  1. Clean string
  2. Convert a two dimensional array to a one dimensional array
  3. Get first empty column in a Google Sheet
  4. Get first empty row in a Google Sheet
  5. Get last row in a Google Sheet
  6. Replace html entities in each array element
  7. Calculate if paramater is a letter
  8. Test if input is a letter or a number
  9. Get today’s date

Source code on Github.

1. Clean string


/**
* Strips spaces and line breaks from string
*
* @param {String} s
* @return {String} stripLineBreaks
*/
function cleanString(s) {

  var stripSpaces = s.replace(/\s+/g, '');
  var stripLineBreaks = stripSpaces.replace(/\r?\n|\r/g, '');

  return stripLineBreaks;
}

2. Convert a two dimensional array to a one dimensional array


/**
* Converts a two dimensional array to a one dimensional array
*
* @param {Array} twoDimArray
*
* @return {Array} oneDimArray
*/
function convertTwoToOneDimArray(twoDimArray) {
  "use strict";
  var oneDimArray, outputArrayCount, i, k, j, m;
  oneDimArray = [];
  outputArrayCount = 0;
  for (i = 0, k = twoDimArray.length; i < k; i = i + 1) {

    for (j = 0, m = twoDimArray[i].length; j < m; j = j + 1) {

      oneDimArray[outputArrayCount] = twoDimArray[i][j];
      outputArrayCount = outputArrayCount + 1;

    }
  }

  return oneDimArray;

}

3. Get first empty column in a Google Sheet


/**
* Gets first empty column from spreadsheet object
*
* @param {Object} sheet
* @return {Number} total
*/
function getFirstEmptyCol(sheet) {

  /*source: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script*/
  var range = sheet.getDataRange();
  var values = range.getValues();
  var valueLength = values.length;
  var count = 0;
  var total = 0;
  for (var row=0; row<valueLength; row+=1) {
   for (var col=0; col<values[row].length; col+=1) { 
     count++; 
     if (count > total) {
       total = count;
     }
   }
   count = 0;
  }
return (total+1);
}

4. Get first empty row in a Google Sheet


/**
* @desc Get number of first empty row of spreadsheet
* @param ss (object)
* @return count (number)
* @source: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script
*/
function getFirstEmptyRow(ss) {
  "use strict";
  var column, values, count;
  column = ss.getRange('A:A');
  values = column.getValues();
  count = 0;
  while ( values[count][0] !== "" ) {
    count = count + 1;
  }
  count = count + 1; /*because index starts at 0*/
  return count;
}

5. Get last row in a Google Sheet


/**
* Get last row from column
*
* @param {object} sheet
* @param {number} columnNum
* @param {number} lastRow
*
* @return {Number}
*/
function getLastRowInCol(sheet, columnNum, lastRow) {
  var data = sheet.getRange(1, columnNum, lastRow, 1).getValues();
  var lastRowInCol = 0;
  for (i = lastRow -1; i > 0; i--) {
    if (data[i] != "") {

    return i+1;
   }
  }
}

6. Replace html entities in each array element


/**
* @desc replaces html entities in each array element
* @param outputArray (array)
* @return outputArray (array)
* source: http://css-tricks.com/snippets/javascript/htmlentities-for-javascript/
*/
function htmlEntities(outputArray) {
  "use strict";
  var outputArrayElement, i, j;
  outputArrayElement = "";
  for (i = 0, j = outputArray.length; i < j; i = i + 1) {
    if (typeof outputArray[i] === "string") {
     outputArrayElement = outputArray[i];
     outputArrayElement.replace(/&/g, '&').replace(//g, '>').replace(/"/g, '"');
     outputArray[i] = outputArrayElement;
    }
  }
  return outputArray;
}

7. Calculate if parameter is a letter


/**
* Calculates if parameter is a letter
*
* @param {String} text
* @return {Boolean}
*/
function isLetter(text) {
  var letters = /^[A-Za-z]+$/;
  if (text.match(letters)) {
    return true;
  } else {
    return false;
  }
}

8. Calculate if parameter is a letter or a number


/**
* Calculates if parameter is a letter or number
*
* @param {String} text
* @return {Boolean}
*/
function isLetterOrNumber(text) {
  var characters = /^[A-Za-z0-9]+$/;
  if (text.match(characters)) {
    return true;
  } else {
    return false;
  }
}

9. Get today’s date


/**
* @desc Returns today's date in (d)d/(m)m/yyy format
* @return today (string)
*/
function todayDate () {
  "use strict";
  var today, day, month, year;
  today = new Date();
  day = today.getDate();
  month = today.getMonth()+1; /*January is 0*/
  year = today.getFullYear();
  today = day+'/'+month+'/'+year;
  return today;
}