[Client bug]: v5 RangeWithAddress doesn't return cell values

[MartinM85]

Describe the bug
When calling

GET https://graph.microsoft.com/v1.0/me/drive/items/{driveId}/workbook/worksheets/{driveId}/range(address='A1:XX1')

The response contains cell values in text property

When doing the same in v5

var workbookRange = await m_client.Drives["Me"].Items[{driveId}]
        .Workbook.Worksheets[{driveId}]
        .RangeWithAddress("A1:XX1")
        .GetAsync();

there is nothing in workbookRange.Text. Microsoft.Graph.Models.Json doesn't contain any property or method to get cell values.

To Reproduce
Steps to reproduce the behavior:
Described above

Expected behavior
SDK returns cell values

Screenshots

Client version
5.4.0

Desktop (please complete the following information):

• OS: Windows 11

Additional context
Workbook endpoints are broken since v5 has been released.

[Stephan-Hoffmann]

Blocking migration to latest version

[Kane-Baden]

Hey team

I'm also facing this issue when trying to retrieve and/or post back WorkbookTableRow data, the values property is simply missing from the Json object returned.

I thought I might need to select/expand the values but could not add these to the call using QueryParameters.

<EntityType Name="workbookTableRow" BaseType="graph.entity">
<Property Name="index" Type="Edm.Int32" Nullable="false"/>
<Property Name="values" Type="graph.Json"/>
</EntityType>

As a workaround I have had to use the .ToGetRequestInformation() and .ToPostRequestInformation to allow for manual execution through HTTPClient.

[MartinM85]

@Kane-Baden Thanks for info. I would like to avoid any workaround because it's time consuming to write workaround and then remove it.

@Stephan-Hoffmann Be aware that there is another issue which can block you from migration #1673

[Stephan-Hoffmann]

When can we expect some movement on this case? As I said before - its blocking migration to the latest MS Graph SDK

[MartinM85]

I would expect that msgraph-sdk-dotnet team will test workbook api properly. v5 was released more than one month ago and it still doesn't work.

[Stephan-Hoffmann]

Still the case with nuget version 5.6.0

[bgdmrq]

I am still having the issue using nuget version 5.7.0.

@Kane-Baden can you provide more info regarding how to use HttpClient and how to deserialize the responses?

I have noticed that the issue might come from the deserialization.

Here's a code snippet with what I tried (using BatchRequests to run requests directly through GraphServiceClient instead of HttpClient):

var batchRequestContent = new BatchRequestContent(_graphServiceClient);
var rangeRequest = workbook.Worksheets["Outputs"].UsedRange.ToGetRequestInformation();
var rangeRequestId = await batchRequestContent.AddBatchRequestStepAsync(rangeRequest);
var batchResp = await _graphServiceClient.Batch.PostAsync(batchRequestContent);

// These are basically the same call, with the same return value
var dataAsWorkbookRange = await batchResp.GetResponseByIdAsync<WorkbookRange>(rangeRequestId);
var sdkRawData = await workbook.Worksheets["Outputs"].UsedRange.GetAsync();

// If I try to deserialize the response to another object, the object properties are not mapped accordingly, 
// but the property named AdditionalData holds all the missing data (Values, Text etc).
var dataAsOtherModel = await batchResp.GetResponseByIdAsync<WorkbookWorksheetCollectionResponse>(rangeRequestId);

Maybe my findings could help, @andrueastman 😄

[MartinM85]

@andrueastman Is there any estimate (weeks, months) when it will be fixed? I would like to use v5 for a new project but I'm afraid that v4 is only version which I can use in production without worry.

[Stephan-Hoffmann]

Still the case with nuget version 5.11.0

[frankbjr]

Still the case with version 5.13.0

I can't get any of the "Range" retrieval methods to work (UsedRange , RangeWithAddress etc).

I noticed in the returned object, WorkbookRange.Worksheet field is NULL as well...

Any progress? Seems like a fairly large hole in the surface of the SDK, and I'm a little surprised this issue has been open for as long a time and as many versions 5.xx as it has.

[handsomedave]

