1

I'm using the googlesheets package (CRAN version, but available here: https://github.com/jennybc/googlesheets) to read data from a Google Sheet in R, but would now like to add rows. Unfortunately, every time use gs_add_row for an existing sheet I get the following error:

Error in gsheets_POST(lf_post_link, XML::toString.XMLNode(new_row)) : 
  client error: (405) Method Not Allowed

I followed the tutorial on Github to create a sheet and add rows as follows:

library(googlesheets)
library(dplyr)
df.colnames <- c("Project Short Name","Project Start Date","Proj Stuff")
my.df <- data.frame(a = "cannot be empty", b = "cannot be empty", c = "cannot be empty")
colnames(my.df) <- df.colnames
## Create a new workbook populated by this data.frame:
mynewSheet <- gs_new("mynewsheet", input = my.df, trim = TRUE)
## Append Element
mynewSheet <- mynewSheet %>% gs_add_row(input = c("a","b","c"))
mynewKey <- mynewSheet$sheet_key

Rows are added successfully, I even get the cheery message Row successfully appended.

I now provide mynewKey to gs_key, as I would if this were a new sheet I were working with and attempt to add a new row using gs_add_row (Note: before evaluating these lines, I navigate to the Google Sheet and make it public to the web):

myExistingWorkbook <- gs_key(mynewKey, visibility = "public")
## Attempt to gs_add_row
myExistingWorkbook <- myExistingWorkbook %>% gs_add_row(input = c("a","b","c"), ws="Sheet1", verbose = TRUE)

Error in gsheets_POST(lf_post_link, XML::toString.XMLNode(new_row)) : 
  client error: (405) Method Not Allowed

Things that I have tried:

1) Published the Google Sheet to the web (as per https://github.com/jennybc/googlesheets/issues/126#issuecomment-118751652)

2) Enabled the sheet as editable to the public

Notes

In my actual example, I have an existing Google Sheet with many worksheets within it that I would like to add rows to. I have tried to use a minimal example here to understand my error, I can also provide a link to the specific worksheet that I would like to update as well.

I have raised an issue on the package's github page here, https://github.com/jennybc/googlesheets/issues/168

Rentrop
  • 20,979
  • 10
  • 72
  • 100
Charlie Joey Hadley
  • 788
  • 8
  • 22

1 Answers1

5

googlesheets::gs_add_row() and googlesheets::gs_edit_cells() make POST requests to the Sheets API. This requires that the visibility be set to "private".

Above, when you register the Sheet by key, please do so like this:

gs_key(mynewKey, visibility = "private")

If you want this to work even for Sheets you've never visited in the browser, then add lookup = FALSE as well:

gs_key(mynewKey, lookup = FALSE, visibility = "private")

jennybryan
  • 2,606
  • 2
  • 18
  • 33