AllCalendars

Create, update, delete, and query all calendar events in your Google Account.

Table-Specific Information

This is an example on how all calendar events in your account are exposed in a single table.

Select

Query events of all calendars.

SELECT * FROM [AllCalendars]

Insert

Create a new event in a certain calendar. At least StartDateTime, EndDateTime and CalendarId must be specified.

INSERT INTO [AllCalendars] (Summary, Description, StartDateTime, EndDateTime, CalendarId) VALUES ('Great Event', 'Description for event', '8/27/2017', '8/28/2017', '[email protected]')

You can create an All-Day event by setting the StartDate and EndDate columns to the actual start and end dates of the event.

INSERT INTO [AllCalendars] (Summary, Description, StartDate, EndDate, CalendarId) VALUES ('Google Cal Test Event 1', 'Example All Day Event', '2017-01-01', '2017-01-09', '[email protected]')

You can create a Time Limited event by setting the StartDateTime and EndDateTime columns to the actual start and end dates and times of the event.

INSERT INTO [AllCalendars] (Summary, Description, StartDateTime, EndDateTime, CalendarId) VALUES ('Google Cal Test Event 2', 'Example Time Limited Event', '2017-09-05T22:13:03', '2017-09-11T13:24:10', '[email protected]')

Update

Update details of a specific event. At least the Id of the event being updated must be specified.

UPDATE AllCalendars SET Summary = 'Test Event' WHERE id = '6bjelf33p0al4d8ei5ft5ghqjs' AND CalendarId = '[email protected]'

You can update a Time Limited event to an All-Day one by setting the StartDate and EndDate columns to the actual start and end dates of the event, and the StartDateTime and EndDateTime columns to Null-s.

UPDATE [AllCalendars] SET StartDate = '2017-09-05', EndDate = '2017-09-11', StartDateTime = NULL, EndDateTime = NULL WHERE Id = '8ba774m3anenroqcepfi7ka6ok' AND CalendarId = '[email protected]'

You can update an All-Day event to a Time Limited one by setting the StartDateTime and EndDateTime columns to the actual start and end dates and times of the event, and the StartDate and EndDate columns to Null-s.

UPDATE [AllCalendars] SET StartDate = NULL, EndDate = NULL, StartDateTime = '2017-09-05T22:13:03', EndDateTime = '2017-09-11T13:24:10' WHERE Id = '8ba774m3anenroqcepfi7ka6ok' AND CalendarId = '[email protected]'

Delete

Delete an event from AllCalendars by specifying at least its Id.

DELETE FROM [AllCalendars] WHERE Id = '8ba774m3anenroqcepfi7ka6ok' AND CalendarId = '[email protected]'

Order Events

When you query from AllCalendars table, the events will not be ordered by the StartDate, but rather by the CalendarId. You can order the calendars by either the StartDate or StartDateTime column, depending if the event is an AllDayEvent or not. Alternatively, you can order both Event types using the example query below.

SELECT CalendarId, Id, Summary, 
CASE 
     WHEN StartDateTime IS NULL THEN startDate 
     ELSE StartDateTime 
