Produce a document with conditional table cell colors

[JamesWB]

Hi, I want to produce a document with highlighted cell colours depending on the value in the cell (eg, a risk table, showing green/yellow/red) and am hitting problems because Google Docs doesn't support conditional table cell backgrounds. Appsheet can only work from a non-macro-containing Word doc as email template apparently.

I can do this within the Google sheet, but that presents other issues. I can send the data to Excel, but only to a non-macro enabled sheet on 365 online, so I can't a put a button in that to launch a templated word doc with the data. (MS Word does support conditional table cell colors via VBA.)

I guess I can point my user to the relevant folder in 365 and ask them to download the sheet, or even automate that download, but this seems clunky.

Has anyone else solved this issue please?

Many thanks

James

[Aurelien]

Hi @JamesWB 

In my humble opinion, the best possible formatting you can expect from a generated document with AppSheet is the one described here:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Color-format-in-PDF/m-p/259698/highlight/true#M...

[JamesWB]

Interesting, thanks, but unfortunately insufficient - a standard risk table always has coloured cells in any industry I've ever worked in. Seems like quite a gap in the Appsheet offering this, as they apparently have left no stone unturned to ensure it cannot be done.

[Suvrutt_Gurjar]

Are the cells that need to be shown red/yellow/green, need to have only cell colors with status colors or will those cells will also have some texts?  Any samples possible? Of course it may not be possible  but if it is only cell colors , some workarounds could be thought about.

[JamesWB]

Hi, thanks for replying. The cells need to have a numeric integer value and then they are either green, yellow or red background depending on that value.

The font solution directed to by @Aurelien above works, but yeah, it would be wonderful to have a cell background colour change solution.

Crushingly, you can get the data to Excel in Office 365 which of course does support conditional colours but you cannot run a VBA from there to a separate live document online!

[Suvrutt_Gurjar]

Will something like this do? The below of course involves using a workaround.

The below is a PDF report  created from Google doc template.

[JamesWB]

That would be brilliant! How did you manage it??

[Suvrutt_Gurjar · Accepted solution]

@JamesWB ,

I thought of posting the approach as a tip. Please take a look at the below tip

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Insert-Colorful-Status-RYG-Images-in-the-Reports...

Please note the approach is based on using an extrnal image hosting/rendering service.

[JamesWB]

Hi, I tried a variation for an ENUM field and the email works but my formula doesn't seem to succeed in testing the ENUM values, this is what I tried:

CONCATENATE("https://placehold.jp/",
IFS([Q1] = "Yes" ,"c6d9f1"),
,"/ffffff/50x50.png")

It doesn't load the blue thumbnail I was seeking.

Do ENUM types have a different testing method than strings?

[JamesWB]

Strangely, it displays the resulting thumbnail on the App!?

[Suvrutt_Gurjar]

Yes, obviously it will show if that column is included in view and show_if for that column is enabled. If you do not wish to show it in the app, please either exclude that column from view or disable show_if for that column.

[JamesWB]

Oh I see, thanks, sorry, that's obvious!

Any thoughts on why it doesn't come through to the template? The code evidently works.

[Suvrutt_Gurjar]

Could you share more details ? Does the column show in the app? If so you may need to check your template expressions. Are correct rows where this column is populated are included in the template? 

[JamesWB]

It works now in terms of delivering the templated PDF, but I am puzzled as to how to choose a white box by default. I am using this code:

CONCATENATE("https://placehold.jp/",
IFS([Q1] = "No" ,"c6d9f1/c6d9f1/150x150.png"),
[Q1] <> "No" ,"ffffff/ffffff/150x150.png")

which looks like it should load the white thumbnail with white text when the answer is not "No", but instead it loads a grey one as below.

The second box just calls the column cell by name, eg, <<BoxB>>.

Thanks for any help!

James

[Suvrutt_Gurjar]

There seems to be an issue with the parenthesis. At least syntactically, the expression should be something like below

CONCATENATE("https://placehold.jp/",
IFS([Q1] = "No" ,"c6d9f1/c6d9f1/150x150.png",
[Q1] <> "No" ,"ffffff/ffffff/150x150.png"))

[JamesWB]

Thank you! I got it working just by simplifying it all in the end. It was sufficient to just have the first IFS test and nothing more needed.

[JamesWB]

Oh that's strange, I've got it working now just by not loading it in a table. Evidently there was something in the Word table that was blocking a thumbnail. Back to the drawing board!

[Suvrutt_Gurjar]

  I am sorry that it is a bit late here past 11 PM in my timezone.  I will share the detailed approach tomorrow.  Hope that is fine. I quickly tested that the approach works at basic level. A bit of detailing to make it flexible remains.