Encoding of Sheet Names with "+" Sign

[ashokkumarrathore]

It pertains to the encoding of sheet names containing a plus sign ("+"). When making API calls to retrieve data from a spreadsheet, if the sheet name includes a plus sign, such as in the example "Users+", the plus sign is being incorrectly encoded, resulting in unexpected behavior. Specifically, the plus sign is being replaced with a space in the URL's range property.

Here's an example of the request URL and the accompanying error: GET https://sheets.googleapis.com/v4/spreadsheets/1SnJxI2ExTpCCumlHQhWdgTtsknbZsD9hYlMFnK-jmKc/values/Users+!1:101?valueRenderOption=FORMATTED_VALUE

Error:

{
  "code": 400,
  "errors": [
    {
      "domain": "global",
      "message": "Unable to parse range: Users !1:101",
      "reason": "badRequest"
    }
  ],
  "message": "Unable to parse range: Users !1:101",
  "status": "INVALID_ARGUMENT"
}

Furthermore, even if I attempt to manually encode the plus sign in the range formula as "%2B" (e.g., Users%2B!1:101), the Java library's behavior results in double encoding, producing "Users%252b!1:101", where the percent sign is also encoded to "%25". This issue hinders the proper functioning of the API calls, as the encoded sheet name does not match the actual sheet name in the spreadsheet, leading to errors in processing.

More details here : https://issuetracker.google.com/issues/322267497

[diegomarquezp]

Hi @ashokkumarrathore, thanks for bringing this up. Would you mind sharing a small reproducing snippet that causes this error? Thanks!

[diegomarquezp]

Hi @ashokkumarrathore, any chance we can have a quick snippet to look into? Thanks!

[ashokkumarrathore]

Using this snippet to create the service client and get data: In the range, provide tab name which contains a '+' sign.

    logger.info("Logging in to Google Sheets API...")
    val credentials = GoogleCredentials.fromStream(
      new ByteArrayInputStream(credentialsJson.getBytes(StandardCharsets.UTF_8))
      ).createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS))
    val requestInitializer = new HttpCredentialsAdapter(credentials)
    // Create the sheets API client
    val service = new Sheets.Builder(new NetHttpTransport(),
      GsonFactory.getDefaultInstance(),
      UipToolUtils.setHttpTimeout(requestInitializer)
    )
      .setApplicationName("UIP Gsheet connector")
      .build(); 

      val rawData = service.spreadsheets().values()
        .get(args.sheetId.get, readRange)
        .setValueRenderOption(args.valueRenderOption.get)
        .execute()
        .getValues