Skip to content

Instantly share code, notes, and snippets.

Embed
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
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.