Using a token from `googleAuthR` in `googlesheets`

Question

The googleAuthR is an R package which wraps the google API client library (authentication and further usage of the API). The googlesheets R package is used to integrate with the google sheets API (i.e., a wrapper around the google sheets API).

Each of these packages has a seperate OAuth2.0 process of their own. I'm using the googleAuthR in order to log into a shiny app, which in turn also uses the googlesheets library (but with a different authentication process).

The question: How can we set the googlesheets package to use the initial googleAuthR credentials as well?

Here is the process I'm using (within a shiny app):

For the googleAuthR login I'm using:

options(googleAuthR.webapp.client_secret = "***REMOVED_FROM_EXAMPLE***")
options(googleAuthR.webapp.client_id = "***REMOVED_FROM_EXAMPLE***")
options(googleAuthR.scopes.selected = c("https://www.googleapis.com/auth/userinfo.email",
                                        "https://www.googleapis.com/auth/userinfo.profile",
                                        "https://www.googleapis.com/auth/spreadsheets"))

And for the googlesheets package, I'm currently using a pre-registered (separate) token which I saved into an RDS file, as suggested in the package's vignette:

suppressMessages(gs_auth(token = "googlesheets_token.rds", verbose = FALSE))
gsheet_log <- googlesheets::gs_url("https://docs.google.com/spreadsheets/d/***REMOVED_FROM_EXAMPLE***/edit#gid=0")

I would like a flow which replaces the use of gs_auth with the token generated by googleAuthR.

IMPORTANT NOTE (Added to this question at a later time):

I think that perhaps the use of googlesheets is relevant only for the short term and should be discouraged, because google are going to deprecate it on March 3, 2020 see message

Hence @Aurèle's comment might be the way to go here (hoping that googlesheets4 will get, eventually, all the capabilities of its predecessor). The question might still be interesting in the broader sense.

Consider the (not yet on CRAN) successor googlesheets4 github.com/tidyverse/googlesheets4 , that relies on gargle for auth (on CRAN, cloud.r-project.org/web/packages/gargle/index.html ) — Aurèle
– Aurèle, Commented Oct 4, 2019 at 14:15
Thanks, but at this point in time googlesheets4 doesn't have a lot of the capabilities of googlesheets, e.g., adding a single row to an existing sheet. — Adi Sarid
– Adi Sarid, Commented Oct 5, 2019 at 12:56
A workaround that I did was to download the files using the googledrive package, then read the file in using readxl or readr — Vivek Katial
– Vivek Katial, Commented Nov 25, 2019 at 8:27

jjankowiak · Accepted Answer · 2019-11-29 16:49:41Z

I have struggled myself to authenticate via googleAuthR and then use my credentials to read a sheet using googlesheets4 library. Unfortunately, I still cannot make it work, but I found a crude workaround in "pure" googleAuthR. Using gar_api_generator function you can actually call any requests that is allowed in Google API.

library(shiny)
library(googleAuthR)
options(shiny.port = 8787)
options(googleAuthR.redirect = "http://localhost:8787")

# JSON with you client data from GCP
gar_set_client(scopes = "https://www.googleapis.com/auth/spreadsheets.readonly",
               web_json = "<YOUR_JSON>")
spreadsheet_key <- "<YOUR SHEET>"

read_googlesheet <- gar_api_generator(
  baseURI = "https://sheets.googleapis.com/v4/",
  http_header = 'GET',
  path_args = list(spreadsheets = spreadsheet_key,
                   values = "A:U"), #column range 
  data_parse_function = function(x) x$values
)

## ui.R
ui <- fluidPage(title = "googleAuthR Shiny Demo",
                tableOutput("gs")
)

## server.R
server <- function(input, output, session){
  gar_shiny_auth(session)

  output$gs <- renderTable({
    df_raw <- read_googlesheet()
    # make the first row of the dataset as a header
    df <- df_raw[c(2:nrow(df_raw)), ]
    colnames(df) <- df_raw[1, ]
    df
  })
}

shinyApp(gar_shiny_ui(ui, login_ui = gar_shiny_login_ui), server)

KKW · Accepted Answer · 2019-11-17 21:37:27Z

-1

Can you try using service account token instead for authentication? https://gargle.r-lib.org/articles/get-api-credentials.html

Might be a little tedious at first to set up but it was worth it for me

Go to console.cloud.google.com
Add project
Create credential and download json
Enable API (google sheets)
Copy the address of the "new credential" and go to the google sheet you want to access/manipulate (add user)

library(googlesheets4)

sheets_auth(path = "/path/to/a/service-account.json")
df <- read_sheet(ss = "sheet_key_here")
df

answered Nov 17, 2019 at 21:37

KKW

3831 silver badge11 bronze badges

1 Comment

jjankowiak Over a year ago

There are some cases when you cannot use API key (e.g. access a non-public sheet).

Collectives™ on Stack Overflow

Using a token from `googleAuthR` in `googlesheets`

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related