SDK issue getting/updating values of named ranges

[gotmike]

Describe the bug

i am trying to update the value of named ranges in excel using the microsoft graph api, but i don't seem to be able to pass the value the same way i can with the microsoft graph explorer.

to debug, i started with simply GETting the data that's in the cell. this url will work through the graph explorer...
https://graph.microsoft.com/v1.0/me/drive/items/ITEM_ID/workbook/names/TESTNAMEDRANGE/range
and it returns a response like this...

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#microsoft.graph.workbookRange",
    "@odata.id": "/users('USER_ID')/drive/items('ITEM_ID')/workbook/names(%27TESTNAMEDRANGE%27)/range()",
    "@microsoft.graph.tips": "Use $select to choose only the properties your app needs, as this can lead to performance improvements. For example: GET me/drive/items('<key>')/workbook/names('<key>')/microsoft.graph.range?$select=address,addressLocal",
    "address": "Sheet1!C3",
    "addressLocal": "Sheet1!C3",
    "columnCount": 1,
    "cellCount": 1,
    "columnHidden": false,
    "rowHidden": false,
    "numberFormat": [
        [
            "General"
        ]
    ],
    "columnIndex": 2,
    "text": [
        [
            "abcd"
        ]
    ],
    "formulas": [
        [
            "abcd"
        ]
    ],
    "formulasLocal": [
        [
            "abcd"
        ]
    ],
    "formulasR1C1": [
        [
            "abcd"
        ]
    ],
    "hidden": false,
    "rowCount": 1,
    "rowIndex": 2,
    "valueTypes": [
        [
            "String"
        ]
    ],
    "values": [
        [
            "abcd"
        ]
    ]
}

so then i tried to update using the same method, again through the graph explorer. using this url...
https://graph.microsoft.com/v1.0/me/drive/items/ITEM_ID/workbook/names/TESTNAMEDRANGE/range
and i use this json body...

{
"values" : [["HELLO"]],
"formulas" : [[null]],
"numberFormat" : [[null]]
}

this also works, with the following response...

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#microsoft.graph.workbookRange",
    "@odata.id": "/users('USER_ID')/drive/items('ITEM_ID')/workbook/names(%27TESTNAMEDRANGE%27)/range()",
    "address": "Sheet1!C3",
    "addressLocal": "Sheet1!C3",
    "columnCount": 1,
    "cellCount": 1,
    "columnHidden": false,
    "rowHidden": false,
    "numberFormat": [
        [
            "General"
        ]
    ],
    "columnIndex": 2,
    "text": [
        [
            "HELLO"
        ]
    ],
    "formulas": [
        [
            "HELLO"
        ]
    ],
    "formulasLocal": [
        [
            "HELLO"
        ]
    ],
    "formulasR1C1": [
        [
            "HELLO"
        ]
    ],
    "hidden": false,
    "rowCount": 1,
    "rowIndex": 2,
    "valueTypes": [
        [
            "String"
        ]
    ],
    "values": [
        [
            "HELLO"
        ]
    ]
}

so i concluded that the API does "work" using this method.

now i try in code, using the c# SDK. in the version 4.x SDK, this would work...

        public static async Task<WorkbookRange> UpdateRange(string itemId,
                                                            string sheetName,
                                                            string rangeName,
                                                            object rangeValue,
                                                            string sessionId)
        {
                var rangeUpdate = new WorkbookRange
                {
                    Values = JsonDocument.Parse(rangeValue.Serialize())
                };

                var result = await _graphUser.Drive.Items[itemId].Workbook.Worksheets[sheetName]
                    .Range(rangeName)
                    .Request()
                    .Header("workbook-session-id", sessionId)
                    .PatchAsync(rangeUpdate).ConfigureAwait(false);

                return result;
        }

now i'm trying to update to v5.x and having trouble. even GETting the value of a named range isn't working for me.

i've got code that looks like this...

                var drive = await _graphClient.Users[EnvConfig.AzureGraphUserId].Drive.GetAsync();
                var range = await _graphClient.Drives[drive.Id].Items[itemId].Workbook
                    .Worksheets[sheetName].RangeWithAddress(rangeName).GetAsync();
                var ranges = await _graphClient.Drives[drive.Id].Items[itemId].Workbook.Names[rangeName].GetAsync();
                ranges.Value = Common.convertToJson(rangeName, rangeValue);
                var result = await _graphClient.Drives[drive.Id].Items[itemId].Workbook.Names[rangeName].PatchAsync(ranges);

when this runs, the GetAsync() calls return data, pointing to the correct sheet/cell and such, but they do NOT include values.

the PatchAsync() call also completes correctly and returns data, but does not update the value nor does it return a value.

Expected behavior

i would expect it to both return and update the value that is in the cell.

How to reproduce

this full code snippet should work... of course you need to have a session and ids that exist in a test environment.

        public static async Task<WorkbookNamedItem> UpdateRange(string itemId,
                                                            string sheetName,
                                                            string rangeName,
                                                            object rangeValue,
                                                            string sessionId)
        {
                var drive = await _graphClient.Users[EnvConfig.AzureGraphUserId].Drive.GetAsync();
                var range = await _graphClient.Drives[drive.Id].Items[itemId].Workbook
                    .Worksheets[sheetName].RangeWithAddress(rangeName).GetAsync();
                var ranges = await _graphClient.Drives[drive.Id].Items[itemId].Workbook.Names[rangeName].GetAsync();
                ranges.Value = Common.convertToJson(rangeName, rangeValue);
                var result = await _graphClient.Drives[drive.Id].Items[itemId].Workbook.Names[rangeName].PatchAsync(ranges);

                return new WorkbookRange();
        }

SDK Version

5.48

Latest version known to work for scenario above?

4.x

Known Workarounds

i don't have a workaround, although it does seem to work via the graph explorer

Debug output

Click to expand log

```

</details>


### Configuration

_No response_

### Other information

_No response_

[MartinM85]

There are more issues related to workbook API: https://github.com/microsoftgraph/msgraph-sdk-dotnet/issues?q=is%3Aopen+is%3Aissue+workbook Not supported, but they are working on it.

[craig-blowfield]

Hi @MartinM85,

Where does it state that the SDK doesn't support the workbook APIs?

As the SDK is generated by the Kiota tooling, I just think the SDK is very broke in this area, specifically the deserialization from responses.

My answer in this thread might help

#1783 (comment)

[gotmike]

this seems like a major issue. is the plan to just drop the workbook api entirely? i'm surprised that this isn't higher priority.

[craig-blowfield]

To be honest @gotmike I think if you look at the amount of issues being reported by people for the SDK for V5.x I think the whole project has an issue, especially around support (or lack of).

And as the whole SDK is reliant on tooling (don't get me started on that), it means issues in the SDK tend to be due the tooling that generates it. So the turnaround time for these issues seems to be very slow.

[MartinM85]

The Graph API itself is huge and there is a lack of people that can maintain SDKs. It's fulltime job and even if the community can help, we don't have enough time for it.

There are lot of issues related to support workbook api, I can't find them all. The initial issue is here:

microsoft/kiota#2319

microsoft/OpenAPI.NET.OData#511
microsoftgraph/msgraph-metadata#596

[MartinM85]

@gotmike Try the latest version of SDK. The result of await _graphClient.Drives[drive.Id].Items[itemId].Workbook.Names[rangeName].GetAsync() should now contain properties like Values, Text, Formulas, etc.

[andrueastman]

Closing as this is resolved in latest version of the SDK.