In the post Updating Google Calendars from a Google Spreadsheet, I described a recipe for adding events to a Google Calendar from a Google Spreadsheet using Google Apps Script. After a quick chat with the person who was compiling a spreadsheet they wanted to use to populate a set of calendars, I revisited the script to make a few tweaks and hopefully increase its usability.
So here’s a glimpse of the spreadsheet they’re using to list dates for various campaigns and channels where related activity might occur. Firstly, we have some columns relating to the event or activity, and the dates on which they occur. The first column (added to calendar) is a control switch that identifies that the calendar details have been updated for that event:
Within the spreadsheet, I set the two date columns to have the Date type (from the Tools menu, set the Data Validation option to Date). I’m not sure how the spreadsheet is (correctly) identifying the US date format (MM-DD-YY) – maybe from a US timezone as a global setting for the spreadsheet?
As well as various other admin columns, there are columns relating to whether or not a channel will be used to support a particular event:
From what I could ascertain, the way the spreadsheet is supposed to work goes along the lines of: someone adds details of an event and the associated channels for the event to the spreadsheet. “Add” in a channel column says that event is to be added to that channel calendar. When the updating script is run, for each event it checks the control column A to see that an event hasn’t been added to the various channel calendars, and if it hasn’t checks the channel columns; if a channel column is set to “Add” the event details are added to that event calendar.
So – how do I need to modify the original script? Firstly, the original script use the default calendar. In this case, we need a separate calendar for each channel, so in Google Apps I created one calender per channel:
We can grab a calendar by name from a spreadsheet apps script using a call of the form:
var cal_broadcast=CalendarApp.openByName("broadcastDemo");
When the script runs, we need to grab the appropriate range of cells from the spreadsheet to see which calendars to update. For testing purposes, I only grabbed a few rows…
var startRow = 2; // First row of data to process
var numRows = 4 // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 26);
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var title = row[1];
var desc=row[15];
var added = row[col_added]; //Check to see if details for this event have been added to the calendar(s)
var tstart = row[2]; //start time - I have defined the column in the spreadsheet as a Date type
var tstop = row[3]; //start time - I have defined the column in the spreadsheet as a Date type
var broadcast=row[col_broadcast]; // is this event one to "Add" to the broadcast calendar?
var itunes=row[col_itunes]; // is this event one to "Add" to the itunes calendar? etc
var youtube=row[col_youtube];
if (added!="Added") { //the calendar(s) have not been updated for this event
if (broadcast=="Add") {
cal_broadcast.createEvent(title, tstart,tstop, {description:desc}); //add the event to the "broadcast" calendar
}
if (itunes=="Add"){
cal_itunes.createEvent(title, tstart,tstop, {description:desc});
}
// etc for each channel
var v = parseInt(i)+2; // +2 is an offset to do with the numbering of rows and the "blank" header row 0;
sheet.getRange(v, 1, 1, 1).setValue("Added"); //set the fact that we have updated the calendars for this event
}
}
In order to identify which columns to use to identify the broadcast, itunes, etc channels, I went defensive (the following bit of code comes before the previous snippet; what is does is to look at each column heading, and then set the column number for each channel appropriately based on its name; I should probably use a similar technique to identify the start/stop dates. What this approach does is accommodate changes to the spreadsheet in terms of the insertion of additional columns or the reordering of columns, for example, at a later date):
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); //need a routine to set active sheet by name?
//go defensive
var col_broadcast,col_itunes, col_youtube=1;
var maxcols=sheet.getMaxColumns();
for (var j=1;j<=maxcols;j++){
var header= sheet.getRange(1, j, 1, 1).getValue();
switch(header){
case "Added to Google (Y/N/Hold)":col_added=j-1;
case "Broadcast":col_broadcast=j-1; break;
case "iTunes":col_itunes=j-1; break;
case "YouTube": col_youtube=j-1; break;
default:
}
}
Running the combined function thus searches the spreadsheet for the appropriate channel columns and control column, checks the control column for each event entry to ensure that the event hasn’t been added to the selected calendars, and then adds the event to the appropriate channel calendars if required.
Playing with the script, it seemed a little bit clunky, so I tweaked it to update the channel cells with the word “Added” if it had been set to Add, and the calendar had been updated:
if (broadcast=="Add") {
cal_broadcast.createEvent(title, tstart,tstop, {description:desc});
dataRange.getCell(parseInt(i)+1,col_broadcast+1).setValue('Added'); // Replace "Add" with "Added"; +1 is offset for sheet numbering
}
It also struck me that if the settings of a channel was updated to “Add” after that event was updated, that channel’s calendar would never get updated. So I created a variant of the updating function that would just run on a per column basis and update a calendar entry for an event if it was set to “Add”, rather than checking the control column:
function caltestAddtoCal_broadcast(){ caltestAddtoCal("broadcast"); }
function caltestAddtoCal(addCal){
//...
if (addCal!="") {
if ((addCal=="broadcast")&&(broadcast=="Add")) {
cal_broadcast.createEvent(title, tstart,tstop, {description:desc});
dataRange.getCell(parseInt(i)+1,col_broadcast+1).setValue('Added'); //+1 is offset for sheet numbering
}
// ...
}
}
What this means is is that a channel controller can update entries in their calendar by running the script just for that channel and adding “Add” to any event they want adding to the calendar, the list of “Added” entries showing which events have already been added to that calendar:
Having doodled a script that sort of works, it’s now time to hack it around it so it looks a little more elegant. Which means refactoring.. sigh… and another reprise in a day or two, I guess…?!
OUseful.Info, the blog... 
Yes to more elegance!
This is great. This code works perfectly when there are no empty cells in the spreadsheet. but using a form some clients choose to make appointments and some other clients simply want to subscribe to the newsletter. How can the code be tweaked to bypass empty cells and only select cells with contents? Many thanks. George
Do you mean empty cells in a row with data?
Wow, very useful stuff. Thank you for posting the details!
Getting my head around these examples and wondering how to update/change existing calendar entries or remove them outright.
Thx
Hi! Very useful…
This only makes insertions, have you found a easy way to make Updates por removals?
e.g.: I update the title of an event on the cell, or just delete it.
I guess it could be managed adding “Update” (and then “Updated”) and “Remove” (and then “Removed”) to the appropriated control column, but how do I call the calendar procedure to identify the event?
Have a nice day!
I am also worried about updates.
Will this script take into account changes to existing events or removal of rows?
I am afraid it will create duplicates.
Thanks
@mark
Yes – I think there may well be an issue with the problem of duplicates…. I will try to have a look at this again when I get a chance…unless you have a fix?;-)
I would like to pay you to do this for me. Do you have time today?
Is there any place to download all of the code used to accomplish the task?
This workflow just seems fundamentally broken to me. Yes it works, but it is fragile. Why can’t google calendars be set up to subscribe to properly formatted spreadsheets? All of this coding for something that should be fairly easy to implement as a built in google function.
This is exactly what i need. Im not very good at coding and have been trying to follow this but for some reason it just isnt working. is there anyway you could post a link to the complete code for this. Just like this example i have three calendars made in google calendar and a spreadsheet like the one shown on this page. Please help!!!!
Hi
I want to change the status to “Add” if i edit any column in a row. Is it possible.
I could not find the openByName function for the CalenderApp object
so I had to change
CalendarApp.openByName(“broadcastDemo”);
to
CalendarApp.getCalendarsByName(“broadcastDemo”);
to make it work
actually, I can’t seem to get getCalendarsByName but getCalendarsByid works e.g.
var cal = CalendarApp.getCalendarById(“ebrahim.makda@gmail.com”);
Can anyone currently get openbyname or getcalendersbyname to work?
This may seem very stupid but… Should I assemble all those code pieces into just big one or are those different scripts inside a single project? Or are those different projects? (I guess this last one is not an option).
Sorry guys! I’m completely new to scripts; I’m looking for a way to get my job done and I supposed the answer should be in here…
I assembled all the code together, but when I try to run it I receive a message wth a warning about there’s no function to run… Any idea why? Here’s the code (all var defined at the beginning):
//Inicia código para llamar calendarios
var cal_JALISCO = CalendarApp.getOwnedCalendarsByName(“JALISCO”);
var cal_MORELOS = CalendarApp.getOwnedCalendarsByName(“MORELOS”);
var cal_MEXICO = CalendarApp.getOwnedCalendarsByName(“MEXICO”);
//Termina código para llamar calendarios
// Inicia código para nombrar columnas
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); //need a routine to set active sheet by name?
//go defensive
var col_JALISCO, col_MEXICO, col_MORELOS=1;
var maxcols=sheet.getMaxColumns();
for (var j=1;j<=maxcols;j++){
var header= sheet.getRange(1, j, 1, 1).getValue();
switch(header){
case "Added to Google":col_added=j-1;
case "JALISCO":col_JALISCO=j-1; break;
case "MORELOS":col_MORELOS=j-1; break;
case "MEXICO": col_MEXICO=j-1; break;
case "Subject": col_Subject=j-1; break;
case "Location": col_Location=j-1; break;
case "Start Date": col_Start=j-1; break;
case "End Date": col_End=j-1; break;
case "Description": col_Description=j-1; break;
case "Color": col_Color=j-1; break;
default:
}
}
//termina código para nombrar columnas
// Inicia código de actualización de calendarios
var startRow = 2; // First row of data to process
var numRows = 10 // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 100);
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var title = row[col_Title]; // First column
var desc = row[col_Description]; // Second column
var added = row[col_added];
var tstart = row[col_Start];
var tstop = row[col_End];
var loc = row[col_Location];
var color = row [col_Color];
var reminder = row [col_Reminder];
var JALISCO=row[col_JALISCO]; // is this event one to "Add" to the broadcast calendar?
var MORELOS=row[col_MORELOS]; // is this event one to "Add" to the itunes calendar? etc
var MEXICO=row[col_MEXICO];
if (added!="Added") { //the calendar(s) have not been updated for this event
if (JALISCO=="Add") {
cal_JALISCO.createEvent(title, tstart,tstop, {description:desc}); //add the event to the "broadcast" calendar
dataRange.getCell(parseInt(i)+1,col_JALISCO+1).setValue('Added'); // Replace "Add" with "Added"; +1 is offset for sheet numbering
}
if (MORELOS=="Add"){
cal_MORELOS.createEvent(title, tstart,tstop, {description:desc});
dataRange.getCell(parseInt(i)+1,col_MORELOS+1).setValue('Added'); // Replace "Add" with "Added"; +1 is offset for sheet numbering
}
if (MEXICO=="Add"){
cal_MEXICO.createEvent(title, tstart,tstop, {description:desc});
dataRange.getCell(parseInt(i)+1,col_MEXICO+1).setValue('Added'); // Replace "Add" with "Added"; +1 is offset for sheet numbering
}
// etc for each channel
var v = parseInt(i)+2; // +2 is an offset to do with the numbering of rows and the "blank" header row 0;
sheet.getRange(v, 1, 1, 1).setValue("Added"); //set the fact that we have updated the calendars for this event
}
}
//termina código para actualización de calendarios
Tony, do you have some example files for a Code dummy as me????
This is a really excellent example, so first of all, Thanks! I have been able to use it in multiple automated workflows using forms to trigger calendar entries and mail-merge emails. My non-techie coworkers don’t have to see any of the background spreadsheet to communicate with each other or keep me posted – it just works, and so they use it. Instant gratification.
My question is, do you have any suggestions for a work-around when we all get transferred to the “new and improved” Google forms? My understanding is that there will no longer be a way to enter numbers through a form (numbers get an automatic single quote inserted in front of them, thereby rendering them as text) in the new forms. In order to make scripts like this work, a user would have to go into the spreadsheet, delete the single quote in front of the date field, and then run the script from the sheet for every single entry. This kind of bypasses the whole point of the automation – it’s a potential roadblock for keeping my coworkers engaged and adds a whole new workload for me. Any ideas on a fix?
We work on a spreadsheet where the details and dates change regularly. Will this script automatically update the google calendar with revised dates? Thanks in advance.
@Gary – the updates only happen when you run the script actions. For auto-updates, you’d need to tweak the script.