Counting Colours in Google Sheets

A function to count the number of cells with a given background colour:

function COUNTCOLOUR(range, colour) {
  // Get sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get the formula from the cell and extract the range as a string
  // Google doesn't pass ranges through, it passes arrays of data within the range instead :(
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  // So... let's extract the bits between the brackets in the formula
  var args = formula.match(/\((.*?)\)/);
  // Now let's grab the first argument which should be a string representing the range
  var rangeString = args[1].split(',')[0]
  try {
    var range = sheet.getRange(rangeString);
  catch(e) {
    throw new Error(rangeString + ' is not a valid range.');

  // Get an array of all the colours in the range
  var colours = range.getBackgrounds();

  var count = 0;
  //Check each colour
  for (var i in colours){
    if (colours[i] == colour) {
      count = count + 1;

  return count;

The function should be called like so:

=COUNTCOLOUR(A1:A100, '#ff0000')

This example would count the number of red cells in the range A1:A100. Unfortunately, Google Sheets doesn’t trigger an update for this when a colour changes, it only does so when cell contents change rendering it largely useless 🙂

Synthesia 10.2 on Ubuntu

Synthesia runs very well on Ubuntu under wine, however, only if using OpenGL rather than Direct3D in my experience.

There are many guides that reference changing this setting by editing the wine registry, or using SynthesiaConfig.exe, however with 10.2 (and presumably above) neither of these seem to work. The registry entry is ignored and the config tool is missing.

Settings are now stored in an XML file located at: /home/<your username>/.wine/drive_c/users/<your username>/Application Data/Synthesia/settings.xml

Edit this file to include the line “<setting key=”Graphics.Renderer”>opengl</setting>” under <settings> and start Synthesia.

Add eduroam (or any other 802.1x) wireless network to Ubuntu Phone

I’m currently running Ubuntu Touch on a Nexus 4, unfortunately at present, there is no way to connect to 802.1x secured networks through the GUI. It is, however, possible to do this through the terminal.

Start by enabling developer mode on the Ubuntu handset, go to System Settings/About this phone/Developer mode and enable the “Developer Mode” tickbox. You will need to set a passcode/passphrase to do this. The code/phrase you set will be the password for the phablet user once we get a shell on the phone.

Now connect your phone to an Ubuntu laptop/desktop and then run the following in a terminal:

sudo apt-get install android-tools-adb
adb shell
cd /tmp

So at this point, we’ve opened a shell on the phone and changed to the /tmp directory. Due to the write-protected nature of the phone’s file system, we now have to create a network config file by piping its contents into place. Simply using nano or any other editor here won’t allow you to save your changes.

As you’ll need to modify this line for your own 802.1x network, it can be a little cumbersome, but the key fields are id (currently set to eduroam), identity (the username you’ll use, here) and then password (shown here as YourPasswordHere). Modify these fields accordingly and ensure that the file you’re piping to is called the ssid of the network, so here eduroam.

printf "[connection]\nid=eduroam\nuuid=f789bb72-99e9-43ba-a5e4-68b2b53d6e98\ntype=802-11-wireless\n\n[802-11-wireless]\nssid=eduroam\nmode=infrastructure\nsecurity=802-11-wireless-security\n\n[802-11-wireless-security]\nkey-mgmt=wpa-eap\n\n[ipv4]\nmethod=auto\n\n[ipv6]\nmethod=auto\n\n[802-1x]\neap=peap;\\nphase2-auth=mschapv2\npassword=YourPasswordHere\n" > eduroam

You can check for obvious errors by typing “cat eduroam” to show the contents of the new file and how it’s formatted. If you need to change anything you’ll have to printf the entire string to the file again, overwriting what’s there already. Now we move this file to the appropriate directory for Ubuntu to be able to use it:

sudo mv /tmp/eduroam /etc/NetworkManager/system-connections/

The password for phablet is the pin/security phrase you set earlier.

And then set the correct permissions for it, this is very important else it will be ignored:

sudo chmod 600 /etc/NetworkManager/system-connections/eduroam

That’s it… if it hasn’t connected straight away, turn wifi off and back on or try selecting the network’s ssid from the list and it should just connect.

Ubuntu 14.04 Ultimate Server Guide

This guide will help you configure Ubuntu Server Edition 14.04 for a small/medium business. The server will provide DHCP, DNS, NTP, LDAP, Kerberos and NFS services such that users can login to any machine on the network and all their files and settings will be the same across the entire network. This will be one unified setup, supporting Ubuntu, Windows and OS X clients… let the fun begin! Continue reading Ubuntu 14.04 Ultimate Server Guide

DLNA/UPnP Servers on Ubuntu

DLNA is great. It allows smart TVs, blu-ray players, phones, laptops etc. to access your Photos, Videos and Music across your network. Once you’ve set it up… it “just works”. The first step in setting it up though, is choosing a DLNA server to run. Ubuntu has a myriad of options in its repositories and there are even some non-free third party options with support for Ubuntu.


My setup is relatively simple, I have one LG smart TV and one Ubuntu powered file server housing all my media. Occasionally I use VLC on a laptop to access DLNA shares, but I usually use sFTP instead. Which brings us on to the first issue… Every one of the DLNA servers I’ve tried (MediaTomb, Rygel, MiniDLNA/ReadyMedia, et. al.) relies on inotify to be informed of changes to your media library. If, like me, you usually manage this library over sFTP… then inotify events aren’t triggered and you have to manually reload the DLNA server to pick up file changes.

MediaTomb, however, also offers a “timed” setting. This tells MediaTomb to check for changes to the media library at a defined interval. It’s also not at all resource intensive… in fact, in my example config below, MediaTomb checks for changes every 30 seconds and places an almost immeasurable load on my atom powered server when doing so.


An excellent choice if you want to run the DLNA server on your laptop/desktop. Perhaps you don’t have a dedicated server, but you do have a smart TV and would occasionally like to watch media stored on your computer across your network. Rygel has a simple UI and is easily configured by even the most novice of computer users. Whilst it is possible to run it on a headless server, the default packages in the Ubuntu repositories do not provide the scripts required to manage it as a service. It is possible to write your own… but I didn’t find that Rygel offered anything above the other contenders to make this worthwhile.


An up and coming entry… ReadyMedia (or MiniDLNA as it was previously known and is still called in the repositories) is written by Netgear for their ReadyNAS productline, like the other offerings here, it is completely free and open source (GPLv2).

ReadyMedia was my preferred choice for a while. It’s configuration is far simpler than MediaTomb and it’s not only rock solid in daily use, but is also still being actively developed with both new features and fixes. However, it currently lacks anything other than inotify support for detecting changes to media. Furthermore, simply restarting the service with “sudo service minidlna restart” doesn’t check for new media either. Instead, “sudo service minidlna force-reload” is required, which rebuilds the entire database from scratch every time and is a somewhat lengthy process. If you manage your media files using NFS, or indeed directly on the server, then this shouldn’t be a problem… but if you find you’re usually connected to your server of sFTP for file management, then ReadyMedia probably isn’t for you.


MediaTomb is fairly old… and it’s fair to say that work on it appears to have slowed over the years. However, there were fresh commits at the end of 2013 and the latest release is still less than a year old. I’d say it’s far from being dead and that actually, the longer release cycles are more to do with the projects maturity than it being dead.

It has more features than most of the other servers out there combined! But better yet… they work! In my testing it has been by far the most stable and reliable software in its field and with this sort of service, that’s what you want. If you’re sitting down to watch a film, or the latest episode of some US series you’ve obtained perfectly legally… the last thing you want to have to do is ssh into your server and restart services, edit configs etc.

My full config is below. It shares media from /home/dan/Videos (which MediaTomb has read-only access to) and it rescans this folder every 30 seconds in case something is changed over sFTP. Simple. You can of course get MediaTomb to do much more, including re-encoding media on the fly if your device isn’t capable of playing it. The configuration can be a little complicated… but then you’d expect that with such a myriad of options. For more detailed config consult the official docs.

<?xml version="1.0" encoding="UTF-8"?>
<config version="2" xmlns="" xmlns:xsi="" xsi:schemaLocation=""><!--
     Read /usr/share/doc/mediatomb-common/README.gz section 6 for more
     information on creating and using config.xml configration files.
    <ui enabled="no" show-tooltips="yes">
      <accounts enabled="no" session-timeout="30">
        <account user="mediatomb" password="mediatomb"/>
    <storage caching="yes">
      <sqlite3 enabled="yes">
      <mysql enabled="no">
    <protocolInfo extend="yes"/><!-- For PS3 support change to "yes" --><!--
       Uncomment the lines below to get rid of jerky avi playback on the
       DSM320 or to enable subtitles support on the DSM units
      <add header="X-User-Agent: redsonic"/>

    --><!-- Uncomment the line below if you have a Telegent TG100 --><!--
      <ffmpegthumbnailer enabled="no">
      <mark-played-items enabled="no" suppress-cds-updates="yes">
        <string mode="prepend">*</string>
  <import hidden-files="no">
    <autoscan use-inotify="auto">
      <directory location="/home/dan/Videos" mode="timed" interval="30" level="full" recursive="yes" hidden-files="no"/>
    <scripting script-charset="UTF-8">
      <virtual-layout type="builtin">
      <extension-mimetype ignore-unknown="no">
        <map from="mp3" to="audio/mpeg"/>
        <map from="ogg" to="application/ogg"/>
        <map from="asf" to="video/x-ms-asf"/>
        <map from="asx" to="video/x-ms-asf"/>
        <map from="wma" to="audio/x-ms-wma"/>
        <map from="wax" to="audio/x-ms-wax"/>
        <map from="wmv" to="video/x-ms-wmv"/>
        <map from="wvx" to="video/x-ms-wvx"/>
        <map from="wm" to="video/x-ms-wm"/>
        <map from="wmx" to="video/x-ms-wmx"/>
        <map from="m3u" to="audio/x-mpegurl"/>
        <map from="pls" to="audio/x-scpls"/>
        <map from="flv" to="video/x-flv"/>
        <map from="mkv" to="video/x-matroska"/>
        <map from="mka" to="audio/x-matroska"/><!-- Uncomment the line below for PS3 divx support --><!-- <map from="avi" to="video/divx"/> --><!-- Uncomment the line below for D-Link DSM / ZyXEL DMA-1000 --><!-- <map from="avi" to="video/avi"/> -->
        <map from="audio/*" to="object.item.audioItem.musicTrack"/>
        <map from="video/*" to="object.item.videoItem"/>
        <map from="image/*" to="object.item.imageItem"/>
        <map from="application/ogg" to="object.item.audioItem.musicTrack"/>
        <treat mimetype="audio/mpeg" as="mp3"/>
        <treat mimetype="application/ogg" as="ogg"/>
        <treat mimetype="audio/x-flac" as="flac"/>
        <treat mimetype="image/jpeg" as="jpg"/>
        <treat mimetype="audio/x-mpegurl" as="playlist"/>
        <treat mimetype="audio/x-scpls" as="playlist"/>
        <treat mimetype="audio/x-wav" as="pcm"/>
        <treat mimetype="audio/L16" as="pcm"/>
        <treat mimetype="video/x-msvideo" as="avi"/>
        <treat mimetype="video/mp4" as="mp4"/>
        <treat mimetype="audio/mp4" as="mp4"/>
        <treat mimetype="application/x-iso9660" as="dvd"/>
        <treat mimetype="application/x-iso9660-image" as="dvd"/>
        <treat mimetype="video/x-matroska" as="mkv"/>
        <treat mimetype="audio/x-matroska" as="mka"/>
    <online-content><!-- Make sure to setup a transcoding profile for flv -->
      <YouTube enabled="no" refresh="28800" update-at-start="no" purge-after="604800" racy-content="exclude" format="flv" hd="no">
        <favorites user="mediatomb"/>
        <standardfeed feed="most_viewed" time-range="today"/>
        <playlists user="mediatomb"/>
        <uploads user="mediatomb"/>
        <standardfeed feed="recently_featured" time-range="today"/>
      <Weborama enabled="no" refresh="28800" update-at-start="no">
        <playlist name="Active" type="playlist" mood="active"/>
        <playlist name="Metal" type="playlist">
      <AppleTrailers enabled="no" refresh="43200" update-at-start="no" resolution="640"/>
  <transcoding enabled="no">
      <transcode mimetype="video/x-flv" using="vlcmpeg"/>
      <transcode mimetype="application/ogg" using="vlcmpeg"/>
      <transcode mimetype="application/ogg" using="oggflac2raw"/>
      <transcode mimetype="audio/x-flac" using="oggflac2raw"/>
      <profile name="oggflac2raw" enabled="no" type="external">
        <agent command="ogg123" arguments="-d raw -o byteorder:big -f %out %in"/>
        <buffer size="1048576" chunk-size="131072" fill-size="262144"/>
      <profile name="vlcmpeg" enabled="no" type="external">
        <agent command="vlc" arguments="-I dummy %in --sout #transcode{venc=ffmpeg,vcodec=mp2v,vb=4096,fps=25,aenc=ffmpeg,acodec=mpga,ab=192,samplerate=44100,channels=2}:standard{access=file,mux=ps,dst=%out} vlc:quit"/>
        <buffer size="14400000" chunk-size="512000" fill-size="120000"/>

Using WordPress Behind a Proxy

Occasionally, it’s useful to setup a test WordPress installation on a local machine, however, if this machine is on a corporate network, there’s a good chance it uses a proxy server for external requests… this is fine, until you try to install a theme, or a plugin through the built in tools. When these pages try to access to find plugin/theme data, errors are going to be returned and WordPress will return something like:

An unexpected error occurred. Something may be wrong with or this server’s configuration. If you continue to have problems, please try the support forums.

Try again

To fix this, simply tell WordPress about your proxy server by adding the following to wp-config.php in the root of your WordPress install:

/* Configure proxy Server */
define('WP_PROXY_HOST', '');
define('WP_PROXY_PORT', '3128');
define('WP_PROXY_USERNAME', '');
define('WP_PROXY_PASSWORD', '');
define('WP_PROXY_BYPASS_HOSTS', 'localhost');

The above example is configured for The University of Leeds’ proxy server. You should be able to obtain your network’s own configuration from your browser config.

Ubuntu 12.04 Ultimate Server Guide

This guide will help you configure Ubuntu Server Edition 12.04 for a small/medium business. The server will provide DHCP, DNS, NTP, LDAP, Kerberos and NFS services such that users can login to any machine on the network and all their files and settings will be the same across the entire network. This will be one unified setup, supporting Ubuntu, Windows and OS X clients… let the fun begin! Continue reading Ubuntu 12.04 Ultimate Server Guide

How to Build an Ubuntu 11.10 SBS (Small Business Server)

This guide will help you configure Ubuntu Server Edition 11.10 for a small/medium business. The server will provide DHCP, DNS, NTP, LDAP, Kerberos and NFS services such that users can login to any machine on the network and all their files and settings will be the same across the entire network.
Continue reading How to Build an Ubuntu 11.10 SBS (Small Business Server)