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
Comments
Close the Communications Loop with a Forms-Driven Email and Tracking System
Problem of practice
Solution
Cast your vote
Tweet this
Post to TwitterBrowse other items with similar tags
powered by educators
empowered by
"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
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!!
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.
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~
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
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.
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
"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
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
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.
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
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!
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
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!
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
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
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!
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!
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
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.
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!
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.