If you have read previous posts on here, I tend to get asked questions like, “Would it be possible to…” Well two of my colleagues, Kim Lowden & Jess Verrault, are putting together a pretty cool way to celebrate the holidays with a ‘tech-ified’ gift exchange.
They will have teachers fill out a Google Form where they submit a digital resource of some type & request one in return. Then they will go into the Sheet results and try to match up, as best as possible, each staff member with someone who submitted a resource they are looking to receive. Utilizing a couple of formulas & add-ons (I’ll describe them in more detail) a Google Doc will be created which will be delivered to the staff member’s inbox. In the Doc will be the link to the resource, a description of how it is used (with students, if possible) and a means to send a pre-formatted “Thank You Card” email back to the giver. Below I will embed all of the Google-y things (Form, Sheet, & created Doc from the Template with <<tags>>)
Formulas & Add-ons, Oh My…
I needed a means to create a pre-formatted link to an email address. I had to insert a few columns into the Sheet:
- Column B: =IF(A1=“”,“”,“mailto:”)
- Column D: =IF(A2=“”,“”,“?subject=Thank%20You&body=Dear%20Secret%20Snowman,”)
- Column E: =CONCATENATE(B2:D2)
This will create all the text needed to take the ‘response recorded’ email address in Column C and map it out to create a hyperlink ‘mailto’ trigger with a specific subject and body.
Next, they wanted it to be as personalized as possible using the correct pronoun in the Google Doc. I added a column to the Sheet which uses Data Validation to create a dropdown list for “he” or “she”. Kim or Jess will have to select the pronoun when they are matching up colleagues so the correct pronoun will appear in place of the <<tag>> in the template…
Finally, another column was added where Kim or Jess could input the email address for the “gift” recipient.
Now as for some Add-ons used:
- copyDown – will insert each formula when a Form is submitted
- autoCrat – will insert data from the Sheet into <<tag>>ed text within a Google Doc template