0

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

Dev
  • 2,428
  • 2
  • 14
  • 15
chip
  • 3,039
  • 5
  • 35
  • 59
  • 1
    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. (1) Can you check if the affected excel file you mentioned can be opened in the excel client? (2) See if you have lot of empty columns in excel, if exists then remove and see if helps you move forward. – Dev Nov 24 '20 at 03:48
  • thanks for your comment. (1) I am able to open the file in the browser and I can see the records. I also tried downloading the file and I am able to open it (not in excel tho since I'm on ubuntu) on libreoffice calc. (2) there is a timestamp column that doesn't show any values so this goes for a million cells as well that are empty – chip Nov 24 '20 at 04:00
  • Glad that you can open it. Hopefully the issue doesn't contain any complex forumla's in it. Sounds like the empty column with million cells may be causing the issue here. Try remove it, save it, run the API call to see how it works – Dev Nov 24 '20 at 04:09
  • sadly I am getting the same error. I had high hopes that it would fix it but yeah still I appreciate your comment – chip Nov 24 '20 at 04:30
  • hmm... thanks for the words...Also i noticed 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... Make sure its not the case for you!! – Dev Nov 24 '20 at 04:34
  • Here's a related document talks about the same - (https://learn.microsoft.com/en-us/office/troubleshoot/excel/request-too-long-opening-excel-workbook). Let me know how it goes. – Dev Nov 24 '20 at 04:35
  • thanks for the resource. the file that I'm working on is fairly simple, I just retrieve data from the database and post it in the excel file at sharepoint. no stylings or formmating is added – chip Nov 24 '20 at 12:05
  • Thanks for the confirmation @niccolo. I will move this to answer, so it can be useful to others (also i see that you opened another stackoverflow item, let me see if i can help there too)!! – Dev Nov 24 '20 at 13:46

3 Answers3

0
  • 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.
Dev
  • 2,428
  • 2
  • 14
  • 15
0

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.

chip
  • 3,039
  • 5
  • 35
  • 59
0

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
Akshay thakur
  • 91
  • 1
  • 4