Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Google Spreadsheet to Calendar Script
// Script for adding events to a Google Calendars from a Google Spreadsheet
// A comms channel is defined for each separate calendar.
// Placing "Add" in a channel column will allow an event declared in a particular row to be added to the calendar.
// Once an event has been added to a calendar, its status is automatically changed from "Add" to "Added"
// Menu entries are automatically added to the Spreadsheet Menu that allow:
// -- bulk processing of all events and all channels - events are only processed if the control column does not contain "Added"
// -- channel processing; the control column is ignored, and for a specified channel events are added if "Add" is contained in the appropriate cell.
// Events cannot currently be removed or updated from the spreadsheet.
// If you set a column to "Add", channel process that column, set it to "Add" again and reprocess, you will get duplicate entries
// channelDetails lists the column names and calendar names for each channel
// use: [ spreadsheet_column_name, calendar_name ]
// the calendar_name is the name of the calendar you have set up in the same user account as the spreadsheet
var channelDetails=[
["YouTube","youtubeDemo"],
["iTunes","itunesDemo"],
["Broadcast","broadcastDemo"]
];
// eventDetails is used to identify the columns that describe event info
var eventDetails={
title:"Event", // the title of the event
desc:"URL", //the description of the event
datefrom:"Date From", //the start date of the event
dateto:"Date To", //the end date of the event
}
// controlDetails is used to identify columns that control "permissions"
var controlDetails={
added:"Added to Google (Y/N/Hold)" // column that describes whether a row has been processed in Bulk Add mode; if this column contains "Added" it will not be processed
}
//--------------- DO NOT WRITE BELOW THIS LINE
function caltestAddtoCals_concise(){
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);//need a routine to set active sheet by name
//go defensive
var channels=[];
var maxcols=sheet.getMaxColumns();
for (var j=0;j<channelDetails.length;j++){
var chName=channelDetails[j][0]; var calName=channelDetails[j][1];
channels[chName]={};
channels[chName].calName=calName;
channels[chName].cal=CalendarApp.openByName(calName);
}
var colList=[];
for (j=1;j<maxcols+1;j++){
var header= sheet.getRange(1, j, 1, 1).getValue();
if (channels[header]!=undefined) channels[header].col=j-1;
switch(header){
case controlDetails.added:colList["added"]=j-1;break;
case eventDetails.title:colList["title"]=j-1;break;
case eventDetails.desc:colList["desc"]=j-1;;break;
case eventDetails.datefrom:colList["datefrom"]=j-1;break;
case eventDetails.dateto:colList["dateto"]=j-1;break;
default:
}
}
var startRow = 2; // First row of data to process
var numRows = sheet.getMaxRows(); // Number of rows to process ??maxrows-1 getMaxRows
var dataRange = sheet.getRange(startRow, 1, numRows, maxcols); //?maxcols?
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var title = row[colList["title"]];
var desc=row[colList["desc"]];
var added = row[colList["added"]]; //could change this handler so we just check the campaign cols to see if "Added"?
var tstart = row[colList["datefrom"]]; if (typeof tstart!='object') tstart=new Date(tstart);
var tstop = row[colList["dateto"]]; if (typeof tstop!='object') tstop=new Date(tstart);
if (added!="Added") {
for (var channel in channels)
if (row[channels[channel].col]=="Add") {
channels[channel].cal.createEvent(title, tstart,tstop, {description:desc});
dataRange.getCell(parseInt(i)+1,channels[channel].col+1).setValue('Added');
}
var v = parseInt(i)+2; //need to set the '2' value properly by looking up row number/label transform?
if (title !="") sheet.getRange(v, 1, 1, 1).setValue("Added");
}
}
}
//maybe for the all calndars update, we carry the extra processing burden and run through all events for each calendar?
function caltestAddtoCal_concise(addCol,addCal){
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);//need a routine to set active sheet by name
var maxcols=sheet.getMaxColumns();
var cal=CalendarApp.openByName(addCal);
var colList=[];
for (j=1;j<maxcols+1;j++){
var header= sheet.getRange(1, j, 1, 1).getValue();
switch(header){
case controlDetails.added:colList["added"]=j-1;break;
case eventDetails.title:colList["title"]=j-1;break;
case eventDetails.desc:colList["desc"]=j-1;;break;
case eventDetails.datefrom:colList["datefrom"]=j-1;break;
case eventDetails.dateto:colList["dateto"]=j-1;break;
case addCol:colList["currcol"]=j-1;break;
default:
}
}
var startRow = 2; // First row of data to process
var numRows = sheet.getMaxRows(); // Number of rows to process ??maxrows-1 getMaxRows
var dataRange = sheet.getRange(startRow, 1, numRows, maxcols);
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var title = row[colList["title"]];
var desc=row[colList["desc"]];
var tstart = row[colList["datefrom"]]; if (typeof tstart!='object') tstart=new Date(tstart);
var tstop = row[colList["dateto"]]; if (typeof tstop!='object') tstop=new Date(tstop);
var currAdd=row[colList["currcol"]];
if (currAdd=="Add") {
cal.createEvent(title, tstart,tstop, {description:desc});
dataRange.getCell(parseInt(i)+1,colList["currcol"]+1).setValue('Added'); //+1 is offset for sheet numbering
}
}
}
/*
function caltestAddtoCal_broadcast(){ caltestAddtoCal_concise("Broadcast","broadcastDemo"); }
function caltestAddtoCal_itunes(){ caltestAddtoCal_concise("iTunes","itunesDemo"); }
function caltestAddtoCal_youtube(){ caltestAddtoCal_concise("YouTube","youtubeDemo"); }
//function caltestAddtoCal_openlearexplore(){ caltestAddtoCal_concise(addCol,addCal); }
//function caltestAddtoCal_openlearncore(){ caltestAddtoCal_concise(addCol,addCal); }
//function caltestAddtoCal_marketing(){ caltestAddtoCal_concise(addCol,addCal); }
//function caltestAddtoCal_media(){ caltestAddtoCal_concise(addCol,addCal); }
//function caltestAddtoCal_platform(){ caltestAddtoCal_concise(addCol,addCal); }
function onOpen_x() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Bulk Update Calendar", functionName: "caltestAddtoCals_concise"} ];
// Is it possible to define a function for the menu calls with variables that are in scope from the menu?
//for (var i=0;i<channelDetails.length;i++){
// var menutxt="Add to "+channelDetails[i][0]+" Calendar";
// menuEntries[menuEntries.length]={name: menutxt, functionName: function(){ caltestAddtoCal_concise(channelDetails[j][0],channelDetails[j][1]) }}
// }
//horrible special case hack:-(
menuEntries[menuEntries.length]= {name:"Add to Broadcast Calendar",functionName: "caltestAddtoCal_broadcast"};
menuEntries[menuEntries.length]= {name:"Add to iTunes Calendar",functionName: "caltestAddtoCal_itunes"};
menuEntries[menuEntries.length]= {name:"Add to YouTube Calendar",functionName: "caltestAddtoCal_youtube"};
// menuEntries[menuEntries.length]= {name:"Add to OpenLearn Explore Calendar",functionName: "caltestAddtoCal_openlearnexplore"};
// menuEntries[menuEntries.length]= {name:"Add to OpenLearn Core Calendar",functionName: "caltestAddtoCal_openlearncore"};
// menuEntries[menuEntries.length]= {name:"Add to Marketing Calendar",functionName: "caltestAddtoCal_marketing"};
// menuEntries[menuEntries.length]= {name:"Add to Media Explore Calendar",functionName: "caltestAddtoCal_media"};
// menuEntries[menuEntries.length]= {name:"Add to Platform Calendar",functionName: "caltestAddtoCal_platform"};
ss.addMenu("OUseful", menuEntries);
}
//*/
///testing - another fudge, to handle a max number of channel cols
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries=[ {name: "Bulk Update Calendar", functionName: "caltestAddtoCals_concise"} ];
//if you do not want the Bulk Update menu option, uncomment the following line:
//menuEntries= [];
var entries= (channelDetails.length >10)?10:channelDetails.length;
for (var i=0;i<entries;i++){
var fname= "caltestAddtoCal_"+i;
var cname= "Add to "+channelDetails[i][0]+"Calendar";
menuEntries[menuEntries.length] = {name: cname, functionName: fname};
}
ss.addMenu("OUseful", menuEntries);
}
function caltestAddtoCal_N(n){
caltestAddtoCal_concise(channelDetails[n][0],channelDetails[n][1]);
}
function caltestAddtoCal_0(){ caltestAddtoCal_N(0); }
function caltestAddtoCal_1(){ caltestAddtoCal_N(1); }
function caltestAddtoCal_2(){ caltestAddtoCal_N(2); }
function caltestAddtoCal_3(){ caltestAddtoCal_N(3); }
function caltestAddtoCal_4(){ caltestAddtoCal_N(4); }
function caltestAddtoCal_5(){ caltestAddtoCal_N(5); }
function caltestAddtoCal_6(){ caltestAddtoCal_N(6); }
function caltestAddtoCal_7(){ caltestAddtoCal_N(7); }
function caltestAddtoCal_8(){ caltestAddtoCal_N(8); }
function caltestAddtoCal_9(){ caltestAddtoCal_N(9); }
//Unused utility function
function getColNumber(colName){
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);//need a routine to set active sheet by name
var colNum=-1;
var maxcols=sheet.getMaxColumns();
for (j=1;j<maxcols+1;j++){
var header= sheet.getRange(1, j, 1, 1).getValue();
if (header==colName) colNum=j-1;
}
return colNum;
}​
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment