Google Sheets Calender Export

gistfile1.md

Forked and modified from ljones140's code for exporting calendar dates from google calendar to google sheets. Please link to this page if you like this. Thank you!

Modifications from original code:

• Automatically detects the Google account you use so no need to hardcode your email address
• Automatically scans cells B3 and D3 for the starting and end dates, respectively

This code is part of tutorial here:

===from ljones140's description

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 = Session.getActiveUser().getEmail();
var cal = CalendarApp.getCalendarById(mycal);

//put dates here
var events = cal.getEvents(new SpreadsheetApp.getActiveSheet().getRange('B3').getValue(), new SpreadsheetApp.getActiveSheet().getRange('D3').getValue(), {search: '-project123'});

var sheet = SpreadsheetApp.getActiveSheet();

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

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

  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];
}