Receiving a 504 Gateway Timeout error for workbook/createsession

Question

Hi,

When using the graph API to create a session for a large (18MB) workbook stored in Sharepoint I'm consistently getting a 504 Gateway Timeout after exactly 30 seconds. When I attempt this using a smaller Excel workbook it is always successful. This is the documentation I've been using: https://learn.microsoft.com/en-us/graph/api/workbook-createsession?view=graph-rest-1.0&tabs=http

I'm using a Powershell script to connect using Invoke-RestMethod (The Microsoft.Graph module does not contain function for working with Excel that I could find.

Is there a way of setting the timeout beyond 30 seconds? Bear in mind the SDK does not contain the functions I need.

This is a sample of the script:

$sessionBody = @{
  "persistChanges"="false"
} | ConvertTo-Json

$session = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$($siteid)/drive/items/$($itemid)/workbook/createSession" `
                    -Method POST `
                    -Headers @{"Authorization"="Bearer $($accessToken -join '')"} `
                    -Body $sessionBody `
                    -ConnectionTimeoutSeconds 300;

Answer 1

Hi Dale Stewart,

When working with APIs, especially those involving large datasets or complex operations, it’s common to encounter timeouts. For instance, opening a large workbook can take an indeterminate amount of time, leading to potential timeouts. To address this, Microsoft Graph provides a long-running operation pattern, which helps manage these extended processes without hitting timeout limits.

Long-Running Operation Pattern

The session creation Excel API in Microsoft Graph supports the long-running operation pattern. Here’s how you can implement it:

1. Add the Prefer: respond-async Header:
   • When creating a session, include the Prefer: respond-async header in your request. This indicates that the operation is long running.
2. Receive a 202 Accepted Response:
   • For long-running operations, the API returns a 202 Accepted response along with a Location header. This header provides a URL to check the status of the operation.
   • If the session creation completes quickly, you’ll receive a regular create session response instead.
3. Check Operation Status:
   • Use the URL provided in the Location header to retrieve the operation status. The status values can be notStarted, running, succeeded, or failed.
4. Retrieve the Result:
   • Once the operation completes, you can get the session creation result through the specified URL in the succeeded response.

Error Handling

As mentioned in official document occasionally, you might receive a 504 Gateway Timeout error. The recommended approach is to retry the request. Here’s a brief overview of the error handling process:

• Retry Logic: Implement a retry mechanism to handle transient errors like 504 Gateway Timeout. This involves catching the error and retrying the request after a short delay.

For more detailed guidance, refer to the official documentation:

• Work with APIs that take a long time to complete

By following these best practices, you can effectively manage long-running operations and handle potential timeouts gracefully.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".