Remote server authentication to read Googlesheets from R script not using service accounts?

Question

I'm automating a process in R that pulls, pushes, analyses & stores data in a certain way and all in all, juggles data between Googlesheets, Postgresql, and salesforce. Till now, I had it running in my system but I will need to run the scripts from my remote login ssh server on aws.

The thing is though, I use 'googlesheets' package in R to auth & read google sheets but that needs an interactive environment to register/setup. I've read other answers on SO about this issue but they all suggest opening a google service account.

However, I dont want to do this because all I need is to read those google sheets which by itself is free. I have no GCP, google compute, bigquery or any of those right now and I certainly cant get pay for anything now.

Is there a way to read google sheets from a non interactive environment (linux terminal remote login interface)? I'm open to trying other libraries or any other hack that you may have.

Anything guys?

Answer 1

I was struggling with something similar for many hours, so even if this an old question, I think some other people might find this solution useful. My problem was the authentication in a non interactive way to read and modify a google sheet through a Shiny App. The app use to always trigger the dance auth process no matter if I would have saved it in a cache folder embedded in the app.

I have used the following guides and questions to guide myself through the process:

• https://cran.r-project.org/web/packages/gargle/vignettes/non-interactive-auth.html
• https://community.rstudio.com/t/shinyapps-io-non-interactive-auth-fails-for-googledrive/46504
• https://docs.rstudio.com/shinyapps.io/getting-started.html#deploying-applications

Try the following reproducible example:

library("googledrive")
library("googlesheets4") # I am using the developing version 0.1.0.9000
library("shiny")

# You want to deploy an app in Shinyapps.io or other server
# FIRST STEP----
# Get the token an store it in a cache folder embedded in your app directory
# designate project-specific cache
options(gargle_oauth_cache = ".secrets")
# options(gargle_quiet = FALSE) # So you can know what is happening
# Authenticate in interactive mode (run the app) ONCE and check if the token 
# has been stored inside the .secrets folder, after that just comment this line
#drive_auth() # Authenticate to produce the token in the cache folder
# Grant permission to googlesheets to access to the token produced
#sheets_auth(token = drive_token())

# SECOND STEP----
# Comment lines 10, 13 and 15 and uncomment lines 21 and 22
# You tell gargle to search the token in the secrets folder and to look
# for an auth given to a certain email (enter your email linked to googledrive!)
drive_auth(cache = ".secrets", email = "enter_your_email@here")
sheets_auth(token = drive_token())

# THIRD STEP---
# Now you can deploy your app in shinyapps.io!!
# Test if your app runs properly in the local version
# Authenticate in ShinyApps.io
# rsconnect::setAccountInfo(name="<ACCOUNT>", token="<TOKEN>", secret="<SECRET>")
# setwd() in your App directory
# library(rsconnect)
# deployApp()
# Enjoy your new App!!

ui <- # Define UI for application that plots random distributions 
  fluidPage(
    
    # Application title
    titlePanel("Hello Shiny!"),
    
    # Sidebar with a slider input for number of observations
    sidebarLayout(
      sidebarPanel(
        sliderInput("obs", 
                    "Number of observations:", 
                    min = 1, 
                    max = 1000, 
                    value = 500),
        actionButton(
          "add",
          "Add new entry")
      ),
      
      # Show a plot of the generated distribution
      mainPanel(
        "Check your googlesheet!!"
      )
    )
  )

server <- function(input, output, session) {
  # Expression that generates a plot of the distribution. The expression
  # is wrapped in a call to renderPlot to indicate that:
  #
  #  1) It is "reactive" and therefore should be automatically 
  #     re-executed when inputs change
  #  2) Its output type is a plot 
  #
  observeEvent(input$add, {
    # You should have or create a googlesheets through google drive with
    # the name "example_shiny"
    wb <- drive_get("example_shiny")
    dt <- read_sheet(wb)
    new_entry <-
      data.frame(ID = tail(dt$ID, 1) + 1, NAME = "new",
                 OBSERVATION = input$obs)
    sheets_append(new_entry, wb)
  })
}

shinyApp(ui, server)

EDIT---------------------------------------------------------------------

Here is a summary of what I've learned from the gargle auth process to access google APIs from R: googledrive, googlesheets and gmailr. There are many ways to authorize the access:

• Access directly to the required functions and let the corresponding package (i.e. googledrive) run the dance auth process. Enter your user and password and authorize the access. This is the way to go if you do not want to execute a given script recurrently.
• Provide your own OAuth2 app: you can provide your own dance-auth app process. This will help you to avoid having API calls from the standard package (i.e. googledrive package) OAuth2 app. You will store your OAuth2 app details (client ID and secret) into a JSON file. Nevertheless, you will still have to enter your information and grant access. Afterwards, you can store the tokens within your project, i.e. within a ".secrets" folder, so the app can access automatically future calls.
• Use a google service account: this allows you to have a JSON key, so you can interact server to server directly with Google. You will have to grant the necessary permissions to the email service account to do various tasks such as: modify googlesheets, create directories (googledrive), etc. However, you will not be able to send emails from this account as it doesn't have access to the Gmail service, as it is not a free normal user account. In order to surpass this, you can "impersonate" a given Gmail account and send mails on behalf of a given user account through the service account. Nevertheless, in order to do so you have to have a G-suite (Google workspace) account and grant domain-wide delegation.
• Workload identity federation: access google API through an external provider such as AWS or OIDC.

Answer 2

I think this bit of documentation from Rstudio shows how to do it, if I understand your question: https://support.rstudio.com/hc/en-us/articles/217952868-Generating-OAuth-tokens-from-a-server