I recently purchased a Mac as my personal machine. While working on my “Intermediate” Excel posts, 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.
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(n=5) # A tibble: 5 x 3 name id drive_resource * 1 Sample Data V2 1BjuvUiphfAxvatjQtj-g_2COo-97JmUCHYOH6Jhl_NE 2 To Do List 1Pe5vK8UkV6XIrmKVxU9KVpVjGcWFaK5jfwhD4kKLrAY 3 Finances 1tLYA6lB6reuyV0nYiU8-VZVQu0zhlA6SExh051ChEok 4 Google Drive with R 1XwkaA68yyiR7dTMfKtxxWW9kcBSj-JyzI80UjeV9Q5Q 5 “Intermediate” Excel with Python 1IHh_I4Z_AwIuzQ8THWyjxr5qZiCIRCg3ZUJ4it2mTzw
Note I’ve added
n=5 to limit the number of files returned. You can remove this argument to get the full list of the files in your drive.
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
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
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: 127BlguSF6eKvwHiAfLVbcVfn_5GtEoUQVloa3kl318E 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.
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.
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")