Qlik Community

Qlik Sense Documents

Documents about Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER

Loading data from Google Sheets document via REST connector

Luminary
Luminary

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:

ValueArray.png

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:

Result.png

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.

Attachments
Comments
Explorer
Explorer

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!

Thanks!

Foo Lin

0 Likes
Luminary
Luminary

Hello Foo Lin,

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

Juraj

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