UPDATE
This article is fairly old and definitelly out of date. Google has changed its API and i have not had time to update this post. I would recommend reading this article written by Fredrik which details how to use the new api. Happy Coding.

For a long time now I have been trying to find an easy way for the volunteers under my command to submit vacation requests.  I searched high and low for a PHP scheduler but nothing suited my needs.

I the realized that google had a calendar API which would allow external forms to add events to my calendar, but sadly this didn’t pan out either till I found out that Google also had its own form generator.  But sadly again, doing searched on how to add an event to Google Calendar from the form turned up either useless or empty.

I did manage to find a script that came close to what I wanted.  Bruce Burge create a great article on how to add events, however his code was lacking for what I wanted, but not by much.  His code is the base for my code found in this article.  The major modification of course was to remove the code which sets the start time to 0:00 and end time to 23:59, since I wanted the absence tracker to show their shift and not the full day.  Some tweaks to what gets added to the comments and description as well as the title were also changed.

Another feature I wanted was to have an email sent to me when someone submitted the form. Amit Agarwal has a great tutorial on this, however again, I wanted to make a few modifications based on my needs.  The second script is a slightly modified version of his code, namely making the subject dynamically created based on the values in the spreadsheet/form.

Now that I have given credit to the original creators, and given an explanation as to what I did to their code to make it better, lets get on with how its done:

The First Step – Create Your Form

create-formI made the mistake of making the spreadsheet first, although you can do this, it is much easier to create the form, as once you are done with the form, it will automatically create the spreadsheet for you.

For my form there were a total of 6 fields:

  • Name (Text, Required)
  • Absence Type (Check Boxes, Required)
  • Shift Start (Date, Required)
  • Shift End (Date, Required)
  • Reason (Chose from a list, Required)
  • Comments (Paragraph Text, Not Required)

Now for the Shift Start and End fields I also selected “include time” as I wanted the calendar to show only time of the event I was creating.

You can have more columns if you like, however for the Calendar you need to have a Start and Stop time, as well as fields to create the Title and what you want to put into the description.

Accessing the Spreadsheet and Adding Your Code

[notification type=”info”] It doesn’t matter what you name your Script. When it asks you for Authorization, please authorize the script to run or it will not work. [/notification]

Once you have completed your form, click on “View Responses“, this will bring up your form generated spreadsheet:
view-responses

Once you are on your spreadsheet, you will select “Script editor” under “Tools”

tools

Copy and paste the code below overwriting anything that is currently in the Code.gs file, this created the calendar event:

NOTE: Your Calendar ID needs to have the “@group.calendar.google.com” at the end of it

[php] //this is the ID of the calendar to add the event to, this is found on the calendar settings page of the calendar in question
//Look for “Calendar Address:” and the ID shows up beside it.
var calendarId = “CALENDAR_ID_GOES_HERE”;

//below are the column ids of that represents the values used in the spreadsheet (these are non zero indexed)

//Column containg the Start Date/Time for the event
var startDtId = 4;
//Column containg the End Date/Time for the event
var endDtId = 5;
//Column containg the First Part of the Title for the event (In this case, Name)
var titleId = 2;
//Column containg the Second part of the Title for the event (In this case, Absence type)
var titleId2 = 3;
//Column containg the Comments for the event
var descId = 7;
//Column containg the Time Stamp for the event (This will always be 1)
var formTimeStampId = 1;

function getLatestAndSubmitToCalendar() {
//Allow access to the Spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
//Removed setting of Hour and Minute for the Start and End times as these are set i our form
var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
//Create an addition to the Description to included who added it and when
var subOn = “Added :”+sheet.getRange(lr,formTimeStampId,1,1).getValue()+” by: “+sheet.getRange(lr,titleId,1,1).getValue();
//Setting the Comments as the description, and addining in the Time stamp and Submision info
var desc = “Comments :”+sheet.getRange(lr,descId,1,1).getValue()+”n”+subOn;
//Create the Title using the Name and tType of Absence
var title = sheet.getRange(lr,titleId,1,1).getValue()+” – “+sheet.getRange(lr,titleId2,1,1).getValue();
//Run the Crete event Function
createEvent(calendarId,title,startDt,endDt,desc);
};

function createEvent(calendarId,title,startDt,endDt,desc) {
var cal = CalendarApp.getCalendarById(calendarId);
var start = new Date(startDt);
var end = new Date(endDt);
//Manually set the Location, this can be modified to be dynamic by modifying the code if need be
var loc = ‘Computer Centre’;

//Set the Options, in this case we are only using Description and Location, as we do not need Guests or sendInvites
var event = cal.createEvent(title, start, end, {
description : desc,
location : loc
});
};
[/php]

In order to make it so that the form also sends you an email, create a new code file:
file

Name this files something like “Email” so you remember what it does. Then copy and past the below text over writing anything that Google automatically adds:

[php] //Left Original Coder’s info as I just modified the file
/* Send Google Form by Email v2.0 */
/* For customization, contact the developer at amit@labnol.org */
/* Tutorial: http://goo.gl/7Ujaqw */

//Added some variables that we will use to pull info for the subject
var startDtId = 4;
var titleId = 2;
var titleId2 = 3;

function SendGoogleForm(e)
{
try
{
// Added code to allow it to read the spreadsheet
// this was taken from the previous script as we know it works
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
// You may replace this with another email address
// i.e. var email = “email@domain.com”;
var email = Session.getActiveUser().getEmail();

// Customized the Subject to pull the Name, Absence REason, and Start date from the database
var subject = sheet.getRange(lr,titleId,1,1).getValue()+” – “+sheet.getRange(lr,titleId2,1,1).getValue()+” – “+sheet.getRange(lr,startDtId,1,1).getValue();

var s = SpreadsheetApp.getActiveSheet();
var columns = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
var message = “”;

// Only include form fields that are not blank
for ( var keys in columns ) {
var key = columns[keys];
if ( e.namedValues[key] && (e.namedValues[key] != “”) ) {
message += key + ‘ :: ‘+ e.namedValues[key] + “nn”;
}
}

// This is the MailApp service of Google Apps Script
// that sends the email. You can also use GmailApp for HTML Mail.

MailApp.sendEmail(email, subject, message);

} catch (e) {
Logger.log(e.toString());
}

}
[/php]

Now we need to add the triggers so that when we submit our form, it adds it to Google Calendar and sends out an email. To do this, while still in the Coding section, Select Resources in the top menu:
resources
Then make sure that you have two triggers set like the image below (Click the link to Add them as they will not automatically be there)
triggers

Notes to Keep in mind when Testing Your Form

It can take up to five minutes for the entry to be added to your spreadsheet, once it shows there you will see an entry in your Google Calendar (the one you provided the ID for,  don’t forget to change this), and you should receive an email as well with all the form fields.

If for what ever reason something doesn’t work. in your triggers, set the notification to Immediate notification, not once a day at midnight.

If you have any questions, I will do my best to answer them.

Plans for Future Posts

Now that I have had a bit of experience with coding for Google Forms, I will try and create a better version, one that will allow you to create a booking app, one that would check if the time is already taken in the calendar (prevents double booking) as well as allows Invites and Guests (the full options abilities).

Post Views: 1,556