Internal Server Error: 500: Problem with some Google Sheets:
Google seems to be rolling out some new code changes and this is breaking a small fraction of our customers. If you see the dreaded “Internal Server Error 500” message, read on. This is an error from Google Drive when the AppSheet platform tries to read your sheet.
As background, there are two stages when AppSheet tries to read a Google Sheet:
a) app change in the editor: when you add a table to your app, regenerate, or Save & Verify —
b) app sync: when the mobile app syncs
For case (a), our backend system asks Google Drive to export the sheet as an .xlsx file. This provides all the information needed to infer the column structure. For case (b), there are two ways this can work — if you enable the “Bulk Access” option (in Behavior - Settings), then it uses a lightweight Google API that just gets the data for each worksheet. If you disable the “Bulk Access” option, then it asks for an export to .xlsx format.
The problem here is that Google Drive is failing to export the sheet in .xlsx format. This is a bug that we are reporting and following up with Google. If you enable the “Bulk Access” option, it will allow you apps to continue to run.
WHAT WE ARE DOING
We are automatically enabling Bulk Access for all apps and accounts to prevent disruption while this is being figured out. This will help in case (b) above but not case (a) – i.e. if you add a table, regenerate, or Save & Verify in the editor.
WHAT CAN YOU DO?
Enable the Bulk Access option in your app in the Behavior - Settings pane
https://issuetracker.google.com/issues/72484871 - go here, please add the docId for your sheet and star the bug report so that Google treats it as higher priority.
You can test for this problem directly in your Google Sheet by going to the menu and trying to “Download as” an Excel file. If it fails, there is something in your Google Sheet that is triggering Google’s bug. Consider removing pivot tables or other such things that might be causing it. If you can get Google Sheets to successful export your sheet as an Excel file, then you will not see this problem in AppSheet either.
Some of our customers have run into this problem, and then have tried to fix it by hitting Regenerate in the app editor. Unfortunately, this compounds the problem because AppSheet now throws away the old column structure but is unable to read the data to generate the new structure. If you have done this, please revert to an older working version of the app (via the Manage -> Versions pane) and then enable the Bulk Access option.