UPDATE : This has now been superceded by
QVSource - The QlikView API Connector.
UPDATE 24/11/09: I posted a related entry here which encapsulates an improved version of this in an easier to use component.
I wanted to experiment with the Google Spreadsheet API a little this week and what better way than to build a sample which uses the API to load some data into QlikView.
The QlikView application (see end of post for the download link) I have developed uses a macro to authenticate a specific user with the Google Spreadsheet API and then query it for the xml representation of a worksheet, which is then downloaded and saved as a file before being loaded into QlikView.
For the purpose of the demo I have created a gmail account which I will use for this and any future demos. Of course, I need to provide the username and password for this account and you will see these values in the input box at the top of the application. Please only use these in the context of these demos as logging into the account and changing settings might break functionality for other users!
You should be able to run the demo straight off (e.g. by clicking one of the buttons in the QlikView application) using the credentials, Spreadsheet key and worksheet name supplied. If successful you should find an xml file created alongside the QlikView application named 'GoogleSpreadsheetDataTemp.xml'.
However you should also be able to substitute them with your own values. The spreadsheet key is the value highlighted below which is visible in the the browser address bar when you are viewing the spreadsheet, as highlighted below:
The other values should be self explanatory.
If you do update the input values to point to your own spreadsheet then you should also update the load script to load from this new xml file which will have a different schema to the test spreadsheet I created the demo file for.
In order for the Macro to be able to run you will need to make sure that you have allowed system access, you can check this on the Edit Module screen (hit 'Ctrl + m' in QlikView) and check the following setting:
You can download the QlikView application here. It was created in QlikView 8.50. The macros are written in JavaScript but could easily be ported to VB Script if required.
I think the code illustrates many useful techniques, including authenticating with the Google Docs API, selecting nodes using XPath expressions and saving files to disk. These should provide a basis for querying many of the Google Docs services in other ways in order to provide data for QlikView.
