Create and Format a Google Sheet Within R: Exercises

[This article was first published on R-exercises, 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.

In this exercise set, we will practice using the Google Sheets package to create and manipulate a Google spreadsheet within R.

After completing this exercise set, you will be able to prepare a basic Google Sheets document using just R, leaving behind a reproducible R-script. Note that using Google Sheets is free of cost, but requires you have access to or create a Google account.

Solutions are available here.

Exercise 1

Install and load the Google Sheets package, along with its recommended pairing, dplyr. If you haven’t already, take a look at usage examples.

Exercise 2

Authenticate your Google account and authorize Google Sheets to access your Google Drive.

Exercise 3

Make a new empty sheet/spreadsheet called “iris_r”, containing a single worksheet called “test1.”

Exercise 4

Write the built-in Iris data.frame to the “test1” sheet using gs_edit_cells().

Exercise 5

Again, write the built-in Iris data.frame to a new worksheet “test2” directly with the function gs_ws_new().

Exercise 6

Now, write mtcars’ first 5 rows to a new sheet called “test3”, this time by using gs_add_row(). Note that it seems you first have to add a header and one row to the sheet, so you need to first use gs_edit_cells() to do that.

Exercise 7

Create a new sheet called “My favorite R-ex” and add a hyperlink to any exercise set on with the text “this.”

Exercise 8

Now, create a new sheet “test1b.” Get the first four columns names from “test1” by linking to them. Then calculate their averages with Google Sheet formulas.

Exercise 9

Use a Google Sheet function to extract the URL of your new document. This way, you can access it quickly in the future (or coworkers, once you have shared access.)

Exercise 10

Turns out one of your coworkers doesn’t want to create a Google account! Download the file as an Microsoft Excel document using R and the Google Sheet package.

To leave a comment for the author, please follow the link and comment on their blog: R-exercises. 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)