You can access arbitrary ranges via REST – here’s the trick

Reader JJDE asked in this post if it was possible to access arbitrary ranges with REST – you just need to use a pipe character where you would usually place a colon:

https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('Sheet1!A1|Z100')

This will return the Sheet1!A1:Z100 range.

As for why we had to do this.. IIS does not like colons in the path portion of a URL – any colon in the Path causes IIS to not allow the request to go through. Now, there’s a workaround to that by tweaking some registry key, but we did not want admins to do it nor did we want to do this automatically, so we decided to use the pipe symbol instead.