Spreadsheet formula - impact on Performance

AppSheet AppSheet Q&A
1

Hi @Steve @Suvrutt_Gurjar If i have a complex and lengthy Spreadsheet formula like the one given below, will it affect the app performance or sync time. How are values from cells with formula fetched by the app. Is the value (result of formula) fetched directly from sheet or does appsheet do the calculation after reading the formula from sheet. I have searched the community for anything relevant but couldn’t find any.

=IF(OR(ISBLANK($A113),ISBLANK(INDEX(FILTER($S$1:$S112,$B$1:$B112=$B113),COUNTA(FILTER($S$1:$S112,$B$1:$B112=$B113)),1))),“”,iferror(edate(INDEX(FILTER($S$1:$S112,$B$1:$B112=$B113),COUNTA(FILTER($S$1:$S112,$B$1:$B112=$B113)),1),6),“”))

2
Spreadsheet formulas - AppSheet Help
2 Likes
3

Thanks for the reply @SkrOYC :blush: but I’m not clear whether such lengthy spreadsheet formulas will affect app performance. Can you pls comment on this.

4

I’m not an Spreadhseet formulas kind of guy so I can’t relate from my experience.

All I can say is what’s available on the docs.

SkrOYC_0-1665154371971.png

Since the calculation is not done by AppSheet nor your app, this shouldn’t affect performance. Some people even consider this a more efficient way of performing calculations.

3 Likes
5

I’ll chime in here:

The reason spreadsheet formulas have such a significant impact on sync time is because of how the update-system works. You have to think about things in a step-by-step way:

  1. You initiate the sync
  2. AppSheet sends the first update to the sheet
  3. The Sheets API accepts the value, and runs ALL sheet formulas (EVERY SINGLE ONE)
    • And only after all these formulas have processed does it indicate that it’s okay to move on
  4. It then sends the updated values back to AppSheet
  5. AppSheet then sends the next update
    • Repeating this whole process over and over for each update

So you can see how, if I had 20 updates to make AND a lengthy spreadsheet formula computation time, these two things combined together would make things take

For-ev-er (Sandlot).gif

5 Likes
6

@MultiTech thank you :blush: for the detailed explanation :+1:

2 Likes
7

Do you know how different Performance options or user actions will affect this?

If I have a summary table with many spreadsheet formulas, but the output of those formulas will rarely change, I don’t want them to recalculate unnecessarily.

From https://support.google.com/appsheet/answer/10099797?hl=en&ref_topic=10099796&sjid=16148067913589632934-NC

“When the mobile app reads data from the spreadsheet, the formula values are computed”

This is pretty broad. Does it simply mean that ANY read from any table in the spreadsheet file will cause all formulas (even in other tables?) to be recalculated? Or is there more nuance to what will cause formulas to be recalculated?

1 Like
8

i will suggest to implement the corresponding formulas in appsheet directly.

9

What the performance impact if the table with these complex spreadsheet formula is made read-only in appsheet