Google Sheets - Set background color of cell based on text value automatically

Question

Is it possible to change the cell's background, based on the text value of the cell, automatically? These would be for an arbitrary amount of values, not just pre-defined. The idea would be that a 'hash' is generated based on the text value, and then based on that hash, a color is chosen randomly. It doesn't matter which color it is, as long as different values have different colors to be differentiated easily.

For example, imagine the cell having values:

Dog
Cat
Mouse

They would all have different background colors, because they contain a different text value. The actual background color doesn't make a difference. What would ideally make a difference is that if the background color is 'dark', the text color would be white, and if the background color is 'light', text color would be black. This is so you can always read the text.

are you going to be specific about the column whose text decides what the whole row is? That's the use case that makes sense to me... — MattKing, May 26 '22 at 14:45

Emel · Accepted Answer · 2022-05-26T14:46:26.453

This is my approximation to the problem:

const sS = SpreadsheetApp.getActiveSheet()
const docProperties = PropertiesService.getDocumentProperties()

function colorTheDifference() {
  const { getRow, getNumRows, getColumn, getNumColumns } = sS.getDataRange()
  const [rw, nRw, cl, nCl] = [getRow(), getNumRows(), getColumn(), getNumColumns()]
  const values = sS.getRange(rw, cl, nRw, nCl).getValues()
  for (let i = rw; i <= nRw; i++) {
    for (let j = cl; j <= nCl; j++) {
      const word = values[i - 1][j - 1]
      if(!word) continue
      sS.getRange(i, j).setBackground(saveColor(word))
    }
  }
}

function saveColor(word) {
  if (docProperties.getKeys().includes(word)) {
    return docProperties.getProperty(word)
  } else {
    const randomColorPick = randomColor()
    docProperties.setProperty(word, randomColorPick)
    return randomColorPick
  }
}

// https://stackoverflow.com/a/5092846/14271633
const randomColor = () => '#' + (Math.random() * 0xFFFFFF << 0).toString(16).padStart(6, '0');

function onOpen() {
  // colorTheDifference()
  SpreadsheetApp
    .getUi()
    .createMenu('Custom Menu')
    .addItem('Color It', 'colorTheDifference')
    .addToUi()
}

The script analyzes all cells values, associates them within DocumentProperties to a random color (this could cause some kind of problem when the sample size increases and a control should be implemented to check that such a color exists) and then adds that color to the cell.

As you can see I add it to a function inside a menu, but it could be called inside onOpen so that it is parsed directly when opening the document, or associate it to onEdit (I think this would be an overkill).

This would not be the best way to approximate the problem and should move towards a batchUpdate.

Thanks! I was curious if there was a possibility to do this without any scripting. However, it seems that scripting is the only way to go. Thanks a lot for your time - I'll mark this as answer as it seems good enough from what it does. — Karl Cassar, Jun 02 '22 at 23:35

score 1 · Answer 2 · answered May 26 '22 at 19:50

interesting question.

I wrote something that uses an onEdit() function to format/highlight/color a given row/range based on the value in a certain column.

I'll comment below when I finish fully documenting it, but hopefully it's pretty easy to figure out how to adjust the userParams object.

Give it a try here on this sample sheet. Values in column B will highlight the row based on the background/font color /text style of the "legend"

Google Sheets - Set background color of cell based on text value automatically

2 Answers2