How can I add an html colored square inside a Table cell?

[llatore]

The new confluence format for tables has gotten rid of adding a colored background to table cells and has messed up my file.

Does anyone know how to add a color in there using the html iframe or something similar?

Im just looking to add a square in html that I can change the hex value to.

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @llatore ,

You may try the Table Filter and Charts for Confluence app and its Table Transformer macro with the wiki markup feature.

Wrap your table in the Table Transformer macro and use a custom SQL query to color your cells and/or text:

SELECT
FORMATWIKI("{cell:bgColor=yellow}" + T1.'Rep' + "{cell}") AS 'Rep',
FORMATWIKI("{cell:bgColor=#FFE5B4}" + T1.'Department'+ "{cell}") AS 'Department',
FORMATWIKI("{cell:bgColor=orange|textColor=yellow}" + T1.'City'+ "{cell}") AS 'City',
FORMATWIKI("{panel:bgColor=orange|textColor=white}" + T1.'City'+ "{panel}") AS 'City 1',
FORMATWIKI("{color:orange}" + T1.'City' + "{color}") AS 'City 2'
FROM T1

You can also use this macro for conditional formatting (like in Excel).

Here is an example of the possible SQL query and the obtained result:

SELECT
T1.'Month',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Bread' <= "100" THEN "#FA7E70"
WHEN T1.'Bread' > "100" AND T1.'Bread' <= "200" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + "$" + T1.'Bread' + "{cell}")
AS 'Bread',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Rolls' <= "100" THEN "#FA7E70"
WHEN T1.'Rolls' > "100" AND T1.'Rolls' <= "200" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + "$" + T1.'Rolls' + "{cell}")
AS 'Rolls',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Cookies' <= "100" THEN "#FA7E70"
WHEN T1.'Cookies' > "100" AND T1.'Cookies' <= "200" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + "$" + T1.'Cookies' + "{cell}")
AS 'Cookies',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Muffins' <= "100" THEN "#FA7E70"
WHEN T1.'Muffins' > "100" AND T1.'Muffins' <= "200" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + "$" + T1.'Muffins' + "{cell}")
AS 'Muffins'
FROM T*

[llatore]

That worked well. Thanks @Katerina Kovriga _Stiltsoft_

[Kelly Hall]

Formatwiki is not available on my companies version. Is there another method using transformer or CSS separately?

[Katerina Kovriga _Stiltsoft_]

Hi @Kelly Hall , what do you mean by the phrase that formatwiki is not available for your company? If you have our app with the Table Transformer macro, then the macro will understand formatwiki markup.

If the Table Transformer doesn't recognize these commands, it means that you use an old version of the app and you need to update it (it concerns Server and Data Center). For Cloud the app is always up-to-date.

[Kelly Hall]

Thank you for your response. Yes, I can use the table transformer macro but it gives me an error when I put in the formatwiki command line.

Short of getting the version update, is it possible to use a if then query?

I did see that an if then can render handy status results but I'd like to make the cell color-coded based on the date entered. 

Thanks again.

[Katerina Kovriga _Stiltsoft_]

To work with the cells background, you need to update the app (FORMATWIKI was added in October, 2020).

The IF THEN query adds an additional column based on the second mini table inside the Table Transformer macro. It seems that you are referring to this case.

But you can't work with the cells background and text color without the FORMATWIKI functionality. This feature was added specifically to cover the conditional formatting (for example, like you have in Excel).

[Kelly Hall]

ok. thank you for your help.

[Kelly Hall]

Formatwiki is not available on my companies version. Is there another method using transformer or CSS separately?