Export Google Calendar Events to a Spreadsheet｜Easily Get Schedules for Multiple Users with Google Apps Script

2025年7月22日2025年7月24日

このページにはプロモーションが含まれています。

Google Calendar is an incredibly useful tool that allows you to view multiple people’s schedules all in one place.

However, when your team has many members or a wide variety of schedules, it can sometimes become difficult to view everything clearly.

For this reason, it’s often easier to manage schedules by displaying them as a list in a Google Spreadsheet.

In this article, I’ll show you how to use Google Apps Script to retrieve events from multiple Google Calendars all at once and organize them in a single spreadsheet.

By consolidating multiple users’ schedules into one sheet, it becomes much easier to coordinate and adjust plans.

For those who want to save time and start using this right away, we also offer a ready-to-use (paid) template with the script already set up.

For more details, please see the article below.

Googleカレンダー予定一覧出力テンプレート｜スプレッドシート×GAS | 合同会社橋爪オフィス

Sample Output

Retrieve and list schedules from multiple members’ Google Calendars
View each member’s events for a selected date range

With Google Apps Script, you can organize information from each Google Calendar into a spreadsheet list.

Fields Included:

Calendar ID
Title
Description
Start Date
Start Time
End Date
End Time
Location
Guest Information

Preparation Required

Google Calendar Access Permissions

To retrieve schedules from another user’s calendar, you need to be granted viewing access by that user.

Here’s how to set up calendar access permissions:

How to Set Access Permissions

Open Google Calendar and click the settings icon in the top right corner of the screen.

In the menu on the left, go to “Settings for my calendars” and click on the calendar you want to use.

On the calendar’s detail settings page, scroll down until you see “Share with.”

Click “Add people or groups” within that section.

Set the permission level to “See all event details” before sharing.

The shared calendar will appear under “Other calendars.”
If it does not show up even after sharing, try the following steps:

Click the sharing notification link sent by email.

Click the “+” next to “Other calendars”
Select “Subscribe to calendar”
Click “Add calendar”
Enter the Calendar ID

How to Check the Calendar ID

You can find the “Calendar ID” on the settings page of the calendar you want to access.
Usually, the Calendar ID is in the form of an email address, but for company or shared calendars, a different type of ID may be displayed.

How to Find Your Google Calendar ID

Open Google Calendar and click the settings icon in the top right corner.

In the menu on the left, go to “Settings for my calendars” and click on the calendar you want to use.

On the calendar’s detail settings page, scroll down and you will see the “Calendar ID.”

Typically, the Calendar ID is in the format “xxx@gmail.com” or “xxxx@group.calendar.google.com.”

Step-by-Step Instructions

In the script, you specify multiple calendar IDs. The script then retrieves events from each calendar within the specified period and outputs them to the spreadsheet.

STEP

Prepare Your Spreadsheet

Entering Calendar IDs

Starting from cell B1, enter the Google Calendar IDs (usually email addresses) that you want to retrieve events from, moving to the right (B1, C1, D1, etc.).
If you want to manage multiple calendars at once, enter each calendar ID in B1, C1, D1, and so on.
You can find the Calendar ID on the settings page of each calendar.

Set the Retrieval Period

Cell B2: Enter the start date for retrieving events (e.g., 01/11/2024).
Cell B3: Enter the end date for retrieving events (e.g., 08/11/2024).

With this setup, only events within the specified period will be exported to the spreadsheet.

Setting the Sheet Name

In this example, set the sheet name of your spreadsheet to “Schedule List.”
The script will use this sheet name when referencing the sheet.

STEP

Open the Apps Script Editor

Open your Google Spreadsheet, click on “Extensions” in the menu, and select “Apps Script” to open the editor.

STEP

Create the Script

function myFunction(){

}

is included from the beginning, so delete it and paste the previous script.

