Objective : This guide demonstrates how to use Google Apps Script to pull data from Google Calendar and display them in a Google Sheet. This process allows you to automate the retrieval of calendar events and manage them directly within a spreadsheet.
Create a New Google Sheet: Open Google Sheets and create a new spreadsheet or open an existing one where you want the calendar data to be displayed.
Open Apps Script: In your Google Sheet, click on Extensions > Apps Script.
Insert the Script: In your Apps Script project, you’ll have two files: Code.gs and props.gs. Delete any existing code in the script editor and replace it with the code snippet provided below. Copy the provided code snippet for Code.gs and props.gs into their respective files.
props.gs
const emailIds = [
'email@gmail.com'
];
Code.gs
function fetchCalendarData() {
// Get the active sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.clear(); // Clear any previous content
sheet.appendRow(["Meeting ID", "Name", "Attendees", "Duration", "Video Link"]); // Set headers
// Loop through each email ID
emailIds.forEach(email => {
const events = CalendarApp.getCalendarById(email).getEvents(new Date('start_date'), new Date());
// Loop through events and write them to the sheet
events.forEach(event => {
const eventId = event.getId();
const eventName = event.getTitle();
const attendees = event.getGuestList().map(guest => guest.getEmail()).join(', ');
const duration = (event.getEndTime() - event.getStartTime()) / (1000 * 60);
let videoLink = "";
const description = event.getDescription();
if (description) {
const meetLinkRegex = /https:\/\/calendly\.com\/events\/[\w-]+\/google_meet/i; // Regex to match Video link
const match = description.match(meetLinkRegex);
if (match) {
videoLink = match[0];
}
}
sheet.appendRow([eventId, eventName, attendees, duration, videoLink]);
});
});
Logger.log("Calendar data has been fetched and updated in the sheet.");
}
Update Email IDs: In props.gs, add the email ID of the calendar you want to pull data from. You can adjust the date range by modifying the start_date in the code.gs file.
Execute the script: You might be prompted to authorize the script to access your calendar and spreadsheet. After running the script, your Google Sheet should populate with the event data from the specified Google Calendar.
By following these steps, you can easily automate the process of pulling calendar data into your Google Sheets, making it easier to manage and analyze your schedule.
