topic Re: Load from google sheet limited to 100 rows in QlikView

Load from google sheet limited to 100 rows

rory_mchugh — Tue, 21 Apr 2015 11:59:20 GMT

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

Re: Load from google sheet limited to 100 rows

buzzy996 — Tue, 21 Apr 2015 12:30:57 GMT

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

Re: Load from google sheet limited to 100 rows

rory_mchugh — Wed, 22 Apr 2015 09:33:52 GMT

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&gid=1

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.

Re: Load from google sheet limited to 100 rows

buzzy996 — Wed, 22 Apr 2015 15:13:47 GMT

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

Re: Load from google sheet limited to 100 rows

Thu, 23 Jul 2015 03:55:49 GMT

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

Re: Load from google sheet limited to 100 rows

d_prashanthredd — Mon, 21 Aug 2017 10:18:29 GMT

Hi Roy,

It helped for me too..

Thanks.

Re: Load from google sheet limited to 100 rows

Anonymous — Sat, 24 Feb 2018 21:50:45 GMT

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.

Re: Load from google sheet limited to 100 rows

cristian24r — Tue, 28 Aug 2018 22:40:30 GMT

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.

Re: Load from google sheet limited to 100 rows

JuanCharleston — Thu, 13 May 2021 14:45:53 GMT

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/589512/page/3

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=xlsx

Re: Load from google sheet limited to 100 rows

DiogoUnika — Fri, 22 Mar 2024 12:43:24 GMT

WHAT KIND OF BLACK MAGIC IS THAT!!??

lol but it works.. tks so much!