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:

http://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.

Comments (2)

  1. Lewis Worley says:

    What if the worksheet has a space in the name? ie "Sheet 1" instead of "Sheet1"  

    This does not seem to work

    http://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('Sheet 1!A1|Z100')

    it gets converted to

    http://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('Sheet%201!A1|Z100')

    and I get a 404 error

    Thanks,

    Lewis

  2. Dr.Sharepoint says:

    thanks for the tip I was looking for this