Sheet Performance issue

AppSheet AppSheet Q&A
, ,
1

I am looking for tips on a strategy to improve my App performance.

I have built a CRM where the status of many contacts will change in a single day. Let’s call it sheet1

Now, I have 8 other sheets that read data from sheet1 and do some filtering, counting, summing, vlookuping… to reshape the data for reporting purposes. This takes many seconds per record. More data makes it worse.

It appears Appsheet cannot start another action before all sheets have been updated. The reporting data doesn’t need to be available right away after a record update. (It could be a few minutes later.)

I might not have the best strategy for this scenario and I am curious to here any suggestion.

2

This is correct! When posting data to the sheets, AppSheet will wait until all updates have completed before synchronizing the results back to the app. If each row kicks off a cascade of sheet-side updates and the user submits several updates quickly, each update will wait longer and longer. This will only get worse as time goes by and there is more data in the sheets - the formulas will get slower and delay the updates even more.

It is strongly suggested to use sheets strictly as a datasource. Nearly any updates made by the sheet-side formulas can be done in some fashion in the AppSheet app.

Is there any specific reason why there are formulas in the sheet??

2 Likes
3

Thanks @WillowMobileSys for confirming.

I have many sheets used as data storage. Other sheets that depend on these (mostly just one sheet) are used to generate report views by aggregating the data in different ways.

Based on this Appsheet constraint, what is the best reporting approach that does not impact Appsheet performance?

4

You can try to move the reporting sheets to a different gsheet file, using importrange to pull the data in from the original sheet. This should stop the formulas from having to recalculate on every app sync, without having to change too much of your existing workflow.

4 Likes
5

Thanks @Marc_Dillon for the suggestion.

I have:

  1. created a new gsheet.
  2. created a script that automates the import when the gsheet is closed. This script uses importrange() to import the data without Appsheet being involved

I will try that for a few days and see if the performance improves.

1 Like
6

I report that performance has improved.

1 Like