O365 / python-o365

A simple python library to interact with Microsoft Graph and Office 365 API
Apache License 2.0
1.6k stars 411 forks source link

Update Excel get_used_range methods to fix/enable "onlyValues" API parameter #1062

Closed zstrathe closed 2 months ago

zstrathe commented 3 months ago

Currently for the Excel.Range.get_used_range() and Excel.WorkSheet.get_used_range() methods, they do not work with the MS Graph API parameter "onlyValues" (for considering only Excel cells with values/ignoring empty but formatted cells).

The Excel.Range.get_used_range() method currently has an "only_values" parameter, but the Excel.Range endpoint for "get_used_range" currently does not have the "onlyValues" parameter included. So I have updated that endpoint. And since the Excel.Range.get_used_range() already has the "only_values" parameter set to default as True, I left that unchanged.

The Excel.WorkSheet.get_used_range() does not have an "only_values" parameter, so I added it. And similarly, the Excel.WorkSheet "get_used_range" endpoint also currently lacks the "onlyValues" parameter, so I added it as well. And since the Excel.Range.get_used_range() already has the "only_values" parameter set to default as True, I kept that default in this method as well.

Additionally, the "only_values" parameter needs to be converted into a lowercase string for proper functionality with the API (see example HTTP Request using the valuesOnly parameter (https://learn.microsoft.com/en-us/graph/api/worksheet-usedrange?view=graph-rest-1.0&tabs=http#request-1):

GET https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/{id|name}/usedRange(valuesOnly=true)

As for testing, I'm not sure of any great way to test these updated methods. However, please see attached screenshots from running the modified code on my machine: used_range_test_sheet used_range_test_code

alejcas commented 2 months ago

Thanks!