The QVSource Google Spreadsheets Connector, allows you to load spreadsheets into your QlikView applications.
Usage
On opening the Google Spreadsheet Connector, you should see the following:
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:
Select a spreadsheet and then click on the 'Connector Data' tab.
You will now see all the worksheets in the spreadsheet:
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:
Finally, click on the 'QlikView Load Script' tab where you can
copy and paste the QlikView load script into your QlikView application.
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:
To access this sheet you should open it in a web browser and note its gid value as highlighted below:
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:
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/IndustrialCodeBox_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.