Search code examples
excelmicrosoft-graph-api

Update range request returns 200 OK but doesn't update the Excel sheet

I'm having trouble updating a few cells inside an Excel sheet. The PATCH request goes through alright and everything looks fine but when I open the document its not updated.

The excel file is hosted in SharePoint, not OneDrive.

I'm using the Microsoft Graph via Graph explorer and I can read the cells in question:

/v1.0/drives/{id}/list/items/{id}/driveItem/workbook/worksheets/{id}/range(address='B2:B3')

The write request returns success, but doesn't make any changes

PATCH https://graph.microsoft.com/v1.0/drives/{id}/list/items/{id}/driveItem/workbook/worksheets/{id}/range(address='B2:B3')

{
  "values": [["foo bar"], ["hello world"]]
}

Response (200 OK) (omitted a few sensitive fields and a few for brevity)

{
  "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#workbookRange",
  "@odata.type": "#microsoft.graph.workbookRange",
  "columnCount": 1,
  "cellCount": 2,
  "columnHidden": false,
  "rowHidden": false,
  "numberFormat": [["General"], ["General"]],
  "columnIndex": 1,
  "text": [["foo bar"], ["hello world"]],
  "hidden": false,
  "rowCount": 2,
  "rowIndex": 1,
  "valueTypes": [["String"], ["String"]],
  "values": [["foo bar"], ["hello world"]]
}

That's the response I'm expecting but when I open the Excel document, the cells are not updated.

I also tried using a workbook-session, but to no avail

  1. Create session via 

    http
POST https://graph.microsoft.com/v1.0/drives/{id}/list/items/{id}/driveItem/workbook/createSession

{
  "persistChanges": true
}

  2. Copy the workbook-session-id. The entire thing - it's crazy long!

  3. Add the workbook-session-id header to the previous PATCH request and send.
  4. Close the session via POST https://graph.microsoft.com/v1.0/drives/{drive-id}/list/items/{item-id}/driveItem/workbook/closeSession
  5. Check document, but it's unchanged

I also find the documentation a bit misleading. In the Update Range doc it says

Optional request headers

Workbook-Session-Id Workbook session Id that determines if changes are persisted or not. Optional.

Suggesting that if not used changes are not saved. However the session docs says

Note: The session header is not required for an Excel API to work. However, we recommend that you use the session header to improve performance. If you don't use a session header, changes made during the API call are persisted to the file.

So why isn't it persisted?

Solution

  • So the problem was that the Excel file had a macro.

    The call works for .xlsm files that do not have macros. Sadly the file in question was a .xlsm file with macros.