Skip to content
The Formula
This info has just been discovered, so this page is a write-up of everything I know so far. As we find out new info together, I’ll update this doc.
Syntax. How to use the formula.
The _GRADIENT formula requires a Number column followed by a seemingly unlimited number of Color Stops. Color stops are comprised of a Number and a Color. The row’s formatting is determined by comparing the row’s Number column with the Color Stop’s you’ve defined.
_GRADIENT(
thisRow.[Lightness],
1, "#000000",
10, "#FFFFFF"
)
Color
Lightness
1
2
3
4
5
6
7
8
9
10
There are no rows in this table
In this example, we color the row based on a Number column called Lightness.
- If the row’s lightness is 1, it’ll be fully black.
- If the row’s lightness is 10, it’ll be full white.
- And anything in between will be an appropriate shade of gray.
Defining Colors. Three ways to define colors.
There are three ways to define the color of Color Stops (as far as I know, there could be more). I go into more detail about each on their respective pages.
Color Stops.
These examples explain how to setup Color Stops. There doesn’t seem to be a limit to the number of color stops supported, though I’m sure there may be a performance impact when having too many.
Example One
In this example, we setup three color stops:
1 Light Pink
5 Lavender
10 White
We then use the Table’s Position column to determine the row’s format. In this example, the color stop’s color is being defined by a Hex Value.
_GRADIENT(
thisRow.[Position],
1, "#ffb6c1",
5, "#b6c1ff",
10, "#ffffff"
)
Position
Boys
Girls
1
Liam
Olivia
2
Noah
Emma
3
Oliver
Ava
4
Elijah
Charlotte
5
William
Sophia
6
James
Amelia
7
Benjamin
Isabella
8
Lucas
Mia
9
Henry
Evelyn
10
Alexander
Harper
There are no rows in this table
Example Two
In this example, I want to show you want happens when a row’s Number column is out of range. We’ll adjust the numbers in the table’s Position column so that some fall out of the range we specified for the color stops. The table’s _GRADIENT() formula remains exactly the same as in Example One.
Position
Boys
Girls
Out of Range
-10
Noah
Emma
-5
Benjamin
Isabella
1
Liam
Olivia
3
Oliver
Ava
4.5
Lucas
Mia
5
William
Sophia
7
John
Joan
9
Henry
Evelyn
10
Alexander
Harper
20
Elijah
Charlotte
40
James
Amelia
There are no rows in this table
You’ll see that when a row’s Number column is out of range, it applies the closest color stop’s color. This is also how Color Scales work when you set a Min or Max value.
Rows after our highest color stop (10) are white, and rows before the lowest color stop (1) are pink.
Applying the Formula.
Where do I write this formula?
Applying the _GRADIENT formula is pretty easy, and because it’s just a single copy-paste, applying it to multiple tables is really quick.
Note that when I was copy-pasting formulas into the editor, I’d have to: Right-click in the editor and select “Paste as Text” to get it to work.
Here’s a video:
Using this doc’s generators
On each page within , you’ll find a Generator. This is just a handy way to generate the conditional format without having to type it out line by line.
Important Caveat
HTML Named Colors Don’t Transition
If you define colors using HTML Named Colors, they don’t seem to transition when the row’s number column is between two color stops. in the example below, we’ve used HTML Named Colors instead of Hex Values, and rows seem to just “Round Down” to the color stop lower than their Position column.
Here’s the formula:
_GRADIENT(
thisRow.Position,
1, "LightPink",
5, "Lavender",
10, "White"
)
Position
Boys
Girls
1
Liam
Olivia
2
Noah
Emma
3
Oliver
Ava
4
Benjamin
Isabella
5
William
Sophia
6
Lucas
Mia
7
Elijah
Charlotte
8
James
Amelia
9
Henry
Evelyn
10
Alexander
Harper
11
Jono
Bianca
There are no rows in this table
To Summarize.
The Formula.
Specifying Colors.
You can define the color of color stops in three ways (as far as I know, there could be more)
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.