2

Normally when I use googlesheets package on my desktop R studio, it puts me through a browser based authentication, where I select my Google account for authentication and it gets approved.I use the gs_ls() function for authentication.

Now I am trying to use the googlesheets package in R in my Amazon EC2 instance, which does not have a browser available. How do I go through the authentication process in this environment?

Arpit Solanki
  • 113
  • 1
  • 6

3 Answers3

3

This is an old questions, but I just ran into the same problem using python on ec2 with google sheets and found this question while searching. Here is how I solved it.

Upon trying to connect to google sheets from python, this message popped up:

Please visit this URL to authorize this application: 
https://accounts.google.com/o/oauth2/auth?#######

I put the url into my browser on my local machine and selected my Google account for authentication and it got approved. However, the ec2 instance was still stuck.

The url in the browser after authentication changed to a localhost link. I opened up a second connection to the ec2 instance, and using the python requests library, I did

import requests
requests.get(localhost_url_from_browser)

The python script then continued successfully. I'm sure R studio has a library similar to python requests you can use for this.

Note that this does not require the webpage to be publicly accessible.

aeglaeca
  • 31
  • 4
1

You may refer with this thread. It stated that the simplest way to create a gs_auth token from a server is to set the httr_oob_default option to true, which will tell httr to use the out of band method for authenticating. You will be given a URL and expected to return an authorization code.

You may also check this Google Sheets R API tutorial on how to access and manage Google spreadsheets from R with googlesheets.

abielita
  • 13,147
  • 2
  • 17
  • 59
1

I found the solution to this question.

What i basically did was published the Google sheet as a web page and set the access rights to public. If we do this we can access the google sheet without any browser based authentication. I used the following code-

sheet_key='xxxxxxxxxxxxxxxxxxxx'
sheetdata <- sheet_key %>%  gs_key(lookup = FALSE) %>% gs_read()

Sheet key is basically the portion of url between /d and /edit

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxx/edit#gid=0

Arpit Solanki
  • 113
  • 1
  • 6