I have a file with CSV data stored in a cloud storage, OneDrive or Dropbox. Last year, I would have had to make two steps to get this file to Google Sheets:
- Download it to my device
- Upload it to Google Sheets
This workflow is a pain when you need to update the data recurrently. Fortunately, I can synchronize both files and automate data refresh on a schedule. How? Check out the life-saving solution below!
Export OneDrive CSV to Google Sheets
I tackled the issue with a Google Sheets add-on, Coupler.io. It provides several ready-to-use integrations to Google Sheets from third-party apps, such as Airtable, Xero, and others. For my case, there is a CSV importer. It is a solution to import data from online published CSVs. As a baseline, you need a URL to your CSV file to fetch data. Check out the entire flow to import data:
Install Coupler.io from the G Suite Marketplace.
Once installed, open the Google Sheets doc you’re going to import CSV data to. Go to the following menus:
Add-ons => Coupler.io => Open dashboard
Click Add Importer and select CSV.
Set up the CSV importer
During this step you need to do the following:
1. Fill out the “Title” field
Enter the name of your importer.
2. Set up your data source
Enter the embedding code of your CSV file on OneDrive into the “CSV URL” field.
How do you get the embedding code of a OneDrive CSV file?
Right-click on your CSV file, select “Embed” and click “Generate”.
<iframe src="https://onedrive.live.com/embed?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211476&authkey=ADB3Kq8OVtkIh8w" width="98" height="120" frameborder="0" scrolling="no"></iframe>
Note: Do not use a shareable link to your CSV file on OneDrive as the CSV URL. The importer will fail to fetch data.
3. Set up your data destination
Enter the name of the sheet that 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.
Click Save & Run to save your importer and initiate the first import. For more about the additional fields and settings available, refer to the Coupler.io knowledge base.
Export Excel files from OneDrive to Google Sheets
The CSV importer by Coupler.io supports the export of Excel files to Google Sheets. When you open a CSV file in OneDrive, it is converted into Microsoft Excel Workbook (.xlsx).
Meanwhile, you don’t have to edit the importer that has been set up to import CSV data to Google Sheets. It will work as-is.
If you want to import another Excel file from OneDrive, get its URL in the same manner as for the CSV files. Insert the downloadable URL of a .xlsx file into the CSV URL field and run the importer to retrieve data.
How to import a OneDrive CSV file to Google Sheets with IMPORTDATA
IMPORTDATA is a native Google Sheets function to import data from a given URL in either CSV or TSV format.
Check out more about the function and how it differs from the CSV importer by Coupler.io in our IMPORTDATA Tutorial.
However, the function doesn’t work smoothly for importing CSV data from OneDrive. First, you need to prepare a CSV URL in the same way, as we did for the CSV importer above.
I applied the IMPORTDATA formula with the CSV URL that works with the CSV importer, and here is what I got:
IMPORTDATA pulled the data from the CSV file but malformed it.
To fix the issue, you should nest IMPORTDATA with ARRAYFORMULA, IFERROR, and SPLIT functions as follows:
=arrayformula( iferror( split( importdata("https://onedrive.live.com/download?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211477&authkey=AL-1fUS84RieELw") ,";") ) )
Read our blog post to learn more about How to Split Text, Date-Time And Other Data in Google Sheets.
CSV from Dropbox to Google Sheets
What about Dropbox users? What if they need a similar solution for CSV data import? Not to mention, the search interest in Dropbox is greater than OneDrive.
Coupler.io will help them as well. The setup flow is the same. The only difference is that you need to enter the shared link of a CSV file on Dropbox into the “CSV URL” field.
How to get a shared link of a Dropbox CSV file?
- Point your CSV file on Dropbox and click “Share”.
- Click “Create link”.
- Click “Copy link”.
Example of a Dropbox shared link:
So, insert the Dropbox shared link into the “CSV URL” field and welcome your CSV data in Google Sheets!
How to import a CSV file from a Dropbox to Google Sheets using IMPORTDATA
Unfortunately, you can’t just insert a shared link to a Dropbox CSV file in the IMPORTDATA formula – this won’t work:
Here is a workaround.
IMPORTDATA + SPLIT + ARRAYFORMULA to import a Dropbox CSV to Google Sheets
First, you need to tweak your CSV URL. Replace “
dl=0” with “
raw=1” in the shared link of your Dropbox CSV file. So, instead of
you should have
Use the edited CSV file URL in the IMPORTDATA formula, nested with ARRAYFORMULA, IFERROR, and SPLIT functions:
=ARRAYFORMULA( IFERROR( SPLIT( IMPORTDATA("CSV-URL") ,";" ) ) )
Here is how it works:
=arrayformula( iferror( split( importdata("https://www.dropbox.com/s/r73ppy9wdkoi4qd/Driver_Activity_Hour.csv?raw=1") ,";" ) ) )
Read more about How to Split Text, Date-Time And Other Data in Google Sheets.
Coupler.io or IMPORTDATA – which option to choose?
The option to choose depends mostly on your workflow.
If it’s a one-time import. IMPORTDATA is a native Google Sheets function, which requires no installation.
If you’re planning to perform recurring imports, Coupler.io is quite the better choice because:
- No manipulations with OneDrive or Dropbox links are needed. It’s very useful if you deal with multiple CSV files.
- Automatic data refresh is available. You can customize the schedule to automate your CSV data import from either Dropbox or OneDrive or both
- Import from multiple sources with one tool. Coupler.io is not only about CSV data import. You can set up integrations with Airtable, Pipedrive, Quickbooks and other sources.
Other data sources to import CSV to Google Sheets using Coupler.io
Besides the mentioned Dropbox and OneDrive, Coupler.io lets you fetch CSV data from any publicly available CSV file. Some of the use case have already been described on our blog:
- How to import CSV file on Google Drive to Google Sheets
- How to integrate Clockify data with Google Sheets to import reports
- How to import issues from Jira Server to Google Sheets
If you need help to set up your CSV importer, feel free to contact us. Besides, you’re welcome to tell us about any integration with Google Sheets you’d like to have. Fill out this form, and your request may result in a ready-to-use importer. For example, a request from one of our users kickstarted the Clockify importer. Good luck!Back to Blog