Welcome Guest, you are in: Login

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

NOTE, QVSource also has Connectors to:



With this connector you can:
  • List the spreadsheets and their worksheets and load the contents of these spreadsheets.
  • List all files on your Google Drive or only those matching your query (see below).
  • List all the file revisions for a document.
  • Create or update a Google Spreadsheet from a CSV file - for example you could use a store command in your QlikView/Qlik Sense load script to create a CSV file containing data from your application and turn this into a spreadsheet on Google Drive.
    • Using the above feature, roll your own simple report creation and distribution services for QlikView based data using Google Apps Script (see example below).
  • Download (export) the raw contents of supported docs into QlikView/Qlik Sense.
  • Download any Google Drive file to your local machine.
    • Note that if you have published a Google Apps Script which returns content, you can also use the connector to read the response from this script.
  • List and load your Google Spreadsheets into QlikView / Qlik Sense as tables.

Listing And Accessing Your Spreadsheets

The functionality of the Google Spreadsheet Connector for QlikView has also been moved into version 0.9.2 and later of this Connector.

To list all your spreadsheets, simply run the ListSpreadsheets table:

Image

Locate the spreadsheet you are interested in, and copy its key to your clipboard (Ctrl + C). Now go to the ListWorksheets table and paste this value into the key input. Running the table should then show all the worksheets in that spreadsheet:

Image

Now repeat the same process for the worksheet you would like to load into QlikView - copy its worksheet id and then on the GetSpreadsheet table paste this value in:

Image

You should now be able to extract the spreadsheet and then copy and paste the generated QlikView script for this into your QlikView application.

Sheet Not Found - Overriding the Sheet Index

If you have deleted and re-added sheets to your Google Spreadsheet you might find that when you attempt to extract a worksheet, you see the following error:

Image

To access this sheet you should open it in a web browser and note its gid value as highlighted below:

Image

You should then enter this value into the 'GID Override' input on the GetSpreadsheet table (see screenshot above). You should then find you can access the spreadsheet.

Scripting Tips

Adding a row index

If you want to add a row index to your data you can use the standard QlikView RowNo() function:


Requests:
LOAD
	RowNo() as Requests_Index,
	Timestamp as Requests_Timestamp,
	Name as Requests_Name,
	...etc...