This still seems to be an issue with 5.17.0

[MartinM85]

@maisarissi Kindly reminder, workbook API is not supported since v5 has been released.

[MartinM85]

Is blocked by: microsoft/kiota#2319

[maisarissi]

Is blocked by: microsoft/kiota#2319

Thanks for posting it and yes, we are blocking by this issue.

[Kane-Baden]

@kryogenyk sorry for the delayed reply.

I created custom classes to map against the response data, looks like this.

WorkbookTableItemRequestBuilder request = GenerateTableRequest();
var TableColumns = await request.Columns.GetAsync();

HttpClient httpClient = new HttpClient();
    var GetAllRowsRequest = request.Rows.ToGetRequestInformation(
requestConfiguration =>
{
requestConfiguration.Headers.Add("workbook-session-id", _session.Id);
}
);
var ManualGetAllRowsRequest = await _graphClient.RequestAdapter.ConvertToNativeRequestAsync<HttpRequestMessage>(GetAllRowsRequest);
var ManualGetAllRowsResult = await httpClient.SendAsync(ManualGetAllRowsRequest);
var ManualGetAllRowsContent = await ManualGetAllRowsResult.Content.ReadAsStringAsync();
return JsonSerializer.Deserialize<ManualWorkbookTableRows>(ManualGetAllRowsContent); 


public class ManualWorkbookTableRows
{
    // [JsonProperty("value")]
    [JsonPropertyName("value")]
    public List<ManualWorkbookTableRow> TableRows { get; set; } = new List<ManualWorkbookTableRow>();
}

public class ManualWorkbookTableRow
{
    //[JsonProperty("index")]
    [JsonPropertyName("index")]
    public int RowIndex { get; set; }

    // [JsonProperty("values")]
    [JsonPropertyName("values")]
    public List<List<dynamic>> RowValues { get; set; }
}

[Ruud2000]

I wasted quite some time today trying to obtain Excel cell data using the Graph SDK. Sad to see this issue is already open for so long although I understand from reading microsoft/kiota#2319 this is a tough nut to crack.

[trumpetchris]

I struggled half a day till I found this bug here and a workaround. (I used Microsoft.Graph v5.44.0)
First I tried direct over HTTPClient but in my WebAPI I was not able to resolve an access token. (access on behalf of a user from a desktop app)
Now I use the following workaround in the Web API to get the data:

// create the request
var requestInformation = new RequestInformation()
 {
         HttpMethod = Method.GET,
         // Check this uri first in the graph-explorer to be sure that it works: https://developer.microsoft.com/en-us/graph/graph-explorer
         URI = new Uri("https://graph.microsoft.com/v1.0/me/drive/items/XXXXXXXXXXXXXX/workbook/worksheets/YYYYYYYYY/tables/{ZZZZZZZ}/rows")
};
         
var nativeResponseHandler = new NativeResponseHandler();
requestInformation.SetResponseHandler(nativeResponseHandler);
_graphServiceClient.RequestAdapter.SendNoContentAsync(requestInformation).GetAwaiter().GetResult(); // this will be authenticated with the authenticationprovider

var response = nativeResponseHadnler.Value as HttpResponseMessage;
if (response.IsSuccessStatusCode)
{
              string result = response.Content.ReadAsStringAsync().GetAwaiter().GetResult();
               // Do with the response what you want...
               dynamic obj = JsonConvert.DeserializeObject<dynamic>(result);
               var rows = obj.value;
               JArray a = rows;
}

In Programm.cs I use:

builder.Services.AddMicrosoftIdentityWebApiAuthentication(builder.Configuration,"AzureAd")
                   .EnableTokenAcquisitionToCallDownstreamApi()
                       .AddMicrosoftGraph(builder.Configuration.GetSection("DownstreamApi"))
                       .AddInMemoryTokenCaches();

