msgraph-sdk-dotnet
msgraph-sdk-dotnet copied to clipboard
[Client bug]: v5 RangeWithAddress doesn't return cell values
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.
Blocking migration to latest version
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.
@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 https://github.com/microsoftgraph/msgraph-sdk-dotnet/issues/1673
When can we expect some movement on this case? As I said before - its blocking migration to the latest MS Graph SDK
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.
Still the case with nuget version 5.6.0
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 😄
@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.
Still the case with nuget version 5.11.0
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.
This still seems to be an issue with 5.17.0
@maisarissi Kindly reminder, workbook API is not supported since v5 has been released.
Is blocked by: https://github.com/microsoft/kiota/issues/2319
@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; }
}
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 https://github.com/microsoft/kiota/issues/2319 this is a tough nut to crack.
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:
Hope this helps!
Depends on https://github.com/microsoft/OpenAPI.NET.OData/issues/511