AppSheet or Spreadsheet formula for columns that should be updated automatically

[maretec]

In a Google Sheet, I have a column that must calculate the days remaining for the expiration of contracts.This column must be updated each day automatically. It can not be a Virtual Column because this column is read to be included in a report.

Is a Spreadsheet formula the best way to implement it?

[SkrOYC]

---

@maretec wrote:

It can not be a Virtual Column because this column is read to be included in a report.

---

What do you mean? Virtual columns can be used on reports

[maretec]

What I mean is that I need that column in the spreadsheet and Virtual columns do not show in the spreadsheet.

In order to have this column updated each day automatically, what kind of formula should I use, an AppSheet formula or a Spreadsheet formula?

[dbaum]

A spreadsheet formula will always recalculate anytime you access the spreadsheet. An app formula in an AppSheet column will only recalculate if something within AppSheet triggers it (e.g., editing a record triggers recalculation of that record's app formula columns). If you need to use an app formula instead of a spreadsheet formula for other reasons, you could create a scheduled automation in your app to trigger recalculation of pertinent records.

[maretec]

The article https://support.google.com/appsheet/answer/10099797?hl=en says:

"When the mobile app reads data from the spreadsheet, the formula values are computed."

When the app is reading the spreadsheet?  If I  just navigate in the app (without adding, deleting or editing data), is the app reading the spreadsheet?  

[maretec]

When you say:

"A spreadsheet formula will always recalculate anytime you access the spreadsheet."

I understand that if I open the spreadsheet, it will recalculate.  But if instead of me to open the spreadsheet, the app enters, deletes or updates data, the spreadsheet will recalculate?

[SkrOYC · Accepted solution]

It means on sync, which afaik is needed when you change columns that has spreadsheet formulas.

Anyway, just make a real column with app formula and update it daily with a bot

[maretec]

It sounds good. Thank you.

[kvngo94]

hi SkrOYC, 
could you advise how you set up a bot to update the real column with app formula ? And what if this column has too many rows  like 1,000 rows or 5,000 rows ? 

Thanks.

[SkrOYC]

You use an scheduled event.

It doesn't matter if there are too many rows as long as the expression is efficient

[kvngo94]

thank you