Use a script to copy the formatting of another column in google sheets

Question

I have a series of values in one column (human heights, in this instance), which I have made as human-readable as possible. I want to color-code these, however, so I have a second column right next to it, where I have typed out these heights as pure numbers, which I have then applied a color scale (conditional formatting) to. Normally, it would be somewhat easy to have conditional formatting based on another cell, by using a custom formula, but with the color scale feature, there isn't a way to input conditions (at least that I'm aware of).

As such, I want to try copying the formatting from the pure-number column to the human-readable column (then I can just hide the former). Unfortunately, since this is conditional formatting, I can't just copy over the colors directly, since they'll change based on the new cell values. I know that Google Sheets supports some sort of scripting, so I figured there must be some way to set the current background/fill color of one cell to match that of another automatically, which can also update if that other cell's formatting changes. Unfortunately, I have no idea how to do any of this scripting stuff, so I wanted to ask here if anyone can help me figure that out.

Edit: Here's a very basic example of what I have now. What I'm trying to do is apply the same formatting as on the right to the left without changing the values.

Answer 1

Try this:

1. In your Spreadsheet, go to Tools -> Script Editor
2. Delete any code in the script editor
3. Paste the code provided below.
4. Press Run button

Code:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Sheet1");
  var sourceBG = sh.getRange("B2:B8").getBackgrounds();
  sh.getRange("A2:A8").setBackgrounds(sourceBG);
}

Note: Just change the range if you have more rows and make sure that the source and destination range have the same size.

Output:

References:

• Range.getBackgrounds()
• Range.setBackgrounds(color)