Importing CSV from Google Drive to Google Sheets

Excelify.io
Sep 13, 2018 · 2 min read

So you ran into that issue where you have a CSV file on your Google Drive, and you wanted to import it into your Google Sheets using the =IMPORTDATA formula.

You are excited to automate your life, but sadly, you get this error:

Error:
Result was not expanded automatically, please insert more columns (63).

There Is a Solution!

To get your CSV file imported into that Google Sheets table, you need to do the following.

Step 1: Share that file as the publicly accessible link (view only)

Right-click on that file on your Google Drive.

Choose the “Get shareable link” on the menu.

Copy that link you get, and paste in some text editor.

Your link will look like this:

https://drive.google.com/open?id=1CzOceMwNnrX0Pkw4CHaeZdEMDoWVNPZD

or like this:

https://drive.google.com/file/d/1CzOceMwNnrX0Pkw4CHaeZdEMDoWVNPZD/view?usp=sharing

Step 2: Change this link to a downloadable file link

Google Drive has a special secret link structure which allows accessing the file in such a way that it becomes downloadable instead of opening in the Google Drive file preview.

Take the “id” part from that link. In this case, it is this part:
1CzOceMwNnrX0Pkw4CHaeZdEMDoWVNPZD

And construct the new URL for that file, which looks like this:

https://drive.google.com/uc?export=download&id=1CzOceMwNnrX0Pkw4CHaeZdEMDoWVNPZD

You can even test it — when opening this new link, the browser should just download that CSV file. That's a sign that you got it correct!

Step 3: Put that link in your Google Sheets =IMPORTDATA formula

The =IMPORTDATA formula with imported data from CSV file which is on Google Drive

Great job! One more superpower of yours!

______________________
Author:
Maris Veide
Excelify.io
Bulk Import Export Update Migrate app for Shopify

Excelify.io

Written by

Shopify App to Bulk Import Export Update Migrate your Store

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade