6

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.

4
  • why don't you record a macro (a new feature of google sheets) and apply to to all rows.
    – Oren
    Dec 13, 2018 at 9:10
  • @OrenPinsky I tried it, but this only works with the same rows I record, with no shifting to other rows & tables (and I have lots of such tables with about 30 rows in each one). Is there possibility to get and improve the code of macro to make it appropriate for my task?
    – AivanF.
    Dec 13, 2018 at 12:19
  • It should not be too hard. Can you share a (sanitized) version of your spreadsheet so that I can make a try?
    – Oren
    Dec 13, 2018 at 12:21
  • @OrenPinsky I created a shareable link.
    – AivanF.
    Dec 13, 2018 at 12:36

2 Answers 2

4

I know I'm a bit late to the party... but I had this same question myself and then ended up finding the answer on my own with a little tinkering.

Basically, you just need to use "Number" for your min/mid/max values and then enter a formula with a partially locked reference (i.e. add a "$" before the column/row depending on your needs).

In my case I needed to values to reference on a "per row" basis, so I used "$F2" to ensure the reference value for each cell only changed as I shifted rows--but would remain the same across all columns in a given row. (So B2:E2 all reference F2, but B3:E3 instead reference F3.)

Hopefully that makes sense?

Example: Screenshot

If you're not trying to compare to a fixed value, you can also use =MIN(), =AVERAGE(), and =MAX() to set your points like in the example below (which also serves as an example of assessing "by column" instead of "by row")

Screenshot

^Is a bit cut off, so to clarify "=MIN(O$3:O$7)" was used to ensure the reference shifts to "MIN(P3:P7)" for the next column.^

2
  • Wow, it's the easiest solutions! What a pity I meet it after several years 😄
    – AivanF.
    Apr 13, 2022 at 9:21
  • Lol, yeah I found it hard to believe there wasn't a solution that didn't involve coding/manually applying lots of rules... (even if it was all I could find anywhere). After tinkering a bit more and finding one I couldn't help but make an account just to post it xD
    – BlackHatCS
    Apr 15, 2022 at 18:00
2

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!

5
  • I think you didn't understand my question... Your code makes the same as clicks in GUI: it creates conditional formatting with color scale on selected range. But I asked how to create many color scales on each row independently at once. So that it would possible to select all the table and get multiple different formatting ranges simultaneously... (I added more details and screenshots in the Q.) Maybe it's possible to add for loop on the selected rows to create different formatting rules?
    – AivanF.
    Dec 13, 2018 at 13:38
  • On the script above, you should select every row and then press ctrl-shift-1. Once per row.
    – Oren
    Dec 13, 2018 at 13:53
  • I understand, but it is not a significant improvement comparing with usual conditional formatting per each row :(
    – AivanF.
    Dec 13, 2018 at 13:55
  • I've edited the code to do what you need. Good luck!
    – Oren
    Dec 13, 2018 at 14:13
  • Awesome! This macro script does more than I was looking for, thans @Oren Jul 27 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.