API Connector Documentation

Print

Import Google Calendar Data to Google Sheets

Last Updated OnJanuary 3, 2024
byAna
< Knowledge Base

In this guide, we’ll walk through how to pull Google Calendar API data data directly into Google Sheets, using the API Connector add-on for Sheets.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to the Google Calendar API

The easiest way to get started with the Google Calendar API is through API Connector's built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select Google Calendar from the drop-down list of applications
    googlecalendar-application
  3. Under Authorization, click Connect to Google Calendar
    googlecalendar-authorization
  4. You will be directed to google.com and asked to allow API Connector to access your calendars. Click Allow.
    google-calendar-img2
  5. You'll then be returned to your Google Sheet, and can verify that your Google Calendar connection is active in the Connections screen.

Part 2: Pull Data from Google Calendar to Sheets

Now that we’re connected, let’s pull some data into Sheets.

  1. Select the /calendars/{calendar_id}/events endpoint, , which allows us to retrieve the events on our calendars
    googlecalendar-endpoints
  2. Under the calendar_id parameter, enter the word primary. This will fetch data from your primary calendar (if you prefer to fetch data from a different calendar, enter the email address associated with that calendar, e.g. [email protected])
    googlecalendar-parameters
  3. Optionally populate other request parameters to customize your request, e.g. date ranges and keyword searches.
  4. Choose a destination sheet, name your request, and hit Run. A moment later you’ll see the response data in your sheet.
    googlecalendar-response

Part 3: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in the API documentation. Here's an example setup:

  • Application: Custom
  • Method: GET
  • Request URL: https://www.googleapis.com/calendar/v3/calendars/primary/events?timeMax=2022-07-20T07:00:00:000Z&timeMin=2022-01-01T00:00:00:000Z
  • OAuth: Google Calendar


googlecalendar-custom

Part 4: Handle Pagination

By default, the Google Calendar API will send back just 250 events. To retrieve more, set the maxResults parameter to 2500.

If you need more than 2500 records, you can cycle through multiple pages of 2500 using API Connector's pagination handling feature, like this:

  • Pagination type: cursor
  • Next token parameter: pageToken
  • Next token path: nextPageToken
  • Run until: choose when to stop running the request
    googlecalendar-pagination
Google Calendar doesn't send back a nextPageToken for requests using the timeMin or timeMax parameters. For those requests, change the start/end dates to retrieve additional records.

Part 5: API Documentation

Official API documentation: https://developers.google.com/calendar/api

9 thoughts on “Import Google Calendar Data to Google Sheets”

  1. I'm trying to import two specific calendars into Google sheets. Under request parameters, I do not have the calendar_id field.

    When I attempt to run, I get the following error:
    1) : Completed with errors
    - We received an error from googleapis.com (403) show response

    Reply

  2. Is there a way to make a formula so that specific cell in google sheets generates and displays the event name that's on a specific google calendar at a specific date and time.

    I'm imagining a formula that would be something
    =/calendar event (calendar id) (cell with date and time)

    Reply

    • Sure, you can use the IMPORTAPI custom function for that. Here are some steps to follow:
      1) Save a custom request to https://www.googleapis.com/calendar/v3/calendars/primary/events with OAuth = Google Calendar. Filter out all fields but the event name via the field editor, and tick the box to remove headers.
      2) Enter your date & time of interest into cell A1
      3) In cell B1, convert your timestamp to Google Calendar's required format with this function: =text(A1,"yyyy-mm-ddThh:mm:ssZ")
      4) In cell C1, add the following function: =IMPORTAPI("Events","https://www.googleapis.com/calendar/v3/calendars/primary/events?timeMax="&B1&"&timeMin="&B1

      Now every time you enter a date into cell A1, the custom function will run and fetch the event for that time.

      Reply

  3. I am able to set up the google calendar integration but when the underlying calendar changes and I rerun it no updates happen. Am I doing something wrong?

    Reply

    • Can you please click Edit fields and check if any fields have been filtered out? Or, if the fields are all there but records are missing, it could be related to pagination -- if you're only seeing 250 records, please change the maxResults parameter as shown here.

      Reply

      • There are no filters and the results do not exceed 250 rows. It is a Little League schedule that updates fairly often but probably only has 40 rows in it. When I "re-run" to sync it pulls the same stale data despite the underlying subscribed calendar showing the newer data

      • Do you mean you’re adding new events and they’re not showing up? In that case please make sure you haven’t set an end date before the cutoff time. Or do you mean that you’re editing an existing event? I tested and didn’t have any issue fetching updated event data, but I did find some online discussion showing that not all elements of a changed event can be retrieved via API: https://issuetracker.google.com/issues/180946049. If none of these address the issue please feel free to contact support so I can test your request on this side.

Leave a Comment

Jump To