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, drb502@york.ac.uk 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;\nidentity=drb502@york.ac.uk\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.