Let’s say, you have a CSV file stored in a cloud storage, OneDrive or Dropbox. The older you would have had to make two steps to get this file to Google Sheets:
- Download it to your device
- Upload it to Google Sheets
This workflow is a pain when you need to update the data recurrently. Fortunately, the newer you can synchronize both files and automate data refresh on a schedule. Read on to learn how to do this.
Export OneDrive CSV to Google Sheets
Coupler.io is the life-saving solution below that lets you tackle the CSV export issue. It provides several ready-to-use integrations to Google Sheets from third-party apps, such as Airtable, Xero, and others. To import CSV to Google Sheets, there is a CSV importer. Here is how it works.
Sign up to Coupler.io with your Google account and click Add new importer. Name it as you wish and complete the setup:
Alternatively, you can install Coupler.io add-on for Google Sheets from the Google Workspace Marketplace and do the setup right from your spreadsheet.
- Choose CSV as a source application.
- 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=EC25D9490D879F34%211476&authkey=ABB3Hq4ODtkIh8w" 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.
- Click Jump to Destination Settings.
- Choose Google Sheets as the destination application and connect to your Google account.
- Select a Google Sheets file and sheet where the CSV data will be loaded. You can create a new sheet right here.
Optionally, you can change the first cell for your imported data range, change the import mode from replace to append, and add a column to the spreadsheet that contains the date and time of the last data refresh.
Click Save & Run to load your CSV data from OneDrive to Google Sheets. To automate data load on a schedule, enable the Automatic data refresh and configure the frequency.
After the import, click View Results to go to your spreadsheet.
Export Excel files from OneDrive to Google Sheets
Initially, you could use the CSV importer to export Excel files (.xlsx) from OneDrive to Google Sheets. But now, you can do this with the dedicated Excel to Google Sheets integration, which allows you to simply select an Excel Workbook on OneDrive and automate its export to Google Sheets.
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 as follows:
- Right-click on your CSV file and Select “Embed”. Click “Generate” to get the embedding code of your CSV file. For example:
<iframe src="https://onedrive.live.com/embed?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211477&authkey=AL-1fUS84RieEL" width="98" height="120" frameborder="0" scrolling="no"></iframe>
- Get the URL from the embedding code:
- In the URL, replace
Here is what you’ll get if you use this CSV URL in the IMPORTDATA formula:
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:
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
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") ,";" ) ) )
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