How to get the Hexadecimal codes of colors in Google Sheets
In this tutorial, I'll show you how to get the Hexadecimal codes (also known as HTML color codes) of the colors in Google Sheets' color picker.
PrerequisitesYou're familiar with the basics of Google Sheets.
You're familiar with HTML color codes: What they are and how to use them.
You know how to create a Custom Function using Google Apps Script.
Step 1 — Create a Google Sheets spreadsheet and fill a couple of cells with different colors
Step 2 — Write a Custom Function to return the HEX code of a cell's background color
You're familiar with the basics of Google Sheets.
You're familiar with HTML color codes: What they are and how to use them.
You know how to create a Custom Function using Google Apps Script.
Step 2 — Write a Custom Function to return the HEX code of a cell's background color
Open the script editor from the Extensions menu (Extensions —> Apps Script) and replace the code in the editor with the code below.
/**
* Returns the Hexadecimal value of a cell's background color.
*
* @param {number} row The cell's row number.
* @param {number} column The cell's column number.
* @return The Hexadecimal value of the cell's background color.
* @customfunction
*/
function BGHEX(row, column) {
var background = SpreadsheetApp.getActive().getDataRange().getCell(row, column).getBackground();
return background;
}How does the above code work?The above code creates a custom function that you can use from your spreadsheet like any other built-in function.
The function takes the row and column coordinates of a single cell and it returns the background color of the cell.
Why do we need to pass the row and column coordinates to the custom function? Why not just reference the cell directly like you would in other functions?
When you call a custom function with a cell or a range as input, only the values in the cell or range are passed to the function. So, the function will not know which cell or range the values came from.
In this case, the function needs to know the coordinates of the cell in order to extract its background color. Therefore, just knowing the value contained in the cell will not work and we need to explicitly tell the function the coordinates of the cell.
Step 3 — Use the function to populate the HEX codes in the spreadsheet
The above code creates a custom function that you can use from your spreadsheet like any other built-in function.
The function takes the row and column coordinates of a single cell and it returns the background color of the cell.
Why do we need to pass the row and column coordinates to the custom function? Why not just reference the cell directly like you would in other functions?
When you call a custom function with a cell or a range as input, only the values in the cell or range are passed to the function. So, the function will not know which cell or range the values came from.
In this case, the function needs to know the coordinates of the cell in order to extract its background color. Therefore, just knowing the value contained in the cell will not work and we need to explicitly tell the function the coordinates of the cell.
Use the BGHEX() function in your spreadsheet to get the Hex code of any cell's background color.
For example, entering the formula =BGHEX(ROW(B1), COLUMN(B1)) results in the Hex code #000000, which is the Hex code for the color black.
Step 4 — Populate the HEX codes for all the colors in the color picker
Use the formula to generate Hex codes for all of the colors in the color picker. The result should be a spreadsheet like the one below.
Hex codes of all the colors in Google Sheets' color picker
The table below lists all of the 80 colors in the color picker along with their names and Hex codes.
Row# | Color | Name | Hex code |
|---|---|---|---|
1 | black | #000000 | |
1 | dark gray 4 | #434343 | |
1 | dark gray 3 | #666666 | |
1 | dark gray 2 | #999999 | |
1 | dark gray 1 | #b7b7b7 | |
1 | gray | #cccccc | |
1 | light gray 1 | #d9d9d9 | |
1 | light gray 2 | #efefef | |
1 | light gray 3 | #f3f3f3 | |
1 | white | #ffffff | |
2 | red berry | #980000 | |
2 | red | #ff0000 | |
2 | orange | #ff9900 | |
2 | yellow | #ffff00 | |
2 | green | #00ff00 | |
2 | cyan | #00ffff | |
2 | cornflower blue | #4a86e8 | |
2 | blue | #0000ff | |
2 | purple | #9900ff | |
2 | magenta | #ff00ff | |
3 | light red berry 3 | #e6b8af | |
3 | light red 3 | #f4cccc | |
3 | light orange 3 | #fce5cd | |
3 | light yellow 3 | #fff2cc | |
3 | light green 3 | #d9ead3 | |
3 | light cyan 3 | #d0e0e3 | |
3 | light cornflower blue 3 | #c9daf8 | |
3 | light blue 3 | #cfe2f3 | |
3 | light purple 3 | #d9d2e9 | |
3 | light magenta 3 | #ead1dc | |
4 | light red berry 2 | #dd7e6b | |
4 | light red 2 | #ea9999 | |
4 | light orange 2 | #f9cb9c | |
4 | light yellow 2 | #ffe599 | |
4 | light green 2 | #b6d7a8 | |
4 | light cyan 2 | #a2c4c9 | |
4 | light cornflower blue 2 | #a4c2f4 | |
4 | light blue 2 | #9fc5e8 | |
4 | light purple 2 | #b4a7d6 | |
4 | light magenta 2 | #d5a6bd | |
5 | light red berry 1 | #cc4125 | |
5 | light red 1 | #e06666 | |
5 | light orange 1 | #f6b26b | |
5 | light yellow 1 | #ffd966 | |
5 | light green 1 | #93c47d | |
5 | light cyan 1 | #76a5af | |
5 | light cornflower blue 1 | #6d9eeb | |
5 | light blue 1 | #6fa8dc | |
5 | light purple 1 | #8e7cc3 | |
5 | light magenta 1 | #c27ba0 | |
6 | dark red berry 1 | #a61c00 | |
6 | dark red 1 | #cc0000 | |
6 | dark orange 1 | #e69138 | |
6 | dark yellow 1 | #f1c232 | |
6 | dark green 1 | #6aa84f | |
6 | dark cyan 1 | #45818e | |
6 | dark cornflower blue 1 | #3c78d8 | |
6 | dark blue 1 | #3d85c6 | |
6 | dark purple 1 | #674ea7 | |
6 | dark magenta 1 | #a64d79 | |
7 | dark red berry 2 | #85200c | |
7 | dark red 2 | #990000 | |
7 | dark orange 2 | #b45f06 | |
7 | dark yellow 2 | #bf9000 | |
7 | dark green 2 | #38761d | |
7 | dark cyan 2 | #134f5c | |
7 | dark cornflower blue 2 | #1155cc | |
7 | dark blue 2 | #0b5394 | |
7 | dark purple 2 | #351c75 | |
7 | dark magenta 2 | #741b47 | |
8 | dark red berry 3 | #5b0f00 | |
8 | dark red 3 | #660000 | |
8 | dark orange 3 | #783f04 | |
8 | dark yellow 3 | #7f6000 | |
8 | dark green 3 | #274e13 | |
8 | dark cyan 3 | #0c343d | |
8 | dark cornflower blue 3 | #1c4587 | |
8 | dark blue 3 | #073763 | |
8 | dark purple 3 | #20124d | |
8 | dark magenta 3 | #4c1130 |
ConclusionIn this tutorial you learned how to write a custom function BGHEX() that returns the Hexadecimal code of a cell's background color.
You used the BGHEX() function to generate the Hexadecimal codes of all the colors in Google Sheets' color picker.
In this tutorial you learned how to write a custom function BGHEX() that returns the Hexadecimal code of a cell's background color.
You used the BGHEX() function to generate the Hexadecimal codes of all the colors in Google Sheets' color picker.
Thanks for reading!