Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

Loading data from Google Sheets document via REST connector

juraj_misina
Valued Contributor

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
chaifoolin
Visitor

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

juraj_misina
Valued Contributor

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

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