Error "The service sheets has thrown an exception"

[hellfry]

Hey everyone,

So I have a CRM that started as most things do, pretty small and has ended up fairly large scale, with lots of referenced tables and visual columns. As you can expect, poor optimisation on my part has left my app very slow with sync times on average being 120 seconds. I have noticed a lot of sync errors lately while I have been trying to reduce the size of my app and so far reduction in size and removal of tables has only resulted in longer sync times....

Below is an error I got today. My question is, it seems I have hit the limit of how many times my table can be read by the users I have (about 10 people) so I'd like to know, if I was to split this table into 2 and merge them together using the scale options if this would reduce the issue im having. 

Love to know your thoughts

Operation: Edit row

Message: Unable to update row in table 'Enquiries'. → The service sheets has thrown an exception. HttpStatusCode is TooManyRequests. Quota exceeded for quota metric 'Read requests' and limit 'Read requests per minute' of service 'sheets.googleapis.com' for consumer 'project_number:-'.: Message[Quota exceeded for quota metric 'Read requests' and limit 'Read requests per minute' of service 'sheets.googleapis.com' for consumer 'project_number:-'.] Location[ - ] Reason[rateLimitExceeded] Domain[global] The service sheets has thrown an exception. HttpStatusCode is TooManyRequests. Google.Apis.Requests.RequestError Quota exceeded for quota metric 'Read requests' and limit 'Read requests per minute' of service 'sheets.googleapis.com' for consumer 'project_number:-'......

[WillowMobileSys]

Google has limitation on the number of operations submitted to the sheet in a given amount of time. This is a aggregate from ALL users and not only includes updates submitted by AppSheet but also activated formulas in the sheets themselves.

Do yo have sheet side formulas??

This limit is typically reached when the Google Sheet has a number of sheet formulas that get triggered multiple times from only a single AppSheet update. 

[hellfry]

Hey

yes the sheet has formulas on it, mostly array formulas. 

I assume by removing these I would improve the errors but also speed?

[scott192]

Absolutely you would improve the speed by removing the array formulas 😎

[hellfry · Accepted solution]

Turns out this was totally the way to go. 

Over the night I removed one of the array formulas (not all of them yet as some are used for reporting, so ill replace them with appsheet bots I think) and reduced the size of some of the tables being read by using security filters. 

since then ive seen the average sync time from from 120 seconds to 17 seconds. which is a huge improvement. Thanks a bunch for the help!

[scott192]

If some are used for reporting, then you also have the option of creating a brand new sheet and using IMPORTRANGE()  to pull the sheet and columns used in appsheet.  Then you could add your array formulas in this sheet and point your reporting tool (looker?) at this sheet instead.  You could really speed things up that way!

[hellfry]

Amazing idea actually. No idea why I'm not using this already since I have a sheet using this forumla already for something else haha. 

Probably sum this up as me moving too fast with app and forgetting all the good things about google sheets... 

thank you for the help

[hellfry]

Was indeed a good idea until I realised IMPORTRANGE() has a 100,000 cell limit. One of the tables I need to bring over to a separate sheet has quite a few more than that, so we may be at a loss with that

[Domearian]

If you have a data item which is primary to the operation of the App, and everyone will access it to operate the App then you will reach the quota limit for the number of calls made to that data. Say it was a Google Sheet, everyone using the App may make a call to that same Google Sheet at the same time. If they try again 60 seconds later it should work because some of the previous calls by other people who have succeeded. Hope that makes sense.