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.

[Stiltsoft support · Accepted solution]

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.

[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

<td class="highlight-#ff5630 confluenceTd" data-highlight-colour="#ff5630" title="Background color : Medium red 100%">1</td>

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]

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.

Here's what I am trying to do still with table transform:

WHEN 'Package'->tfView LIKE "%<td class="confluenceTd">DB2</td>%"
THEN "Unassigned" but I can't quite figure this out with the middle quotes.

• I've tried "%<td class=\"confluenceTd\">DB2</td>%"
• As well as '%<td class="confluenceTd">DB2</td>%'
• And "%<td class='confluenceTd'>DB2</td>%"

[Stiltsoft support]

Hi @Cale Kuchnicki ,

You may try the following syntax:

Example string:

<td class="confluenceTd">1</td>

Example query:

WHERE REPLACE('Col 1'->tfView, CHAR(34), "") LIKE "%<td class=confluenceTd>1</td>%"

Hope it helps your case.

[Cale Kuchnicki]

That worked! Thank you!  
I did discover for my case specifically of using a non-colored catch all, this ends up working just as well:

WHEN 'Package'->tfView NOT LIKE "%colour%"
THEN "Unassigned"

[Cale Kuchnicki]

posted reply in wrong area.