Connecting to Web Files in SaaS Editions of Qlik Sense

by

If you want to connect to web data via URL using Qlik, the web file connector is the way to go. However, it is still not available in the SaaS editions. Luckily, there is a workaround using Google Sheets and Qlik’s Google Drive and Spreadsheets connector.

Step 1 – Google Sheets

For this example I will use the Premier League table. The data load with the web file connector would have been pretty simple:

As we do not have this option in the cloud, let’s try with Google Sheets and the IMPORTHTML function. It imports data from a table or list within an HTML page. The syntax is as follows:

IMPORTHTML(url, query, index)

url - the URL of the page
query - either "list" or "table"
index - the table or list index, starting at 1

Create a new sheet and add the following in cell A1:

=IMPORTHTML("https://www.skysports.com/premier-league-table", "TABLE", 1)

The result looks like this:

Depending on the data source you could also use IMPORTXML or IMPORTDATA. Note that if the data is too big, it might exceed the maximum size allowed by Google. Nevertheless, if you are not loading tens of thousands of lines, the size limit should not be a problem.

Step 2 – Google Drive and Spreadsheets connector

Now that the data is in Google Sheets, the next step is to set up the Google Drive and Spreadsheets connector in Qlik. After the authentication process, open the Select data window.

  • Go to the ListSpreadsheets tab, click on Preview data and copy the key of your spreadsheet.
  • Next, open the ListWorksheets tab, paste the key and click on Preview data. This will return the worksheetKey that you need to copy.
  • Finally, open the GetWorksheet tab, paste the worksheetKey and click on Preview data again. Here you can select which fields to load. When you are done, click on Insert script.

Make sure to place square brackets around special characters in the SELECT statement, otherwise you will get Connector error: Could not parse SELECT statement. In this example the special character is the number sign # in the first column.

Step 3 – Data refresh

By default, the IMPORTHTML function refreshes once an hour. However, formulas are recalculated when their arguments change. Therefore, you could speed up the refresh interval by concatenating the URL with a function that updates more frequently. GOOGLEFINANCE, for example, recalculates every few minutes. In the formula below the USD:BTC exchange rate has been appended to the main URL. This ensures that the data in the sheet will be refreshed multiple times an hour.

=IMPORTHTML("https://www.skysports.com/premier-league-table#"&GOOGLEFINANCE("CURRENCY:USDBTC"), "TABLE", 1)

The last thing to do is to schedule the refresh interval of your Qlik app.

That was all. I have been using this workaround for over an year now, also for this app. The only error that might occur is if Qlik reloads the data at the exact same time when the sheet refreshes. Then you will get Connector error: Fields […] do not exist in results.