Back to Blog

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

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:

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

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

Example:

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

How to get a shared link of a Dropbox CSV file?
  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/r73ppy9wdkoi4qd/Driver_Activity_Hour.csv?dl=0

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:

=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")
   ,";"
  )
 )
)

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. 

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