nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Can I query against cell properties?

Cale Kuchnicki

For Example, I have a table where my associates are using a background color to denote status. I would like to get counts of each status by looking at the bgColor of the cell.

Ultimately I'd like to use case like this:

SELECT COUNT(

CASE WHEN t1.'Developer' FORMATWIKI("{cell:bgColour=Medium green}")

THEN "COMPLETE") END AS Complete,

COUNT(CASE WHEN t1.'Developer' FORMATWIKI("{cell:bgColour=Yellow}")

THEN "COMPLETE") END AS "In Progress"

FROM T*

Here is what I have tried:

SELECT *

FROM T*

WHERE FORMATWIKI("{cell:bgColor=Medium green}")

This appears to bring back all rows no matter what color I use.

Select * FROM T* WHERE T1.'Developer' = FORMATWIKI("{cell:bgColor=Medium green}")

This brings back no rows.

Accepted answers

Stiltsoft support

Hi @Cale Kuchnicki ,

You haven't mentioned the macro that you use but the query looks similar to the queries we use in the Table Transformer macro provided by the Table Filter, Charts & Spreadsheets for Confluence app (that is our add-on).

So, if this is true, then your case is reversed. The FORMATWIKI function is used to set formatting to the virtual table coming as an output of the Table Transformer macro.

You can apply formatting on the whole column or on the specific cells within this column. The latter option is called conditional formatting: you check cells for the stored values and set the required formatting.

If you need more options with manually set background colors (filter out cells by color, for example), you may take the Table Spreadsheet macro and work with your tables in Excel-like style.

All comments

Stiltsoft support

Hi @Cale Kuchnicki ,

So, if this is true, then your case is reversed. The FORMATWIKI function is used to set formatting to the virtual table coming as an output of the Table Transformer macro.

If you need more options with manually set background colors (filter out cells by color, for example), you may take the Table Spreadsheet macro and work with your tables in Excel-like style.

Stiltsoft support

An update from our team: firstly we thought that your colored cells were empty. We mean that instead of pasting statuses, your colleagues just manually color empty cells.

But then we payed attention that you refer to the column named 'Developer' - if this column stores any data (names and so on), you can use the getView function.

For example, the clause for filtration will look smth like this

WHERE 'Status'->tfView like "%red%"

As your colleagues may use different colors, you may firstly use 'Status'->tfView AS 'Status in HTML' expression to get the full contents of your cell.

You'll get smth like this

Here you can find the background color that was used.

But once again, the cell should contain some data, the getView function doesn't work with empty cells. In this example we store "1" in the cell.

Cale Kuchnicki

posted reply in wrong area.

Cale Kuchnicki

Using 'Status'->tfView like "%red%" seems to have worked the way I would like. I am using developer as one Column for my pivot table and that column won't always have a value, but my team is highlighting entire rows, so I was able to use a column that will always be populated for the table transform as explained. Thanks!

however, I am still wondering if I could be helped with my catch all statement.