0

So I've been looking online and I am not able to find an answer that is working for me. I have a Google Sheet, and some of the cells within that sheet have hex values in them. What I am trying to do is make it so that the cell background changes to the hex value within that cell, and if need be the font will inverse that color so it's always readable.

I checked How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets? that link which kind of does what I'm looking for but it appears to do the whole range I'm working on, not individual cells.

Here is my code, which like I said isn't doing quite what I want. Seems close, but not there yet.

function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Color Groups")
    var range = sheet.getRange("A2:G1000");
    range.sort({column: 2, ascending: true});

    var color = e.value;
    var newRange = sheet.getRange("C2:G1000");
    newRange.offset(0, 1).setBackground(color);
}

Any help is appreciated, kinda new to scripts within Google Sheets.

  • How many colors do you need, do you really need to display millions of different colors, each with varying text color to prevent illegible text? You should provide specific sample input data, desired output, and a third sheet showing your current output with your formula. – CodeCamper Nov 17 '19 at 23:43
  • Not millions but there would be a few hundred if I can ever figure out how to make it work. – Dgener8Snoogns Nov 17 '19 at 23:45
  • Not sure if you saw my comment edit it would help to have sample input data, desired output, and current output. – CodeCamper Nov 17 '19 at 23:46
  • If you only needed 10-20 colors this could easily have been accomplished with simple conditional formatting, technically you could accomplish it with conditional formatting but for each color it would make life more difficult. You should always prefer to avoid google app scripts when not needed. – CodeCamper Nov 18 '19 at 0:34
  • So basically it's intended purpose is I have a group of color schemes. Example one is #000000, #ffffff, #ff0000, #00ff00, #0000ff. I'm trying to make it so it programmatically shows those colors. Conditional formatting is fine when you have one or two groups but when you have 50 or 100 it's a bit much for conditional, a script is a cleaner solution. I'm just lost on how to make mine do it. I think I'm close but not sure. – Dgener8Snoogns Nov 18 '19 at 3:10
0

I created a sheet called colors and with the code below it will automatically change the background color from #ff00ff format. I included comments for you to understand, but it should be strait forward.

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
  }
}

https://developers.google.com/apps-script/guides/support/best-practices

  • That worked awesome. Thank you – Dgener8Snoogns Nov 21 '19 at 15:31

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.