How to treat spaces/single quotes in Sheet names in REST

  • Article

When using REST to access portions of a workbook, people sometimes use direct addressing (Sheet1!A1). I thought I’d drop a line about the icky way this works when you have single quotes in your sheet name.

Reminder…

First – a short reminder. This is how Excel Services REST works when trying to access a range:

https://server/_vti_bin/ExcelRest.aspx/doclib/File.xlsx/Model/Ranges('sheet1!A1')?$format=html

This will give back an HTML fragment representing the range. Now, what if sheet1 had a space in it? Say it was called Sheet 1?

Handling spaces in sheet names

The standard way of handling spaces in sheet names is to surround them in single quotes. And herein lies the problem – we already use single quotes to specify the range. The solution is to escape them (not URL escape – that will not help us). So here's what this looks like:

https://server/_vti_bin/ExcelRest.aspx/doclib/File.xlsx/Model/Ranges(' ''sheet 1'' !A1')?$format=html

This may be a little unclear. Here's what's inside the braces:

<single quote-1><single quote-2><single quote-3>sheet<space>1<single quote-4><single quote-5>!A1<single quote-6>

1 – Denotes the beginning of the range name.

2,3 – the first single-quote in 'Sheet 1' encoded by doubling it.

4,5 – the second single quote in Sheet 1' encoded by doubling it.

6 – The end of the range name.

But wait.. It gets even uglier

What if the sheet name has a single quote in it? Say: Sheet '1 (that's Sheet<space><single quote>1). In Excel, this would be represented in this fashion:

image

See the double-single-quote in there? It's because Excel faces the same issue as we did in the previous example – it needs to escape the character. But what does that look like in REST?

https://server/_vti_bin/ExcelRest.aspx/doclib/File.xlsx/Model/Ranges(' ''sheet ''''1'' !A1')?$format=html

Yeah. That's four single quotes in there…

<sq-1><sq-2><sq-3>sheet<space><sq-4><sq-5><sq-6><sq-7>1<sq-8><sq-9>!A1<sq-10>

1 – Denotes the beginning of the range name.

2,3 – the first single-quote in 'Sheet ''1' encoded by doubling it.

4,5 – the second single quote character in 'Sheet ''1' encoded by doubling it.

6, 7 – the third single quote character in 'Sheet ''1' encoded by doubling it.

8, 9 – the fourth single quote character in 'Sheet ''1' encoded by doubling it.

10 – The end of the range name.