3MW (Using Google Sheets in R)

Author

Albert Rapp
May 17, 2023

Guten Tag!

Many greetings from Ulm, Germany.

Before we jump into this week’s issue, here’s a final reminder for you: I’m giving a talk for the R User Group at the Harvard Data Science Initiative tomorrow, i.e. May 18. If you want to join us, feel free to sign up here.

Also, it seems that I forgot to link to the GitHub code last week. Let me fix that right now. You can find last week’s code on GitHub (and since I’m using my personal URLs in today’s screenshots, today’s code won’t be available on GH.)

Now, coming to this week’s content: As you may have noticed, I’ve become more active on YouTube in the last couple of weeks. Currently, I’m releasing one video per week. For example, last Saturday I released a video on how to clean Excel files with R.

Of course, all of this video making is still new for me so I’m trying to analyze my YT analytics data in order to find out what works and what doesn’t. The problem with that is that I have only two bad options for exporting my analytics data to R:

  1. Manually download multiple .csv-files and move them somewhere where R can find them.

  2. Export the data to Google sheets and get the data to R from there.

As it turns out, the second option isn’t too bad after all. That’s because, unknown to me before this week, the Tidyverse has a package called {googlesheets4}. And you guessed it, it works wonderfully with Google sheets. Let me show you how.

Authenticate with Google Sheets

Honestly, I thought setting up {googlesheets4} would be painful. After all, I had to somehow save credentials so that I can log in to my Google sheets account from R. But it turns out, I was completely wrong. {googlesheets4} does a wonderful job navigating the authentication. Here’s the workflow

  1. Find the URL of your Google sheets file.

  2. Pass it to read_sheet().

  3. Follow the instructions.

That’s it. R will either ask you whether you want to use a Google account from a previous session or open a browser window so that you can log into Google sheets. Then, there will be a prompt so that you can give all necessary permissons to R.

Don’t worry if you get an error message because you forgot to allow all necessary permissions. You can just restart the process with gs4_auth(). In any case, once all the authenticating is done, you will get access to your data in a familiar tibble format.

Access different sheets in your file

Often, a spreadsheet contains more than just one page. You can access them by setting the range argument of read_sheet().

Use IDs instead of URLs

You don’t have to extract a URL from your Google sheets account. You can also use its unique ID. Of course, that’s just as tedious when you have to repeatedly find out a Sheet’s ID. Instead, you can use the {googledrive} package to look for spreadsheets with drive_find().

This will give you a list of files and their IDs. Then, you can extract the ID of the file that you’re looking for and pass it to read_sheet().

Alright, that’s it for today. There’s lots more you can do with {googlesheets4} like creating new spreadsheets and modifying formulas. But these are not really of interest to me since I only want to get the data into R and not the other way around. But if that’s your thing, check out the excellent docs online.

Hope you’ve enjoyed this week’s newsletter. If you want to reach out to me, just reply to this mail or find me on Twitter.

See you next week!
Albert 👋

If you like my content, you may also enjoy these:

Reply

or to participate.