Google Sheets: Change background color of cell using content of another cell

Question

I have a google sheet as above where I am typing in colour codes in a hex format and then naming them. I would like to automatically update the Colour Block column's background colour with the hex code in the code column.

Script I have tried, but setBackground function doesn't work.

function myFunction() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var cells = sheet.getDataRange().getValues();
    for(n = 1; n < cells.length; n++) {
        var cell = cells[n];
        cell[n][2].setBackground(cell[n][1]);
    }
}

user2759865 · Accepted Answer · 2016-08-11 10:47:10Z

4

I did manage to make this work with the following:

function myFunction() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getDataRange();
    var values = range.getValues();
    for(r = 1; r < values.length; r++) {
        var row = values[r];
        var code = row[1];
        range.getCell(r+1,3).setBackground(code);
    }
}

answered Aug 11, 2016 at 10:47

user2759865

2553 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Cooley Over a year ago

Can you explain how this works? My cells are in Column H and I need the output to be in Column G

user7856768 Over a year ago

@TimCooley You're probably going to want to change the var code = row[1] line to perhaps var code = row[7] (though you may need to mess around with that value), and then the range.getCell(r+1,7) will need changing too. This is just a guess though so you may need to play around with the values.

Shian Han Over a year ago

@TimCooley did you get it to work? I have no idea how to use this function. I only know about entering formulas inside of the cells. If anyone would be willing to share a sample sheet that would be awesome.

Ryan · Accepted Answer · 2024-05-26 15:38:02Z

Thanks for your answer, @user2759865.

The following is an adaptation for anyone who wants a function that takes a range of cells, iterates over each of them, and sets the background color of each based on the text value of the cell.

/**
 * https://stackoverflow.com/a/78535683/470749 based on https://stackoverflow.com/a/38894241/470749
 */
function setCellBackgroundColorBasedOnValue() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('colors');
  const values = range.getValues();
  const missingColorCodes = [];
  for (let r = 0; r < values.length; r += 1) {
    for (let c = 0; c < values[r].length; c += 1) {
      const desiredBackgroundColorCode = values[r][c];
      const cell = range.getCell(r + 1, c + 1);
      cell.setBackground(desiredBackgroundColorCode);
      const actualBackgroundColorCode = cell.getBackground();
      if (actualBackgroundColorCode === '#ffffff' && desiredBackgroundColorCode !== 'white') {
        // https://developer.mozilla.org/en-US/docs/Web/CSS/named-color
        missingColorCodes.push(desiredBackgroundColorCode);
      }
    }
  }
  if (missingColorCodes.length > 0) {
    Logger.log(`Missing color codes:\n${missingColorCodes.join('\n')}`);
  }
}

Collectives™ on Stack Overflow

Google Sheets: Change background color of cell using content of another cell

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related