Accessing Google Drive with R

I recently purchased a Mac as my personal machine. While working on my “Intermediate” Excel posts (R, python ), I found myself needing to create and manipulate some sample data. Without Microsoft Excel and not wanting to shell out even more money for software, I turned to the next best of option: Google Sheets. Let’s face it, no one uses Numbers if they don’t have to.

Google Sheets is great because it’s free and accessible on just about every device. Sometimes you have a quick thought and it’s awesome to be able write it down quickly with your phone. The downside is it can be a bit annoying to edit a file, export the file, and then load the file into something like R. Luckily, the googledrive package completely eliminates this hassle by offering several functions to do just that. Let’s take a look a closer look at these functions in particular.

Start by installing and loading the package.

install.packages("googledrive")

Find Files

First, we’ll take a look at drive_find(). This will give us a list of the files that are in your google drive. The first time you run a function from this package, you’ll need to authorize the package to access your drive. Enter 1 to store the credentials locally and you should be prompted to allow access in the browser. In my experience, after you’ve done this the first time, the required token will automatically be refreshed the next time you try to access your drive.

Once authorization is complete, you can call drive_find().

drive_find(n=5)

# A tibble: 5 x 3
  name                             id                                           drive_resource
*
1 Sample Data V2                   <redacted id>
2 To Do List                       <redacted id>
3 Finances                         <redacted id>
4 Google Drive with R              <redacted id>
5 “Intermediate” Excel with Python <redacted id>

Download File

Once we’ve found the file we’re looking for, we can download it using drive_download(). Note here that path is where we’re saving the file locally. This is important so that we can read the file into R once it’s been downloaded.

drive_download(
+   "Excel Python or R Data",
+   path = "/users/nickbautista/documents/data/Excel Python or R Data.csv",
+   overwrite = TRUE)
 
File downloaded:
  * Excel Python or R Data
Saved locally as:
  * /users/nickbautista/documents/data/Excel Python or R Data.csv

If I navigate to this path in the finder, I’ll find Excel Python or R Data.csv

Return Files

Now that we have our file, let’s do a quick calculation to show that we can edit the file before returning.

data <- read_csv("/users/nickbautista/documents/data/Excel Python or R Data.csv")
 
data$CPD <- data$Spend / data$Downloads

With that done, first, we’ll save the file locally and then return the file using drive_upload().

write_csv(data, "/users/nickbautista/documents/data/Excel Python or R Data v2.csv")
 
drive_upload("/users/nickbautista/documents/data/Excel Python or R Data v2.csv",
             path = "Blog/Excel Python or R Data v2.csv",
             type = "spreadsheet")
 
Local file:
  * /users/nickbautista/documents/data/Excel Python or R Data v2.csv
uploaded into Drive file:
  * Excel Python or R Data v2.csv: <redacted id>
with MIME type:
  * application/vnd.google-apps.spreadsheet

If we happen to be organized and use folders within our drive, we can specify the exact path. For this to placed in the right folder, the exact folder name needs to be used and is case sensitive. For spreadsheets, we also need to specify the file type otherwise the data will be dropped into Google Drive as a word document. If we navigate over to our drive in a browser we should see the file we just edited.

Wrap Up

Aside from helping me avoid paying for software, this works really well for lookup tables as many people can access and edit the files from anywhere. I can then download the file for my use and then return with any changes I’ve made. These are only just a few of the functions available in this package. Jenny Bryan has a much more detailed walk through of the package that can be found here.

Code Snippet

install.packages("googledrive")
 
library(tidyverse)
library(googledrive)
 
# See Files in Google Drive
drive_find(n=5)
 
# Download Files
drive_download(
  "Excel Python or R Data",
  path = "/users/nickbautista/documents/data/Excel Python or R Data.csv",
  overwrite = TRUE)
 
# Read File into R
data <- read_csv("/users/nickbautista/documents/data/Excel Python or R Data.csv")
 
data$CPD <- data$Spend / data$Downloads
 
# Return File to Google Drive
write_csv(data, "/users/nickbautista/documents/data/Excel Python or R Data v2.csv")
 
drive_upload("/users/nickbautista/documents/data/Excel Python or R Data v2.csv",
             path = "Blog/Excel Python or R Data v2.csv",
             type = "spreadsheet")