Search code examples
google-apps-scriptgoogle-sheetscolorsrgb

How to inverse font color based on cell's background Color in Google Apps Script?

I'm wondering how to inverse the font color of a cell's value based on the background color automatically to make it readable.

I've used the following script to get the background color automatically set upon pasting hex color codes into given cells How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?, with sample sheet here

function onEdit(e) {
  r = e.range;

  if(r.getSheet().getSheetName() == "colors"){ //the sheet I want to apply this to is called colors

    var rows = r.getNumRows();
    var columns = r.getNumColumns();
    var colors = [] //this is our 2 dimensional array of colors in case you copy and paste a large amount of colors

    for (var i = 1; i <= rows; i++) { //go down each row
      var row = [] //create a new row array to clear the old one when we go down a row
      for (var j = 1; j <= columns; j++) { //then go across each column
        row.push(r.getCell(i,j).getValue()) //put together the row of colors
      }
      colors.push(row); //insert our row of colors so we can go down the next row
    }
    r.setBackgrounds(colors) //batch update in case you update many colors in one copy and paste otherwise it will be very slow
  }
}

but the remaining issue is the font is not showing on the dark background cells.

I've also found this related question How to decide font color in white or black depending on background color?. And those Javascript specific answers with functions, but I've not been able to make them work with the above script in GAS.

JavaScript code 1

JavaScript code 2

JavaScript code 3

JavaScript code 4

JavaScript code 5

JavaScript code 6

I've also looked into the documentation for setFontColors(colors) and saw we could use the method r.setFontColors(colors) in the script above. I tried calling the JavaScript codes 1 to 6 above, but I'm not succeeding.

For example, I've tried this way based on JavaScript code 3 ):

function onEdit(e) {
  r = e.range;

  if(r.getSheet().getSheetName() == "colors"){ //the sheet I want to apply this to is called colors

    var rows = r.getNumRows();
    var columns = r.getNumColumns();
    var colors =  [] //this is our 2 dimensional array of colors in case you copy and paste a large amount of colors

    for (var i = 1; i <= rows; i++) { //go down each row
      var row = [] //create a new row array to clear the old one when we go down a row
      for (var j = 1; j <= columns; j++) { //then go across each column
        row.push(r.getCell(i,j).getValue()) //put together the row of colors
      }
      colors.push(row); //insert our row of colors so we can go down the next row
    }
    r.setBackgrounds(colors) //batch update in case you update many colors in one copy and paste otherwise it will be very slow
    r.setFontColors(lum([111, 22, 255]));
  }
}

function lum(rgb) {
    var lrgb = [];
    rgb.forEach(function(c) {
        c = c / 255.0;
        if (c <= 0.03928) {
            c = c / 12.92;
        } else {
            c = Math.pow((c + 0.055) / 1.055, 2.4);
        }
        lrgb.push(c);
    });
    var lum = 0.2126 * lrgb[0] + 0.7152 * lrgb[1] + 0.0722 * lrgb[2];
    return (lum > 0.179) ? '#000000' : '#ffffff';
}

What am I missing?

Thanks for your insights!

Solution

  • I believe your goal is as follows.

    • When the hex values are put to the cells, you want to set the background color using the inputted hex values. At that time, you want to set the font colors for each cell depending on the background colors.

    Modification points:

    • In your script, I think that the value of colors can be retrieved by r.getValues(). In this case, the for loop is not required to be used.

    • From r.setFontColors(lum([111, 22, 255]));, When you want to set the same font colors to the range, you can modify as follows.

      • From

          r.setFontColors(lum([111, 22, 255]));

      • To

          r.setFontColor(lum([111, 22, 255]));

    But, from your script, I thought that you might want to set the font colors by each background color. If my understanding is correct, how about the following modification?

    Modified script:

    // I modified this function.
function onEdit(e) {
  var r = e.range;
  if (r.getSheet().getSheetName() == "colors") {
    var colors = r.getValues();
    r.setBackgrounds(colors);
    var fonrColors = r.getBackgroundObjects().map(r => r.map(c => {
      var obj = c.asRgbColor();
      return lum([obj.getRed(), obj.getGreen(), obj.getBlue()]);
    }))
    r.setFontColors(fonrColors);
  }
}

// This is from your script.
function lum(rgb) {
  var lrgb = [];
  rgb.forEach(function (c) {
    c = c / 255.0;
    if (c <= 0.03928) {
      c = c / 12.92;
    } else {
      c = Math.pow((c + 0.055) / 1.055, 2.4);
    }
    lrgb.push(c);
  });
  var lum = 0.2126 * lrgb[0] + 0.7152 * lrgb[1] + 0.0722 * lrgb[2];
  return (lum > 0.179) ? '#000000' : '#ffffff';
}

    • In this modification, in order to convert the hex to RGB, I used the method of getBackgroundObjects(). Of course, you can convert this using Javascript. If you don't want to use getBackgroundObjects(), please check this thread

    • In this modification, when you put the hex values to the cells, the background colors are set using the hex values, and the font colors are set depending on the background colors using the function lum of your script.

    References: