{"id":22555,"date":"2025-07-24T23:41:49","date_gmt":"2025-07-24T14:41:49","guid":{"rendered":"https:\/\/84office.jp\/?p=22555"},"modified":"2025-07-24T23:41:50","modified_gmt":"2025-07-24T14:41:50","slug":"google-calendar-free-time-extraction","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/google-calendar-free-time-extraction\/","title":{"rendered":"How to Find Common Free Time in Google Calendar: Manage Shared Schedules with Google Apps Script"},"content":{"rendered":"
<\/div>\n

Have you ever found it time-consuming to search for common free time when coordinating schedules within your team?
Even though Google Calendar allows you to view multiple calendars at once, comparing everyone\u2019s schedules side by side can quickly become confusing.<\/p>\n\n\n\n

To solve this, I\u2019ll show you how to use Google Apps Script to automatically extract shared free time slots from each member\u2019s Google Calendar and manage them in a Google Spreadsheet.<\/p>\n\n\n\n

With this method, scheduling meetings and coordinating team events becomes much easier and more efficient.<\/p>\n\n\n\n

\n

If you want to save even more time, I also offer a ready-to-use, pre-configured template (paid) in a separate article\u2014perfect for those who want to get started right away.<\/p>\n\n\n\n

For more details, check out the article below.<\/p>\n\n\n

\t\t\t\n\t\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/svg>\t\t\t\t<\/span>\n\t\t\t\tGoogle\u30ab\u30ec\u30f3\u30c0\u30fc\u7a7a\u304d\u6642\u9593\u62bd\u51fa\u30c6\u30f3\u30d7\u30ec\u30fc\u30c8\uff5c\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8 \u00d7 GAS<\/span>\n\t\t\t<\/a>\n\t\t<\/div><\/div><\/div>\n\n\n\n

Example Output<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n

The script will display a list of shared free time slots within your selected date range.<\/p>\n\n\n\n

For each day between your specified start and end dates, the spreadsheet will show the common available time slots for all participants.<\/p>\n\n\n\n

Each row contains the date and a list of free time periods when everyone is available, making it easy to see which times do not overlap with any existing events across multiple Google Calendars.<\/p>\n\n\n\n

This allows you to quickly find common availability for meetings or team appointments, streamlining your scheduling process.<\/p>\n\n\n\n

You can also customize the interval (in minutes) for checking available time slots by entering a value in cell B6. In the example shown, free time is displayed in 20-minute increments.<\/p>\n\n\n\n

What You Need to Prepare<\/h2>\n\n\n\n

Before creating the Google Apps Script, you\u2019ll need to complete the following two setup steps.<\/p>\n\n\n\n

If you\u2019ve already done these, feel free to move on to the next section.<\/p>\n\n\n\n

Setting Google Calendar Sharing Permissions<\/h3>\n\n\n\n
\n
How to Share Your Google Calendar<\/span><\/i><\/i><\/span><\/summary>
\n
\"\"<\/figure>\n<\/div>\n\n\n

Open Google Calendar<\/a> and click the settings icon in the top right corner.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

In the left-hand menu, select Settings for My Calendars and click on the calendar you want to use.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

Scroll down on the calendar settings page to find Share with specific people or groups.<\/p>\n\n\n\n

Click Add people or groups within this section.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

Set the permission level to See all event details and share the calendar.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

The shared calendar will be added under Other calendars in Google Calendar.<\/p>\n\n\n\n

Tip:<\/strong>
If you don\u2019t see the shared calendar after it\u2019s been shared, try the following:<\/p>\n\n\n\n

Go to Other calendars<\/strong> \u2192 +<\/strong> \u2192 Subscribe to calendar<\/strong> \u2192 Add calendar<\/strong> and enter the Calendar ID.<\/p>\n\n\n\n

Click the sharing notification link you received via email.<\/p>\n<\/div><\/details>\n<\/div>\n\n\n\n

How to Find Your Google Calendar ID<\/h3>\n\n\n\n
\n
How to Find Your Google Calendar ID<\/span><\/i><\/i><\/span><\/summary>
\n
\"\"<\/figure>\n<\/div>\n\n\n

Open Google Calendar<\/a> and click the settings icon in the top right corner.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

In the left-hand menu, select Settings for My Calendars and choose the calendar you want to use.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

On the calendar settings page, scroll down to find the Calendar ID section.<\/p>\n\n\n\n

The Calendar ID is usually in the format xxx@gmail.com<\/code> or xxxx@group.calendar.google.com<\/code>.<\/p>\n<\/div><\/details>\n<\/div>\n\n\n\n

Step-by-Step Instructions<\/h2>\n\n\n\n
\n
STEP<\/span><\/div>
Prepare Your Spreadsheet<\/div>
\n
\"\"<\/figure>\n<\/div>\n\n\n

Open a new Google Spreadsheet and enter the following:<\/p>\n\n\n\n

Input Fields<\/th><\/tr><\/thead>
B1<\/td>Google Calendar ID(s) you want to check
(If you have multiple calendars, enter the IDs from B1 to the right)<\/td><\/tr>
B2<\/td>Start date of the period to check<\/td><\/tr>
B3<\/td>End date of the period to check<\/td><\/tr>
B4<\/td>Start time of available slots<\/td><\/tr>
B5<\/td>End time of available slots<\/td><\/tr>
B6<\/td>Time interval (minutes)
(For example, entering \u201c30\u201d will display available time slots in 30-minute increments)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

For this example, we\u2019ll name the sheet Check Availability.
(The script will refer to this sheet by name.)<\/p>\n\n\n\n

    \n
  • Enter the start time (cell B4) and end time (cell B5) for the free time slots as text strings (e.g., 09:00), not as numbers or Google Sheets\u2019 time format.<\/li>\n\n\n\n
  • If you use only numbers or the default time format, the script may not read them correctly.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n
    STEP<\/span><\/div>
    Open the Apps Script Editor<\/div>
    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    Open your Google Spreadsheet, click on \u201cExtensions\u201d in the menu, and select \u201cApps Script\u201d to open the editor.<\/p>\n<\/div><\/div>\n\n\n\n

    STEP<\/span><\/div>
    Create the Script<\/strong><\/strong><\/strong><\/div>
    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    function myFunction(){<\/p>\n\n\n\n

    }<\/p>\n\n\n\n

    is included from the beginning, so delete it and paste the previous script.<\/p>\n\n\n\n