UPDATE : This has now been superceded by
QVSource - The QlikView API Connector.
In a previous post I illustrated a QlikView macro which downloads a Google Worksheet using the
Google Spreadsheet API then saves it to a file before reloading it into the QlikView application.
Although the macro code and associated demo QlikView application are a good illustration of how to interact with a Google API, I wanted to produce something which could be consumed much more easily by QlikView developers using just a few lines of code (rather than the approx 160 lines which the macro used).
I also noticed that, depending on the specific data contained in the Google Worksheet, sometimes QlikView would actually be unable to load the xml which had been downloaded from the API.
To solve these issues I have produced a new component which allows a Google worksheet to be consumed more reliably by a QlikView application and using just a few lines of code, as illustrated here in JavaScript:
var qlikViewMacroHelper = new ActiveXObject("IndustrialCodeBox.QlikViewMacroHelpers.Mgr");
var googleSpreadsheetHelper = qlikViewMacroHelper.GetHelper("GoogleSpreadsheets", ActiveDocument);
// Arguments to .Download are:
// username
// password
// spreadsheetkey
// worksheetindex
// targetFileName (if no path is specified it will be downloaded next to the QVW file)
// format: [tsv|csv|xls|html]
googleSpreadsheetHelper.Download("[email protected]", "yourpassword", "0AgIa62_your_spreadsheet_key_uaFlOTHc", 1, "worksheet.tsv", "tsv");
ActiveDocument.DoReload()
googleSpreadsheetHelper.DeleteFile("worksheet.tsv");
And here in VB Script:
Set qlikViewMacroHelper = CreateObject("IndustrialCodeBox.QlikViewMacroHelpers.Mgr")
Set googleSpreadsheetHelper = qlikViewMacroHelper.GetHelper("GoogleSpreadsheets", ActiveDocument)
googleSpreadsheetHelper.Download "[email protected]", "yourpassword", "0AgIa62_your_spreadsheet_key_uaFlOTHc", 1, "worksheet.tsv", "tsv"
ActiveDocument.DoReload()
googleSpreadsheetHelper.DeleteFile "worksheet.tsv"
This new version uses a different feature of the Google Docs API which allows the worksheet to be saved in a number of different formats, including csv, tsv, xls and html (you just specify one of these strings in the Download method shown in the code above).
The code needed in the QlikView application is now a lot simpler than the previous full macro version and it could actually be reduced further to just 1 line of code for the download and 1 for the cleanup (i.e. deleting the downloaded file, which is optional anyway).
Beta Download
If you would like to try out the beta of this component just let me know by filling out this form and I'll send you a download link and licence file as soon as I can.
UPDATE 30/11/09: You can now download a demo of this component from here. You will still need to fill out this form if you would like to try the component on your own Spreadsheets.
