Color scale with conditional formatting in Google Spreadsheet
Solution 1
Google Sheets now supports conditional color scales under Menu "Format > Conditional formatting..." then select the tab "Colour scale".
Solution 2
@Underlines shows only a single colour for the example but Sheets can achieve:
when applying the options as shown.
Solution 3
I'm referencing @JacobJanTuinstra, who compiled/created a script to solve a similar issue, please see this: https://webapps.stackexchange.com/questions/48783/colorize-a-cell-in-google-spreadsheets-based-on-cell-data?rq=1
Related videos on Youtube
Comments
-
Underlines almost 2 years
I need a color scale on a row or column in Google Spreadsheet, like the one provided by Microsoft Office Excel conditional formatting with color scale:
I couldn't find a Google Script Gallery Script that can do exactly this job. Setting conditional formatting manually for all possible values is not a solution.
My abilities are limited to write a proper script. Therefore I came up with this pseudo code:
colorRangeFormatting(cellRange, minColorHex, maxColorHex) { float cellValueMax = getHighestValue(cellRange); float cellValueMin = getLowestValue(cellRange); int cellCount = range.length; int colorValueMax = maxColorHex.toInt(); int colorValueMin = minColorHex.toInt(); int colorSize = colorValueMax - colorValueMin; cellValueSize = cellValueMax - celLValueMin; int colorIncrement = (colorSize/cellValueSize).Round(); int[] colorGradients = colorGradients[colorSize]; foreach(int color in colorGradients) { color = colorValueMin + colorIncrement; colorIncrement = colorIncrement + colorIncrement; } int i = 0; foreach(Cell c in cellRange) { c.setBackgroundColor(colorGradients[i].ToHex()); i++; } }
- Is there any way to do it natively?
- or are there any google app scripts that do this (which I overlooked)?
- or is someone willing to help me bring my pseudo-code to a proper google app script for spreadsheet?
Thanks
-
Underlines over 10 yearsIt's a good base. I took the different script parts from the question and answer there and pasted a working version of their script: It will not color depending on max and min value like in Microsoft Excel's Function. It will just take each cells value, convert it to the RGB value and color it with that RGB value. So from 1-100 it will color from red to violet, and from 100-200 it will use the same colors again.