Concerns about Using an AppSheet Application for 400 Users

[11198]

We are currently subscribed to Google Workspace, and our organization has 400 accounts. As a result, all employees have been assigned Core licenses.

We are planning to create an AppSheet application that can be used by all 400 employees, with Google Sheets as the database. For now, let's set aside concerns related to data volume and user management (such as adding or removing users).

I would like to know if there are any potential issues that could arise when using a single AppSheet application with 400 users. In particular, I am concerned about the risk of the app crashing or becoming unresponsive due to a large number of users accessing it simultaneously.

Additionally, if there are issues, would upgrading from Core to an Enterprise license help resolve them?

Since I need to explain this to my colleagues, I would appreciate it if you could provide details on whether or not issues are likely to occur, along with an explanation of how AppSheet handles such situations.

Thank you in advance for your assistance.

[scott192]

Effectively, only ONE user will be accessing the spreadsheet.  The app owner.

All data access to your data is performed through your account, unless you mess with the default settings of the table.

As Appsheet talks to your spreadsheet via the Sheets API, there would be some limits imposed by Google. https://developers.google.com/sheets/api/limits

I think Appsheet developers should chime in here with some more details...

[11198]

Thank you for your help. Thanks to your advice, the discussion became very lively, and I was able to obtain the information I needed. I really appreciate it.

[erasmo]

Good question. I use Google Appsheet database and have two tables with 400 rows each; 2 tables with 600 rows each; and one spreadsheet table with amost 3.000 lines.

But we have only one core user so far.

[MultiTech]

This is a good question!

I've never had an app with that many simultaneous users, not sure what would happen.  Given the infrastructure of how AppSheet is put together, @scott192 outlined it perfectly... eventually you'll hit some sort of sheets API limit I would think.

@lizlynch it would be helpful if we could get some actual insight from the devs on this one.

• What happens if 400 people, each with a core license, all attempt to open the same app at the same time?  
• Say the scenario is it's 9 am Monday morning, everyone is logging in to start work... all 400 of us.  How does the backend infrastructure handle this?
• Would there be an issue with the sheets API if people then began editing things?

What sort of theoretical hard limits are we talking about here?

[11198]

Thank you for your reply. I understand that operating an app with 400 users is quite a large scale. I appreciate you reaching out to others on my behalf.

[lizlynch]

Great questions and thank you all for raising this! Escalating with the team.

[Rich_E · Accepted solution]

I have a detailed explanation around concurrency (number of simultaneous users) below.  In general with the 400 users you will need to know how many of the users will be interacting with the app at any given moment. Realistically if your app approaches 50 concurrent users and is connected to a Google Sheet, then you need to perform the following analysis to make sure users will not run into issues. When the limits are reached, users will receive timeout error messages in their app.    

The concurrency limits are based on the backend datasource used. Below are the limits to Sheets, AppSheet Databases and other datasources. 

Google Sheets
Google Sheets can handle 300 API calls per minute. An API call is what AppSheet uses to read/update from any particular table in the same sheet.

For simplicity purposes imagine an app with 3 tables, this app can handle 100 users that can open and sync the app per minute (300 divided by 3).  If the app has 6 tables then you decrease the number of users to 50 users/min (300 divided 6). Note this does not consider any updates, adds or deletes which add additional API call requirements and reduce concurrency.    

Note: you can split your tables into their own separate spreadsheets, each with their own 300 API call limit. We even support data partitioning as an advanced option to further expand this limit.  https://support.google.com/appsheet/answer/10105383?hl=en

AppSheet Databases (ASDB)
An AppSheet databases can handle 100 transactions per second (6000 tx per minute).  The same rules for Google Sheets apply above. So for an apples to apples comparison: 
3 Tables: 2000 users/min
6 Tables: 1000 users/min

Just like with sheets you can split and partition tables as well to increase the concurrency limit.  

Other Datasources
The limits when using other datasources will depend on these services. Generally the app itself will not be the bottleneck for concurrency, it will be the number of connections the datasource can handle.

Read only tables
Read only tables are cached at 5min, 30min, 1hr, or 1day intervals (default is 5 mins). Read only tables would only consume one call/tx for all users in that time period. So lets say you had 1 read only table in the above examples. Then your concurrency limits would effectively look like the following as there would be one less table needed to be accessed with every sync:

Sheets (3 tables): 150 users/min
Sheets (6 tables): 60 users/min
ASDB (3 tables): 3000 users/min
ASDB (6 tables): 1200 users/min

[lochtefeldm]

If each sheet supports up to 300 API calls per minute, are you saying appsheet has a total limit of 300 api calls per minute to google sheets rather than the per sheet cap of 300?
Therefore its splitting its 300 limit across all sheets rather than having 300 per sheet?

For apps that sync at launch does this mean you're only as good as your weakest table? Meaning if we have a appsheet databases but something still in a google sheet thats always going to be limiting for simultaneous app launch connections? 

[Rich_E]

Take AppSheet out of the equation and look at it from the datasource point of view.  The 300 limit is per spreadsheet.  AppSheet has to send an API call for each table in a spreadsheet. If a spreadsheet has 3 tables then AppSheet needs to make 3 API calls. If each of those tables are in their own spreadsheet then that is only 1 API call per sheet.   

