In the 15th episode of Season 2, I am joined by Andreas Johansson to talk about things that we can do to support less tech-savvy staff members, especially non-teaching staff. Tools discussed include Google Forms, Sheets & Sites; the FormMule, AutoCrat & FormRanger Add-Ons; the VLookUp, Concatenate & Substitute Google Formulas; Lean Thinking and more!
Years ago, as a STEM teacher, I had my students build basswood bridges. We’d then test them by hanging weights from them. I’d submit the results to a Google Form, which would kick it to a Google Spreadsheet, where a formula was all set up to calculate the “Engineering Efficiency” (a measure that leveled the playing field between heavy, strong bridges and light, strong bridges). Unfortunately, formulas don’t automatically apply to the new rows created by new Form Submissions. I had to have a student manually drag the formula down each time a new result was submitted.
Enter the CopyDown Add-On
I later discovered this wonderful little add-on. It automatically pulls that formula down to a each new form submission. No manual dragging necessary. This is super, super useful when your Form & Sheet are part of a bigger system that triggers other actions in other add-ons (i.e., autoCrat, formMule) that require those formulas.
Here’s a GIF of how it works, followed by a step-by-step guide to using it:
Set up your Google Form.
Open up the connected Spreadsheet.
Start with an initial form submission. You’ll need this in the next step.
Create your formulas in Row 2 (the row with your first submission).
Click Add-Ons and follow the steps to add CopyDown.
Click Add-Ons > CopyDown > CopyDown Settings.
Flip the switch to “On.”
Generally, I select to paste the results “as values” (otherwise it puts the formula itself into each cell which, if it’s a lengthy spreadsheet, will ultimately slow it down).
I love me some Add-Ons. One of my favorites is FormRanger from New Visions Cloud Lab. It can be used to pull in a column of information from a Google Sheet as multiple choice or dropdown options.
This is nice for quickly creating a lot of options for a multiple choice or dropdown question, but what takes it from nice to awesome is . . . you can set it to automatically update based on changes made to the spreadsheet. Whaaaaat!? I know, right?