Google Sheets Calender Export

gistfile1.md

The script below can be used to export your calender dates from google calender to google sheets.

Turns out google sheets allows you to write scripts as .gs files which is basically javascript with some specific features for querying and editing google docs files. Just like the developer mode in Excel but in a nicer language.

Original script I based this on was found here: http://blog.cloudbakers.com/blog/export-google-calendar-entries-to-a-google-spreadsheet. I edited it for my needs.

To use open a new file in google sheets, click on tools > script editor. Paste the code in and put your email address and the date range you want.

Select export_gcal_to_gsheet and click play.

function export_gcal_to_gsheet(){


//your calendar email address here
var mycal = "[email protected]";
var cal = CalendarApp.getCalendarById(mycal);


//put dates here
var events = cal.getEvents(new Date("October 09, 2015 00:00:00 CST"), new Date("October 24, 2015 23:59:59 CST"), {search: '-project123'});


var sheet = SpreadsheetApp.getActiveSheet();

var header = [[ "Week day", "Date", "Event Title", "Event Location" ]]
var range = sheet.getRange(1,1,1,4);
range.setValues(header);

  

for (var i=0;i<events.length;i++) {
  var row=i+2;

  var details=[[ getWeekDay(events[i].getStartTime()),  events[i].getStartTime() , events[i].getTitle() , events[i].getLocation() ]];
  var range=sheet.getRange(row,1,1,4);
  range.setValues(details);
 }
}


function getWeekDay(date){
  var dayNumber = date.getDay();
  var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];
  return days[dayNumber];
}