Google Script - Create Calendar Events from Spreadsheet Data
Yesterday I shared how I added reminder dates to a spreadsheet for a person in my department who keeps track of software subscriptions. Today I'm sharing how we take the entries on that spreadsheet and create calendar events, so the reminders are built into her calendar.
Here are the steps to create calendar events from spreadsheet data:
1. Either open the spreadsheet with the data (or create a new spreadsheet & populate it with data).
This is the spreadsheet where I first added the script to add a "remind date" (yesterday's post).
2. Open the Script Editor
Go to the Tools Menu and select Script editor
Replace the default code -
with this - (all the text highlighted in gray - including the final bracket)
function createCalendarEvent() {
var sheet = SpreadsheetApp.getActiveSheet();
var calendar = CalendarApp.getCalendarById('calendarID');
var startRow = 2; // First row of data to process - 2 exempts my header row
var numRows = sheet.getLastRow(); // Number of rows to process
var numColumns = sheet.getLastColumn();
var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
var data = dataRange.getValues();
var complete = "Done";
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var name = row[1]; //Item Name
var vendor = row[2]; //Vendor
var contact = row[3]; //vendor contact info
var notes = row[7]; //Notes
var date = new Date(row[5]); //renewal date
var rDate = new Date(row[10]); //remind date
var eventID = row[11]; //event marked Done
if (eventID != complete) {
var currentCell = sheet.getRange(startRow + i, numColumns);
calendar.createEvent(name, rDate, rDate, {
description: vendor + '\r' + contact + '\r' + date + '\r' + notes
});
currentCell.setValue(complete);
}
}
}
Let's break it down.
First, I'm establishing some variables -
var sheet = SpreadsheetApp.getActiveSheet();
var calendar = CalendarApp.getCalendarById('calendarID');
var startRow = 2; // First row of data to process - 2 exempts my header row
var numRows = sheet.getLastRow(); // Number of rows to process
var numColumns = sheet.getLastColumn();
var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
var data = dataRange.getValues();
var complete = "Done";
The most notable of these is the variable calendar - in that one you have to put the calendarID of the calendar you will be writing to (you must have edit rights on the calendar for this to work!). If you don't know, you grab the calendar ID by going to that calendar's settings, and copying the calendar ID:
Paste this ID in the script so that they are connected.
There are 2 other lines of note in this section - as they are related -
var startRow = 2; // First row of data to process - 2 exempts my header row
and
var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
The first line lets me skip over any header rows - so I can set startRow to the first row I want the script to run on (as I don't want calendar events to run on my headers!). BUT - I need to adjust that second line so that numRows-# will match the number of rows I want processed. This # will always be my start row number - 1 (in this example, 2-1=1 so numRows-1 is what mine is set at).
The next section of script is setting my loop to grab data from each row (established with i)-
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var name = row[1]; //Item Name
var vendor = row[2]; //Vendor
var contact = row[3]; //vendor contact info
var notes = row[7]; //Notes
var date = new Date(row[5]); //renewal date
var rDate = new Date(row[10]); //remind date
var eventID = row[11]; //event marked Done
These rows correspond with my spreadsheet headers - note that the bracketed number is one less than if you count columns starting from 1. I added the commenting tags so I knew what each variable was.
Of special note here - the variables setting dates. Whenever you have a date, you need to establish it is a date - hence the new Date(row[#]) format. Otherwise when you run the script it is unable to read the numbers as a date. (This piece probably took me the longest to wrangle out...)
The next section of script is creating my calendar events -
if (eventID != complete) {
var currentCell = sheet.getRange(startRow + i, numColumns);
calendar.createEvent(name, rDate, rDate, {
description: vendor + '\r' + contact + '\r' + date + '\r' + notes
});
I have my last column being marked, so this first checks to see if an event has already been created - if the last column is not marked (the !=complete piece), then it proceeds to create an event with that row's data. If it is marked, it skips that row.
The variable current Cell is used later, but I established it here.
The calendar.create Event is set up so that it gives the event a title, sets the start and end dates, then puts information into the description area of the event.
Some notes about this:
-I find it easier to establish everything as variables, then reference the variables. Here is the general setup for an event -
cal.createEvent("Title" or variable, new Date("MM DD, YEAR HR:MM:SS") or variable, new Date("MM DD, YEAR HR:MM:SS") or variable,
{description:"description" or variable, location:"location" or variable, guests:email1 or variable, email2 or variable});
-You can note I've included how to add location and guests (which will add it to their calendars via invitations) to the event as well, though I did not use those in this example.
-You can create a string of items for the description with plus signs. The '\r' is a line break.
Finally -
currentCell.setValue(complete);
This final line marks the last column in the row with the word Done - preventing it from running the script on the same row each time I run it in the future...(which will result in multiple calendar events for the same information - I know this personally...).
That's it! You now can create calendar events from a spreadsheet - even one that is populated from a form. Just think - you can:
- auto make appointments that people sign up for via a form
- set reminders from data on a spreadsheet
- create a birthday calendar, and have your students input their birthdate via a form
