Google Spreadsheet to Calendar Script

gSS2Cal.js

// 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;
}​