Friday, December 6, 2013

Creating Google Calendar Appointments from Your Google Forms

I am always looking for ways to make my life easier.  When I got to Region One, they asked me to help with the iPad COWS (Cart on Wheels) for our department.  I said sure.  Initially, one would get on the ESC1 site, click on the form and emails would be sent to two different people.  Both would put the information on their calendar and keep track that way.  Problem was, if someone forgot to write it down, or both, then the COW wasn't ready or overbooked.

Now, Region One Staff can look at both calendars, for iPad Cart 1 and iPad Cart 2 and see when there is an opening.  Then, they can fill out the form and the dates are automatically entered into the shared calendar so I and my co-workers don't have to manually input the reservation.  The person filling out the request receives an email confirmation for the reservation and I receive an email letting me know that the reservation was put on the calendar.

A miracle you say?  Yes, now I have 3 calendars that run themselves and allows the staff to see when there are openings.

So here is how I did it......

First I created the calendar.



Second, I created the form making sure that I had all the information I needed, including the date the COW was needed and the date the COW would be returned.





Next, I had to work with the Response Spreadsheet to set up the Email and Calendar merges.  The Script that has to be installed is called FormMule.  You find scripts under the Insert Tab, then scroll down to where it says scripts.  When you search, make sure FormMule is typed in as one word.


Now, you will need to install the FormMule.  After you install, it FormMule will ask for authorization to run.



After installation, FormMule will have a number of steps to follow.  The first is to define merge settings.  I touched nothing and selected SAVE.


Next, you will be asked to submit the email merge settings.  All I did was click the top two check boxes - to turn on email merge feature and trigger this feature on form submit.

On the bottom of the spreadsheet, you will see a new tab that says Email Template - you need to fill out the appropriate information.  This is what mine looked like:


Now, for the calendar merge information: I toggled the first to check boxes and added the calendar ID.  The Calendar ID is found under calendar details. (Calendar Address)


I checked the top check boxes on the left and added the information you see below.
 I also added the Event start time and Event End time - like you see below.
 After running a test drive, your template will look like so.


Last, you fill out the form and you will see the request on your calendar!  Here are the results updated.




I can't tell you how awesome it feels to have this system up and working.  I had a fellow Google fanatic here at work that helped me sort all of this out. Thank you Efren!!!

I have used this for Air Card Checkouts and COW checkouts.  Can you think of ways to use FormMule?  Tell us about it!

7 comments:

  1. This is amazing! I am looking for exactly this. I will give it a go tonight, but you have become a life saver if this works out for me!

    ReplyDelete
  2. Ann,

    This is a great tutorial - thank you so much for posting it! You mentioned at the beginning of the post that you use FormMule for three different calendars - how do you set up FormMule to work with the tell when to put different requests on different calendars? I've tried searching everywhere for this answer and can't find it. I would really appreciate any help you could give me!

    ReplyDelete
  3. Thank you so much for this! This is exactly what I was looking for to schedule the use of technology equipment/ set-up!

    ReplyDelete
  4. Ann,
    I was glad to find this tutorial. I am like KASmithGA - I would love to know how you set it up to put different requests on different calendars.

    ReplyDelete
  5. I also would like to know how you handle different calendar selections. Also, how do you handle double-bookings?

    ReplyDelete
  6. This is fantastic. Does it protect from double bookings? Or do you have to follow up for those.

    ReplyDelete
  7. I wish to add my thanks, too. You inspired me to develop a teacher absence online process. I'm using the old formMule to create the calendar events (date, name of teacher, time of absence). It works, but continues to try to create events after the last row with data, all the way to the bottom of the sheet. Would you have an idea why this is happening? Thank you very much.

    ReplyDelete