You are missing some Flash content that should appear here! Perhaps your browser cannot display it, or maybe it did not initialize correctly.

Notes

We wrote the  formMule (a.k.a. "The Mule") to generalize a process we began to see over and over again when automating school communications with advisors, teachers, parents and administrators on student issues being logged via form.   We tend to believe "The Mule" is our most generically useful building block for creating your first systems improvement in a school if you want to use forms and automated emails and calendar appointments to get the right people in the loop around student success.   

formMule can be installed from the Apps Script gallery from any Google Spreadsheet, though we recommend you start with one that has a Google Form already set up in it as the source of the data you want to operate on.

How to install from the Scripts Gallery:

  • Spreadsheet->Tools->Script Gallery
  • Search "formMule"
  • Install

Out of the box, formMule requires a moment to understand.  Here are the basics:

1) Google-Form-submitted data can auto-trigger the send of up to three separate, templated, merged emails and or the auto-creation of a single calendar event with specified guests.

2) Because, in many cases, it would be too demanding or annoying to expect users to remember all the email addresses of the recipients of auto-triggered emails and calendar events, this script also handles the copying-down of formulas running in columns to the right of form entry data, which may often include a VLOOKUP function to fetch the contact email addressesof various relevant parties within a workflow or communications loop.   If you don't know what a VLOOKUP is and would like a quick tutorial geared towards educators, visit: http://www.youpd.org/node/1801

3) Email and calendar event functionality can be switched on and off, and set to trigger based on conditional values in the source data from the form.  For example, I might choose to send a different email depending on the values submitted by the user, or calculated in a calculated column.

4) This script is built to enabled non-coders to create and share custom workflow solutions.  If you are an educator and have built a solution using this script that is likely to be useful to other schools, select the "Package workflow for others to copy" option in the formMule script menu and follow the instructions.  Other users will then be able to copy your entire system, including all script settings, and begin using it immediate in their own school.  Show your appreciation for this script by posting a screencast showing off your workflow at http://www.youpd.org

Examples of formMule in use:

Successful small schools often manage students in advisory caseloads, where a single adult is expected to serve as the first point of contact between the school and the home around issues from behavior to attendance and academic performance/needs.  Logs of events corresponding to each of the areas on the part of any teacher in the school can be submitted in Google Forms and set up to automatically trigger communications with advisors, who are then empowered to serve as point person for communicating with the home. In this example, a VLOOKUP might be used to reference master lists of student email addresses and advisor email addresseses against unique field (student name or ID#) in the form data.   With the use of email conditions, different kinds of incidents might trigger additional emails to various members of a school team.

A best practice for developmental supervision of teachers is providing regular feedback.  Many of our school leaders have adopted Google Forms based "mini-observation" trackers.  formMule can easily be installed on these trackers to enable the option to instantly email feedback or an email requesting an appointment for discussion following an observation.

 

Push my thinking

Need help figuring out how to use this? Ask away! Found a way of implementing this script in your school? Please share!

Comments

jorgvanelst

More than 6 email templates possible?

Hey Andrew,

First of all, great script! I love the simplicity of use. I´ve only got one problem:
I need to make 10 different email templates for replying, but there is a maximum of 6. Is there a way to expand this amount to 10? 

All the best,

cstapleton

Formmule Calendar

Hi Andrew,

I'm creating a calendar merge using formmule for the admissions office at my school.  They are asking me to take multiple fields and put it into the Event Description of the Calendar Event.  This part is easy, but when I create the event with multiple fields, the description puts <br/> between each field.  I would like to know how to do two things:

a)Eliminate the html break symbol

b)Create line breaks between fields in a description box so that they aren't all clumped.

Thanks!

Huge fan of your work, Jay Atwood introduced me to your material and it's really transformed our school.

Chris Stapleton

whammo18

Script and On Form submit command

Just started looking at this today. I've installed the script via script gallery running 11/15/13 version. Not seeing the script to copy and paste on this page. Also I am not seeing on form submit from the drop down. I see formmule_onformsubmit but it doesn't seem to work like in the video.

Bajarnicles

"Service invoked too many times in a short time: properties"

Hi, Andrew.

I posted a question (subject "Service invoked too many times in a short time: properties") about a month ago. Might you have any insight into the problem I'm encountering? I'd be so grateful to hear your thoughts.

 

