Send information to Google Spreadsheets/Google Form

Question

I want to use Google google spreadsheet or form + spreadsheet to collect a response from an R user and sent it to a speadsheet.

Here is a minimal Google form: https://docs.google.com/forms/d/1tz2RPftOLRCQrGSvgJTRELrd9sdIrSZ_kxfoFdHiqD4/viewform

And the accompanying spreadsheet: https://docs.google.com/spreadsheets/d/1xemHzTdeqgMuGbsP6oqDYuMu4PymBdw7k7kMC_-Dsi8/edit#gid=102827002

I thought I'd use Jenny Bryan's googlesheets package like so:

library(googlesheets);library(dplyr)
(my_sheets <- gs_ls())

minresp <- gs_title("minimal (Responses)")
minresp %>% gs_add_row(ws = "dat", input = mtcars[20, 1:2]) 

Works great but if I include the code and someone else (i.e., who is not me) tries to use the code:

Error in gs_lookup(., "sheet_title", verbose) : 
  "minimal (Responsess)" doesn't match sheet_title of any sheet returned by gs_ls() (which should reflect user's Google Sheets home screen).

So in the vignette I see there's apart:

# Need to access a sheet you do not own?
# Access it by key if you know it!

I figured this was the ticket to allow others to input data into a spreadsheet so I tried:

minresp2 <- gs_key("1xemHzTdeqgMuGbsP6oqDYuMu4PymBdw7k7kMC_-Dsi8")

yielding:

Authentication will be used.
Error in gs_lookup(., "sheet_key", verbose) : 
  "1xemHzTdeqgMuGbsP6oqDYuMu4PymBdw7k7kMC_-Dsi8" doesn't match sheet_key of any sheet returned by gs_ls() (which should reflect user's Google Sheets home screen).

This all works if I'm logged into and authenticated via google, but how could I make it so users could add to my spreadsheet. I don't care what approach/packages one uses. It's be nice if the user could only edit the spreasheet via the use in the function and not simply look at the source and get the url and edit manually but that's less of a concern.

Essentially I want to be able to collect responses from an R user in a spreadsheeet (or other data form) and append with each new user.

Answer 1

Your users should register the Sheet with gs_key(YOUR_KEY, lookup = FALSE, visibility = "private"). These two arguments are important for two different reasons.

• lookup = FALSE allows them to register the Sheet even if they have never visited in the browser. Note it's still important that the Sheet is "published to the web" and they have read permission.
• visibility = "private" affects the URL we build for the eventual POST required to add a row. To POST, visibility must be "private", otherwise you get the 405 error reported in https://github.com/jennybc/googlesheets/issues/168.

I still needed to make lookup information part of the registered Google Sheet object for the example below to work for non-owners of the Sheet. Which means you'll need to install the development version from GitHub.

To achieve your goal, your user must also have write permission. How to achieve that? Easiest but yucky is to make the Sheet world writable, as done below. For a small number of people, you could grant permission individually. I know of no way to make a Sheet writable by, say, people who happen to have the key. If you really want to "bake in" this sort of permission into a function or package, I suspect you'll need to find a secure way to transmit a token, as described in my original comment.

ss <- gs_new("add-row-test", input = head(iris))
#> Sheet "add-row-test" created in Google Drive.
#> Range affected by the update: "A1:E7"
#> Worksheet "Sheet1" successfully updated with 35 new value(s).
#> Worksheet dimensions: 1000 x 26.

In the browser, do two things:
File > Publish to the web
Share button > Public on the web - Anyone on the Internet can find and edit

Now your users can access and add rows like so:

ss_key <- "114cXPTe9whThS3lmpa3neY2vplpUX1hcnM8o8Oo6QtM"
add_row_result <- ss_key %>%
  gs_key(lookup = FALSE, visibility = "private") %>%
  gs_add_row(input = c("can", "you", "hear", "me", "now?"))
#> Authorization will not be used.
#> Worksheets feed constructed with private visibility
#> Row successfully appended.
add_row_result %>%
  gs_read()
#> Accessing worksheet titled "Sheet1"
#> Source: local data frame [9 x 5]
#>
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          (chr)       (chr)        (chr)       (chr)   (chr)
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9           3          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5            5         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa
#> 7          can         you         hear          me    now?

Answer 2

It potentially could be the setting you've used in your Google Sheets set up. @jennybryan mentions something like this in her issue #126,148 below:

https://github.com/jennybc/googlesheets/issues/148

OK yes I get same error with the package and yet can view it in the browser. It's is also not an old sheet. Can you double check it is published to the web? Merely being public on the web is not sufficient for API access. I am going to make a new function to test for this! Please see this comment for the distinction:

126 (comment)

Answer 3

I've used a ton of your functions and packages, so I just want to say thanks. :)

Anyway, I've run into a similar issue that you have in the past and I worked my way around it by using this function. I basically just hacked the Auth function that comes from RGoogleAnalytics package.

# client id and secret for google drive ####
client.secret <- "YOUR SECRET"
client.id <- "YOUR ID"

# auth function to get at files in google drive ####
# and implementing it 
Auth <- function(client.id,client.secret) {

  require(httr)

  myapp <- oauth_app("google", client.id,
                     client.secret)
  google.token <- oauth2.0_token(oauth_endpoints("google"), myapp,
                                 scope = "https://www.googleapis.com/auth/drive.file")
  return(google.token)
}


token <- Auth(client.id,
              client.secret)

This has worked for me in the past, but let me know if it doesn't work.