my code stock.com

John Galve

Google Apps: Google Spreadsheet to Google Calendar
by John Galve

Publish schedule from Google Spreadsheet to Google Calendar

Snippet options

Download: Download snippet as google-apps-google-spreadsheet-to-google-calendar.js.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

function caltest1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = sheet.getSheetByName("sheet name");
  var startRow = 2;  // First row of data to process
  var numRows = mainSheet.getMaxRows();
  var headerRows = 1
  var range = mainSheet.getDataRange();
  var data = range.getValues();
  //var row2 = 1;
  
  var calid = " "; // Calendar ID
  var cal = CalendarApp.getCalendarById(calid); // Get the id of calendar that will be use
  var timeZone = cal.getTimeZone();
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    
    var title = row[1];           
    var tstart = row[14]; // Date Start
    var date = new Date(tstart)
   
    //return new Date(tt.getFullYear(), myDate.getMonth(), myDate.getDate());
    var tstop = row[15]; // Date End
    var loc = row[10]; 
    var desc = row[7];
    var status = row[0];
    var id = row[5];     // Event ID. Must have to check for duplicate events
    try {
                   // Check if event already exists, update it if it does
                   var event = cal.getEventSeriesById(id);
                   event.deleteEventSeries();
                   row[5] = '';  // Remove event ID    
        }
   catch (e) {
  // do nothing - we just want to avoid the exception when event doesn't exist
        }
    if(!!tstart) // must have time start and time end
      if(!!tstop){
   if(status!=="TBA"){
   //var newEvent = cal.createAllDayEventSeries(title, new Date(tstart),  CalendarApp.newRecurrence().addDailyRule().until(new Date(tstop)), {description:desc, location:loc});
     var description = Utilities.formatString( '%s from %s to %s', title, dateString_( tstart, timeZone ), dateString_( tstop, timeZone ));
     var newEvent = cal.createEventFromDescription(description);
     newEvent.setDescription(desc);
     newEvent.setLocation(loc);
     row[5] = newEvent.getId();  // Update the data array with event ID
   
    }
    }
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}



/**
Create a tab in the spreadsheet
**/
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Publish to Calendar", functionName: "caltest1"} ];
  ss.addMenu("Publish", menuEntries);
}

function dateString_(date, timeZone ) {
 // format like 21 Apr 2013
 var format = ' dd MMM yyyy';
 var str = Utilities.formatDate(date, timeZone, format);
 return str;
}

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.