Re-constructing Google Forms responses with Quarto and {glue}

quarto

Published

August 8, 2023

Recently I had to go through about 50 applications to a short course that were submitted via Google Forms and whittle it down to about 20 applicants. Google Forms collects data into a Google Sheet document, which is nice for machine readability, but in this case made it very difficult to read long format answers to questions. I spent about an hour using Quarto and the glue package to convert that data into something that looked more like an application.

I can’t share the original form or data, so for the sake of this blog post, I made a simple example form.

Form: https://forms.gle/yJjME2yZMZPzw3p28

Responses: https://docs.google.com/spreadsheets/d/1SUy92T7I3ZoEyZjTxLP7F5pAup58c0xKl0QbvElIfxA/edit?usp=sharing

Reading in the data

googlesheets4 is the package to use to read in the data. We’ll need the sheet ID bit of the URL above to access it.

library(googlesheets4)
applicants_raw <- read_sheet("1SUy92T7I3ZoEyZjTxLP7F5pAup58c0xKl0QbvElIfxA")

applicants_raw

# A tibble: 3 × 11
  Timestamp           Name         Email Depar…¹ Caree…² How c…³ How c…⁴ How c…⁵
  <dttm>              <chr>        <chr> <chr>   <chr>   <chr>   <chr>   <chr>  
1 2023-08-08 11:33:44 Eric Scott   eric… Biology Staff   Very c… Very c… Very c…
2 2023-08-08 11:35:39 Perrald Mas… perr… Art Hi… Grad S… Not co… Somewh… Not co…
3 2023-08-08 11:37:27 BMO          BMO@… Physics Underg… Not co… Not co… Not co…
# … with 3 more variables:
#   `How comfortable are you with the following? [Using git commands]` <chr>,
#   `How comfortable are you with the following? [Using Quarto or RMarkdown]` <chr>,
#   `Why do you want to take this course?` <chr>, and abbreviated variable
#   names ¹Department, ²`Career Stage`,
#   ³`How comfortable are you with the following? [Using shell commands]`,
#   ⁴`How comfortable are you with the following? [Writing for loops in R]`, …

You’ll notice that the column headings are long and unruly, but also have important information. I’ll save the originals and then clean them up in the dataframe with janitor::clean_names(). I’ll remove the repetitive question “How comfortable are you with the following?” from the matrix question answer columns with some regex and stringr.

library(janitor)


Attaching package: 'janitor'

The following objects are masked from 'package:stats':

    chisq.test, fisher.test

library(stringr)

questions <-
  colnames(applicants_raw) |> 
  str_replace("How comfortable are you with the following\\? \\[(.+)\\]", "\\1")

applicants <- 
  applicants_raw |> 
  clean_names()

questions

 [1] "Timestamp"                           
 [2] "Name"                                
 [3] "Email"                               
 [4] "Department"                          
 [5] "Career Stage"                        
 [6] "Using shell commands"                
 [7] "Writing for loops in R"              
 [8] "Data wrangling in R"                 
 [9] "Using git commands"                  
[10] "Using Quarto or RMarkdown"           
[11] "Why do you want to take this course?"

applicants

# A tibble: 3 × 11
  timestamp           name         email depar…¹ caree…² how_c…³ how_c…⁴ how_c…⁵
  <dttm>              <chr>        <chr> <chr>   <chr>   <chr>   <chr>   <chr>  
1 2023-08-08 11:33:44 Eric Scott   eric… Biology Staff   Very c… Very c… Very c…
2 2023-08-08 11:35:39 Perrald Mas… perr… Art Hi… Grad S… Not co… Somewh… Not co…
3 2023-08-08 11:37:27 BMO          BMO@… Physics Underg… Not co… Not co… Not co…
# … with 3 more variables:
#   how_comfortable_are_you_with_the_following_using_git_commands <chr>,
#   how_comfortable_are_you_with_the_following_using_quarto_or_r_markdown <chr>,
#   why_do_you_want_to_take_this_course <chr>, and abbreviated variable names
#   ¹department, ²career_stage,
#   ³how_comfortable_are_you_with_the_following_using_shell_commands,
#   ⁴how_comfortable_are_you_with_the_following_writing_for_loops_in_r, …

Creating the markdown

The “trick” here lies in the fact that you can use the chunk option output: asis in Quarto (and RMarkdown) to treat the output of a code chunk as markdown. So we can use the glue package to programatically create markdown, and because glue::glue() is vectorized, we only have to generate a “template” of sorts and it will apply it to every response to our form (i.e. every row of the applicants tibble). You can see below this chunk how the “applications” get formatted by this template.

```{r}
#| output: asis

library(glue)
glue("### {applicants$name}
     {applicants$career_stage} | {applicants$department} | <{applicants$email}>
     
     #### How comfortable are you with the following? 
     
     |                |                    |
     |----------------|--------------------|
     |{questions[6]}  | {applicants[[6]]}  |
     |{questions[7]}  | {applicants[[7]]}  |
     |{questions[8]}  | {applicants[[8]]}  |
     |{questions[9]}  | {applicants[[9]]}  |
     |{questions[10]} | {applicants[[10]]} |
     
     #### {questions[11]}
     
     {applicants[[11]]}
     
     
     ")
```

Eric Scott

Staff | Biology | ericrscott@arizona.edu

How comfortable are you with the following?

Using shell commands	Very comfortable
Writing for loops in R	Very comfortable
Data wrangling in R	Very comfortable
Using git commands	Very comfortable
Using Quarto or RMarkdown	Very comfortable

Why do you want to take this course?

I love learning

Perrald Mason

Grad Student | Art History | perry@notreal.org

How comfortable are you with the following?

Using shell commands	Not comfortable
Writing for loops in R	Somewhat comfortable
Data wrangling in R	Not comfortable
Using git commands	Not comfortable
Using Quarto or RMarkdown	Very comfortable

Why do you want to take this course?

While my background predominantly lies in the field of Art History, I believe that this course presents a unique and valuable opportunity for me to expand my horizons and develop essential skills that can greatly enhance my academic and professional pursuits.

BMO

Undergraduate | Physics | BMO@mo.com

How comfortable are you with the following?

Using shell commands	Not comfortable
Writing for loops in R	Not comfortable
Data wrangling in R	Not comfortable
Using git commands	Not comfortable
Using Quarto or RMarkdown	Not comfortable

Why do you want to take this course?

I want to go to school so I can learn all kinds of sweet coding tricks to impress Finn and Jake and also Football.

Putting it all together

For the cherry on top, you can enable a table of contents and annotation with hypothes.is to allow easy navigation between applicants and allow you to take notes. Just add the following to the Quarto YAML header:

toc: true
comments: 
  hypothesis: true

I put the whole example together in a repo where you can see the .Qmd source code and the rendered HTML.