Color Scales Spreadsheet
STOP PRESS: Color Scale has now been released as an GSheet Add-on.
The Google Apps Script contained in this spreadsheet sets the background color of the cells in a selected range according to their numeric value. This is similar to the ‘color scales’ or color gradient feature in Microsoft Excel. The lowest number found in the selection is set to the low color, the highest value to the high color and every value in between is colored dependant on their position between the high and the low.
Get in touch now if you need any help with this or any other GAS scripts or have a look through some other free scripts and snippets.
Usage
Make your own copy and run it from there:
- Follow the link to view a read-only copy of the spreadsheet.
- Login with your own Google account (Sign-up top right-hand corner).
- Make a copy of the spreadsheet (File>Make a copy…).
- The copy will appear in the root of your Google Drive from where you can open it.
- Select a range of numbers and click on Color Scale>Color Scale Selection.
- To change the low and high colors click on Color Scale>Choose Colours and add your own colors as three decimal RGB values between 0 and 255.
Note: I know I’m British and I should be using “colour”, but for the international audience I’m using “color”. 🙂
Versions
Version 0.2
- Added a dialog box to choose the colors rather than hard-coding them (they are now stored in UserProperties).
- Added single call (setBackgrounds()) to set backgrounds plus other optimisations.
Version 0.1
- First Version.
If you find any of the free software on this site useful please make a contribution to support further development:
How do I use this in my own google sheet? It only shows up in the copy sheet but I can’t figure out how to run the script in my own sheet
Hi, Copy the script into your own sheet. Go to Tools>Script editor… in your copy of my sheet, copy the content of code.gs (CTRL-A, CTRL-C), go back to Tools>Script editor… on your own sheet and paste the contents of my code.gs into the blank code.gs you find there. You’ll need to authorise the script again and save it, but once you go back to your sheet and refresh the page you should now have a Colour Scale menu.
I’ve just found another, more comprehensive, implementation of excel’s color scales called color ramp.
Thank you very much for pointing me to this other color ramp.
Thank you very much Andrew for sharing your work. This will do for now until I have some time to look at how to use the color ramp implementation. Hopefully Google will integrate this natively in the next couple of years. 😉