FROM
[http://localhost:5555/QVSource/GoogleDriveConnector/?table=GetSpreadsheet1&......]
(qvx);

Skipping The First X Rows

If you have a certain number of header rows you wish to avoid loading into your QlikView datamodel, one technique is to first store all of the data to a QVD file (with an additional index field as shown above) and then load the data into QlikView from this file with a where clause. This is illustrated below:


Tmp:
LOAD
	rowNo() as rowNo,
	Timestamp as GetSpreadsheet_Timestamp,
	Name as GetSpreadsheet_Name,
	Email as GetSpreadsheet_Email
FROM
[http://localhost:5555/QVSource/GoogleDriveConnector/?table=GetSpreadsheet&appID=&worksheetKey={YOUR KEY HERE}]
(qvx);

store Tmp into Tmp.qvd (qvd);

drop table Tmp;

Data:
load
* 
from Tmp.qvd (qvd)
where rowNo > 2;

Breaking out CSV cells into separate tables

You may have a google spreadsheet created from a google form where one of the questions on the form is a multiple select. This will result in cells in your spreadsheet containing comma separated values of each of the checked items.

The load statement for the main table might look something like this:


Requests:
LOAD
	RowNo() as MyTable_Index,
	Name as MyTable_Name,
	MyMultipleChoiceQuestion as MyTable_MyMultipleChoiceQuestion,
FROM
...

To split this out into a separate linked table with a row for each of the options selected you could use something like the following:


MultipleChoiceAnswers:
LOAD
	MyTable_Index,
   	trim(subfield(MyTable_MyMultipleChoiceQuestion, ',')) as Answer
Resident Requests;

You can find out more about the subfield function in the QlikView documentation.

Listing Your Files

You can use the ListFiles table to list out all the files in your Google Drive:

Image

Additionally you can specify a search query as discussed below.

Searching

To construct a search query see the notes here. Here are some examples:

Search for files with the title "hello"
title = 'hello'

Search for folders using the folder-specific MIME type
mimeType = 'application/vnd.google-apps.folder'

Search for files that are not folders
mimeType != 'application/vnd.google-apps.folder'

Search for files with a title containing the words "hello" and "goodbye"
title contains 'hello' and title contains 'goodbye'

Search for files with a title that does not contain the word "hello"
not title contains 'hello'

Search for files containing the word "hello" in the content
fullText contains 'hello'

Search for files writeable by the user "test@example.org"
'test@example.org' in writers

Search for the ID 1234567 in the parents collection. This finds all files and folders located directly in the folder whose ID is 1234567.
'1234567' in parents

Search for files writeable by the users "test@example.org" and "test2@example.org"
'test@example.org' in writers and 'test2@example.org' in writers

Search for files containing the text "important" which are in the trash
fullText contains 'important' and trashed = true

Search for files modified after June 4th 2012
modifiedDate > '2012-06-04T12:00:00' // default time zone is UTC
modifiedDate > '2012-06-04T12:00:00-08:00
'

Search for files shared with the authorized user with "hello" in the title
sharedWithMe and title contains 'hello'

List File Revisions

To list the revisions for a file, copy the id of a file from the ListFiles table and enter it in to the ListFileRevisions table:

Image

Creating A Spreadsheet

To create a spreadsheet use the table shown below and pass in a name for the new spreadsheet and a file path to a CSV file containing the data. Note that you can use QlikView's store command from your load script to create this CSV file from data in your QlikView data (see example below).

Image

Updating A Spreadsheet

You can also update an existing spreadsheet.

Please note that although this does not change the Spreadsheet/File ID it does result in a new Worksheet ID (e.g. #gid=1234567 parameter in the URL).

To do this, first locate the ID for the spreadsheet, you can find this from your browser:

Image

Or alternatively from the 'ListSpreadsheets' table:

Image

You can now go to the 'UpdateGoogleSpreadsheet' table and enter this ID and a path to your CSV file. On running the table you should find your online Google Spreadsheet has updates.

Image

Example: Creating and distributing a Report using QlikView, QVSource and Google Drive

Putting the pieces above together, it would be possible to build a reasonably functional reporting system using QlikView, QVSource and Google Drive.

These steps could include:
  1. Build a data model in QlikView using the load script and combining data from any and all sources required.
  2. Create a CSV file (or multiple CSV files) as part of the QlikView load script process containing the data you would like to email to your users (perhaps via email or a link to a document on Google Drive).
  3. Create one or more spreadsheets on Google Drive containing the data from these CSV files.
  4. Write some Google Apps Script to either distribute these spreadsheets to your users, either in the raw format or with some more processing. For example the data could be converted into charts or into a more attractive Google Document, then converted to a PDF and emailed as an attachment. Or the URL to the document could be distributed.

In this example we will look at the first three steps.

The script below shows an example script which searches for all the Tweets matching a certain search term (in this case, all recent Tweets in English mentioning the term 'QlikView'), creates a sorted, aggregated table containing the top Tweeters and saves this to a CSV file. Note that we are saving the file to a location which we know QVSource has read permission to.


Tweets:
LOAD
	id as Search_id,
	created_at as Search_created_at,
	user_name as Search_user_name,
	user_screen_name as Search_user_screen_name
FROM
[http://localhost:5555/QVSource/TwitterConnectorV2/?table=Search&appID=&searchTerm=qlikview&maxNoPages=15&lang=en]
(qvx);

TweetsUnsorted:
load
Search_user_screen_name as ScreenName_tmp,
count(Search_id) as NoTweets_tmp
resident
Tweets
group by Search_user_screen_name;

TweetsSorted:
first 10 load
'@' & ScreenName_tmp as ScreenName,
NoTweets_tmp as NoTweets
resident
TweetsUnsorted
order by NoTweets_tmp desc;

drop table TweetsUnsorted;
drop table Tweets;

store ScreenName, NoTweets from TweetsSorted into c:\QVSource\Temp\spreadsheet_data.csv (txt);

After this script has run, you should find the CSV created on your local machine:

Image

We can now extend our QlikView script to make use of the CreateGoogleSpreadSheet table, as follows:


Tweets:
LOAD
	id as Search_id,
	created_at as Search_created_at,
	user_name as Search_user_name,
	user_screen_name as Search_user_screen_name
FROM
[http://localhost:5555/QVSource/TwitterConnectorV2/?table=Search&appID=&searchTerm=qlikview&maxNoPages=15&lang=en]
(qvx);

TweetsUnsorted:
load
Search_user_screen_name as ScreenName_tmp,
count(Search_id) as NoTweets_tmp
resident
Tweets
group by Search_user_screen_name;

TweetsSorted:
first 10 load
'@' & ScreenName_tmp as ScreenName,
NoTweets_tmp as NoTweets
resident
TweetsUnsorted
order by NoTweets_tmp desc;

drop table TweetsUnsorted;
drop table Tweets;

let vFile = 'c:\QVSource\Temp\spreadsheet_data.csv';

store ScreenName, NoTweets from TweetsSorted into $(vFile) (txt);

GoogleDriveConnector_CreateGoogleSpreadSheet:
LOAD
	id as CreateGoogleSpreadSheet_id,
	kind as CreateGoogleSpreadSheet_kind,
	title as CreateGoogleSpreadSheet_title
FROM
[http://localhost:5555/QVSource/GoogleDriveConnector/?table=CreateGoogleSpreadSheet&appID=&filePath=$(vFile)&fileName=My+Spreadsheet]
(qvx);

Note that the name of for the newly created spreadsheet (fileName) is part of the load script and so could also be updated, for example to include today's date.

Now, when we run this load script we should find a newly generated spreadsheet on our Google Drive:

Image

We could have a Google Apps Script running on a timer to continually monitor for updates to this file and, when found, distribute it via email or transform it in some way. For example a template Google Doc could be used and the data here could be turned into charts or tables and inserted into the doc before distribution.

Downloading Raw File Content

You can also use this connector to load the raw contents of a file on your Google Drive into QlikView.

For example, lets suppose we use the ListFiles table to locate the following CSV file :

Image

Locate and copy the value from the 'downloadUrl' column for this row and paste it in to the Export Path parameter of the 'GetRawFile' table and then run the table:

Image

We can now copy the URL for this request:

Image

And use it as the path to a Web File in QlikView as shown below:

Image

Downloading A File To Your Local Disk

Version 0.9.2 of this Connector introduced a new DownloadRawFile table which allows you to download a file from Drive to your local hard disk.

To illustrate this, lets imagine we have a zipped CSV file on our Google Drive which we wish to load into QlikView. We can locate this file using the ListFiles table, optionally using a search query (as shown) to make this easier:

Image

We now copy the value from the downloadUrl column for this file (Ctrl + C) and move to the DownloadRawFile table. Enter the download url value into the input shown and also a destination file name where this file should be saved. On running the table you should then see a status OK message in the result table and a confirmation of the downloaded path you entered.

NOTE that QVSource must have write access to the target location you specify and, for the steps below, QlikView must also have read access.

Image

Unzipping A File From QlikView

We can now use QlikView's Execute command to run an external program to unzip this file. In this example we use 7zip, so you should ensure this is installed on your machine.

We can then simply use the following QlikView load script statement to unzip the file downloaded in the previous step:


execute "c:\Program Files\7-Zip\7z.exe" e "C:\QVSource\Temp\My CSV File.zip" -y -oc:\QVSource\Temp

After this has run, the extracted .csv file should be available on your local disk for loading into QlikView using the standard procedures.

Change Log

1.0.8 - 19/03/16
  • GetSpreadsheet table now downloads data as CSV instead of TSV internally which was used previously. This may resolve occasional issues with parsing cells which contain combinations of ,'s and "'s.

1.0.7 - 10/03/16
  • GetSpreadsheet table will now retry once after a 10s wait in the case of a 429 error response (and also still wait 1s between successive successful requests).

1.0.6 - 03/03/16
  • GetSpreadsheet table will now retry once after a 3s wait in the case of a 429 error response and also wait 1s between successive successful requests.

1.0.5 - 30/08/15
  • Added two new inputs to GetSpreadsheet table to rename column headers (Column1, Column2 etc.) and to skip first X rows.

1.0.4.7 - 28/07/15
  • Fixed issue with GetSpreadsheet which appeared July 2015 where a change in the Google API response caused QVSource to introduce a line feed into the last column name of any spreadsheets downloaded which would break the generated load scripts.

1.0.4.6 - 19/05/15
  • Added GetRawFileByIdAsText table. This is initially intended for use with the new UnsampledReport tables in the Google Analytics Connector.

1.0.4.5 - 26/04/15
  • Updated label on target path parameter to clarify that both directory and filename are required.

1.0.4.4 - 14/11/14
  • Added retry logic with waits in between to UpdateGoogleSpreadSheet table.

1.0.4.3 - 18/09/14
  • Added a note to UpdateGoogleSpreadSheet about it updating the worksheet ID (gid).

1.0.4.2 - 17/06/14
  • Added and UpdateGoogleSpreadSheet table.

1.0.4.1 - 05/05/14
  • Added GetRawFileAsBinary table.
  • Added GetRawFile table now named GetRawFileAsText (old name will be automatically mapped to new name).

1.0.4 - 24/04/14
  • Added a note to the ListFiles table about the query parameter.

1.0.3 - 11/04/14
  • Fixed minor issue where authentication issue when requesting a spreadsheet resulted in an empty response now correctly returns error page.

1.0.2 - 31/03/14
  • Minor performance improvements.
  • Now supports extracting new Google Spreadsheets.

1.0.1 - 03/02/14
  • Can now specify folder id when creating a Google Spreadsheet.

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

0.9.2 - 10/10/13
  • Added DownloadRawFile table.
  • Minor changes to some UI labels.

0.9.1 - 20/09/13
  • Added Google Spreadsheet functionality.

0.9.0 - 18/09/13
  • Create spreadsheet table should now work even if the CSV file is already open.

0.8.3 - 19/03/13
  • Added md5Checksum, webViewLink and 14 more Exif image metadata info columns.
  • Correctly set file version back to beta.
  • Added ListFileRevisions table.

0.8.2 - 09/03/13
  • Minor changes to authentication code.

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

0.8.0 - 13/06/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