Google-sheets – Conditional Formatting based on another sheet, tying a value to a colour, Google Sheets

You are trying to update the value AND the background colour of a cell when a certain "DMC" number is entered. DMC information is held on a sheet ("DMC no.") and consists of cells with given numerical values and specific background colours.

Revising the DMC data format

There is no function that enables Google Sheets to get the background colour of a given cell, so I propose that the DMC information needs to be recorded in a different format - a 2 Column list. The first column is the DMC value and the second column is the hex colour value associated with that DMC value.

This function modifies the existing DMC information. I created a new sheet "DMC_data" to hold the revised information.

function wa13636001() {

  // built a DMC value/colour matrix

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "DMC no.";
  var sheet = ss.getSheetByName(sheetname);

  // get DMC sheet info
  var dmcLR = sheet.getLastRow();
  var dmcLC = sheet.getLastColumn();

  // get the Range and Values  
  var dmcRange = sheet.getRange(1, 1, dmcLR, dmcLC);
  var dmcValues = dmcRange.getValues();

  //  create termporary array
  var dmctemparray=[];

  // loop rows and columns to build value list
  for (var i = 0; i<dmcLR;i++){
    for (k=0;k<dmcLC;k++){
      dmctemparray.push([dmcValues[i][k]])
    }
  }

  //  get length
  var rows = dmctemparray.length

  // setup target sheet
  var targetname = "DMC_data";
  var target = ss.getSheetByName(targetname);

  // get target range - Column 1 - values
  var targetRange = target.getRange(1, 1, rows);
  // update values
  targetRange.setValues(dmctemparray);

  // get the background colours
  var bgColors = dmcRange.getBackgrounds();

  // create a temporaryarray for the colours
  var dmccoloursarray = [];
  for (var i in bgColors) {
    for (var j in bgColors[i]) {
      dmccoloursarray.push([bgColors[i][j]]);
    }
  }

  // get target range - Column 2 - cdolours
  var targetRange = target.getRange(1, 2, rows);
  // update colour values
  targetRange.setValues(dmccoloursarray);

}

Custom Function - not an option

I wrote a custom function to update the background colour in the target cell (A7) and got an error:

"You do not have permission to call setBackground".

I then read Google Apps script - change the background color of a cell with a hex value from another cell and, as they point out, "Formulas cannot modify cell format". So it's not feasible to create a function that will do this in real time.

Two options seemed available:

• a script that can be called from a button which would update any/all DMC values.
• an onEdit(e) script. This is a far more sensible option in the circumstances

onEdit() - a logical option

onEdit(e) needs to take advantage of the Event objects.

The evaluation of edited data is in three parts:

1. sheetname === labelsname: ensure that the edit took place on sheet = "Labels".

2. modrow === 0 && modcol===0

   • DMC Values appear in predictable rows and columns.
   • Rows: the first row is #4, and then every third row thereafter. So the remainder value is predictable.
   • var modrow = (+row-dmcstartrow)%3;: this value should be zero; if not then the DMC value has not been edited.
   • Columns: the first column is 1, and then every third column thereafter. Again the remainder value is predictable.
   • var modcol = (+col-dmcstartcol)%3;: this value should be zero; if not, then the DMC value has not been edited.

3. the script then loops through the DMC data looking for a match on the edited value. If a match is found, then the hex background colour is obtained from the adjacent column, and the edited cell is updated

   • range.setBackground(data[nn][1]);

 function onEdit(e) {
  // wa13636003 

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var labelsname = "Labels";
  var labels = ss.getSheetByName(labelsname);
  //Logger.log(JSON.stringify(e)); //DEBUG

  // get event objects  
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  var value = e.value
  var sheetname = range.getSheet().getSheetName();

// dmc sheet start row/col  
  var dmcstartrow = 4;
  var dmcstartcol = 1;
  // calculate mod on row and colum
  var modrow = (+row-dmcstartrow)%3;
  var modcol = (+col-dmcstartcol)%3;
  //Logger.log("DEBUG: modrow:"+modrow+", modcol:"+modcol)

  // test if mod for row and col are zero and that the sheet is Labels.
  if (modrow === 0 && modcol===0 && sheetname === labelsname){
    // Logger.log("DEBUG: this cell is a match")

    // get DMC data
    var dmc = ss.getSheetByName("DMC_data");
    var last=dmc.getLastRow();
    var data=dmc.getRange(1,1,last,2).getValues();// create an array of data from columns A and B

    //loop through dmc data to find the match on the value, and return the colour
    for(nn=0;nn<data.length;++nn){
      if (data[nn][0]==value){
        // Logger.log("DEBUG: "+data[nn][0]+" is a match for "+value);

        // set the background colour        
        range.setBackground(data[nn][1]);

        // break out rather than cvontinue going through the loop.
        break;
      } // if a match in column B is found, break the loop
      else{
        // Logger.log("DEBUG: "+data[nn][0]+" is NOT a match for "+value)
      }
    }
  }
  else{
    // Logger.log("DEBUG: this cell is NOT a match")
  }

}