The manual import of a published online CSV file from an app or Google Drive to Google Sheets is a breeze. A few clicks will do the job. If you want to automate this process, you will need to write custom code with Google Apps Script. But, can I do this without any coding? Now you can. Spend a few minutes exploring Coupler.io, a data analytics and data integration platform that lets you automate parsing and importing online CSV data into Google Sheets.
Along with this, we will be covering other methods to import a CSV file to Google Sheets. Read on to learn more.
Why convert CSV into spreadsheets?
The CSV, which stands for comma-separated values, is mostly a go-between data format. It is supported by versatile apps and most databases. Here is how it usually works:
- you export data from a source app into a CSV file
- you import the CSV file to a target app
In our case, Google Sheets is the target app. Users opt for this multifunctional tool to handle calculation-centered and data visualization tasks. Entrepreneurs and small business owners do budgeting, accounting, and tax calculations in spreadsheets. Data analysts build custom reporting and versatile performance dashboards. You might have your own use case and reasons to automate CSV import to Google Sheets. Now, let’s discover how this can be done in less than 5 minutes of your time.
How to import SCV into Google Sheets in different ways?
As you might need to import CSV to Google Sheets for many reasons, there are different methods that you can use depending on where your CSV file is located and other details. Here are some of them:
- Automated import with a data integration tool. You can use a third-party solution that will help you export your data from CSV files to Google Sheets automatically on your preferred schedule. This is especially convenient when you need to update your information often.
- Using formulas. You can import CSV to Google Sheets with the help of the IMPORTDATA formula. In this case, information won’t be updated entirely automatically and you will need to run the formula every time you need to update.
- Manual transfer. This is a simple and free method, but it quickly gets inefficient if you need to update your information in the spreadsheet often – in this case, you will have to repeat this process manually every time your data in the CSV file changes.
Now, let’s explore each of these methods in more detail.
How to auto import CSV to Google Sheets from the cloud?
Coupler.io is a data analytics and data automation platform that allows you to address virtually any data-related tasks. In particular, it offers a data integration solution that allows you to export data from over 75 different data sources and transfer it to Google Sheets, Excel, Looker Studio, or BigQuery. CSV files are one of the available data sources. Coupler.io comes in two versions – as a web app and an add-on for Google Sheets.
To use the web app, sign up to Coupler.io (you can use your Google account), and click Add new importer in the My Importers tab. Then, complete the following steps:
- Select CSV as a data source and Google Sheets as a destination. Click Proceed.
- Enter the URL of your CSV file into the “CSV URL” field.
- For the HTTP method, select GET.
- If your file is password-protected, you will need to authorize. In the Key field type in Authorization, in the Value field – provide your authorization key, for example, Bearer xoxb-1h345kj24.
- You can also make use of the other optional settings – for instance, select the fields to be imported, specify the number of rows to skip, and others. If those settings are not required, click Jump to Preview and Transform to move forward.
At the next step, you can preview your data and rearrange it if needed – hide or rename columns, add new ones, calculate custom metrics directly into the Coupler.io interface, and so on. Once everything looks good, click Continue in the upper right corner.
Note: Here are some additional details for you if you’re exporting CSV files stored on OneDrive or Dropbox.
- Click Connect to connect your Google account, grant the requested permissions, then click Continue.
- Select a spreadsheet on your Google Drive and a sheet to import data to. Click Continue.
- You can also choose the import mode – replace or append – as well as toggle on the Last updated 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.
You can toggle on Automatic data refresh and customize the schedule for your data imports.
Click Save and Run to save the parameters and run the initial import right away. For more information about additional fields and parameters available, refer to the Coupler.io knowledge base.
Her’s the exported data:
That’s it! Now you know how to import CSV to Google Sheets automatically.
Note: The setup flow for the Coupler.io add-on for Google Sheets is the same, but you’ll need first to install it from the Google Workspace Marketplace.
How to automate CSV import from a Google Drive folder
To automate CSV import from a Google Drive folder, you can use two ways:
- Choose Google Drive as a source application and connect Google Drive to Excel or Google Sheets or BigQuery.
- Choose CSV as a source application and connect CSV to your destination app. In this case, however, you’ll need to get a shareable link of your CSV file:
- Go to Google Drive and right-click on your CSV file
- Select “Get shareable link”
- Change “Restricted” to “Anyone with the link”
Now your CSV file is shared: Anyone on the internet with this link can view
Note: The folder on your GDrive with the CSV file must also be shared with “Anyone with the link.”
Enter your CSV file’s shareable link to the “CSV URL” field for data import. Check out more about CSV from Google Drive to Google Sheets.
How to import multiple CSV files into Google Sheets
You can easily do this with Coupler.io. To begin, add a new importer and connect it to your first CSV file. We already explained how to do this in the previous sections of this article. Once your first CSV file is linked, press Add one more source.
SCV will be selected by default, as it’s your first data source. Provide the link for your second CSV file, choose the GET method, and complete the setup.
Once you are ready, click the Add one more source button again and connect your third CSV file. You can add as many as you like.
Once you are ready, switch to the Preview and transform module to check your data. Then, update the destination information, if needed, or add the destination account if you haven’t done so yet. You can use our instructions from the previous sections if you need help with this.
In the end, Save and Run your importer. After this, Coupler.io will import multiple CSV to Google Sheets automatically.
How to bulk-import CSV to Google Sheets on a schedule
In the previous chapter, we explained how you can transfer data from several CSV files at once. If you want to go further and bulk-import CSV to Google Sheets on a schedule, you can easily do this with Coupler.io as well.
First, connect your multiple CSV files to the importer as explained above. Once you finished configuring the destination (Google Sheets), you can switch to the Schedule block.
Toggle on the Automatic data refresh feature and specify your preferences. In our example, Coupler.io will transfer updated information from your CSV files to Google Sheets every hour on business days, from Monday to Friday.
When your schedule is ready, press Save and Run.
Import CSV to Google Sheets from the cloud with IMPORTDATA
In Google Sheets, there is a dedicated function, IMPORTDATA, for importing CSV or TSV data from a published online file. Just enter the file URL in the formula, =IMPORTDATA("URL"), and the data will be fetched within a few seconds.
Read our blog post to learn more about IMPORTDATA in Google Sheets.
IMPORTDATA vs. Coupler.io CSV importer
| IMPORTDATA | Feature | Coupler.io CSV importer |
|---|---|---|
| CSV, TSV | File formats supported | CSV, TSV, XLSX Read more about how to import Excel to Google Sheets with the CSV importer. |
| You are not safe from data loss, as each error cleans your data. | Output data type | You are always safe from total data loss if any error occurs. |
| You can make up to 50 calls on a single spreadsheet. | Limitations & errors | Only if the result is too big to be placed according to Google Spreadsheets limits or Google Sheets API limits that are used to place data. |
| No support has been officially offered. | Secure CSV link as a data source | You can use the Request headers field to provide authentication credentials and use data from secured data sources. |
| No support has been officially offered. | Data update on demand | You can RUN your importers any time you need to update data with one click of a button. |
| No support has been officially offered. | Scheduling | You can set up scheduled data updates. |
| No support has been officially offered. | Global data update | You can use the RUN ALL button or scheduling option. |
| Available with the help of the QUERY function. | Fields selection | You can cut off data you don’t need using the Fields parameter. This will let you import only the selected columns. |
| No support has been officially claimed. | Fields reordering | You can set up fields order for output. |
| Available with the help of the QUERY function. | Skip rows | You can easily skip rows with data you don’t need using the Skip rows parameter. |
| The cell in which you applied the function will be the target cell to get output. | Insert data at cell address | You can set up the target cell to get output where you need it. |
| No support has been officially offered. | Appending option | You can place data from multiple sources (with the same fields structure and order) using the append mode together with the skip lines parameter. This will let you get a single data set to work with (for example, to combine monthly reports into a single year report). |
| Available with the help of the QUERY function. | Advanced requests | You can make more precise requests, such as time ranges, users and others, using the URL query string parameter. This is useful when you import data from secured connections for API-styled requests such as reports from time-tracking tools (Toggl, Clockify) or similar ones. |
A combination of IMPORTDATA and QUERY can give you a wider scope of data import capabilities. We’ve blogged about the Google Sheets Query Function and what you can do with it. For the rest, the CSV importer looks more practical.
IMPORTDATA lacks any automation features. So, each time you need to update your imported CSV data, you’ll have to do it manually. The only way to automate importing purely in Google Sheets is to write a code using Google Apps Script. You may check out Stack Overflow and discover a code sample provided by one of the users. But should you?
Coupler.io already has this feature implemented. All you have to do is to toggle the automatic data refresh on and customize the schedule.
How to manually import CSV data into Google Sheets from your device
OK, if you do not work with CSV recurrently, you may not need to automate this flow. In this case, you should go with the manual import.
Open CSV in Google Sheets
The simplest way to open a CSV file in Google Sheets is to upload it. To do this, open your Google Sheets doc, go to File => Import, click Upload, and select the CSV file from your device.
Then you’ll need to select the Import location, separator type, and whether you need to convert text to numbers, dates, and formulas.
As a result, the CSV data from your file will be converted into a Google Sheets format. For example, this is what the Etsy listings exported in CSV look like after the import to Google Sheets:
So, it’s not a CSV file anymore. If you don’t want to convert CSV to Google Sheets, but need to keep it as CSV with the possibility of opening it in Google Sheets, do the following.
How to open a CSV file in Google Sheets without converting CSV data
Open your GDrive or GDrive folder you want to upload the CSV file to. Go to the Settings menu (click the gear wheel, and select Settings).
Make sure that the Convert uploads checkbox is unchecked.
Note: If you enable this function, all your CSV files will be converted to Google Sheets format when uploaded.
After that, you can drag and drop the CSV file from your device and it will be uploaded without any changes.
When you double-click on the file, the CSV data will be shown as a table.
If you need to open the uploaded CSV file in Google Sheets, click Open with and select Google Sheets in the drop-down menu.
Your CSV file will be open in Google Sheets in a separate tab, and a converted Google Sheets file will be created in the same folder automatically.
How to import large CSV to Google Sheets
In this article, we’ve described several methods that allow you to import CSV to Google Sheets: with a data integration solution, with formulas, and manually. In general, each of these methods is suitable for CSV files of various sizes.
The complications may arise, however, if your CSV file exceeds Google Sheets’ limits. The app allows you to have a worksheet that has:
- Up to 10 million cells
- Up to 18,278 columns (column ZZZ)
- Up to 50,000 characters per cell
If your CSV file is too large and contains more cells or columns than is allowed – unfortunately, in such a case, there’s no way to import such a file into Google Sheets. To deal with this issue, you might try splitting your dataset into several CSV files and then exporting them separately.
If this is not an option – then, maybe, Google Sheets is not such a good fit for your needs. You can consider working with Excel instead as it can handle much bigger files.
How to use CSV data imported to Google Sheets: Income monitor based on a CSV report
We have a database system, which generates a CSV report. We’ve been inspired by the Railsware video: “How to track income” and decided to build our own real-time income monitor. For this, the data has to be updated as frequently as possible.
So, first, we set up a CSV importer in Coupler.io, which will update our data every hour. Once the raw data is in the spreadsheet, we can use the power of Google Sheets: two formulas and a Geo chart to tailor a simple income tracker.
Now, with the automatic data refresh, your chart will update every hour, like this:
Easily import CSV to Google Sheets in minutes
When routine tasks are automated, this lets you focus on other valuable jobs that need to be done. Thus, you save your time and increase your efficiency. Coupler.io was specifically designed to make this happen. You’ll only need to spend less than 5 minutes on the installation and setup. After that, you’ll get a fully automated CSV data importer, which you can use for your project. You will only need to set up the importer once, and after that, Coupler.io will import CSV to Google Sheets automatically according to your schedule. You may also be interested in other integrations available with the use of the CSV importer: Clockify to Google Sheets and Jira Server to Google Sheets.
Good luck!
Zakhar Yung
A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼
