Spreadsheet formula not working with some expressions

[gioKelly]

Hi team,

I have this formula in the Spreadsheet to calculate years from initial date until Today(), it works perfect:

field “Spreadsheet formula” in AppSheet:
DATEDIF(RC[-1], TODAY(),“Y”)

Every new record, appsheet puts the formula, perfectly.
But… when the formula is little bit complex, like bringing the initial date from another sheet in the spreadsheet, appSheet doesn’t put anything at all… leave the cell in blank. This is the formula that’s seems to give issues:

and this is what AppSheet puts in the field “Spreadsheet formula”:
=ArrayFormula(DATEDIF(RC[-1], VLOOKUP(RC[-8], {AtencionAmbul!C1:C1, AtencionAmbul!C9:$I}, 2, false),“Y”))

I have regenerated the columns, nothing. Put the old formula again, and works fine…

[Bellave_Jayaram]

I would send this to support@appsheet.com as it seems to be a bug.

[Koichi_Tsuji]

I suggest that taking this occassion, get out of the spreadsheet formula, which is not recommended to use in spreadsheet Appsheet app is based on. I reckon you can reproduce the same function by Appsheet expression instead of spreadsheet formula.

[Bahbus]

If I recall correctly, this could also be a fundamental thing with spreadsheets formulas within AppSheet - I believe this is to explicitly prevent AppSheet from being ever able to access data outside of the scope that it is allowed within the app.

[Aleksi]

If I remember this correctly, sheet formulas as TODAY(), NOW() etc. are recalculated virtually on your spreadsheet and those updates are not reflected via API what your app is using. You can easily test this if you use NOW() on your spreadsheet. If you set the update as On change and every minute, you will see the new timestamp from your spreadsheet, but it doesn’t change the timestamp in your app even you sync the app.