END AS EventDate 
FROM AllCalendars ORDER BY EventDate ASC

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The Id of the event.
CalendarId [KEY]	String	True	Calendar Id
Summary	String	False	The title of the event.
Description	String	False	The description of the event.
Location	String	False	The geographic location of the event as free-form text.
AllDayEvent	Boolean	True	This value indicates whether or not the event is an all-day event.
StartDate	Date	False	The start date if this is an all-day event..
StartDateTime	Datetime	False	The (inclusive) start time of the event. For a recurring event, this value is the start time of the first instance.
StartDateTimeZone	String	False	The time zone in which the start date time is specified.
EndDate	Datet	False	The end date if this is an all-day event.
EndDateTime	Datetime	False	The (exclusive) end time of the event. For a recurring event, this value is the end time of the first instance.
EndDateTimeZone	String	False	The time zone in which the end date time is specified.
OriginalStartTimeDateTime	Datetime	False	For an instance of a recurring event, this value is the time when the event would start according to the recurrence data in the recurring event identified by RecurringEventId.
SendNotification	Boolean	False	This value sets whether to send a notification when performing an insert or update.
Kind	String	True	The type of the resource, returned in the format calendar#event.
ETag	String	True	The ETag of the resource.
Status	String	False	The status of the event.
HTML_Link	String	True	The absolute link to the event in the Google Calendar Web UI.
Created	Datetime	True	The creation time of the event.
Updated	Datetime	True	The latest modification time of the event.
ColorId	Integer	False	The color of the event. This value is an Id referring to an entry in the event section of the colors definitions.
CreatorEmail	String	True	The creator's email address, if available.
CreatorDisplayName	String	True	The creator's name, if available.
OrganizerEmail	String	False	The organizer's email address, if available.
OrganizerDisplayName	String	False	The organizer's name, if available.
Recurrences	String	False	A pipe-separated list of RRULE, EXRULE, RDATE, and EXDATE lines for a recurring event. This field is omitted for single events or instances of recurring events. OriginalStartTimeDateTime must be set in order to modify this value.
RecurringEventId	String	True	For an instance of a recurring event, this value is the event Id of the recurring event itself.
Transparency	String	False	This value sets whether the event blocks time on the calendar. If set to transparent, the event does not block time on the calendar. If set to opaque, the event blocks time; this is the default value.
Visibility	String	False	The visibility of the event.
ICalUid	String	True	The event Id in the iCalendar format.
Sequence	String	False	The sequence number as per iCalendar.
AttendeesEmails	String	False	A comma-separated list of attendee's email addresses, if available.
AttendeesDisplayNames	String	False	A comma-separated list of attendee's names, if available.
AttendeesOmitted	Boolean	True	This field sets whether attendees have been omitted from the event's representation. When updating an event, this field can be used to update only the participant's response. When retrieving an event, the attendees that are returned are restricted to only the participant by the MaxAttendees query parameter.
ExtendedPropertiesPrivateKey	String	False	This field contains properties that are private to the copy of the event that appears on the calendar.
ExtendedPropertiesPrivateValue	String	False	This field contains properties that are private to the copy of the event that appears on the calendar.
ExtendedPropertiesSharedKey	String	False	This field contains properties that are shared between copies of the event on other attendees' calendars.
ExtendedPropertiesSharedValue	String	False	This field contains properties that are shared between copies of the event on other attendees' calendars.
GuestsCanInviteOthers	Boolean	False	This value sets whether attendees other than the organizer can invite others to the event.
GuestsCanSeeOtherGuests	Boolean	False	This value sets whether attendees other than the organizer can see who the event's attendees are.
GuestsCanModify	Boolean	False	Whether attendees other than the organizer can modify the event.
PrivateCopy	Boolean	True	This value sets whether this is a private event copy where changes are not shared with other copies on other calendars.
RemindersUseDefault	Boolean	False	This value sets whether the default reminders of the calendar apply to the event.
ReminderOverrideMethods	String	False	A comma-separated list of the methods used by the reminder. The possible values are EMAIL, SMS, and POPUP.
ConferenceId	String	False	The ID of the conference. Can be used by developers to keep track of conferences, should not be displayed to users.
ConferenceNotes	String	False	Additional notes (such as instructions from the domain administrator, legal notices) to display to the user. Can contain HTML. The maximum length is 2048 characters.
ConferenceSignature	String	False	The signature of the conference data. Generated on server side. Must be preserved while copying the conference data between events, otherwise the conference data will not be copied.
ConferenceSolutionName	String	False	The user-visible name of this solution. Not localized.
ConferenceSolutionKeyType	String	False	The conference solution type. If a client encounters an unfamiliar or empty type, it should still be able to display the entry points. However, it should disallow modifications. Valid values are: (1) 'eventHangout' for Hangouts for consumers (http://hangouts.google.com) -- (2) 'eventNamedHangout' for classic Hangouts for G Suite users (http://hangouts.google.com) -- (3) 'hangoutsMeet' for Hangouts Meet (http://meet.google.com) -- (4) 'addOn' for 3P conference providers.
ConferenceSolutionIconUri	String	False	The user-visible icon for this solution.
ConferenceRequestId	String	False	The client-generated unique ID for this request. Clients should regenerate this ID for every new request. If an ID provided is the same as for the previous request, the request is ignored.
ConferenceRequestKey	String	False	The conference solution, such as Hangouts or Hangouts Meet.
ConferenceRequestKeyType	String	False	The conference solution type. If a client encounters an unfamiliar or empty type, it should still be able to display the entry points. However, it should disallow modifications. Valid values are: (1) 'eventHangout' for Hangouts for consumers (http://hangouts.google.com) -- (2) 'eventNamedHangout' for classic Hangouts for G Suite users (http://hangouts.google.com) -- (3) 'hangoutsMeet' for Hangouts Meet (http://meet.google.com) -- (4) 'addOn' for 3P conference providers.
ConferenceRequestStatus	String	False	The status of the conference create request.
ConferenceRequestStatusCode	String	True	The current status of the conference create request. The possible values are: (1)
ConferenceEntryPointsAggregate	String	False	Information about individual conference entry points, such as URLs or phone numbers. All of them must belong to the same conference.
ReminderOverrideMinutes	String	False	A comma-separated list of the minutes before the start of the event when the corresponding ReminderOverrideMethod should trigger.
EventType	String	False	Specific type of the event. Read-only. Possible values are: 'default' - A regular event or not further specified. 'outOfOffice' - An out-of-office event. 'focusTime' - A focus-time event. 'workingLocation' - A working-location event.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
SearchTerms	String	Free text search terms to find events that match these terms in any field, except for extended properties.
ShowDeleted	Boolean	Whether to list cancelled events.

Excel Add-In for Google Calendar