Knowledge

Load from google sheet limited to 100 rows

Article Number: 000019321 | Last Modified: 2018/06/12

Description

Qlik Sense loads from an unrestricted Google Sheet but it only seems to load the first 100 rows from the spreadsheet.

Cause

This is due to the default Google drive settings

Resolution

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.

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

Get Answers

Find Answers

Qlik Community

Collaborate with over 60,000 Qlik technologists and members around the world to get answers to your questions, and maximize success.

Join Us

Get Support

Have a Question?

Search Qlik's Support Knowledge database or request assisted support for highly complex issues.

Submit a case

Talk with a Representative

Critical Issues

Experiencing a serious issue, please contact us by phone. For Data Integration related issues please refer to your onboarding documentation for current phone number.

Call Us