How to Use googlesheets to Connect R to Google Sheets

[This article was first published on R Programming – DataScience+, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Often I use R to handle large datasets, analyze the data and filter out the data I don’t need.

When all this is done, I usually use write.csv() to print my data off and reopen it in Google Sheets.
My workflow would look something like this:
in R).

for_gs <- gs_title("for googlesheets")

You can also locate the sheet by the key (the letters, numbers and characters after the /d/ in the URL) for the same result

for_gs <- gs_key("your_key_here")

This gives us a list, which we can turn into a data frame using the gs_read() command.

for_gs_sheet <- gs_read(for_gs)
Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 50 obs. of 2 variables:
 $ Number.x: num 0.4696 0.1587 0.0949 0.1823 0.0885 ...
 $ Number.y: num 0.67551 0.7041 0.00167 0.51302 0.20114 ...
 - attr(*, "spec")=List of 2
 ..$ cols :List of 2
 .. ..$ Number.x: list()
 .. .. ..- attr(*, "class")= chr "collector_double" "collector"
 .. ..$ Number.y: list()
 .. .. ..- attr(*, "class")= chr "collector_double" "collector"
 ..$ default: list()
 .. ..- attr(*, "class")= chr "collector_guess" "collector"
 ..- attr(*, "class")= chr "col_spec"

Step 5: Modify the spreadsheet

Next up, we modify our spreadsheet using the gs_edit_cells() function.
This function has several arguments that we need to employ to edit our spreadsheet properly.
gs_edit_cells(for_gs, ws = "Sheet1", anchor = "A2", input = c(1,2), byrow = TRUE)
The ws argument refers to the sheet name in the spreadsheet. The anchor
argument refers to the cell from which the modification will begin. In my example, I am editing two cells, where the first one will be the anchor cell A2. The byrow argument indicates that the modification will apply horizontally (change to FALSE for vertical editing).

Note that this won’t change our data frame for_gs_sheet that is based on this spreadsheet; just the spreadsheet itself.

Cell A2 now has a value of 1. A3 is 2.

Step 6: Create a Google Sheets file using R

We can create new spreadsheets using this package using gs_new().

We’ll use the mtcars dataset as a test:

gs_new(title = "mtcars", ws_title = "first_sheet", input = mtcars)

It worked, except it didn’t include the rownames, which contains the cars.

That doesn’t matter, we can add them using gs_edit_cells(), changing the byrow argument to FALSE this time.

#register the new mtcars sheet in R
gs_new(title = "mtcars", ws_title = "first_sheet", input = mtcars)

#insert the rownames vertically in column L
gs_edit_cells(mtcars_sheet, ws = "first_sheet", anchor = "L2", input = rownames(mtcars), byrow = FALSE)

Final thoughts

That was a quick overview of the most basic functions of the google sheets package.

This is a really useful package. A lot of my work involves reading data in Google Sheets either before or after using R.

Googlesheets means I won’t have to bother with read.csv() or write.csv() as much in the future, saving me time.

So thanks to Jenny Bryan for creating it!

    Related Post

    1. DataFrames Vs RDDs in Spark – Part 1
    2. Parallel Operations
    3. R Markdown: How to number and reference tables
    4. RDBL – manipulate data in-database with R code only
    5. R Markdown: How to format tables and figures in .docx files

    To leave a comment for the author, please follow the link and comment on their blog: R Programming – DataScience+. offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
    Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

    Never miss an update!
    Subscribe to R-bloggers to receive
    e-mails with the latest R posts.
    (You will not see this message again.)

    Click here to close (This popup will not appear again)