javascript - How to change color of a cell if the previous cell contains the hex color name?

Question

On google sheets I have a single column dataset where each cell contains hex color names (i.e. #eac285)

how can I programmatically set the background color of the corresponding cells in the next column to those colors?

Can I do this in the format "=function(x)"

question from:https://stackoverflow.com/questions/65944323/how-to-change-color-of-a-cell-if-the-previous-cell-contains-the-hex-color-name

Answer 1

Explanation:

The idea is to get the values of the column which contains the hex colors and then use setBackgrounds on the column right next to it to set the background colors.

• You can't use this solution as a custom function (custom formula in the sheet), because custom functions don't allow for methods that require permission such us setting values to the sheet. More details regarding this restriction in the official documentation.

• You can run the following script (with adjustments based on your scenario) from the script editor and/or create a custom menu to execute it from there if you want a more user friendly way to do that.

Code snippet:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sh = ss.getSheetByName("Sheet1");
  const hex_colors = sh.getRange('A1:A'+sh.getLastRow()).getValues();
  sh.getRange('B1:B'+sh.getLastRow()).setBackgrounds(hex_colors);
}

Example sheet used for code snippet: