Feature: No more typos when referencing colors

Colors can now be referenced with no risk of typos, thanks to "enums." XLOOKUP, XMATCH and FORMATDATE also support enums, which help warn you of errors early.

Calcapp gained the ability to customize the colors of apps back in 2017. At that time, we also introduced the COLOR function, which converts a text string to a color.

Using the COLOR function

There are multiple ways to reference the color red using the COLOR function. These formulas are all equivalent:

COLOR("rgb(255, 0, 0)")COLOR("rgb(255, 0, 0)")
COLOR("hsl(0, 100%, 50%)")COLOR("hsl(0, 100%, 50%)")

The first formula refers to the color by its name and the others use text strings which make sense to a web developer (details).

If you make a typo when typing the COLOR parameter, you will get an error when you run your app (or get unexpected behavior, like the wrong color showing up). That’s not ideal — getting an error in Calcapp Creator when you type your formula makes for a much tighter feedback loop, allowing you to correct your formula and move on to the next order of business much faster.

(In fact, we have taken steps to ensure that color formulas entered in the wrong places now generate errors in Calcapp Creator instead of just leading to apps with puzzling behavior.)

Introducing a new way of referencing colors

To eliminate these errors, we are introducing a new way of referencing colors: Simply type Color.Color,, followed by the name of the color.

These two formulas are equivalent:


These two formulas are also equivalent:


Our color system supports the color palettes that are part of Google’s Material Design system, which are designed to work well together. You can reference the Material Design colors not just through the COLOR function, but also by writing Color.Color,, followed by the color name.

These formulas reference the same red color from a Material Design color palette:

COLOR("Red 500")COLOR("Red 500")

These formulas are also equivalent:

COLOR("Blue A700")COLOR("Blue A700")

More enums

In traditional programming, Color is known as an enumerated type, or an enum for short. Unlike a text string, which can be anything, there is a finite number of valid enum values. For that reason, you get an early warning if an invalid value is used.

Now that enums are fully supported by our new formula engine, we have taken this opportunity to introduce an additional five enums — with more to come.

We now support the important functions XLOOKUP and XMATCH, pioneered by Microsoft with Excel 2021. These functions support a match mode and a search mode.

The search mode governs the order in which arrays are searched for a value. In Excel, you need to type 1 to search starting with the first element and -1 to search starting with the last element.

This is mainly problematic in Excel because 1 and -1 aren’t very descriptive. It can be easy to lose track of what these numbers mean.

There is an added problem in Calcapp. If you forget what the search mode parameter does in Excel, and put in something like SQRT(16)SQRT(16), Excel will warn you right away, because it evaluates formulas immediately. Calcapp only evaluates formulas when you run your app, meaning that errors are presented much later.

For this reason, we give you a choice. You can either use 1 and -1, which is great if you copy and paste formulas from a spreadsheet or have become very comfortable with these numbers, or you can use enums. 1 can also be written as SearchMode.FirstToLastSearchMode,FirstToLast and -1 can also be written as SearchMode.LastToFirstSearchMode,LastToFirst. These names are more descriptive, and you’ll get an error straight away if you misspell them.

We also have enums for the match mode accepted by these two functions and for the sort order accepted by the functions SORT, RANK.AVG and RANK.EQ.

When we introduced functions like FORMATNUMBER and PARSENUMBER in 2018, we held off on introducing functions which could turn dates and times into text strings and back again. The primary reason was that we felt that we wanted support for enums to enable app authors to specify the format.

Now that we have enum support, we have taken the opportunity to introduce the functions FORMATDATE, FORMATTIME, PARSEDATE and PARSETIME. Here’s how to format a date using a numeric date format, with an enum given as the last parameter:

FORMATDATE(DATE(1981, 11, 14), DateFormat.Numeric)FORMATDATE(DATE(1981; 11; 14); DateFormat,Numeric)
« Panels are now known as screens — and other name changes Backward compatibility and our new formula engine »