As a Technology Integration Coach, I need to have my requests organized in a calendar. So I created a Google form for staff to fill out for tech integration assistance. After they fill it out, I receive an email notification that the response spreadsheet was modified. Then, I take the information and manually enter it into the appropriate day/time on my Google calendar.
Well, thanks to Jenny Grabiec
@techgirljenny, she enlightened me on a new workflow for my tech integration requests that will allow me to automate the process. Meaning, the entries from the response spreadsheet would automatically merge into my Google calendar and an email would be generated too.
First, you need to create a Google form. Be sure you prompt the user with this information:
- First name
- Last name
- Email Address
- Start time
- End time
For start and end time, be sure you set it as a date field that includes date and time. Of course, you can include other important information on the form. Here is a
sample of my form.
Second, you need to create a new calendar and I recommend you name it the same name as your form. For example, the name of my form is Technology Integration Request form and my calendar name is, Technology Integration Request.
Third, go to
g.co/oldsheets, make a copy of the spreadsheet, rename the spreadsheet to the name of your form and include the word 'responses' in the name. You must use an old version of Google Sheets because the script, FormMule is available in the script gallery. It is not available in the script gallery in the new version of Sheets. Once you rename the spreadsheet, you must go into Drive and delete the original response sheet that was created when you created the form. Then you must choose the old sheet as the new destination response sheet.
Follow these steps to change the destination response sheet:
- Go into the form
- Select 'Responses' from the menu bar
- Select 'Change Response Destination'
- Select 'New Sheet in an Existing Spreadsheet'
- Choose and select the response sheet from the list
Next, in the response sheet, you must install and authorize the formMule script from the script gallery. After the script is installed, you will notice the formMule option on the menu bar. You must run the initial installation of formMule.
Now, you need to setup formMule. Once you have the Form Response 1 sheet created, you can delete the Sheet1 tab.
Step 1: Define merge source settings, just click 'Save Settings'
Step 2a: Setup email merge. Check 'Turn on email merge feature' and 'Trigger this feature on form submit'. Click 'Submit settings.' An Email1 Template sheet will be created.
Now, click on the Email1 Template sheet and you will notice a template as shown below. This part will automatically generate an email to the person who submitted the form.
I would recommend the following:
- REPLY TO: enter your email address
- TO: copy and paste the variable below for the staff member's email address. This would be in the yellow area.
- CC: enter your email address so that you can be cc on the email that was sent out to the staff member
- SUBJECT: enter the subject desired
- BODY: enter a general message that their request was received and scheduled
Now the staff member will receive an email and you will be cc'ed on it. If they have any questions, they will reply to you.
Step 2b: Setup Calendar merge. Be sure 'Turn on calendar event merge feature' and 'Trigger event creation on form submit' is checked. Then, copy the calendar ID in the calendar settings of the calendar. It will be found in the calendar address section of the calendar settings. Paste the calendar ID in the Calendar ID field. Copy and paste the start and end times variables from the drop down list into the appropriate fields on the left side. Once completed, scroll down and click on 'Save Calendar Settings'
Finally, go back into the form and submit an entry and test out the workflow. The entry should appear in the calendar and you should receive an email.
That's it! You automated your workflow using Google Forms and formMule. I plan on using this new workflow this year. In addition to this workflow, I will verify their request and then send out a confirmation email to the staff member manually along with my technology integration lesson plan.
A few recommendations:
- allow staff to enter their name and email address
- Start and End time fields, should be labelled 'Enter Start Time', 'Enter End Time' on the form
- customize color settings on the calendar
- name the form and calendar the same name for clarity
