Spreadsheet formula not applied

Hossam_Riyad · 07-10-2021 11:55 PM

Hello
In my google sheet, I created a formula to calculate how many calls our sales team has to do based on their target, it calculates that from another sheet.

=MAX(0,(H2-(COUNTIFS('Call History'!E:E,A2,'Call History'!B:B,G2,ARRAYFORMULA(MONTH('Call History'!H:H)),MONTH(TODAY())))))

It is picked up by Appsheet and translated to the following formula under Auto Compute – Spreadsheet formula

=ArrayFormula(MAX(0,(RC[-3]-(COUNTIFS('Call History'!C[-6]:C[-6],RC[-10],'Call History'!C[-9]:C[-9],RC[-4],MONTH('Call History'!C[-3]:C[-3]),MONTH(TODAY()))))))

the problem is when a new record is created the Spreadsheet formula is not applied and the cell is empty! any ideas on how to fix this?

Marc_Dillon

Convert the expression to an Appsheet expression and let Appsheet handle the calculation.
Convert your Spreadsheet Formula to an ARRAYFORMULA() and place it in the header row in the sheet so that is calculates on all rows, instead of depending on the formula existing in all of the cells.

Hossam_Riyad

Thank you for that!
I used your second option as I need this row to automatically update based on other tables on that sheet.
but why is the formula that is picked up by AppSheet not applied normally like the rest of the columns that have the Spreadsheet formula?

Marc_Dillon

Probably because it is too complex and Appsheet failed at converting it. Or at least failed at converting it how you expected it to be converted.

Steve

AppSheet’s support for spreadsheet formulas is limited; there are some formulas it cannot handle.