Back to Blog

How to Sync Your CSV File on OneDrive or Dropbox with Google Sheets

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:

  1. Download it to your device
  2. 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.

csv to google sheets

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.

Source

  • Choose CSV as a source application.
csv as source
  • Enter the embedding code of your CSV file on OneDrive into the CSV URL field.
csv url

How do you get the embedding code of a OneDrive CSV file?

Right-click on your CSV file, select “Embed” and click “Generate”.

3 get the embedding code of a OneDrive CSV

Example:

<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

Destination

  • 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.

destination csv onedrive

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.

9 stackby google sheets schedule

After the import, click View Results to go to your spreadsheet.

view results

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.

excel 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:
https://onedrive.live.com/embed?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211477&authkey=AL-1fUS84RieEL
  • In the URL, replace embed with download
https://onedrive.live.com/download?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211477&authkey=AL-1fUS84RieEL

Here is what you’ll get if you use this CSV URL in the IMPORTDATA formula:

=importdata("https://onedrive.live.com/download?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211477&authkey=AL-1fUS84RieELw")

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.

dropbox csv url
  1. Point your CSV file on Dropbox and click “Share”.
  2. Click “Create link”.
  3. Click “Copy link”.

Example of a Dropbox shared link:

https://www.dropbox.com/s/r73pxy5wfkoi3qd/Driver_Activity_Hour.csv?dl=0

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:

=importdata("https://www.dropbox.com/s/r73ppy9wdkoi4qd/Driver_Activity_Hour.csv?dl=0")

Here is a workaround.

IMPORTDATA + SPLIT + ARRAYFORMULA to import a Dropbox CSV to Google Sheets 

Step 1

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

https://www.dropbox.com/s/r73ppy9wdkoi4qd/Driver_Activity_Hour.csv?dl=0

you should have

https://www.dropbox.com/s/r73ppy9wdkoi4qd/Driver_Activity_Hour.csv?raw=1

Step 2

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. 

Choose IMPORTDATA

If it’s a one-time import. IMPORTDATA is a native Google Sheets function, which requires no installation.

Choose Coupler.io

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:

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

Comments are closed.

Access your data
in a simple format for free!

Start Free