MAX and SWITCH functions cause blank preview in Google Docs Templates

[carlosvallee]

Hello AppSheet Community,

MAX() and SWITCH() functions in Google Docs templates, used by an AppSheet automation bot. My template works perfectly with LOOKUP() and basic arithmetic, but introducing MAX() and SWITCH() functions makes the template preview go completely blank in the relevant sections.

What's Working (for months):
My template correctly displays values from LOOKUP() and subtractions, e.g.:
<<NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Upgrades Goal")) - NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Total Upgrade Sales"))>>
This produces results like 10, 0, or even -2 (which I want to fix).

What Fails (Causes Blank Preview):
When I try to use MAX() to prevent negative numbers, the preview goes blank:
<<MAX( (NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Upgrades Goal")) - NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Total Upgrade Sales"))) ,0)>>

Similarly, SWITCH() also causes blank previews (like when you forget to add a '>>' ).

Troubleshooting Done:
Data Types: Confirmed all LOOKUP results are converted to NUMBER().
Blanks Handled: Used IFBLANK(..., 0) to ensure numerical inputs, still blank.

Simple Test: Even <<MAX(-5, 0)>> in the template yields a blank, suggesting the function itself isn't evaluating in the template preview.

Data Validity: Underlying StoreGoals data is confirmed numeric and accurate.

My Conclusion:
Since simple arithmetic works, but core functions like MAX() and SWITCH() cause blank previews even with guaranteed numeric inputs, I suspect a bug in the AppSheet Google Docs template rendering engine's handling of these specific functions.

[Marc_Dillon · Accepted solution]

You're using MAX() wrong. It requires a single List type as an argument.

<MAX(LIST(-5, 0))>> instead of<MAX(-5, 0)>>

I imagine you're probably using SWITCH() wrong as well, but you haven't shown how you're using it.

[carlosvallee]

Hi Marc, thank you for your reply!

I'm using MAX and SWITCH the same way as I use it in Google Sheets.

This is the whole expression I'm using that is working. When I add MAX to the subtraction of both Lookup, the template goes blank.
It is my first time posting and trying to explain in this forum. Any advise or teaching you'd like to suggest me would be welcome. Thanks!  

Store	Goal	Sales	Missing for Upgrade Goal	Projection for Upgrades	Message
Go03	<<NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Upgrades Goal"))>>	<<NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Total Upgrade Sales"))>>	<<NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Upgrades Goal")) - NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Total Upgrade Sales")) >>	<<NUMBER(LOOKUP("Go03","StoreGoals","Tienda","Projection for Upgrades"))>>	<<LOOKUP("Go03","StoreGoals","Tienda","Upgrades Message")>>
Go10	<<NUMBER(LOOKUP("Go10","StoreGoals","Tienda","Upgrades Goal"))>>	<<NUMBER(LOOKUP("Go10","StoreGoals","Tienda","Total Upgrade Sales"))>>	<<NUMBER(LOOKUP("Go10","StoreGoals","Tienda","Upgrades Goal")) - NUMBER(LOOKUP("Go10","StoreGoals","Tienda","Total Upgrade Sales")) >>	<<NUMBER(LOOKUP("Go10","StoreGoals","Tienda","Projection for Upgrades"))>>	<<LOOKUP("Go10","StoreGoals","Tienda","Upgrades Message")>>
Go06	<<NUMBER(LOOKUP("Go06","StoreGoals","Tienda","Upgrades Goal"))>>	<<NUMBER(LOOKUP("Go06","StoreGoals","Tienda","Total Upgrade Sales"))>>	<<NUMBER(LOOKUP("Go06","StoreGoals","Tienda","Upgrades Goal")) - NUMBER(LOOKUP("Go06","StoreGoals","Tienda","Total Upgrade Sales")) >>	<<NUMBER(LOOKUP("Go06","StoreGoals","Tienda","Projection for Upgrades"))>>	<<LOOKUP("Go06","StoreGoals","Tienda","Upgrades Message")>>
Go07	<<NUMBER(LOOKUP("Go07","StoreGoals","Tienda","Upgrades Goal"))>>	<<NUMBER(LOOKUP("Go07","StoreGoals","Tienda","Total Upgrade Sales"))>>	<<NUMBER(LOOKUP("Go07","StoreGoals","Tienda","Upgrades Goal")) - NUMBER(LOOKUP("Go07","StoreGoals","Tienda","Total Upgrade Sales")) >>	<<NUMBER(LOOKUP("Go07","StoreGoals","Tienda","Projection for Upgrades"))>>	<<LOOKUP("Go07","StoreGoals","Tienda","Upgrades Message")>>
Go04	<<NUMBER(LOOKUP("Go04","StoreGoals","Tienda","Upgrades Goal"))>>	<<NUMBER(LOOKUP("Go04","StoreGoals","Tienda","Total Upgrade Sales"))>>	<<NUMBER(LOOKUP("Go04","StoreGoals","Tienda","Upgrades Goal")) - NUMBER(LOOKUP("Go04","StoreGoals","Tienda","Total Upgrade Sales")) >>	<<NUMBER(LOOKUP("Go04","StoreGoals","Tienda","Projection for Upgrades"))>>	<<LOOKUP("Go04","StoreGoals","Tienda","Upgrades Message")>>

[Marc_Dillon]

Appsheet's MAX() isn't the same as GSheets MAX(). Did you see my example usage (I edited my response immediately after posting)

You still haven't shown how you're using SWITCH(), but since you mentioned same as gsheets, the default value is required in Appsheet, but not required in GSheets, so that might be your issue.

[carlosvallee]

Ok! Understood Marc. Then if I can't use MAX to avoid negative values in Google Doc as Templates for AppSheet bots, I guess an <<IF([the result]<0,0,[the result])>> will be the only way I know. And in regards to SWITCH I'm using an IF alternative, therefore I'll drop the subject. Thanks again Sir.

[Marc_Dillon]

Of course you can use MAX, you just have to use the syntax correctly as I described and showed above.

[carlosvallee]

You're totally right Sir. Your suggestion worked perfectly. Thanks again.