The manual import of a CSV file from a device 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 Google Sheets add-on that lets you automate parsing and importing CSV data into Google Sheets.
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 automate CSV data import into Google Sheets?
Coupler.io is a Google Sheets add-on that lets you import data from different sources including CSV files. Install it via this link or find it on the G Suite Marketplace:
For more information about the add-on and available integrations, please visit the Coupler.io home page.
Set up an importer
An importer is an integration that connects Google Sheets to a specific app or data source. Since we’re going to import CSV data, we need to set up a CSV importer. For this, open Coupler.io in the Add-ons tab of a spreadsheet, click on the +Add Importer button and choose CSV.
Fill in the required fields:
- Title – add the name of your data source.
- Sheet Name – type in the name of your sheet.
- Re-import automatically – pick the schedule that you want to use for Coupler.io to update data in your spreadsheet.
- CSV URL – insert the URL of your CSV file.
Additional fields and Settings
Additional fields include: HTTP Method, Request headers, URL query string, Request body, Fields, and Skip rows. The Settings field allows you to choose a data import mode: replace or append, as well as specify a start cell where the data will be imported. For more on Additional fields and Settings refer to Coupler.io knowledge base.
With all the required fields filled in, click Add Importer.
Run your CSV importer and welcome your CSV data into a newly created sheet.
IMPORTDATA function: can I use it to automate importing?
In Google Sheets, there is a dedicated function, IMPORTDATA, for importing data from a given URL in CSV or TSV format. It lets you get your data from a published online CSV file within a few seconds, as follows:
Can this function be an equal alternative to Coupler.io? Let’s see.
IMPORTDATA vs. Coupler.io 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 claimed.||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 claimed.||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 claimed.||Scheduling||You can set up scheduled data updates.|
|No support has been officially claimed.||Global data update||You can use the RUN ALL button or scheduling option.|
|No support has been officially claimed.||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.|
|No support has been officially claimed.||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 claimed||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).|
|No support has been officially claimed||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.|
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, and all you have to do is to pick the schedule for your automatic data re-import.
Let’s explore how it may look in a real-life use case.
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 re-import 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 re-import of your CSV data, your chart will update every hour, like this:
Note: If you need to automate CSV import from a Google Drive folder, you need to use a downloadable file link. To create one:
- Get a shareable link to your file. It will look like this:
- Take the “id” part of your shareable link. In our example, this is “
- Construct a new URL by attaching the “id” part of your shareable link:
The id of your file should remain unchanged. Here is how it looks:
|Shareable file link||Downloadable file link|
Kudos to Maris Veide from Excelify.io for the tip.
To wrap up: program CSV import without a single piece of code
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. Do you have any questions? Feel free to ask them in the comments section. Good luck!