testsheets.Rmdtestsheets is a package for creating testthat files from spreadsheets. It supports Google Sheets and Excel documents and relies heavily on the tidyverse packages googlesheets4 and readxl.
This package is can be installed from GitHub with:
# install.packages("devtools") devtools::install_github("nicole-brewer/testsheets")
Spreadsheet terminolgies among major tech giants are confusing and naming conflicts is worth extricating. In this package, we use three terms that conform to naming convensions in the googlesheets4 package. - sheet: a singular, two dimensional set of tabular data, also called a “sheet” in Google Sheets, and “spreadsheet” and “worksheet” in Excel - ss: a document that contains one or more sheets, called a “spreadsheet” in GoogleSheets, and “workbook” in Excel - testsheet: a sheet of any mimetype that follows formatting rules that allow for conversion a testfile - testfile: a script created from a testsheet that is compatable with the testthat package
Let’s say we want to test a function we have written. In this example we will use a Google Sheets document called “example” that has two sheets named after the functions they were designed to test: “sum” and “mean”.
# Retrieve the google sheet library(googledrive) #> Warning: package 'googledrive' was built under R version 3.6.2 library(testsheets) # indicate there is no need for an access token since we are accessing # a read-only sheet via a public link googledrive::drive_deauth() # get sum_ss <- googledrive::drive_get("https://docs.google.com/spreadsheets/d/1vR1aUlKMGtZIJOZaHNk7kePgeYYrkhoy0PFmZxs8wt4/edit?usp=sharing") # Load the "sum" sheet into a dribble create_testfile(sum_ss, sheet = "sum") #> Using an auto-discovered, cached token. #> To suppress this message, modify your code or options to clearly consent to the use of a cached token. #> See gargle's "Non-interactive auth" vignette for more details: #> https://gargle.r-lib.org/articles/non-interactive-auth.html #> The googlesheets4 package is using a cached token for brewer36@purdue.edu. #> Reading from "testsheets_example" #> Range "'sum'"
Internally, create_testfile obtains a tibble containing testsheet cell data. This tibble is cleaned in certain ways: for example, rows that have “false” in the include column will be removed and the ‘param_’ prefixes indicating input parameters will dropped.
Perhaps you have a lot of injected code in your spreadsheet that needs cleaning or repair to create an error_free testfile. create_testfile is not an ideal funciton in this scenario, because you skip over the intermediary tibble that we will call testdata.
testsheets provides a workaround by providing two functions that separate testfile creation into two steps: read_testsheet and write_testfile.
library(testsheets) # Obtain the path of an example testsheet path <- testsheets_example("excel") # create testdata from the sheet called "sum" testdata <- read_testsheet(path, sheet = "sum") # modify the testdata tibble in some desired way change_testdata <- function(testdata) { message("Testdata has been modified in some way!") testdata <- testdata } testdata <- testdata %>% change_testdata() #> Testdata has been modified in some way! # write the testdata to a testfile write_testfile(testdata, "sum") #> A file /Users/nicolebrewer/Repos/nicole-brewer-github/testsheets/vignettes/testsheet_sum.R already exists. Use the option `overwrite = TRUE` to overwrite it.
Now that we have written and understood whats going on behind the scenes in an advanced workflow, we have the option of cleaning it up by reverting back to the create_testfile() function used in the basic workflow, but with one extra parameter. In R, functions are objects (of class type “function”) just like anything else, so we can pass our custom change_testdata function as a manipulator parameter.
library(testsheets) # Obtain the path of an example testsheet path <- testsheets_example("excel") # define our "manipulator" function change_testdata <- function(testdata) { message("Testdata has been modified in some way!") testdata <- testdata } create_testfile(path, "sum", manipulator=change_testdata) #> Testdata has been modified in some way! #> A file /Users/nicolebrewer/Repos/nicole-brewer-github/testsheets/vignettes/testsheet_sum.R already exists. Use the option `overwrite = TRUE` to overwrite it.