1

I'm attempting to request data from a google spreadsheet using the googleAuthR. I need to use this library instead of Jenny Bryan's googlesheets because the request is part of a shiny app with multiple user authentication. When the request range does not contain spaces (e.g. "Sheet1!A:B" the request succeeds. However, when the tab name contains spaces (e.g. "'Sheet 1'!A:B" or "\'Sheet 1\'!A:B", the request fails and throws this error:

Request Status Code: 400
Error : lexical error: invalid char in json text.
                                   <!DOCTYPE html> <html lang=en> 
                 (right here) ------^

Mark Edmondson's googleAuthR uses jsonlite for parsing JSON. I assume this error is coming from jsonlite, but I'm at a loss for how to fix it. Here is a minimal example to recreate the issue:

library(googleAuthR)

# scopes
options("googleAuthR.scopes.selected" = "https://www.googleapis.com/auth/spreadsheets.readonly")
# client id and secret
options("googleAuthR.client_id" = "XXXX")
options("googleAuthR.client_secret" = "XXXX")

# request
get_data <- function(spreadsheetId, range) {

    l <- googleAuthR::gar_api_generator(
        baseURI = "https://sheets.googleapis.com/v4/",
        http_header = 'GET',
        path_args = list(spreadsheets = spreadsheetId,
                         values = range),
        pars_args = list(majorDimension = 'ROWS',
                         valueRenderOption = 'UNFORMATTED_VALUE'),
        data_parse_function = function(x) x)

    req <- l()

    req
}

# authenticate
gar_auth(new_user = TRUE)

# input
spreadsheet_id <- "XXXX"
range <- "'Sheet 1'!A:B"

# get data
df <- get_data(spreadsheet_id, range)

How should I format range variable for the request to work? Thanks in advance for the help.

why
  • 61
  • 1
  • 1
  • 4

1 Answers1

1

Use URLencode() to percent-encode spaces.

Details:

Using options(googleAuthR.verbose = 1) shows that the GET request was of the form:

GET /v4/spreadsheets/.../values/'Sheet 1'!A:B?majorDimension=ROWS&valueRenderOption=UNFORMATTED_VALUE HTTP/1.1

I had assumed the space would be encoded, but I guess not. In this github issue from August 2016, Mark states URLencode() was going to be the default for later versions of googleAuthR. Not sure if that will still happen, but it's an easy fix in the meantime.

why
  • 61
  • 1
  • 1
  • 4