Qlik Community

Qlik Sense Documents

Qlik Sense documentation and resources.

Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE

Loading data from Google Sheets document via REST connector

Showing results for 
Search instead for 
Did you mean: 

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
Last update:
‎2018-02-05 09:23 AM
Updated by: