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 – IMPORTDATA
- Automated – Coupler.io
IMPORTDATA is a native function to fetch CSV and pull data to spreadsheets. 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.
Which way to go with to import CSV from GDrive into Google Sheets
|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.|
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.
URLis the location of a .csv or .tsv file containing the data to be imported. The URL must be specified, including the protocol.
IMPORTDATA formula example
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.
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"
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 a spreadsheet. 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 of your CSV file. Here is how it may look:
- 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:
At the end of the day, your final link should look like this:
Note: 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.
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, setting up an importer add-on is a convenient option to consider. Coupler.io, for one, is an easy-to-use tool that helps consolidate data from different sources in Google Sheets. You can install Coupler.io as follows:
- Create a new spreadsheet and choose Add-ons -> Get add-ons.
- Type Coupler.io in the Google Workspace Marketplace tab or use this direct link.
- Click “Install” and choose the Google Account you want to connect the add-on to. Then go back to Google Sheets and start the add-on.
An importer is an integration that connects Google Sheets to a specific app or data source. The CSV importer is designed to import CSV, TSV and XLSL (more on this is in the blog post import Excel to Google Sheets).
Open Coupler.io in the Add-ons tab of a spreadsheet, click on the +Add Importer button and choose CSV.
Read more about the CSV importer and how to import CSV to Google Sheets.
Complete the following steps to set it up:
1. Fill out the “Title” field
Enter the name of your importer.
2. Set up your data source
Enter the shared URL of your CSV file stored on GDrive.
3. Set up your data destination
Enter the name of the sheet, which will be receiving data.
4. Configure importer’s settings
Enable the Automatic data refresh if you want to import CSV data on a specific schedule.
For more about additional fields and settings available, refer to the Coupler.io knowledge base.
5. Save & Import
Click Save to save the parameters or Save & Import to save the parameters and run the initial import right away.
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!Back to Blog