Please and thanks!

-Nicholas

tneuser

Awesome script

First of all, I want to say that I LOVE this!! It will save me so much time. Thank you. My question for you is this. Is it possible that the emails could be generated based on a date? So, if I wanted an email to be sent out in the future at a specific time or on a specific date based on a date in the spreadsheet I could do that? If that is possible, or could be made possible, I would love this even more!!

andrew

Create a conditional column

Using the IF() function you can totally do what you are intending.  I intentionally left this level of evaluation out of the script because it can be readily accomplished in the spreadsheet.  Create a date comparison (if need be, use the =GoogleClock() function to create a reference to the current time)

A second step will be to set a trigger on the formMule_sendEmailsAndSetAppointments function to run on a regular time interval and evaluate a conditional send (described above).

Triggers can be set from the "Resources->Current Project's Triggers" menu.

tneuser

Works but now I'm getting this error

OK, that works. Thanks much!! Now I've noticed I've been getting these errors. I don't think it is related to this at all, because I noticed I was getting them last week. It worked fine when I first started using the script, but now I'm getting this error when sending out the emails. They are still sending out, but do you know why I'm getting this error?

 

Request received error: Exception: Unexpected error: http://www.google-analytics.com/__utm.gif?utmwv=5.2.2&utmhn=www.formmule-analytics.com&utmcs=-&utmul=en-us&utmje=1&utmdt&utmr=0=Mailed%20Templated%20Email&utmp=Mailed%20Templated%20Email&utmac=UA-30976195-1&utmcc=__utma%3D451096098.136804046.100000000.200000000.2184.1%3B%2B__utmz%3D451096098.2184.1.1.utmcsr%3D(direct)%7Cutmccn%3D(direct)%7Cutmcmd%3D(none)%3B&utmu=DI~

1800physics

Check boxes for "Copy Down" not appearing

I have a large form, 54 different questions that I want to process and send the processed results off in a form email. However the option to copy formulas down  does show up but their are no check boxes for the sheet(s) that have a large number of columns.

 

The check boxes do appear in step 1 for several other tabs(spread sheets) though those all have just a few columns (4-12).

 

I am wondering if this is a resolvable problem.

 thank you vey much

jwahlgren

check boxes for copy down not appearing

Did you ever get an answer to this?  I am having a similar problem where I get the "Choose any columns to the right of your form data that contain formulas you want copied down..."  message but no way to select the columns.  New form submissions appear and get emails but the formula will not copy down.

andrew

You need to scroll down

Jeff,

The checkboxes are there, but due to poor design you need to scroll down to find them.  I need to deal with this.  Thanks for reminding.

Cheers,

Andrew

eerickson

Solved!? Missing checkboxes for copy down funcitonality

I seemed to solve my problem, and wanted to let others know.  I created a form with many questions, then after the fact, decided to use a table and a VLOOKUP function to minimize the questions in the form.  When I tried to use the 'copy down' funcitonality, I couldn't find checkboxes to select certain columns to be included in the copydown process.  Maybe Andrew the author can be more specific, but Unbolding and removing the grey background from the header information allowed for those columns to be selected!

I hope this helps others if they are in a similar spot.

Erik

eerickson

Copy Down formula (check boxes missing)

Hello,

I very much appreciate all the great work you are doing!  It is helping so much in my school.  I am having difficulty with finding/checking the checkboxes for the 'copy down formula' function.  I have found the few checkboxes by scrolling down and to the far right, but they are not checkable nor are they for the right columns.  Any help would be appreciated.

Erik

Bajarnicles

"Service invoked too many times in a short time: properties"

 

Hello!

I work in the public schools of Minnesota for a charter school that serves 7-12th grade students on the autism spectrum. I run a sensory room where students take regular breaks. In order to track data I log each students visit to the room and, subsequently, I send a form to each student's classroom after they've used my room to take a break. FormMule had enabled me to do this all in one step, saving me lots of time. However, beginning a couple months ago, I started to encounter errors which prevented the forms being triggered to be sent out.

Now, for every form that is sent out, this is the error message that I receive:

Service invoked too many times in a short time: properties. Try Utilities.sleep(1000) between calls. (line 1530, file "formMule")

The line number varies from error to error, but of the hundreds of errors, the majority are line 1530. In the code, this line reads:

var emailCondString = ScriptProperties.getProperty('emailConditions');

The second most common error is line 746, which reads:

var sheetName = ScriptProperties.getProperty('sheetName');

I'm not sure how to interpret these error messages, but I know that it doesn't matter if I submit one form in an hour or 10 forms in the span of 60 seconds - the "service invoked too many times in a short time" error is the same. This has rendered FormMule obsolete for me, sadly. :-/

Might anybody be able to help me or know how to remedy this?

Please and thank you!

-Nicholas

Frank Fernandez

Script debugging?

FormMule is a great tool that we are using for course registration requests. Thank you for creating this!

I am new to Google Apps scripts, and I don't know what options I have for debugging when errors occur. I recently got this message:

The coordinates or dimensions of the range are invalid. (line 820, file "formMule")

How can I debug this? There is no data in the spreadsheet that shares the timestamp of the error, so I don't know what data the student entered, or even who the student was.

~ Frank

bbuckridge

Calendar Merge ConcernProblems

I am trying to run a calendar merge however with the new Google Docs updates and forms I can not get a valid Date Time format for the FormMULE to merge with my google calendar. It seems when I enter the Time and Date in the field even when it has an appropriate format i.e DD/MM/yy.  I had no problems with an older version but it can not be  merged to a google calendar  it seems because upon submissions the google form adds an apostrophe to the date or time. So it reads 'DD/MM/YY and not  DD/MM/YY. Any help would be apprciated this FormMule has saved us a lot but now I am stuck on this.

Bajarnicles

Forms not sending

Hey there.

I've been using FormMule since the beginning of November and have been thrilled with it. The week before Christmas, though, I began to run into a problem. I'd enter the data on my end, the same as always - without having changed any formulas or any of the data in the Vlookup source page - but the follow-up form form wouldn't send...most of the time. 

For example, I submitted my form 10 times for 10 different students one morning and none of the follow-up forms got sent to their respective classrooms. Not right away at least. Five hours later, the flood gates were released and all ten of those forms got sent, one after another. 

After that day everything worked well again for a couple weeks. Yesterday, however, the script stopped sending out follow-up emails altogether and I can't figure out what the issue is.

Thoughts?

 

-Nicholas

Monahan

Google Apps Ninja Training

Thanks for making such a versatile app.  My board is just going online with Google Apps for Education and we were looking for some training materials for teachers and students.  We're using Ninja Program and wanted a way to have quizzes automatically scored and the results emailed immediately to students/teachers.  Your script works perfect.  I put a video up on YouTube describing the process here.  It's dull and specific, but for others who are looking to use the Google Apps Ninja Training program, it might be useful.

Thanks again!

smtailer

Question about scheduling:

Hi.  This looks like a great solution at a district level for an electronic approval process to post open jobs.  We've got it set up to have administrators send in a request, a notification to the budget department to approve it, then an email notification to HR to create an open position to post.  The only thing I'm running into is what to trigger on to automatically send the next email, based on "approved" manually entered by the Budget department.  It would be great to have them just enter approved and have it kick off, but, we'd have to manually run each time right now.  

Any direction would be helpful!

Thanks

mgns.nlssn

Ah.... Found it, super Sweet, its a initial setup

Question where you tell the script to copy down the formula, now, lets see if I can get this baby going!

mgns.nlssn

Problems with formulas in formMule

Hi and thanks for a brilliant script.

But...I have a problem, I want to use VLOOKUP and use data from a form, run it and lookup a corresponding email, as you talk about, but when I put formulas in row 2 and test the form, all data gets into row three, but my own formulas don't copy down to row 3, why?

 

I'm stunned about how much you can do with google forms, I really want to start using this tech. at our school.

 

Regards, Magnus Nilsson, http://gavle.engelska.se

Roni Habib

Problems with E-mail Merge in Form mule

Hi there,

I love FormMule but am having some issues with the E-merge merge functionality.  I completed all the necessary fields in the e-mail template.  the "To:" "Subject:" and "body:" fields.  I get an error that says that Template missing "To" address.  

 Here is a link to a document that shows pictures of the e-mail template and error message:

https://docs.google.com/document/d/1R4j-J9Wq0El4YkH2UJqBZdHJ8B4MDcUBdte1...

Also, if I make a copy of a form that has Formmule on it, can the script be copied and the mail merge settings saved? 

Thanks!

Roni

 

Katrinab

Just what we needed.

This is fantastic! It took me a bit to figure out how to set this up, but once I did... wow! I'm very impressed. Thank you so much for your time in putting this hack together! I use this to track when a student enters our sensory room, what they did while they were here and it automatically sends an email with another form asking how effective a students break was while they were on a sensory break to the classroom they are returning to. Thank you again!

adamcaudell

Cannot get it to work

I have followed all of the instructions.  I get everything set up and click the "Preview and merge" script, and it errors out every time.  The only thing I can think is the condition to send the email.  I want emails to be sent daily, so on the condition, I have it set to the variable "Today's Date" from my form, and the value is set to TODAY() ... the Google Function for today's date.  I have also tried setting it to $currMonth/$currDay/$currYear, but it still does not work.  I thought I had gone through the initialization process as described in the ReadMe file, but I must be missing something.  Please help!

drum4uz

formMule Settings

 

Andrew,

How do you get to the from mule settings?  I can't see where you accessed that.  Also, How did you get those pre-populated gray headers?  I started from scratch, and when I followed the steps to installing the script, my spreadsheet just stayed blank.  I'm excited to get this running.  

David

andrew

formMule setup

Sound like you may have missed two things:

1) You need to have a Google Form already set up.  The grey headings are produced by Google Forms when you create form fields.

2) Once the script is installed in the script editor, you need to run the onOpen function to authorize the script and then run it again to have the custom menu item show up in your spreadsheet.

andrew

Interesting...

That's interesting...you might try playing with the sender email address.  See http://code.google.com/googleapps/appsscript/class_mailapp.html#sendEmail

Try adding a "name" advanced argument in the sendEmail function and specify the sender email address.   You could also play with noReply.

Curious if you are installing the script logged in as a domain user or as your teachingmatters identity?  I believe the default sender is whomever authenticated the script.  So you might look there.

If that doesn't do the trick, post to the Apps Script Issues queue...

http://code.google.com/p/google-apps-script-issues/

FYI just updated to Version 2.0 to include auto-calendaring functionality!

 

eodonnell

Lots of Uses

Really great - I'm getting my feet wet with this, using it for a school's "Contact Us" website form w/ directed questions/comments by department.  Works great, except my messages are getting sent to the spam folder.  Any thoughts?  I've tried a variety of subject headings, and made sure that real content is getting submitted.  

Close the Communications Loop with a Forms-Driven Email and Tracking System

Problem of practice

How can you quickly automate targeted emails and calendar appointments to loop in the right people around regular occurrances like behavior incidents, absences at attendance at after-school tutoring, etc.?

Solution

The formMule (aka "The Mule") is a Google Apps script that is useful for automating lookups, emails and calendar events created dynamically from Google Forms.

Cast your vote

64
Recommend

Tweet this

Browse other items with similar tags

This is a simple script housed in a google sheet that allows teachers to create handouts for their classroom, shares the student appropriately and organizes the handouts in a collection. It can be used to create sheets as well as docs.
Recommended by 19 educator(s).
This hack shows how to install a Google Apps Script in your Google Site to look up the grades of the currently logged-in student or parent and generate a live bar of progress from your Google spreadsheet!
Recommended by 20 educator(s).
This introductory module provides what you need to start building the modular and lightweight data system of your dreams. The first installment is a "Lateness Zapper" that automates logging, communications, and reporting on student lateness.
Recommended by 23 educator(s).
The Reportlet Script, written in Google Apps Script, serves up easily-templated tabular reports that can include custom data, images, and charts keyed to the logged in user. Power users get a dropdown to allow viewing of any report.
Recommended by 17 educator(s).
This well thought-out template, created by a collaboration between a highly successful NYCDOE principal and an Apps Script developer, makes data entry a snap, helps administrators prioritize observations, and supports communications with teachers.
Recommended by 19 educator(s).
This is an attempt to create a simple tool that a team of teachers could use to track indicator-level data on shared assessments. It's low-stakes, hopefully approachable and can be customized to fit the team's needs.
Recommended by 14 educator(s).
Using a script stolen from another project and a student template, this platform will allow a teacher to enter in their classroom roster, hit a button and automatically create goal setting sheets for their class and monitor their students' progress!
Recommended by 9 educator(s).