0

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.

  • why don't you record a macro (a new feature of google sheets) and apply to to all rows. – Oren Pinsky Dec 13 '18 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 '18 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 Pinsky Dec 13 '18 at 12:21
  • @OrenPinsky I created a shareable link. – AivanF. Dec 13 '18 at 12:36
1

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!

  • 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 '18 at 13:38
  • On the script above, you should select every row and then press ctrl-shift-1. Once per row. – Oren Pinsky Dec 13 '18 at 13:53
  • I understand, but it is not a significant improvement comparing with usual conditional formatting per each row :( – AivanF. Dec 13 '18 at 13:55
  • I've edited the code to do what you need. Good luck! – Oren Pinsky Dec 13 '18 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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