Steps to update Google Calendar from Google spreadsheet
1. Choose Calendar for update.
2. Access the Calendar using Google Calendar ID
• var cal = CalendarApp.getCalendarById("xxxxx@gmail.com");
3. Identify the spreadsheet containing Calendar event data, e.g. sheet name = GetEvents
• var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GetEvents");
4. Specifiy cells that we want to grab data from
• var dataRange = mySpreadsheet.getRange("B2:C46");
5. Import data from the spreadsheet
var data = dataRange.getValues();
6. Create Calendar events from spreadsheet with sheet name = GetEvents
• myCalendar.createEvent(myTitle, myStartTime, myEndTime, {location: myLocation, description: myDescription});
Create a Google Sheet with 5 columns
Name of Event, Start date/time, End date/time, Location, Description
Input the details in the spreadsheet with sheet name = GetEvents
Create a Google App Script using the Google Calendar Service with the following code :
function SpreadsheetToCalendar()
{
// This function should be executed from the spreadsheet you want to export to the calendar
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GetEvents");
var myCalendar = CalendarApp.getCalendarById("xxxxxx@gmail.com");
// optional - delete existing Calendar events for Feb 2023 if needed
var events = myCalendar.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++)
{
events[i].deleteEvent();
}
// get data from sheet with name "GetEvents"
// assign data range
var dataRange = sheet.getRange("A2:E17");
var data = dataRange.getValues();
// process the data
for (i in data)
{
var row = data[i];
// assume that each row contains a date entry and a text entry
var myTitle = row[0]; // First column of row
var myStartTime = row[1]; //second column of row
var myEndTime = row[2]; // third column of row
var myLocation = row[3]; // fourth column of row
var myDescription = row[4]; // fifth column of row
// myCalendar.createAllDayEvent(theTitle, theDate);
// Logger.log(myTitle);
//Logger.log(myStartTime);
//Logger.log(myEndTime);
//Logger.log(myLocation);
myCalendar.createEvent(myTitle, myStartTime, myEndTime, {location: myLocation,description: myDescription});
}
}
//Reference https://developers.google.com/apps-script/reference/calendar/calendar#createEvent(String,Date,Date,Object)
//https://developers.google.com/apps-script/reference/calendar/calendar#parameters_7
//title String 活動標題
//startTime Date 事件開始的日期和時間
//endTime Date 活動結束的日期和時間
//options Object 指定進階參數的 JavaScript 物件，如下所示
//description String 活動的說明
//location String 活動地點
Run the App Script and the Google Calendar is updated using data from Google sheet with sheet name "GetEvents"
The
Comments