function exportCalendarEvents() {
  const sheetName = "Schedule List"; // Name of the sheet to output data
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  // Error check: Sheet existence
  if (!sheet) {
    Logger.log(`Error: The sheet "${sheetName}" was not found. Please create the sheet in your spreadsheet.`);
    return;
  }

  // Get start and end dates
  const startDate = new Date(sheet.getRange("B2").getValue());
  const endDate = new Date(sheet.getRange("B3").getValue());

  // Get calendar IDs from B1 to the right
  const calendarIds = sheet.getRange("B1:1").getValues()[0].filter(id => id);

  if (calendarIds.length === 0) {
    Logger.log("Error: No calendar IDs entered. Please enter at least one calendar ID starting from cell B1.");
    return;
  }

  // Clear output area (rows 5 and below)
  const lastRow = sheet.getLastRow();
  if (lastRow >= 5) {
    sheet.getRange("5:" + lastRow).clear(); // Clear only from row 5 onward
  }

  // Add header in row 5
  const headerRange = sheet.getRange(5, 1, 1, 9);
  headerRange.setValues([[
    "Calendar ID",
    "Title",
    "Description",
    "Start Date",
    "Start Time",
    "End Date",
    "End Time",
    "Location",
    "Guests"
  ]]);

  // Center header text and set background color (Light Green 3)
  headerRange.setHorizontalAlignment("center");
  headerRange.setBackground("#b7e1cd");

  // Function to get English day of week
  function getDayOfWeek(date) {
    const dayNames = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"];
    return dayNames[date.getDay()];
  }

  let eventRows = [];

  // Retrieve events for each calendar ID
  calendarIds.forEach(calendarId => {
    const calendar = CalendarApp.getCalendarById(calendarId);
    if (!calendar) {
      Logger.log(`Error: Calendar ID "${calendarId}" was not found.`);
      return;
    }

    const events = calendar.getEvents(startDate, endDate);

    events.forEach(event => {
      const startDateTime = event.getStartTime();
      const endDateTime = event.getEndTime();

      // Add day of the week in English to the date
      const startDateFormatted = Utilities.formatDate(startDateTime, Session.getScriptTimeZone(), "dd/MM/yyyy") + ` (${getDayOfWeek(startDateTime)})`;
      const endDateFormatted = Utilities.formatDate(endDateTime, Session.getScriptTimeZone(), "dd/MM/yyyy") + ` (${getDayOfWeek(endDateTime)})`;

      // Get guest emails, separated by commas
      const guestList = event.getGuestList().map(guest => guest.getEmail()).join(", ");

      eventRows.push([
        calendarId,
        event.getTitle(),
        event.getDescription(),
        startDateFormatted,
        Utilities.formatDate(startDateTime, Session.getScriptTimeZone(), "HH:mm"),
        endDateFormatted,
        Utilities.formatDate(endDateTime, Session.getScriptTimeZone(), "HH:mm"),
        event.getLocation(),
        guestList
      ]);
    });
  });

  // Output events from row 6 downward
  if (eventRows.length > 0) {
    sheet.getRange(6, 1, eventRows.length, 9).setValues(eventRows);
  }

  Logger.log(`A total of ${eventRows.length} events have been exported to the sheet "${sheetName}".`);
}

function exportCalendarEvents() {
  const sheetName = "Schedule List"; // Name of the sheet to output data
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  // Error check: Sheet existence
  if (!sheet) {
    Logger.log(`Error: The sheet "${sheetName}" was not found. Please create the sheet in your spreadsheet.`);
    return;
  }

  // Get start and end dates
  const startDate = new Date(sheet.getRange("B2").getValue());
  const endDate = new Date(sheet.getRange("B3").getValue());

  // Get calendar IDs from B1 to the right
  const calendarIds = sheet.getRange("B1:1").getValues()[0].filter(id => id);

  if (calendarIds.length === 0) {
    Logger.log("Error: No calendar IDs entered. Please enter at least one calendar ID starting from cell B1.");
    return;
  }

  // Clear output area (rows 5 and below)
  const lastRow = sheet.getLastRow();
  if (lastRow >= 5) {
    sheet.getRange("5:" + lastRow).clear(); // Clear only from row 5 onward
  }

  // Add header in row 5
  const headerRange = sheet.getRange(5, 1, 1, 9);
  headerRange.setValues([[
    "Calendar ID",
    "Title",
    "Description",
    "Start Date",
    "Start Time",
    "End Date",
    "End Time",
    "Location",
    "Guests"
  ]]);

  // Center header text and set background color (Light Green 3)
  headerRange.setHorizontalAlignment("center");
  headerRange.setBackground("#b7e1cd");

  // Function to get English day of week
  function getDayOfWeek(date) {
    const dayNames = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"];
    return dayNames[date.getDay()];
  }

  let eventRows = [];

  // Retrieve events for each calendar ID
  calendarIds.forEach(calendarId => {
    const calendar = CalendarApp.getCalendarById(calendarId);
    if (!calendar) {
      Logger.log(`Error: Calendar ID "${calendarId}" was not found.`);
      return;
    }

    const events = calendar.getEvents(startDate, endDate);

    events.forEach(event => {
      const startDateTime = event.getStartTime();
      const endDateTime = event.getEndTime();

      // Add day of the week in English to the date
      const startDateFormatted = Utilities.formatDate(startDateTime, Session.getScriptTimeZone(), "dd/MM/yyyy") + ` (${getDayOfWeek(startDateTime)})`;
      const endDateFormatted = Utilities.formatDate(endDateTime, Session.getScriptTimeZone(), "dd/MM/yyyy") + ` (${getDayOfWeek(endDateTime)})`;

      // Get guest emails, separated by commas
      const guestList = event.getGuestList().map(guest => guest.getEmail()).join(", ");

      eventRows.push([
        calendarId,
        event.getTitle(),
        event.getDescription(),
        startDateFormatted,
        Utilities.formatDate(startDateTime, Session.getScriptTimeZone(), "HH:mm"),
        endDateFormatted,
        Utilities.formatDate(endDateTime, Session.getScriptTimeZone(), "HH:mm"),
        event.getLocation(),
        guestList
      ]);
    });
  });

  // Output events from row 6 downward
  if (eventRows.length > 0) {
    sheet.getRange(6, 1, eventRows.length, 9).setValues(eventRows);
  }

  Logger.log(`A total of ${eventRows.length} events have been exported to the sheet "${sheetName}".`);
}

Script Overview

Getting the Sheet

function exportCalendarEvents() {
  const sheetName = "Schedule List"; // Name of the sheet to output data
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  // Error check: Sheet existence
  if (!sheet) {
    Logger.log(`Error: The sheet "${sheetName}" was not found. Please create the sheet in your spreadsheet.`);
    return;
  }

function exportCalendarEvents() {
  const sheetName = "Schedule List"; // Name of the sheet to output data
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  // Error check: Sheet existence
  if (!sheet) {
    Logger.log(`Error: The sheet "${sheetName}" was not found. Please create the sheet in your spreadsheet.`);
    return;
  }

The variable sheetName is set to the name of the output sheet (in this case, “Schedule List”).
If the sheet does not exist, an error message will be logged and the script will stop.

Retrieving Start Date, End Date, and Calendar IDs

  // Get start and end dates
  const startDate = new Date(sheet.getRange("B2").getValue());
  const endDate = new Date(sheet.getRange("B3").getValue());

  // Get calendar IDs from B1 to the right
  const calendarIds = sheet.getRange("B1:1").getValues()[0].filter(id => id);

  if (calendarIds.length === 0) {
    Logger.log("Error: No calendar IDs entered. Please enter at least one calendar ID starting from cell B1.");
    return;
  }

  // Get start and end dates
  const startDate = new Date(sheet.getRange("B2").getValue());
  const endDate = new Date(sheet.getRange("B3").getValue());

  // Get calendar IDs from B1 to the right
  const calendarIds = sheet.getRange("B1:1").getValues()[0].filter(id => id);

  if (calendarIds.length === 0) {
    Logger.log("Error: No calendar IDs entered. Please enter at least one calendar ID starting from cell B1.");
    return;
  }

The script gets the start date from cell B2 and the end date from cell B3 to specify the date range.
It then retrieves the calendar IDs entered from cell B1 to the right.
If no calendar IDs are entered, an error message is logged and the script stops.

Clearing Data and Setting Headers

  // Clear output area (rows 5 and below)
  const lastRow = sheet.getLastRow();
  if (lastRow >= 5) {
    sheet.getRange("5:" + lastRow).clear(); // Clear only from row 5 onward
  }

  // Add header in row 5
  const headerRange = sheet.getRange(5, 1, 1, 9);
  headerRange.setValues([[
    "Calendar ID",
    "Title",
    "Description",
    "Start Date",
    "Start Time",
    "End Date",
    "End Time",
    "Location",
    "Guests"
  ]]);

  // Center header text and set background color (Light Green 3)
  headerRange.setHorizontalAlignment("center");
  headerRange.setBackground("#b7e1cd");

  // Clear output area (rows 5 and below)
  const lastRow = sheet.getLastRow();
  if (lastRow >= 5) {
    sheet.getRange("5:" + lastRow).clear(); // Clear only from row 5 onward
  }

  // Add header in row 5
  const headerRange = sheet.getRange(5, 1, 1, 9);
  headerRange.setValues([[
    "Calendar ID",
    "Title",
    "Description",
    "Start Date",
    "Start Time",
    "End Date",
    "End Time",
    "Location",
    "Guests"
  ]]);

  // Center header text and set background color (Light Green 3)
  headerRange.setHorizontalAlignment("center");
  headerRange.setBackground("#b7e1cd");

All data from row 5 onward is cleared to remove the previous results.
Headers are placed in row 5, and items such as Calendar ID are automatically added.
The header row is center-aligned and the background color is set to “Light Green 3.”

Day of the Week Conversion & Preparing to Store Events

  // Function to get English day of week
  function getDayOfWeek(date) {
    const dayNames = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"];
    return dayNames[date.getDay()];
  }

  let eventRows = [];

  // Function to get English day of week
  function getDayOfWeek(date) {
    const dayNames = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"];
    return dayNames[date.getDay()];
  }

  let eventRows = [];

The getDayOfWeek function retrieves the Japanese day of the week corresponding to each date.
An array called eventRows is created to store the event information.

Retrieving Schedules

  // Retrieve events for each calendar ID
  calendarIds.forEach(calendarId => {
    const calendar = CalendarApp.getCalendarById(calendarId);
    if (!calendar) {
      Logger.log(`Error: Calendar ID "${calendarId}" was not found.`);
      return;
    }

    const events = calendar.getEvents(startDate, endDate);

  // Retrieve events for each calendar ID
  calendarIds.forEach(calendarId => {
    const calendar = CalendarApp.getCalendarById(calendarId);
    if (!calendar) {
      Logger.log(`Error: Calendar ID "${calendarId}" was not found.`);
      return;
    }

    const events = calendar.getEvents(startDate, endDate);

The script retrieves each calendar ID from calendarIds one by one and converts it into a calendar object.
If a calendar ID does not exist, an error is logged and that calendar is skipped.
The script then retrieves all events that occur between the specified start and end dates.

Writing Schedules to the Sheet

      // Get guest emails, separated by commas
      const guestList = event.getGuestList().map(guest => guest.getEmail()).join(", ");

      eventRows.push([
        calendarId,
        event.getTitle(),
        event.getDescription(),
        startDateFormatted,
        Utilities.formatDate(startDateTime, Session.getScriptTimeZone(), "HH:mm"),
        endDateFormatted,
        Utilities.formatDate(endDateTime, Session.getScriptTimeZone(), "HH:mm"),
        event.getLocation(),
        guestList
      ]);
    });
  });

  // Output events from row 6 downward
  if (eventRows.length > 0) {
    sheet.getRange(6, 1, eventRows.length, 9).setValues(eventRows);
  }

  Logger.log(`A total of ${eventRows.length} events have been exported to the sheet "${sheetName}".`);
}

      // Get guest emails, separated by commas
      const guestList = event.getGuestList().map(guest => guest.getEmail()).join(", ");

      eventRows.push([
        calendarId,
        event.getTitle(),
        event.getDescription(),
        startDateFormatted,
        Utilities.formatDate(startDateTime, Session.getScriptTimeZone(), "HH:mm"),
        endDateFormatted,
        Utilities.formatDate(endDateTime, Session.getScriptTimeZone(), "HH:mm"),
        event.getLocation(),
        guestList
      ]);
    });
  });

  // Output events from row 6 downward
  if (eventRows.length > 0) {
    sheet.getRange(6, 1, eventRows.length, 9).setValues(eventRows);
  }

  Logger.log(`A total of ${eventRows.length} events have been exported to the sheet "${sheetName}".`);
}

The script formats the start and end dates to include the Japanese day of the week, improving readability.
Guest information (the invited email addresses) is retrieved, separated by commas, and combined into a single field.
The event information is then added to the eventRows array.

Outputting the Data

  // Output events from row 6 downward
  if (eventRows.length > 0) {
    sheet.getRange(6, 1, eventRows.length, 9).setValues(eventRows);
  }

  Logger.log(`A total of ${eventRows.length} events have been exported to the sheet "${sheetName}".`);
}

  // Output events from row 6 downward
  if (eventRows.length > 0) {
    sheet.getRange(6, 1, eventRows.length, 9).setValues(eventRows);
  }

  Logger.log(`A total of ${eventRows.length} events have been exported to the sheet "${sheetName}".`);
}

The event information is written in bulk starting from row 6.
If there is no data, nothing will be output.
The number of exported events is recorded in the log, making it easy to check the results of the script.

STEP

Saving the Script