Application Installation
The first step is to run the installer for the product. This should have a file name:
IndustrialCodeBox.QlikViewMacroHelpers.Setup_VERSION.msiIf you would like to request an installer please fill out the form
here.
Running this installer will place a number of files in your program files directory, typically at:
c:\Program Files\Industrial CodeBox\QlikView Macro Helpers\However this may differ slightly on your machine.
Usage
You are now ready to start using the helper in your QlikView macros.
Security Settings
When using the component in your macros you will need to ensure that the Local Security is set to
Allow System Access in the
Edit Module Window of QlikView.
Script
You are now ready to develop your QlikView script - only a few lines of simple code are needed.
VBScript
The first step is to create an instance of the GoogleSpreadsheet helper component. This is done using the following code:
Set qlikViewMacroHelper = CreateObject("IndustrialCodeBox.QlikViewMacroHelpers.Mgr")
Set googleSpreadsheetHelper = qlikViewMacroHelper.GetHelper("GoogleSpreadsheets", ActiveDocument)This component can now be used to download a worksheet using the .Download method of the component which takes the following arguments:
.Download(googleUserName, password, spreadsheetKey, workSheetIndex, targetFileName, format)
googleUserName : This is the account name you use to access your google documents.
password : This is the password you use to access your google documents.
spreadSheetKey : This is the key for the Google spreadsheet which you wish to access as seen in the address bar when you are viewing the spreadsheet in Google docs, as illustrated below:
workSheetIndex : This should be the zero based index of the worksheet you wish to download within the Google Spreadsheet specified by the above key.
IMPORTANT : Although the
Google Docs API Documentation documentation suggests this is the index of the worksheet this does not always seem to be the case. We are investigating whether this is a bug. If the index you specify does not return the sheet you expect or returns an error please experiment with different indexes.
targetFileName : This is the location and filename you would like the document to be saved to. If you do not specify a path then the exported worksheet will be saved alongside the QlikView application.
format : Specify 'tsv' (tab separated file), 'csv' (comma separated file), 'xls' (Excel file) or 'html' (html file).
So a typical usage would look something like this:
googleSpreadsheetHelper.Download "[email protected]", "yourpassword", "0AgIa62_your_spreadsheet_key_uaFlOTHc", 1, "worksheet.tsv", "tsv"Once you have run this code in your macro you should find a file named worksheet.tsv alongside your QlikView application.
You should now edit your QlikView loadscript to load and process this file as you wish. You can then, opotionally add a couple of extra lines to your macro so that in future the downloaded Google Spreadsheet file is automatically reloaded then deleted, as shown below:
ActiveDocument.DoReload()
googleSpreadsheetHelper.DeleteFile "worksheet.tsv"JavaScript
The same code in JavaScript would look something like this:
var qlikViewMacroHelper = new ActiveXObject("IndustrialCodeBox.QlikViewMacroHelpers.Mgr");
var 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");Support
Please post any questions or issues on the
support forum.