1

Starting around 1AM PST on 8/20/2019, the UsedRange.Values property has started returning null.

Not on all Excel spreadsheets but on some, and we can't yet determine what factors cause it. Seems like a bug in the graph APIs, as this was working seamlessly for more than a year.

var getUsedRangeRequest = _sheetRequestBuilder.UsedRange().Request();
usedRange = getUsedRangeRequest.GetAsync().Result;

I expect usedRange.Values to be a non-null JToken and so we can access usedRange.Values.Children().ToList() .... etc.

Praveen Seshadri
  • 67
  • 5

1 Answers1

1

Thanks for reporting this issue. This is recovered now. Could you please try again?

Wenbo Shi
  • 172
  • 4
  • Thanks. It appears to work now. I don't yet know if all the edge cases work, but we'll kick off our automated test pass in a few minutes. So we'll have actual data in the morning. – Praveen Seshadri Aug 21 '19 at 07:50
  • Thank you very much Praveen. Please let us know if you find anything not working. We are closely monitoring this thread. – Wenbo Shi Aug 21 '19 at 10:13
  • All looking good! Thanks for the resolution. The API calls seem a bit slower than before (but we may be imagining it?). We are seeing 15 seconds to get UsedRange on a sheet with about 9800 rows and 17 columns. That seems higher than it was before, but I cannot say this for sure. – Praveen Seshadri Aug 22 '19 at 15:16
  • There are two approaches to call the Excel Graph request: sessionless and sessionful. If you use sessionless request, the latency will be larger because everytime a request was made, it has to open the workbook to create the session, and then execute request operation, and then close the session. If you use sessionful request, you can leverage the same session you created for subsequent operations on same workbook. Could you confirm if you observe this increased latency (15 seconds) latency on sessionless request, or sessionful request? – Wenbo Shi Aug 26 '19 at 03:09
  • We are leveraging session-ful API calls. However, the 15 second measurement is on the first request, which sets up the session. That still seems rather long, right? Also, it is definitely much shorter for a smaller Excel file, so it seems to be related primarily to the size of the file rather than the session set up. As an aside, we found another behavior change --- the docs say that sessions expire roughly after 7 minutes of inactivity, but we find it is more like 5 minutes of inactivity, not 7. We were caching session ids for 7 minutes, but seeing a lot more errors at 5 mins. – Praveen Seshadri Aug 27 '19 at 05:19
  • Yes, if the file is larger, the duration to create session is longer. Typically the persistent session expires after about 5 minutes of inactivity. Non persistent session expires after about 7 minutes of inactivity. As far as I know, this has been this for a while (+1 year?) I'll double check the docs, perhaps the doc is out of date. – Wenbo Shi Aug 29 '19 at 10:18
  • Link to docs: [Sessions and persistence usage](https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0#usage) – Wenbo Shi Sep 09 '19 at 01:28