Re: Troubleshooting Error 500 Loading G Sheet Data...

lochtefeldm · 12-13-2023 06:48 AM

Hi All,

For reasons I am trying to figure out, users are having an issue with loading the application on a hit or miss basis. Sometimes it loads, sometimes its throwing out the error below. I've gone into the audit log but its not really helpful in identifying the issue. I own the file and the appsheet is set up to access as app creator. How else can I troubleshoot this? Could it be a quotas issue for Google Sheets?

diogolupcosta

try to reconnect all your sources on data settings 'storage'. I think it solves.

lochtefeldm

It doesn't seem this helped.

Landan_QREW

How many users do you have accessing the app concurrently?

lochtefeldm

Less than 10 at the moment.

AleksiAlkio

#1 - Try to logout from your AS account and then login again. It refreshes the access token if that's the reason. #2 - Another thing to check.. check that all your tables are set "as app creator". #3 - When you open the gSheet, are you seeing the grey progress bar on the upper right corner?

lochtefeldm

This is happening even when using a new private browsing session logging in fresh. All the tables are set to access as app creator. The sheets are loading independently in the browser just fine. The sheets that have had issues don't even have much in them. The spaces table in another reply only has one entry at the moment... So not sure why there would be a timeout issue or the server error. It is accessing 8 tables concurrently but security filters are applied to some reducing resource utilization- neither of the ones that have thrown errors have or need these filters though.

AleksiAlkio

If it would be a quota issue, the error message would show the error message differently.

AleksiAlkio

Have you investigated the Performance analyzer? Does it show anything?

lochtefeldm

I have but I haven't seen anything that has helped me identify a cause. Here is a performance details for a failed sync. Maybe you'll pick up on something I'm missing.

AleksiAlkio

As you can see from the Performance analyzer, your app has some virtual columns that takes a lot of time to compute. The root cause can be different, but it would be good to test what hapens if you remove the biggest consumer temporarely.

lochtefeldm

So you're saying that the virtual column in a seperate table than those mentioned in the errors could be causing issues for the tables throwing errors?

The current version of the virtual column that is is just an expression splitting for a reference. Splitting no more than 5 comma separated values.

AleksiAlkio

Well, you can see from the Analyzer what virtual column is taking the time if you open more details from that table that shows 3min 46 secs to read.

lochtefeldm

I believe I reduced that sync time and have it down to an average of 96 seconds, that said do you think that would be causing issues for other tables in the app? Not sure how else to mitigate the resource utilization.

AleksiAlkio

There is still something wrong with your setup or structure if it takes 96 secs to compute virtual columns. They are probably cross-reading data from different tables.

lochtefeldm

Some virtual columns are reference columns for other tables, so yes it is cross reading if that is what you mean.

AleksiAlkio

Any chance your gDrive is full or like almost full?

lochtefeldm

No, 1.39 GB used.

Koichi_Tsuji

I saw the same error messages from some of our PROD apps recently. (the error happens quite often). Those apps are connecting to Google Sheet/My SQL as data source. I wonder your app throwing error may have connection to multiple data souces, like Google Sheet + Cloud SQL? Possibly?

lochtefeldm

I'm strictly using Google Sheets at this time. Continuing to battle with error messages. Clearing cache and refreshing results in a successful load, just doesn't resolve the issue.

scott192

I have had similar problems recently and opened a support call. The final gist of the call was basically to sign out of your app logins, delete your browser cache (every user that has the error has to do this) and then log in again with an empty cache.

Google Internal server 500s for Drive items seem to occur often when the cache gets too large apparently.

Did it work for me? Sort of, in that I don't get as many of the errors as I did before.

aijac1314

I got a similar problem, but my scenario is like this:

1) The app running fine at 2023, with some reason, the app is duplicated with a new set (with new empty table)

2) The clone app (with empty data) is working fine before 4-Jan.

3) The Google Drive Internal 500 error happened twice (4-Jan & 5-Jan). but the app able to proceed with force synch upon getting the error message.

4) All table access mode is set as "as app creator"

Wonder what is causing the problem, and below as some of the error message:

1) Data Table is not accessible due to Google Drive Internal Server error 500 while reading docid=

2)Properties: { "Message": "Unauthorized Access Attempt", "HttpStatusCode": "NotFound", "MinimumRoleRequired": "User", "AppTemplateName": "e130a9ae-7005-4c08-9a1b-fd27015c83de", "Operation": "RestApiAuthorizationCheck", "Result": "Failure" }

appreciate for helping.

Thank you.

Troubleshooting Error 500 Loading G Sheet Database