Google Sheets – Conditional Formatting with Color Scale for Multiple Rows

conditional formattinggoogle sheetsgoogle-apps-scriptworksheet-function

I create reports with different cohort tables. Each row of a table must have conditional formatting with color scale (depending only on current row values, i.e. regardless of other rows min/max values). Are there possibilities (in the GUI or using some scripts) to make such formatting quickly?

At the moment, I add conditional formatting rule for each rows independently, it is very tediously and time-consuming!

Screenshot of Google Sheet table

I tried to find a solution on this and other websites, but found no such questions. I hope an answer exists, and someone will share it.


Update

What I need is creating multiple different color scale ranges on different rows independently but simultaneously:

What I need


Update 2

@OrenPinsky's solution is almost perfect! However, it takes only initial position for formatting and then goes down through all the rows until a blank one. It doesn't use selected row width, nor table height. So, better answers will be considered and appreciated as well.

Best Answer

on your spreadsheet, record any macro and create a keyboard shortcut for it. Then go to Tools->Macros->Manage macros and change the macro code to the following one (you might need to change the function name to the same of your macro).

/** @OnlyCurrentDoc */

function xxx() {

  var spreadsheet = SpreadsheetApp.getActive()

  do {
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();

    conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
    conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
      .setRanges([spreadsheet.getActiveRange()])
      .setGradientMaxpoint('#57BB8A')
      .setGradientMidpointWithValue('#FFFFFF', SpreadsheetApp.InterpolationType.PERCENTILE, '50')
      .setGradientMinpoint('#E67C73')
      .build());
    spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

    spreadsheet.getActiveCell().activate().offset(1,0).activate()

  }

  while (spreadsheet.getActiveCell().isBlank() == false)

};

Then, select the range on which you want to do the condition formatting and type your keyboard shortcut (on the example file it is ctrl-alt-shift 1)

Voila!