Graph API fails to update Range in excel.

Question

The URL:

https://graph.microsoft.com/v1.0/sites/<site-id>/drive/items/<item-id>/workbook/worksheets/<worksheet name>/range(address='A1:L21577')

The Error:

404 Error: 'code': 'ResourceNotFound', 'message': 'Invalid version: error'

Use case: The above API is used to update excel workbooks range in my Sharepoint site.
Issue: The api fails with 404 error whenever the range that i am trying to update is greater than 20000 cells.
Data: Each row contains 12 columns data could be either int, float, string. so data becomes (12 * 20000+) Cells

any idea why so ? and how could I resolve this ?

Answer 1

Hi,@Ansh Vidyabhanu,

The error message you received indicates that the API call returned a 404 error with the messages "ResourceNotFound" and "Invalid Version: Error". This error typically occurs when the specified resource (in this case the scope trying to update) cannot be found or accessed.

The limit encountered when the API fails for ranges larger than 20,000 cells can be due to several reasons:

1. API limitations: The API you use may have limitations on the size of a range that can be updated in a single request. An API may be designed to handle smaller scopes, and larger scopes may exceed its capabilities.
2. Resource availability: The SharePoint site or Excel workbook you are trying to update may have resource constraints or limitations that prevent large updates. It's worth checking your environment for any specific limitations imposed by SharePoint or Excel Services.

To resolve this issue, consider the following approaches:

1. Split the range into smaller batches: Instead of updating the entire range in a single request, split it into smaller batches of cells and make multiple API calls to update each batch. For example, you can divide a range into smaller ranges and update them individually.
2. Optimize data processing: If possible, try to optimize your data processing logic to minimize the number of cells that need to be updated. For example, you can implement techniques such as filtering, data compression, or use more efficient data structures to reduce the overall data size.
3. Explore alternative APIs or methods: If the current API does not support large-scale updates, you can explore alternative methods for updating Excel workbooks in SharePoint. This may involve using other APIs, such as the SharePoint REST API or Excel Services API, or utilizing tools such as PowerShell scripts or Excel COM Automation to perform the update.

It's important to note that the exact solution will depend on your environment, requirements, and available APIs. I recommend reviewing the documentation and resources provided by Microsoft for the APIs you are using, and considering the specific limitations and capabilities they provide.

Here is link for your reference:

https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

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

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng