CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docsgs-conditional-formatting
Underlines
Underlines

Reputation: 917

Color scale with conditional formatting in Google Spreadsheet

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:

Color scale within Conditional Formatting in Microsoft Excel

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++;
  }
}

Thanks

Upvotes: 16

Views: 34654

Answers (4)

pnuts
pnuts

Reputation: 59485

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

SO19637514 example

when applying the options as shown.

Upvotes: 1

Underlines
Underlines

Reputation: 917

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

Google Sheets color scale

Upvotes: 21

Andrew Roberts
Andrew Roberts

Reputation: 2808

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.

Upvotes: -1

Gabriel Crivelli
Gabriel Crivelli

Reputation: 105

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

Upvotes: 1

Related Questions