Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rory_mchugh
New Contributor III

Load from google sheet limited to 100 rows

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

1 Solution

Accepted Solutions
rory_mchugh
New Contributor III

Re: Load from google sheet limited to 100 rows

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.

7 Replies
buzzy996
Honored Contributor II

Re: Load from google sheet limited to 100 rows

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
New Contributor III

Re: Load from google sheet limited to 100 rows

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
Honored Contributor II

Re: Load from google sheet limited to 100 rows

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

Not applicable

Re: Load from google sheet limited to 100 rows

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
Contributor III

Re: Load from google sheet limited to 100 rows

Hi Roy,

It helped for me too..

Thanks.

gonzalogil
New Contributor

Re: Load from google sheet limited to 100 rows

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
New Contributor

Re: Load from google sheet limited to 100 rows

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.

Community Browser