Sulka's Game is a weblog by Sulka Haro. The subjects range from gaming to dragonflies.

Nicely colour data in Google Spreadsheets

Update: Google Sheets now has conditional formatting built in, so this script is not needed anymore; leaving it here for reference.

I love a feature in Excel (never thought I’d say that!) where I can choose a range of data and have Excel colour the backgrounds of the cells from red to green or green to red based on the cell’s value in the selected range. Excel calls this “Conditional formatting” and while Google Spreadsheets has a feature that has the same name, it’s really a completely different thing and it’s driving me nuts. But it has scripting, so I had to fix this lack of functionality.

To install this script, open your spreadsheet, open Script editor… in the Tools menu. Paste the source in and save. Then reload the actual spreadsheet. You should now have a new menu in your toolbar called ColourTool, with two options to colour cells. Choose the range you want to colour, choose an option and tada, your cells change background colour.

Note: This has not been tested extensively. There’s no code here to change the actual data, but, use at your own risk.