Load from google sheet limited to 100 rows

[rory_mchugh]

Hi

I can get QlikView to load from an unrestricted Google Sheet OK, but it only seems to load the first 100 rows from the spreadsheet.

Has anyone encountered this? A limitation with Google Sheets or QlikView?

I've searched QlikCommunity and Google but no answers...

Thanks

Rory

[buzzy996]

I don't think so it's qv limitation,can u share ur google sheet url if possible...let's try&come back.

[rory_mchugh · Accepted solution]

I've got a solution from a colleague:

So you can load data from a Google Spreadsheet provided that the sharing settings of that spreadsheet are set to Anyone With the Link Can View, which means the data is semi-public, if you like.

However, Google Sheets will only allow you to load 100 rows max. Same if you do an Excel web query on the Google Sheet.

To get around this you need to either:

1. Publish the Google Sheet and use the new link to load the data in with (but you might not want to publish your spreadsheet)

2. Amend the link to the spreadsheet to include a tqx=out:html parameter e.g.

This is the normal link for the spreadsheet (in this case just some test data with more than 100 rows that I created)

https://docs.google.com/spreadsheets/d/1wYmo3-D-R0H-BXl0hpNL0upJeX-Py3qkSxvpFRIeY6g/edit?usp=sharing

Amend it to

https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=1wYmo3-D-R0H-BXl0hpNL0upJeX-Py3qkSxvpFRIeY6g...

So you need to take the bit from the first link that is highlighted in bold and add 2 bits before and after it to make a new link that QlikView can cope with. I'm not sure how or why this works (a genius colleague of mine came up with the solution) but it does!

If you need your data to be completely private then there is a 3rd party plug in that you can buy - see other posts on this forum.

[buzzy996]

ya,tha's what i said ..it's not limitation of qv in may above post,any how u know whr is the problem now

[#5]

Thanks - solved my issue.

Rory you may like to mark this as answered, sometimes it is easy to skip over "not answered" post when your looking for a resolution.

Cheers

Tim

[d_prashanthredd]

Hi Roy,

It helped for me too..

Thanks.

[Anonymous]

OT: English is not my fist languaje. I hope you can understand me.

Too late to write here, but this workaround is still working.

The Rory's answer helped to me with the rows limit, and it seems that when you use the modified link, the Web File Wizard do a better job. Eg.: the first row is converted  to labels automatically.

[cristian24r]

Excellent solution still working. I've been used a lot of google sheets in models but no one with more than 100 rows until now when I realize that limitation.

[JuanCharleston]

I have tried the solution highlighted by the Author Rory but it does not work any more at the moment on my machine. 

However, another solution still works where posted by Omerfaruk in 2017. 

Please refer to the 3rd page of the post:

https://community.qlik.com/t5/QlikView-App-Dev/How-to-load-data-from-google-docs-spreadsheet/td-p/58...

What the solution is to replace the part between the last / and second last / to the link below, like going to export the sheets as xlsx, then paste this link in QlikView where I am using. Then the QlikView can load over 100 lines (mine is 362 rows).

https://docs.google.com/spreadsheets/d/1kIpJZ148hMFxN3nGPPKBhbQyov734um6ahBcMNG1xD0/export?format=xl...

[DiogoUnika]

WHAT KIND OF BLACK MAGIC IS THAT!!?? 

lol but it works.. tks so much!