Google Sheets Script that Changes Background Color of Range to match the Hex Value in a Separate Lis-CodePudding

I have a range (S3:Z4) in a sheet called SUMMARY that I'd like to be able to set the background color of based on a hex value in a different tab (REFERENCE) of the sheet. Cell W3 of SUMMARY contains the name of a college.

In the REFERENCE tab, there is a column (Column AB) of a list of colleges. In the adjacent column AC, there are a list of Hex values that corresponds to the team color of that school. See here

What I want to happen is that OnEdit if I change the school name in cell W3 of SUMMARY, that all of the range S3:Z4 of summary change to match the hex value found that corresponds with that school in the REFERENCE tab.

I've seen other questions related to this topic but none that really apply to my situation. I'm just getting my feet wet with Google Sheets scripts, so that said anyone have a means to make this happen? I imagine it could be useful for other people even if their ranges are different by just replacing certain things in the script.

CodePudding user response：

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "SUMMARY" && e.range.columnStart == 23 && e.range.rowStart == 3) {
    const rsh = e.source.getSheetByName("REFERENCE");
    const rshsr = 2;//reference sheet data start row
    const bgA = rsh.getRange(rshsr, 28, rsh.getLastRow() - rshsr   1, 2).getValues();
    const row = bgA.find(r => r[0] == e.value)
    sh.getRange("S3:Z4").setBackground(row[1]);
  }
}

CodePudding user response：

About the same as Cooper's:

function onEdit(e) {
  if (e.range.rowStart != 3 || e.range.columnStart != 23) return;
  if (e.range.getSheet().getName() != 'SUMMARY') return;
  
  var ss = e.source;
  var ref_sheet = ss.getSheetByName('REFERENCE');
  var data = ref_sheet.getRange('A1:B3').getValues();
  var colors = {};
  data.forEach(c => colors[c[0]] = c[1]);

  var dest_sheet = ss.getSheetByName('SUMMARY');
  var college = e.value;
  dest_sheet.getRange('S3:Z4').setBackground(colors[college]); 
}

Here is my sheet to test.

Update

Here is the updated version that recolors backgrounds and fonts (based on column AD):

function onEdit(e) {
  if (e.range.rowStart != 3 || e.range.columnStart != 23) return;
  if (e.range.getSheet().getName() != 'SUMMARY') return;
  
  var ss = e.source;
  var ref_sheet = ss.getSheetByName('REFERENCE');
  var data = ref_sheet.getRange('AB2:AD13').getValues();
  var dest_sheet = ss.getSheetByName('SUMMARY');
  var dest_range = dest_sheet.getRange('S3:Z4');
  var college = e.value;

  // set backgrounds
  var backgrounds = {};
  data.forEach(c => backgrounds[c[0]] = c[1]);
  dest_range.setBackground(backgrounds[college]);

  // set font colors
  var font_colors = {};
  data.forEach(c => font_colors[c[0]] = c[2]);
  recolor_text(dest_range, font_colors[college]);
}

function recolor_text(range, color) {
  var style = SpreadsheetApp.newTextStyle().setForegroundColor(color).build();
  var values = range.getValues();
  var rich_values = [];
  for (let row in values) {
    var rich_row = [];
    for (let value of values[row]) {
      var rich_value = SpreadsheetApp.newRichTextValue().setText(value).setTextStyle(style).build();
      rich_row.push(rich_value);
    }
    rich_values.push(rich_row);
  }
  range.setRichTextValues(rich_values);
}

My test spreadsheet is behind the same link.