With appsettings:

 "AzureAd": {
    "Instance": "https://login.microsoftonline.com",
    "ClientId": "XXXXXXX",
    "ClientSecret": "XXXXXX",
    "TenantId": "XXXXXX"
  },
  "DownstreamAPI": {
    /*
       'Scopes' contains space separated scopes of the web API you want to call. This can be:
        - a scope for a V2 application (for instance api://b3682cc7-8b30-4bd2-aaba-080c6bf0fd31/access_as_user)
        - a scope corresponding to a V1 application (for instance <App ID URI>/.default, where  <App ID URI> is the
          App ID URI of a legacy v1 web application
        Applications are registered in the https://portal.azure.com portal.
      */
    "BaseUrl": "https://graph.microsoft.com/v1.0",
    "Scopes": "user.read contacts.read"
  }

Most of the configuration of the client and the Web API comes from:

Sign a user into a Desktop application using Microsoft Identity Platform and call a protected ASP.NET Core Web API, which calls Microsoft Graph on-behalf of the user

Hope this helps!

[andrueastman]

Depends on microsoft/OpenAPI.NET.OData#511

[27k1]

Is there any progress on this? It is critical for my project transferred to WInUI from UWP.

[27k1]

I tried trumpetchris method above but unfortunately it still uses the Kiota thing and no cell values are returned.

I have achieved the result with HttpClient .net 8. WinUi 3 with the latest Microsoft packages. The access token is obtained from the standard Microsoft PublicClient code.

using Microsoft.Identity.Client;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Net.Http.Headers;

    if (authResult != null)
    {

        HttpClient client = new HttpClient();

        var access_token = authResult.AccessToken;
        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", access_token);

        Uri requestUri = new Uri("https://graph.microsoft.com/v1.0/drives/<driveId>/items/<driveItemId>/workbook/worksheets/Sheet1/range(address='A1:X1')");


        try
        {
            var response = await client.GetStreamAsync(requestUri);
            using (var streamReader = new StreamReader(response))
            using (var jsonReader = new JsonTextReader(streamReader))
            {
                JObject messageData = await JObject.LoadAsync(jsonReader);

                ....
            }
        }
        catch (Exception ex)
        {
        }

}

[craig-blowfield]

Building on top of @Kane-Baden workaround you can still use the SDK (for what its worth), but essentially bypass the response handling

For example:

            var nativeResponseHandler = new NativeResponseHandler();

            var workbookRange = await graphClient
                .Drives[workbookDriveItem.ParentReference.DriveId]
                .Items[workbookDriveItem.Id]
                .Workbook
                .Names["named_range"]
                .Range
                .GetAsync(requestConfiguration => requestConfiguration.Options.Add(new ResponseHandlerOption() { ResponseHandler = nativeResponseHandler }));

            var responseMessage = nativeResponseHandler.Value as HttpResponseMessage;

            var result = await responseMessage.Content.ReadFromJsonAsync<ManualWorkbookTableRow>();

But I can't believe this is STILL an issue

[27k1]

Building on top of @Kane-Baden workaround you can still use the SDK (for what its worth), but essentially bypass the response handling

For example:

            var nativeResponseHandler = new NativeResponseHandler();

            var workbookRange = await graphClient
                .Drives[workbookDriveItem.ParentReference.DriveId]
                .Items[workbookDriveItem.Id]
                .Workbook
                .Names["named_range"]
                .Range
                .GetAsync(requestConfiguration => requestConfiguration.Options.Add(new ResponseHandlerOption() { ResponseHandler = nativeResponseHandler }));

            var responseMessage = nativeResponseHandler.Value as HttpResponseMessage;

            var result = await responseMessage.Content.ReadFromJsonAsync<ManualWorkbookTableRow>();

But I can't believe this is STILL an issue

Exactly - "For what its worth"

[27k1]

var result = await responseMessage.Content.ReadFromJsonAsync<ManualWorkbookTableRow>();

Thank you this works a treat.

However, it is more reliable to use NewtonSoft, wiping out reading date fields
var json = responseMessage.Content.ReadAsStringAsync().Result;
var obj = JsonConvert.DeserializeObject(value);

[MartinM85]

Can be closed. WorkbookRange now returns Formulas, Values, Text, etc. as UntypedArray and it allows to access all cells in specified range.