IT Notes : Using Google Apps Script to export Google Calendar events to Google Sheet

Steps :

1.    Open Google Calendar and export events to Google sheet 

2.    Create a 5 columns Google Sheet with sheet name "Feb 2023"  :

Date, Start time, End time, Time slot hours, Total hours for the week

3.     Apply the following format for columns :

Date : date (dd/mm/yyyy)

Start time : time (hh:mm:ss)

End time : time (hh:mm:ss)

Time slot hours : number

4.    Create a Google App Script using the Google Calendar API Service :

function MonthSchedule(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("Feb 2023");

  var cal = CalendarApp.getCalendarById("xxxxx@gmail.com");

  //用 getEvents(startTime, endTime) 來取得資料, e.g. Feb 2023

  var events = cal.getEvents(new Date("2/01/2023 12:00 AM"), new Date("2/28/2023 11:59 PM"));

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

    var title = events[i].getTitle();

    var start_time = events[i].getStartTime();

    var end_time = events[i].getEndTime();

    var loc = events[i].getLocation();

    var des = events[i].getDescription();

// Calculate the event duration in hours for non all day event

if (events[i].isAllDayEvent() == false) {

 var MyDuration = (end_time - start_time)/(1000*60*60);

 } 

    //sheet.getRange(i+2,1).setValue(title);

    sheet.getRange(i+2,1).setValue(start_time);

    sheet.getRange(i+2,2).setValue(start_time);

    sheet.getRange(i+2,3).setValue(end_time);

    sheet.getRange(i+2,4).setValue(MyDuration);

    //sheet.getRange(i+2,4).setValue(loc);

    //sheet.getRange(i+2,5).setValue(des);

  }

   Logger.log("Events have been added to the Spreadsheet");

}

5.    run the App Script and the Google Sheet is updated