Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Loading data from Google Sheets document via REST connector


Loading data from Google Sheets document via REST connector

Recently I needed to load data from Google Sheets document. Normally I would do this using Qlik Web Connectors, but in this case I decided to use the REST API Connector. The reason was quite simple, number of sheets to be loaded was not definite and I needed to load sheets with labels matching certain naming convention. After going through struggle with OAuth authentication and refresh tokens (which is a separate topic well described here: REST CONNECTOR locate the Authorization token) I finally managed to load data from sheets. However, to my surprise, I did not get nice labels and cells, but only one long list of values looking something like this:


So I was able to access the file and any sheet, but still needed to transform those data into tabular format. After giving it some thought I came up with a quite simple solution to this. A short sub routine which will use first few lines (those with __KEY_values=1) as column labels and will transpose all other lines to fill those columns. Here’s the result:


You can find the sub routine in the attached file. To use it you will first need to authenticate (that’s a separate topic described in the link above), than you can get contents of any sheet simply by creating a connection to that sheet (or a dummy GET endpoint) and then calling the routine:

Call GetSheet(SheetID, SheetName, TableName);

This will look into Google Sheet document identified by SheetID, search for a sheet labeled SheetName and load it into a table called TableName. TableName parameter is optional, if you omit it, SheetName will be used as TableName.

I have some plans to improve this sub routine, like checking if a sheet exists in the document etc., but I will appreciate any feedback or feature suggestions.


Hi Juraj,

Just wanna say thanks for posting this up. I've been looking for a way to pull data from a google sheet doc without using the Google Drive Web Connectors and stumbled across your work. It worked perfectly for me!


Foo Lin


Hello Foo Lin,

I'm glad to here it helped you. If you have any suggestions for improvments, just let me know. Thank you.


Version history
Revision #:
1 of 1
Last update:
‎02-05-2018 09:23 AM
Updated by: