Import CSV from Google Drive to Google Sheets
Spreadsheets are the most convenient way to view CSV tables. That’s why, if you have a stash of CSV data on your Google Drive, it’s worth considering moving it to Google Sheets. In this post, we will take a look at two ways to move CSV to Google Sheets:
- Manual – using the IMPORTDATA function
- Automated – using the Google Sheets integration by Coupler.io
Which option is best? Read on to find it out!
Which way to go with to import CSV from GDrive into Google Sheets
IMPORTDATA is a native function to fetch CSV and pull data to spreadsheets. It lets you manually import CSV files from GDrive, but the drawback is that you’ll have to make your files publicly shareable and edit their URLs in a special way to do the job.
Coupler.io is a tool to automate importing data from various sources (CSV, BigQuery, and other) and updating it on a set schedule without human supervision. It’s available as a web app and an add-on for Google Sheets.
If you hesitate which option to choose, check out the following comparison table between manual vs. automate data import.
| Manual data import | Automated data import | |
|---|---|---|
| – No need to install third-party tools. – No coding or tech background is required. – Supports TXT, CSV, and CTV data. | Benefits | – No coding or tech background is required. – Advanced settings – Automatic data refresh is available – Instant integration of multiple apps with Google Sheets in one place |
| – 50 IMPORTDATA requests per spreadsheet. – Security risks. – Manual data refresh. – Limited data type support. | Drawbacks | – Installation is required. |
Automated CSV import from Google Drive with Coupler.io
If you are looking for a scalable, automated way to import CSV data from Google Drive, Coupler.io is a convenient option to consider. Coupler.io, for one, is an easy-to-use tool that helps consolidate data from different apps and sources to Google Sheets, Excel, or BigQuery. Let’s take a look at how you can use Coupler.io to insert a CSV file, stored on a Google Drive, into a Google spreadsheet.
Sign up to Coupler.io using your Google account, click Add new importer and select Google Drive as a source app and Google Sheets as the destination app.
If you want to export CSV data from a publicly stored CSV, you’ll need to choose CSV as a source app. Read more about the CSV importer and how to import CSV to Google Sheets.
Configure the importer as follows :
Set up the data source (Google Drive)
- Click Connect, select and sign in to your Google account. Click Continue.
- Select a CSV file on your Google Drive that you want to export to Google Sheets. Click Continue to proceed with the destination setup.
With the Google Drive source, you can also import Excel to Google Sheets from GDrive.
Set up the destination (Google Sheets)
You can easily change the destination and export data from Google Drive to Excel or BigQuery.
- Select your Google account from the drop-down list, then click Continue.
- Select a spreadsheet on your Google Drive and a sheet to import CSV data to. Click Continue.
- Optionally you can change the first cell address where to import your data. A1 is set by default. Click Continue.
- You can also choose the import mode – Replace or Append – as well as toggle on the Last update column parameter. This will add a column specifying the date and time of the last data update.
If you want to get your CSV data right away, click Save and Run. But you can also automate your data imports on a schedule. For this click Continue.
How to automate export of CSV from Google Drive to Google Sheets
If you toggle on Automatic data refresh, you will be able to automate export of CSV data to Google Sheets on a custom schedule.
Once you configured the schedule, click Save and Run to save the importer and run the initial import right away.
Note: You can configure the CSV to Google Sheets importer right from your spreadsheet with the use of the Coupler.io add-on for Google Sheets. The flow is the same, but you’ll need first to install the add-on from the Google Workspace Marketplace.
Manual CSV import from Google Drive with IMPORTDATA
IMPORTDATA is the function you can use to add comma-separated or tab-separated CSV data to Sheets. To start using IMPORTDATA, you need to have a CSV file stored on a publicly accessible server. A good example is an open database Quandl, which provides access to financial data, sales estimates for top companies, and other relevant information.
=importdata("https://www.quandl.com/api/v3/datatables/SHARADAR/SEP.csv?api_key=woApTk_w2VyzYiFttGxg)")
Check out the IMPORTDATA Google Sheets Tutorial for more on this topic.
Converting a publicly available CSV into a Google Sheets spreadsheet using IMPORTDATA is easy and fast. However, when it comes to using the function with the CSV stored on Google Drive, things don’t go as smoothly.
If you try to import a CSV file from Google Drive, you’ll get #REF! "Result was not automatically expanded, please insert more columns"
=importdata("https://drive.google.com/file/d/1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4/view?usp=sharing")
How to fix the IMPORTDATA error to import CSV from Google Drive
The good news is that you can still use IMPORTDATA to add a CSV file stored on Google Drive to Google Sheets. Here’s how you can dodge the #REF! alert.
- Share the Google Drive folder containing the file. Choose “Anyone with the link” and set the user role to “Viewer”.
- Get a shareable link to your CSV file. Here is how it may look:
https://drive.google.com/file/d/1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4/view?usp=sharing
- Paste the id part of the file (
1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4) straight into a Notepad on your device.
Note: we don’t recommend pasting IDs and links directly to Google Sheets since the platform might add extra characters to it, and the IMPORTDATA function will not run.
- Add the ID the URL below:
https://drive.google.com/uc?export=download&id=At the end of the day, your final link should look like this:
https://drive.google.com/uc?export=download&id=1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4Note: make sure there are no spaces between “=” and the id.
Tip: enter the link in the address bar to make sure it’s legit. If a CSV file starts downloading automatically, that’s a sign you did everything right.
Go back to Google Sheets and use the link you just created in the IMPORTDATA syntax. Your spreadsheet will load successfully.
IMPORTDATA or Coupler.io?
To import data from a CSV file on GDrive with IMPORTDATA, you’ll need to transform a shareable link into a downloadable link to your file. This manipulation will be rather painful if you deal with dozens of CSV files. Coupler.io will release you from any headache and simply imports your CSV data to Google Sheets. In addition to GDrive, you can also export your CSV data from files stored on OneDrive or Dropbox. Is the choice clear? It’s only up to you 🙂 Good luck with your data!
