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. |
AnyoneCanAddSelf | Boolean | True |
This value sets whether anyone can invite themselves to the event. |
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. |
ReminderOverrideMinutes | String | False |
A comma-separated list of the minutes before the start of the event when the corresponding ReminderOverrideMethod should trigger. |
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. |