Qlik Community

Qlik Sense Documents

Qlik Sense documentation and resources.

Announcements
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

cancel
Showing results for 
Search instead for 
Did you mean: 
juraj_misina
Partner
Partner

Loading data from Google Sheets document via REST connector

Attachments

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.

Comments
chaifoolin
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
juraj_misina
Partner
Partner

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