# Functions for loading sheets and setting column types
#' Load Sheet Privately
#' @description Loads a private sheet from google sheets using your secret token.
#'
#' @param worksheet.title : A character string representing the title of the workbook.
#' @param sheet.index : An integer representing which worksheet to load.
#' @param format.changed : A boolean representing whether the format of the workbook
#' has been changed from the default format.
#'
#' @return Returns a data frame representing the workbook.
#' @export
#'
load_sheet <- function(worksheet.title, sheet.index, format.changed=F) {
message(paste("Working Dir:", getwd()))
if (file.exists(".secrets/googlesheets_token.rds")) {
message("Token Found.")
googlesheets::gs_auth(token=".secrets/googlesheets_token.rds", cache=F)
} else {
message("Token not found.")
message(dir(".secrets"))
# first time -- need to go through the oauth workflow
googlesheets::gs_auth(cache=F)
token <- googlesheets::gd_token()
dir.create("./.secrets")
saveRDS(token, file = "./.secrets/googlesheets_token.rds")
}
worksheet <- googlesheets::gs_title(worksheet.title)
df <- googlesheets::gs_read(worksheet, ws = sheet.index, col_names=T)
if(!format.changed)
df <- set_columntypes(df)
return(df)
}
#' Transform Columns
#' @description This function is called automatically by load_sheet() if format.changed
#' is false.
#'
#' This function transforms the column types of the google sheet into
#' appropriate data types. The location data is merged between the free-text "Other"
#' field and the categorical choices the user can make. The other choices are transformed
#' into factors, with the Length category transformed into an ordinal factor, increasing
#' with the time spent at lunch. The timestamp is parsed into a POSIX-style datetime
#' class, so that intuitive comparisons may be performed.
#'
#' NOTE: If your google sheet departs from the format of the original, this function
#' may introduce unexpected results. This function may be used as a model for any custom
#' column transformation. I am not liable for any headache, heartache, or frustration
#' induced by using this function on a custom sheet. You have been warned!
#'
#' @param tibble - the input data frame or tibble from the googlesheet
#'
#' @return tibble - transformed and renamed columns of the appropriate type
#' @export
#'
#' @importFrom magrittr %>%
#' @importFrom dplyr mutate
set_columntypes <- function(tibble) {
# change column names
df_names <- c(
"Timestamp",
"Lunch_Eaten",
"Time_of_Lunch",
"Length",
"Location_Choice",
"Location_Other_Answer"
)
colnames(tibble) <- df_names
# now change types
tibble <- tibble %>% mutate(
Timestamp = as.POSIXct(Timestamp, format="%m/%d/%Y %H:%M:%S"),
Lunch_Eaten = factor(Lunch_Eaten),
Time_of_Lunch = factor(Time_of_Lunch),
Full_Location =
as.factor(
ifelse(Location_Choice != "Other",
Location_Choice,
Location_Other_Answer)
),
Location_Choice = factor(Location_Choice),
Length = factor(Length, ordered=T,
levels=c("0-15 mins","15-30 mins","30-45 mins",
"45-60 mins","More than 1 hr")
)
)
return(tibble)
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.