Automatic colouring of a specific value wherever found in a table [closed]

Question

I am looking for a way to automatically change the color of the minimum cell in a row in Google Sheets. So, for a table like:

1 | 2 | 3
4 | 2 | 1
2 | 1 | 6

it would color all the cells with 1 in them.

Fairhaired

Not necessarily programming related.. – Mabellemable

Conditional formatting, even if this it's done through a user interface is related to programming as it requires to analyze a problem in order to define a set of rules in such way that a computer could do a task. Considering this I think that this question should be reopened. – Hydranth

Answer 1

In the dropdown menu, Format->Conditional formatting...

Then set your rules and your color. You can select multiple cells and do this also.

Edit:

That is the extent of what you can do with coloring. You can maybe find a complex formula to find the minimum of the cells, and then if that matches what is in the cell, then color it, but you may want to use Excel instead of Google docs if this is a critical thing for you.

Arri

IF you can't accomplish what you need using conditional formatting you can always use the script editor to modify the onEdit event to check the cell and change the coloring depending on any condition you could imagine. No need (or use) for Excel. – Hallowell

This has changed since 2015 but it did get me what I needed! You can now click Format -> Conditional formatting... -> Color Scale. – Angus

@Arri Cool, thanks for the update. – Hallowell

Is there a way for it to automatically just use the existing list's formatting? Creating rules one by one is tedious. – Latish

Answer 2

The trick is to tap into the onEdit event trigger and add some intelligence

At first glance I thought conditional formatting would work but the minimum-per-row is a little too complex for the standard conditional formatting. It's a little tricky to figure out but it can be done.

Here's the full script (tested and working):

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveRange();
  var row = r.getRow();
  var cols = s.getDataRange().getNumColumns();

  // crate a range for the row using getRange(row, column, numRows, numColumns)
  var rowRange = s.getRange(row, 1, 1, cols);
  var rowValues = rowRange.getValues();

  // check all the values in the row
  var val = 999;
  for(var i = 0; i < cols; i++) {
    if(val > rowValues[0][i] && rowValues[0][i] !== "") {
      val = rowValues[0][i];
    }
  }
  for(var j = 0; j < cols; j++) {
    if(rowValues[0][j] === val) {
      s.getRange(row,(j + 1)).setFontColor("blue");
    } else {
      s.getRange(row,(j + 1)).setFontColor("black");
    }
  }
}

First you tap into the onEdit event handler to trigger the script with the spreadsheet changes.

function onEdit()

By naming a function onEdit it will automatically know that you want to override the onEdit action.

Note: Event handlers in Docs are a little tricky though. Because docs can handle multiple simultaneous edits by multiple users, the event handlers are handled server-side. The major issue with this structure is that when an event trigger script fails, it fails on the server. If you want to see the debug info you'll need to setup an explicit trigger under the triggers menu that emails you the debug info when the event fails or else it will fail silently.

Fetch the row number:

var r = s.getActiveRange();
  var row = r.getRow();

Pretty self explanatory here. The active range is the cell being edited.

Grab the number of columns:

var cols = s.getDataRange().getNumColumns();

You need to check the data range for the whole spreadsheet for this.

Next you need to construct a data range containing the data for the row in question:

var rowRange = s.getRange(row, 1, 1, cols);

Read the comments in the code to see what the values should be.

Then we cache the results for testing of values:

var rowRange = s.getRange(row, 1, 1, cols);

Due to the nature of Google Docs scripting event callbacks run server-side so, to prevent abuse, Google puts a time limit on script execution. By caching the values, you save the server from making lots of unnecessary round trips to fetch the values from the spreadsheet.

The next two parts are where all the magic happens.

First, we make a pass through all the cells of the row to find the minimum value.

  var val = 999;
  for(var i = 0; i < cols; i++) {
    if(val > rowValues[0][i] && rowValues[0][i] !== "") {
      val = rowValues[0][i];
    }
  }

I set a default ceiling of 999 for the sake of simplicity. This could be change to a more appropriate value. The trick is to test the value vs the current low. If it's lower, then mark the new low value.

You could get off easy by marking the cell number with the lowest value and setting it explicitly but I wanted to cover cases where multiple cells have the lowest value.

Handling multiple cells containing the minimum requires a second pass:

for(var j = 0; j < cols; j++) {
  if(rowValues[0][j] === val) {
    s.getRange(row,(j + 1)).setFontColor("blue");
  } else {
    s.getRange(row,(j + 1)).setFontColor("black");
  }
}

The loop through all the cells in the row remains the same. This time we're just checking whether the cell values match the chosen minimum. If it matches, the font color changes to blue. Otherwise, the font color is changed to black.

---

That about sums it up. It's a little tricky to get used to the manner that Google Apps Scripting deals with referencing spreadsheets and data cells but there's not much that Docs can't do.

I have made a public link to the spreadsheet I used to write/test this code. Feel free to try it out.

Hallowell