Yes it is as strong as the table with the lowest concurrency.  

[lochtefeldm]

Thank you. This explains a lot, I have a lot of tables that I need to break out into separate sheets. No wonder I've been having timeout issues... Been working with appsheet over a year and this the first time this has been articulated well. Documentation should really be updated for first timers, this is not going to be a fun clean up. 🙃 

[takuya_miyai]

Hi @Rich_E 

By your explanation, I understood that the API limit for Sheets, 300 reauests/minuits, is set for each Sheet file.

However, the AppSheet help states that it is per minute, per project, per user request basis.

https://support.google.com/appsheet/answer/10105761?hl=en&sjid=18377445913784691487-AP#data-sources:...

Which is correct?

@lizlynch 
@devingu 

@Koichi_Tsuji 

[takuya_miyai]

Hi @Rich_E 
I just received the following response regarding this Quata issue.

---

This is to notify you that we have got an update from our specialists and they mentioned that "the documentation you are looking at there is for the Sheets API: https://developers.google.com/sheets/api/limits#quota not AppSheet itself. This is the default quota for a new GCP project that uses the Sheets API -- AppSheet has a GCP project that manages its Sheets API usage which has quota increased far beyond the default limits listed in the docs."

This means that the GCP project that AppSheet has in their name to handle API calls to Sheet has far more quota than the general quota listed. Currently there is no specific limitation from AppSheet that could hinder this quota.

---

I feel that this is clearly at variance with the help and your explanation.

https://support.google.com/appsheet/answer/10105761?hl=en&sjid=18377445913784691487-AP#data-sources:...

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Concerns-about-Using-an-AppSheet-Application-fo...

Could you please provide me with the correct information?
My case ID is 8-1735000036536.

[scott192]

@Rich_E would it be possible to surface sheets API stats to the App creators?  Almost like the Audit Logs, but purely for the resources used by the App creators cloud project instance?

We'd be forewarned as to how our apps are performing and whether we should be making steps to change data sources etc.

[MultiTech]

---

@scott192 wrote:

We'd be forewarned as to how our apps are performing and whether we should be making steps to change data sources etc.

---

Things of this nature are sorely lacking at the moment.

• You can see usage, but not necessarily how that usage is stacking up against the limits.  

Information like this would be extremely helpful when you're in the process of adapting your app for scale.

[11198]

Thank you very much for the detailed explanation.

I now understand that when using an app with a large number of users, there is a risk of reaching the Sheets API limits.

Even if the number of users is not as large as 400 but rather in the range of several dozen, if multiple apps are deployed and operated under a single account, the Sheets API usage would be concentrated on that account. Is it correct to assume that when users are using multiple apps simultaneously, this could lead to reaching the Sheets API limits and potentially causing errors?

As a workaround, would it be advisable to distribute the operation of AppSheet across multiple accounts rather than a single account? This way, the Sheets API usage would be spread out, reducing the likelihood of encountering such issues. Is this understanding correct?

[Rich_E]

The Sheets API limit is per spreadsheet and not based on your Google account.  

[scott192]

Brilliant summary @Rich_E. This information needs to be in the documentation.  I know that it probably isn't going to affect the intended audience for Appsheet, but there are people who have taken Appsheet a lot further than intended 🤣

[erasmo]

Very good explanation! But if they migrate their tables from Google Sheets to Google Appsheet databases, would be better?

[Rich_E]

It depends! The storage capacity needs to be considered too. AppSheet Core users have a limit of 2.5k records per database, Enterprise can have an upward limit of 1 Million.  So it depends on the license that the creator has and it may not be best to migrate to ASDB as Sheets can hold 10 million cells.  

[lizlynch]

Thank you, @Rich_E ! And thank you all for the great discussion. We will work on improving the performance considerations in the current documentation. We have a good start in the section Performance considerations for common data sources, but there is a lot of good guidance here that we'll roll into that article and elevate its prominence. 👍

[lochtefeldm]

Thanks @lizlynch & @Rich_E .
I think within appsheet under the performance section you should consider including the ability to analyze user capacity of the app structure, given the attached data source type - tables - & nuances like multiple tables within a sheet.
Breaking it out into some states like concurrent users loading with sync on launch. User capacity based on average user interaction for the app... So its easy to know where performance bottlenecks are and when changes are needed to scale the platform usefully. 

[Ryan_Wagner]

I created an app that 100 users all log into roughly in the same half hour period, though not all exactly at the same time. It is comprised of 20 tables, mostly Google Sheets, but two BigQuery tables also.  A couple of the Google Sheets are around 20,000 rows with 10-15 columns. One table is a Google Calendar data source. I made use of many virtual columns to relate everything, checking in on sync times after creating each one to make sure it would still move smoothly. Average sync time is about 3 seconds.

It's been great so far and we've been using it as an organization for close to a year now.

I would say it can be a totally viable idea.

[T_I_Phong]

This is truly a great question, and the discussions in this topic have been very helpful to me. I've been using AppSheet since 2020, but only now have I learned about these API limits. It would be fantastic if there were a way to track the number of API calls used in 'Usage Statistics' or 'Performance Profile' so that we could easily monitor and adjust our application

Thanks!