Create and Format a Google Sheet Within R: Exercises

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 R-exercises.com 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.