Error when adding rows to a sharepoint excel workbook table MaxRequestDurationExceeded

Question

here is the full error

{
    "error": {
        "code": "MaxRequestDurationExceeded",
        "message": "We're sorry. We couldn't finish what you asked us to do because it was taking too long.",
        "innerError": {
            "code": "gatewayTimeoutUncategorized",
            "message": "The service wasn't able to complete the request within the time limit.",
            "innerError": {
                "code": "MaxRequestDurationExceeded",
                "message": "We're sorry. We couldn't finish what you asked us to do because it was taking too long."
            },
            "date": "2020-11-24T02:48:23",
            "request-id": "a3f533ea-3d8e-4bb6-aa71-4eaf10b79364",
            "client-request-id": "a3f533ea-3d8e-4bb6-aa71-4eaf10b79364"
        }
    }
}

when I'm trying to add records to an excel file, I get this error message which is a bit unclear. At first I was sending 5k to 8k excel records per post so I thought because I was sending too much. what I did was send only 3k records for every post but still I get this error.

I assumed that the excel sharepoint file need some time to refresh the newly posted data so I added a time delay for 3 minutes after a post command but I get the error

I also tried in postman where I only send 1 test record and I still get the same error, not sure why.

Upon further checking, there appears to be a limit of a workbook size that is in sharepoint. 50mb is the limit, and our excel file is already at 40mb. It is already close, but still I got 10 more mb to use so there shouldn't be any issues.

Currently the excel sharepoint file has 1 million plus records

**yeah we might have to rethink treating the sharepoint excel file as a database but for now I'd like to see what causes the error mentioned since it doesn't really give much details.

edit: additional details, earlier when I was checking there are a few instances that the test records are actually added in the excel file but the response is still the mentioned error message

also I didn't posted the code since it was working last week so I think the error is not related to my code but due to some other variables that I'm not aware of

Answer 1

it seems sharepoint is now allowing anymore addition to the excel file since it already has tons of records. as per these limits:

https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

https://support.microsoft.com/en-us/office/file-size-limits-for-workbooks-in-sharepoint-9e5bc6f8-018f-415a-b890-5452687b325e

but still it is a bit weird that the endpoint does not return a more informative error message.

Answer 2

• Sounds like this issue is caused when the workbook takes longer than (time set by default) to open in excel online to check if there is any improvement.
• Check if the affected excel file you mentioned can be opened in the excel client? Check if you have lot of empty columns. You noticed that the empty column with million cells may be causing the issue here. So you tried removing, save it, run the API call. Also suggested when a workbook has too many cell styles, shapes, or formatting, it can cause Excel Online to take many times longer to open a file than usual.
• Shared the document.

Answer 3

One more thing to consider is, if you are using patch or post on address range. In my observation, patch is pretty slow compared to post. To be more precise update range is 2x-10x slower than insert range in my experience. Following are the links to two APIs I am referring to.

https://learn.microsoft.com/en-us/graph/api/range-update?view=graph-rest-1.0&tabs=http

https://learn.microsoft.com/en-us/graph/api/range-insert?view=graph-rest-1.0&tabs=http