Import Google Spreadsheets data into R

googleSheets.r

#Load dependencies
library(XML)
library(httr)

# Sheet needs to be published, replace URL below & update tab number on line 19
# (if more than 1, L-R) where 'sheet = 1'
# Be sure to unfreeze any cells to prevent empty rows on import

url <- "https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/pubhtml"

dfClean <- function(df){
  nms <- t(df[1,])
  names(df) <- nms
  df <- df[-1,-1]
  row.names(df) <- seq(1,nrow(df))
  df
}

readGoogleSpreadsheet <- function(url, sheet = 1){
  library(httr)
  r <- GET(as.character(url))
  html <- content(r)
  sheets <- readHTMLTable(html, header=FALSE, stringsAsFactors=FALSE)
  df <- sheets[[sheet]]
  
  dfClean(df)
}
df <- readGoogleSpreadsheet(url)

#ht @jpmarindiaz, @nickreich, & @devlintufts