Welcome Guest, you are in: Login

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


NOTE: This is now superceded by version 2 of this Connector.

The QVSource Google Spreadsheets Connector, allows you to load spreadsheets into your QlikView & Qlik Sense applications.

Usage

On opening the Google Spreadsheet Connector, you should see the following:

Image

From the 'Account Type' you may select 'Premier' or 'Standard'. If you leave it as 'Auto', the connector will determine the best option.

Next, enter the credentials and click 'Connect'

The Connector will then go and get a list of all the spreadsheets that you have access to:

Image

Select a spreadsheet and then click on the 'Connector Data' tab.

You will now see all the worksheets in the spreadsheet:

Image

On selecting a sheet, the Connector will pull down all the data and display it in a grid in the right panel, so that you can preview it before you load it into your QlikView application:

Image

Finally, click on the 'QlikView Load Script' tab where you can copy and paste the QlikView load script into your QlikView application.

Image

Overriding the Sheet Index

If you have deleted and re-added sheets to your Google Spreadsheet you might find that when you select a sheet in QVSource 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 QVSource and try the sheet again. Note that you must remove this value once you have access and generated the load script for the sheet as it will force this sheet index to be used for all sheets:

Image

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/GoogleSpreadsheetsConnector/?table=Sheet1&key=.....&gid=0&username=........]
(txt, utf8, embedded labels, delimiter is '\t', msq);

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.

Troubleshooting

Column Header Issue

Note that if your spreadsheet column headers contain square brackets ('[' or ']') and spaces then QVSource will try to alias the column name as something like:

[My column header with [square brackets] in]

And this will cause the QlikView loadscript to fail. Please manually rename your column/field aliases in the QlikView load script to fix this.

Newlines in cells

If any of the cells in your spreadsheet contain a newline it will currently break the conversion into a table.

The best work around for this at the moment is to manually remove carriage returns in data in cells.

Change Log

1.0.2 - 28/02/12
  • New input added to override sheet index (gid) which is sometimes necessary if sheets are added and deleted.

1.0.1 - 19/11/11
  • IndustrialCodeBox_GoogleSpreadsheetsConnector now referred to as GoogleSpreadsheetsConnector in load script.

1.0.0 - 14/09/11
  • Made default credentials mode to use credentials stored in connector (rather than embed in script).

0.9.0 - 24/08/11
  • Fix added for foreign characters.

0.8.3 - 24/06/11
  • UI now allows choice of storing credentials in script or using those set in QVSource.

0.8.2 - 23/06/2011
  • No longer caches results, request spreadsheet every time from API.


(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