Color scale with conditional formatting in Google Spreadsheet

Question

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

Embattled

Answer 1

Google Sheets now supports conditional color scales under Menu "Format > Conditional formatting..." then select the tab "Colour scale".

Embattled

Answer 2

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

Nancinancie

It'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. – Embattled

Answer 3

@Underlines shows only a single colour for the example but Sheets can achieve:

when applying the options as shown.

Asquint

Answer 4

There is an add-on that does this. It's open source and the code is a bit long to put in here but you can get it by taking a copy of this GSheet.

Disaccustom