How to link to external data in Google Sheets pivot table

Hi,
I am trying to have a Pivot Table get data from another Google Doc.
I tried putting this into the range:
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1y7_80DDjZ7T122zV3bxg4eapxv4FBGGGUhMudve1UbY/edit#gid=147055643", "range=1:103")
but it did not work.
What is the correct way to do this?
A screenshot of the error is attached.
Thanks!
Josh
Josh FialkoffFounder/CEO Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Josh

The 2nd argument for IMPORTRANGE should be a sheet reference like Sheet1!1:103.

IMPORTRANGE("https://docs.google.com/spreadsheets/d/1y7_80DDjZ7T122zV3bxg4eapxv4FBGGGUhMudve1UbY/edit#gid=147055643", "Sheet!1:103")

You might also have to allow access to the external source.

Once you have the data imported you can then goto Data>Pivot table...
0
NorieVBA ExpertCommented:
Josh

Should have mentioned, you can't use IMPORTRANGE to specify the range for the pivot table directly, use it to import the data to a sheet and then base the pivot off the sheet you've imported to.
0
Josh FialkoffFounder/CEO Author Commented:
Thanks! What should I use instead of importrange?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

NorieVBA ExpertCommented:
Josh

I don't think pivot tables in Google sheets currently have the capability of working directly with external data.
0
Josh FialkoffFounder/CEO Author Commented:
0
Josh FialkoffFounder/CEO Author Commented:
Thanks @imnorie! I tried your solution and I get this error:
Screenshot of Google Sheet with error
0
NorieVBA ExpertCommented:
Josh

I think I might have confused things a little.

You can't use IMPORTRANGE directly for a pivot table.

By that I mean that you can't goto Data>Pivot... and enter the IMPORTRANGE in the dialog that pops up nor can you use IMPORTRANGE in the Pivot Table Editor.

You need to use IMPORTRANGE on a sheet to, well, import the data and then goto Data>Pivot... to create the pivot from the imported data.

PS I saw the two links you posted earlier today and I couldn't see anything that showed how to use external data directly for a pivot table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Josh FialkoffFounder/CEO Author Commented:
Thanks for your help!
0
Josh FialkoffFounder/CEO Author Commented:
@Norie, I've been using the IMPORTRANGE function and it only seems to work for 1999 rows. Is this a true limitation (if it's not what else could be responsible for only that many rows importing) and is there a workaround?
Thanks!
0
NorieVBA ExpertCommented:
Josh

Not familiar with any limitations on IMPORTRANGE but I'll check it out.

Just had a look and I can find examples of people having problems using IMPORTRANGE for large amounts of data but nothing consistent and no mention of any 'official' limitations.

How many columns in the data you are trying to import?

Are you getting error messages?
0
Josh FialkoffFounder/CEO Author Commented:
Thanks Norie! Just over 11,000 rows.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.