Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)


This connector allows you to download all the events in all the Google Calendars you have access to.

Schema

You can obtain the following data using this connector. The demo load script below will allow you to recreate this using the connector.

Note that an EventAttendees table is also available in version 1.0.3 and later of this connector.

Image

Usage

Please note: The screen shots below no longer reflect the latest version (1.0.1) but the functionality is equivalent.

You should first ensure that you have authenticated with the Connector by clicking the 'Authenticate' button and then following the instructions.

Once you have done this you should see a token next to the 'Authenticate' button:

Image

You can then enter the name of a calendar you wish to load event data for. This will typically be the email address associated with the calendar.

You can see a list of Calendars you have access to using the CalendarList table on the 'Connector Data' tab of the connector. Once you have entered a valid calendar name which you have access to you can click the Events table on the 'Connector Data' tab and generate the load script as with other connectors:

Image

Demo Load Script

The following load script should contain the schema above:

NOTE: If you are using version 0.8.3 of the connector or earlier you will need to use the url encoded version of the CalendarList_id filed in the for/next loop. The CalendarList_id_urlEncoded field was added in 0.8.4 to avoid this requirement.

Notice in the load script we use the following additional script to parse some of the dates into QlikView dates -


Date#(replace(mid(start_dateTime, 1, 19), 'T', ' '), 'YYYY-MM-DD hh:mm:ss') as Events_start_dateTimeQV

You may also wish to use similar expressions on some of the other fields in the application.

Note - the following example uses version 1.0.1 of the Connector where QVX is the default format.


GoogleCalendarConnector_CalendarList:
LOAD
    id as CalendarList_id,
    id_urlEncoded as CalendarList_id_urlEncoded,
    summary as CalendarList_summary,
    description as CalendarList_description,
    timeZone as CalendarList_timeZone,
    selected as CalendarList_selected,
    accessRole as CalendarList_accessRole

FROM
[http://localhost:5555/QVSource/GoogleCalendarConnector/?table=CalendarList]
(html, utf8, embedded labels, table is @1);

call logQVSourceError(scripterrordetails, 'TwitterConnector\UserLookup');

LET noRows = NoOfRows('GoogleCalendarConnector_CalendarList');

for i=0 to $(noRows)-1

	let calendarId = peek('CalendarList_id',$(i),'GoogleCalendarConnector_CalendarList');
	let calendarIdEncoded = peek('CalendarList_id_urlEncoded',$(i),'GoogleCalendarConnector_CalendarList');
	
	GoogleCalendarConnector_Events:
	LOAD
		'$(calendarId)' as CalendarList_id,
		id as Events_id,
		status as Events_status,
		htmlLink as Events_htmlLink,
		created as Events_created,
		Timestamp#(created, 'YYYY-MM-DDThh:mm:ssT') as Events_created_timestamp, // 2011-08-02T08:00:57.000Z
		updated as Events_updated,
		summary as Events_summary,
		description as Events_description,
		location as Events_location,
		creator_email as Events_creator_email,
		creator_displayName as Events_creator_displayName,
		creator_self as Events_creator_self,
		organizer_email as Events_organizer_email,
		organizer_displayName as Events_organizer_displayName,
		organizer_self as Events_organizer_self,
		start_date as Events_start_date,
		end_date as Events_end_date,
		start as Events_start,
		start_dateTime as Events_start_dateTime,
		end as Events_end,
		end_dateTime as Events_end_dateTime,
		attendee_emails as Events_attendee_emails,
		iCalUID as Events_iCalUID,
		sequence as Events_sequence,
		privateCopy as Events_privateCopy,
		guestsCanSeeOtherGuests as Events_guestsCanSeeOtherGuests,
		guestsCanInviteOthers as Events_guestsCanInviteOthers,
		guestsCanModify as Events_guestsCanModify,
		visibility as Events_visibility,
		transparency as Events_transparency
	FROM
	[http://localhost:5555/QVSource/GoogleCalendarConnector/?table=Events&calendarName=$(calendarIdEncoded)]
	(qvx);
	
	call logQVSourceError(scripterrordetails, 'GoogleCalendarConnector\Events\$(calendarIdEncoded)');
	
next

GoogleCalendarConnector_Attendees:
LOAD Distinct
	Events_id,
   	trim(subfield(Events_attendee_emails, ',')) as Attendee_Email
Resident GoogleCalendarConnector_Events
where Events_attendee_emails <> '';

Change Log

1.0.3 - 08/04/16
  • Added new EventAttendees table.

1.0.2 - 20/06/15
  • Upgraded to new OAuth2 framework.

1.0.1 - 13/03/14
  • Upgraded to new UI style.
  • BREAKING CHANGE: QVX format is now the default and you should adjust your load scripts.

1.0.0 - 04/12/13
  • Out of beta.

0.8.5 - 04/07/12
  • CanAuthenticate table added.

0.8.4 - 25/06/12
  • id_urlEncoded column added - this should mean additional QlikView script is no longer needed to encode this before being sent to the Events table.

0.8.3 - 03/06/12
  • Added recurrence column.

0.8.2 - 29/05/12
  • Added feature to embed encrypted token in load script.
  • First public release.
  • Default response format of html is no longer included (or needed) in generated script. SDK now has facility for connector to override this.
  • Should now correctly log API calls.

0.8.1 - 28/05/12
  • Added CalendarList table.
  • Tidied up columns in Events table.
  • Should now page through all Events when creating Events table.

0.8.0 - 25/05/12
  • Initial version.


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd