New Bug Encountered: Open Workbook Too Large (Exceeds {0} MB file size limit)

[Sarah_Keown]

I have been getting an Error from my app; it details that the open workbook is too large and the workbook in excel online exceeds the {0} MB file size limit.

This error has disappeared in the editor but the app itself is showing as an error and nothing can be viewed by the user. Granted, I have been adding tables from a very large excel file of about 86 MB? Adn the tables are across 30 + sheets in the one excel file.

Is this an issue with appsheet that large files cannot be accessed/added?

I have closed the workbook and tried refreshing and closing appsheet, switching to recovery mode and what not but nothing seems to shift the error on screen for the user. Any help with this??

[Harry2]

@Sarah_Keown Hi Sarah, this error message came directly from Microsoft. If your workbook exceeds a certain size limit, the Sharepoint API will not be able to open it. Unfortunately this size limit is imposed by Microsoft, not AppSheet, so we have no control over it. Microsoft does not spell out the size limit that is applied specifically for the Sharepoint API in their documentation, but based on the article below, the size limit for Sharepoint online is 30 MB:

support.office.com

File size limits for workbooks in SharePoint Online

Learn about file size limits and how they affect whether you can open a workbook in a browser window.

If you want to avoid this size limit, you can try storing the workbook in another storage provider, such as Google Drive or Dropbox. However, I highly recommend that you reduce the size of the workbook as much as possible. You mentioned that there were 30+ sheets in this workbook. Even if not all of these sheets are used in the app, they will all have to be read by our server when the app syncs, which will cause the sync to slow down unnecessarily. Instead of storing all of the sheets in the same workbook, you should separate them into their own workbooks, and add only the workbooks that are actually used to the app.

[Sarah_Keown]

Thank you @Harry, I thought it might be the case of separating the workbook. Do you think the same error can be caused if all these sheets are sectioned into different workbooks but the data size combined is too large?

[Sarah_Keown]

Also not sure if you can help with this but I have split out the workbook and the first section is under the 30 MB so it is accessible online etc. I have added tables from this workbook into my app but now I get a completely different erro saying the app is not accessible due to cloud services being temporarily unavailable and that the table is empty or unaccessible?

I contacted appsheet support via email at 11:30 am (UK Time) and it is after 1pm the following day, I havent received any word so I am not sure if it is worth another email or not.

This error is happening in this app specifically, no issue from other apps that are one drive source, is thi sa sharepoint issue??

[Harry2]

@Sarah_Keown Hi Sarah, after a quick look at the audit log of your app, I can see that your app regularly takes a very long time to sync. Specifically, the app consistently runs into the error shown in the screenshot after approximately 80 seconds, which suggests that this is actually a timeout error. Of the 2 tables in this app, table “Mech Product Groups” appears to be the problematic one. How many rows does this table have? If this table has a very large number of rows (for example, more than 10,000), I recommend that you consider using a SQL table instead.

[Sarah_Keown]

Using SQL did cross my mind also. That table is not large at all, out of all the workbooks I have tried to add this is the smallest, it consists of 1 column maybe about 30 rows. I have been getting the same error when trying to add sections of the large workbook I think because collectively the max row count is exceeded.

[Steve]

Sarah_Keown:

I think because collectively the max row count is exceeded.

I agree.

[Harry2]

Also, it’s a good idea to check all of the other worksheets in the app as well besides the worksheet “Wastes ABS - Marley”.

[Sarah_Keown]

[Harry2]

@Sarah_Keown Hi Sarah, you’re correct that the worksheet will not work if the row count is exceeded. However, this limit is on a per-worksheet basis, not a collective limit for the entire workbook. Could you check the worksheet “Wastes ABS - Marley” to see if it does indeed have more than 100,000 rows? Please note that the number of rows is counted based on the last used row in the worksheet. For instance, if the last row that has data in any cell is the 100,000th row (not counting the header row), then the row count will be 100,000 even if all of the rows above the last row are blank. If the worksheet does in fact have more than 100,000 rows, then you should definitely use a SQL table instead of Excel spreadsheets.

[Sarah_Keown]

Thanks for the help, as far as I know the worksheet content I am trying to get access to is NOT exceeding this limit but have noticed the worksheets are large and have a large amount of blank rows underneath that arent being used, I am going to try and remove these and shrink the worksheet and see if this helps as only some seem to cause significant issues.