0

Does anyone know how I'd be able to make a specific value always appear with the same formatting & colour in my sheet, based on another tab in the sheet?

The spreadsheet I'm trying to do this on is this: https://docs.google.com/spreadsheets/d/1FDmyTa4hMcBJZGYKlh8LbvLJWwLs-FW13JJEvAkp0fQ/edit?usp=sharing

They're labels for my hobby, I've set it up so that the ref tab goes into each label in order but when I reference a number it won't bring the formatting from the tab named "DMC no." with it, I was hoping someone would be able to help me with this in some way, whether it means conditional formatting on the label sheet or being able to reference a cell and the format being bought with it. Conditional formatting on the label sheet would be preferable, but whatever you can do to help me would be good!

I changed and edited the google sheet to further explain what I want to achieve, with a better explanation at the top of the labels sheet. Thank you

  • I don't understand how the DMC tab is relevant, if you give me an example of desired output and your non working solution I can make it work. – CodeCamper Nov 17 at 23:30
  • Welcome. Your goal is unclear and there are many unexplained issues. 1) "Labels" and "Ref" have 130 values but "DMC no" has 456 values over 10 columns and 46 rows. 2) The relationship between values on "Ref", "Labels" and "DMC no" is not explained. 3) "Labels" links to Ref! Column B & C (which are empty) are not explained. Please read How do I ask a good question? and then edit your question to clarify the outcome you are trying to achieve. Also please provide an example of a successful outcome. – Tedinoz Nov 20 at 4:43
  • May I try to clarify; please correct me. Use Label 11 as an example. There are three cells: A6, B6 and A7. A6 = =Ref!A11 with value "11". B6 = =Ref!B11 that returns an image. You want to enter a number in Cell A7. That number will also be a value on "DMC no"; each value on "DMC no" has a unique fill colour. When you enter the number in Cell A7, you want the fill colour to change to match the equivalent fill colour on "DMC no". e.g., you enter "333" in cell A7. On "DMC no", value "333" has a fill colour of hex #6E2E9B; you wish the fill colour of Cell A7 will change to hex#6E2E9B. – Tedinoz Nov 21 at 11:37
  • Hi, yes that is correct. Sorry that I'm not the best at explaining this – Katie S Nov 24 at 18:07
0

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

}

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.