Escaping plus sign (+) in sheet names

Question

We have a google spreadsheet with a sheet named something like foo + bar (yay). When I try to select data from the sheet using the Java API, I'm getting back an error from Google saying:

{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "message" : "Unable to parse range: foo   bar (yay)!A2:Z",
    "reason" : "badRequest"
  } ],
  "message" : "Unable to parse range: foo   bar (yay)!A2:Z",
  "status" : "INVALID_ARGUMENT"
}

The code looks something like this (some details of authentication elided, and translated from Scala)

Sheets s = ... // constructed using HTTP transport & credentials
String range = "'foo + bar (yay)'!A2:Z";
String spreadsheetId = ... // fill in your own
ValueRange values = s.spreadsheets().values().get(spreadsheetId, range).execute();

It's based on the following code example: https://developers.google.com/sheets/api/quickstart/java#step_3_set_up_the_sample

Notice that the + has been replaced by a single space , as if it were being URL encoded. I tried replacing + with %2B but I still get an error and it repeats %2B back to me as if I meant literally "%2B" - so I don't think it's a matter of URL encoding.

I tried listing the sheet names for this spreadsheet, and I'm getting back a + sign in the sheet name, so I don't think that's the problem. Then I tried renaming the sheet to use the word "and" instead of +, and my code was able to successfully select the range - thus I'm pretty certain the + is the problem. I don't see any description of escaping special characters in the documentation.

I noticed this in the documentation: https://developers.google.com/sheets/api/guides/concepts#a1_notation and tried surrounding the sheet name with single quotes ("'foo + bar (yay)'") but to no avail.

Is there any way I can select this range without renaming the sheet?

user_1681980 · Answer

i've hit this same bug - also using java libraries, and trying to reference a sheet named "Week 1+2".. i tried the "'Week 1+2'!A2:Z" idea but java didn't like it. i then tried "'Week1\+2'!A2:Z" and it compiled, but got an "Unable to parse range 'Week 1\ 2' error. it's like (as someone pointed out) the '+' gets translated to a space. I saw elsewhere someone was having an issue with a - in the name too?

** NOT AN ANSWER!!! tried to add as a comment to above but it won't let me**

interesting fact -> i tested the python library to read the sheet, and it accepts the sheetname with an embedded + quite happily. so this would appear to be a java thing....

user_6281777 · Answer

Finally got this to work.

When a sheet name is used in an API call as the RANGE, such as sheets.googleapis.com/v4/spreadsheets/{sheetID}/values/SHEET_NAME_AS_RANGE?key={key}, the trick is to NOT use single quotes around the range name AND to use encodeURIComponent on the sheet name.

So, for example a sheet with a name of NY/POINTS+AndSpace Here would get encoded as NY%2FPOINTS%2BAndSpace%20Here and look like this in the example I gave:

sheets.googleapis.com/v4/spreadsheets/{sheetID}/values/NY%2FPOINTS%2BAndSpace%20Here?key={key}

That should mean the OP would be able to use the following:

String sheetName = "foo + bar (yay)" 
String range = encodeURIComponent(sheetName) + "!A2:Z"

(I only tested this using the sheet name by itself as the RANGE, not including the A2:Z part, so including that would need to be confirmed).

Escaping plus sign (+) in sheet names

2 Answers