What It Is
FormMule is a Google Sheets add on created by New Visions for Public Schools CloudLab. (I wrote about their AutoCrat add on before – it’s really handy!)
FormMule was designed to take input from a Google Form (that has been pushed into a Google Sheet), use each respondent’s input to fill in a template, and email the completed template to the Form respondent.
Setting up FormMule takes a little bit of figuring and doing. This is because it offers lots of options, and is a powerful tool. Once you’ve used it a few times, the ins and outs become second nature.
What It Does
FormMule turns the information in a Google Sheet into a customized email. It allows you to set up a variety of timing, triggers, and templates, allowing for a lot of customization.
Because FormMule uses the information on a spreadsheet to populate one or more templates and send emails, it’s a great tool for turning Google Form responses into instant emails. It allows up to 15 templates that will each send based on specific Form responses.
Some Examples
In my Argument and Debate class, students completed a Form to select debate topics and share their initial ideas for arguments and how they’d start research. FormMule then sent customized reminders of topic selections with additional guidance as they moved forward with projects.
For my Gifted and Talented class, I did regular one-on-one check-ins with students about projects they were working on. I used a Form to collect my notes, and FormMule sent students copies of my notes to keep everyone in the loop; this let students refer back to our previous conversations easily.
In the library, I’ve used FormMule to send overdue notices to parents when my system options weren’t working the way I wanted. I just built a spreadsheet with the information I wanted, including parent and student email addresses, and FormMule filled out my templates and sent the emails.
And NJASL recently used FormMule to help our members assemble some pre-formatted responses to our Department of Education during the public comment phase of the introduction of our new K-12 Information Literacy standards. Members completed a Form that included both some suggested comments and the option for members to add their own individual comments. When they submitted the form, each member received back an email with a pre-formatted message and directions on how to pass those preformatted comments along to the Department of Education.
Preparing to Use FormMule: Consider Your Work Flow
As mentioned, FormMule works from within a Google Sheet. So you can use it with any information that’s been put into a Sheet, or you can pair it with a Form to accept live input and auto-send emails each time the Form is submitted.
I’ve learned from trial and error experimentation that it helps to think about the big picture before getting too immersed in the details.
For NJASL’s Information Literacy Standards feedback, I started with a basic idea of what the DoE would prefer in public response emails. Having this end point, I worked backwards: With the format of the final email in mind, I put together a rough draft of the template FormMule would need. Knowing what information I would need to collect in order to fill that template helped me get a handle on the questions I’d need to include in the Form folks would fill out.
This reverse engineering was crucial. FormMule can be set up to send different emails based on the responses to a specific Form question. NJASL wanted to take advantage of this feature to more narrowly target the feedback members were providing. So I had to work out where within the process that “use THIS template, not THAT template” question would fall.
Create Your Form
With a basic idea of what information we’d need to collect to achieve our end product, I started putting together the Form that NJASL members would fill out.
As you probably know, when you create a Google Form, you can have the Responses pushed into a Google Sheet spreadsheet. The Form’s questions become the Sheet’s column headers.
This is the really important bit: FormMule works by identifying unique column headers to act as “tags” within the template. This means that every column header on the Sheet – and therefore every question on the Form – has to be unique.
This was a hurdle I had to contend with a few times as I made my way through the design process. There was a stretch of time when I thought I had everything set up perfectly but FormMule would simply not work; it took me a while to realize I had accidentally made two identically worded questions on the Form.
Create The Responses Spreadsheet
The only tricky thing about creating a Sheet to capture the Form responses is that you generally want to have the Form’s final draft figured out before you create the Sheet.
Making the Sheet for Form responses takes just a moment – click a few buttons and the Sheet will appear. And if you go back to the Form and make changes to the questions, those changes will automatically appear on the Sheet.
But here’s the trick: If you add new questions to the Form, those new questions become the last column of the Sheet. This isn’t a problem if you’re adding a question to the end of the Form, but it can be confusing if you’re adding new questions at the beginning or middle of your Form.
It’s a design quirk that, in the long run, doesn’t really matter. And just FYI: You can drag and drop the Sheet columns to re-arrange them. But if you don’t know that newly added questions on the Form will appear in the final column of the Responses Sheet, it can be confusing.
Get FormMule
FormMule is a Google Sheets add on (formerly known as an extension). This means that once you add it to a single Google Sheet, it becomes available as an add on within all of your Sheets. So you only have to add it once – then it’s available in your account forever.
From within an open Google Sheet, go to the Extensions menu and click “Add ons” then “Get add ons”. Search for FormMule. Click on it, and click “Install”.
FormMule works by looking at the data in your spreadsheet and sending emails on your behalf. Therefore, you have to give it permission to access your “stuff.”
Click “Continue” in the next two pop-up boxes.
In the third pop-up, toggle the Select All checkbox and then click “Continue.”
FormMule will then be added to your account. To access it, open a spreadsheet and click the Extensions menu at the top of the screen. FormMule will now be an option!
Starting FormMule
The first time you run FormMule, you’ll need to Launch it. Subsequently, you can use Setup to start it or to alter settings you’re already saved.
Before you can begin using FormMule, you must tell it which tab of your spreadsheet has the data you want to use. (Most of the time, this will probably be “Form Responses 1.”)
Once you’ve told FormMule where to draw information from, it will add columns to the end of your Sheet. This is where it keeps track of whether it has sent an email for a particular row within your Sheet.
Next, pick your Trigger.
Step 1: Triggers
Triggers tell FormMule when you want it to activate. I almost always use “Form trigger,” but you do have other options!
- “Form trigger”: FormMule automatically runs each time someone submits a response to the Form connected to your Sheet.
- “Time trigger”: FormMule automatically sends all responses in the spreadsheet at once at a given time – this can be on a specific date and/or at a specific time; or it can be cyclical, sending hourly, daily, weekly, or monthly.
- No trigger: FormMule will not automatically run; you’ll have to manually choose when to send merged emails.
Step 2: Templates: How Many & Send Conditions
FormMule allows up to 15 different templates, and each one can be set to send based on a different condition. I usually only have one or two templates; but for the NJASL commenting project, I set up four.
After picking the number of templates, you need to choose what the Send Condition for each will be. This is where the pre-planning of your workflow comes in.
Most of the time, I set the Send Condition to be “Timestamp = NOT NULL” – that means someone has submitted a response to my Form and I want FormMule to send an email.
For the NJASL project, there were four strands of the Information Literacy Standards – Information Need (IN), Information Identification and Evaluation (IE), Information Use (IU) and Information Creation and Distribution (CD). I used the multiple choice question “Which of the Information Literacy Standards’ Disciplinary Concepts would you like to comment on?” as my Send Condition column, and each of the responses (IN, IE, IU, CD) as the condition to send an email based on one of my four templates.
Once you’ve saved the template settings, you can move on to editing the templates.
Step 3: Creating & Editing Templates
FormMule uses “tags” to take the information from your Sheet and place it into your template. A tag is a column heading surrounded by angled brackets. So if your first question is “Preferred Email,” then in your template, you can place the tag <<Preferred Email>> in the “To” line of the email template and FormMule will replace the tag with each user’s response.
In addition to the “To” line, FormMule lets you set the CC, BCC, and Reply-to boxes of the email it will send. This is great if you want to have a copy of each email sent to your organization as well as to the respondent. You can also sed the Subject line of the the email. And all of these boxes accept tags!
A very important note: Tags have to exactly match the Form question.
Another important note: Every tag has to be unique within your Sheet. That’s why, as mentioned previously, every question on the Form has to be unique – even if it’s just changing one letter.
FormMule offers a scrollable list of tags in a right side pane next to your template editing window. You can add tags by placing your cursor in the template editor where you’d like the tag to be, then clicking a tag from the right pane. This places a label of the tag’s name followed by the tag wherever your cursor is in the template building window.
Generally, I draft my template text in a separate Doc. This lets me copy-paste column headers and add angled brackets, since I rarely want the tag labelled. You can also use HTML tags to add formatting to FormMule’s templates. For the NJASL project, I was also able to copy-paste chunks of the email body. While each template picked up different parts of the Form to include in the outgoing email, some elements were the same across the templates.
If you’re setting up multiple templates, use the drop down menu in FormMule’s template editor to move between templates. Just make sure you save your changes before you leave a template!
Step 4: “Save and Preview”
FormMule doesn’t auto-save, so make sure you hit the “Save and preview” button regularly!
This will show you what the current template looks like in action. Use the “Row” up and down buttons in the right pane to navigate to a spreadsheet row that 1) meets your merge conditions and 2) hasn’t already been sent. FormMule will then provide a preview of the email it’s going to send.
Return to editing with the “Edit” button, switch to a different template with the upper left drop-down, or use “Save and send all” for a final preview.
Another Option: “Save and send all”
This button looks final, but don’t fear it. “Save and send all” will save any changes you have made, and it will create a preview window showing any existing entries that 1) meet your merge conditions and 2) haven’t been sent yet. But it won’t ACTUALLY send unless you also click the “Send now” button at the bottom of the preview.
Until you hit the “Send now” button, you can use the “Cancel” or “Edit template(s)” buttons to change your mind.
Step 5: Test Your FormMule Settings
In addition to seeing the preview emails as discussed above, I always fill out my Form a few times and check that the emails FormMule is sending come through as I expect. I usually find a few formatting or grammar issues I want to tweak in the template text.
Once in a while, I won’t get a response after submitting my Form, which tells me I’ve messed up a setting somewhere. That’s a good thing to know before going live with a Form you’re expecting to put traffic through!
With the NJASL form, I tested out all the permutations of Form responses that would trigger an email. Three of the four worked perfectly. And that’s why you test it out.
Turn Your Form Loose!
After setting up your triggers and templates, and checking that the output matches your expectations, it’s time to gather Form input.
One thing to remember with Google Forms: if you’re using an organizational account, you may need to change the Publish settings from “anyone in the organization can respond” to “Public/anyone can respond.”
A final important note: By default, the “from” line for all the email FormMule sends automatically populates with the email address of the Google account FormMule is running from (that’s part of why it asked for permission to pretend to be you during the initial installation). You can change the “Reply-to” address in the template to “no-reply” if you don’t want folks replying back to the FormMule-generated entry.
Because the NJ DoE told NJASL that they wouldn’t accept a pile of emails all from the same email address, we decided to have FormMule send each preformatted response to the NJASL respondent member and have the member copy-paste the pre-formatted message to send to the DoE from their own email address.
Here’s what the FormMule-generated output for NJASL’s responses for the NJ DoE looked like:
Whether you’re looking for a way to provide both suggestions and flexibility for sending large sets of email, or seeking to automatically send emails to others based on Form input, FormMule may be right for you!
Recent Comments