Back to Blog

How to Sync an Excel File to Google Sheets from OneDrive/Dropbox or Google Drive

For the last 10 years, the interest in spreadsheet software has pivoted. Microsoft Excel, which had been the undisputed trendsetter since the early 1990s, yielded the palm to Google Sheets, a lightweight and web-based alternative.

However, this doesn’t mean that Excel remains a spreadsheet software just for offline work. Users can store their XLSX files on OneDrive, Dropbox, Google Drive, and other cloud document storage services. And, one day, they may need to convert their Excel data to Google Sheets. Why? Reasons differ, but the data transfer flow is mostly the same:

  • Download an Excel file to a device.
  • Upload an Excel file to Google Sheets.

In this blog post, we’ll show you an alternative way free of any download/upload activities. You’ll be able to schedule data import from Excel to Google Sheets. Interested? Here we go then!

Excel stored on Google Drive to Google Sheets

Google Sheets provides a number of native functions to import different data, such as IMPORTDATA for CSV, IMPORTHTML for HTML and others. But there is no function to import Excel data. So, you’ll need to use a third-party solution – Coupler.io. It’s an importing solution to retrieve data to Google Sheets from online published files including CSV and Excel, as well as apps, such as Airtable, Xero, QuickBooks, and many more. Check out all available Google Sheets integrations.  

Let’s use it to synchronize your Excel Workbook on Google Drive with Google Sheets. You need to sign in to Coupler.io (you can do this with your Google account), click Add Importer and name it whatever you want:

Then configure the following parameters:

  1. Source
  2. Destination
  3. Schedule

Source

  • Application – select CSV as the source application. Click Continue.
  • CSV URL – insert the shareable link to your Excel file on Google Drive. 

Read how to get a shareable link to a file on Google Drive if you need some assistance.

Click Proceed to Destination Settings.

Destination

  • Destination account – add your Google account to connect. Click Connect and log in to Google or select your account from the drop-down list (if you’ve already added one). Click Continue
  • Spreadsheet – select the spreadsheet to synchronize your Excel Workbook with.
  • Sheet – select the sheet, which will be receiving data from your Excel file. You may enter the name of a non-existing sheet, and Coupler.io will create it for you. 

If you want to use optional parameters, Cell address and Import mode, click Continue. Otherwise, click Proceed to Schedule Settings.

Schedule

Here you can configure the schedule for automatic data imports. For example, the data from your Excel file will be refreshed every hour or every week. To do this, toggle on the Automatic data refresh and customize the schedule:

  • Interval
  • Days of week
  • Time preferences
  • Time zone

When ready, click Save and Run. After that you can jump right to your Google Sheets doc if you click the Open button. 

If you need to import CSV from Google Drive to Google Sheets, you should take the same steps as above. Read our blog post if you need guidance.

Excel stored on OneDrive to Google Sheets

Let’s import data from an Excel Workbook on OneDrive to Google Sheets.

This time, we’ll use Coupler.io as a Google Sheets add-on. So, you can connect to your Excel file right from your Google Sheets document. But first, install Coupler.io from the Google Workspace Marketplace.

Set up a CSV importer

After the installation, we need to set up a CSV importer.  

An importer is an integration that connects Google Sheets to a specific app or data source.

The CSV importer’s main purpose is to import CSV data to Google Sheets. You can treat it as an advanced alternative to the IMPORTDATA function in Google Sheets

At the same time, the CSV importer supports XLSX files. So, open Coupler.io (Add-ons => Coupler.io => Open dashboard), click Add Importer and select CSV. 

After that, perform the following steps:

1. Fill out the “Title” field

Enter the name of your importer.

2. Set up your data source

Enter the embedding code of your Excel file on OneDrive into the “CSV URL” field. 

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

Right-click on your Excel Workbook, select “Embed” and click “Generate”.

Example:

<iframe src="https://onedrive.live.com/embed?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211494&authkey=AA3LGeqPkcbY3zc&em=2" width="402" height="346" frameborder="0" scrolling="no"></iframe>

For more about the additional fields and settings available, refer to the Coupler.io knowledge base.

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 Excel data on a specific schedule.
Click Save & Run to save your importer and initiate the first import.

How to transfer Excel files with multiple sheets to Google Sheets

In the example above, the Excel Workbook only has one sheet. Is it possible to import an XLSX file with two or more sheets, like this one:

The CSV importer retrieves the first sheet of an Excel file. 

For example, our Workbook has the following sheets: Xero Invoices, Xero Invoices Simple, and Dashboard. In this case, the CSV importer will fetch the data from the Xero Invoices sheet. 

If you put the Dashboard sheet first in the order and run the importer, you will get the data from this sheet:

It’s quite uncomfortable to move sheets every time you need to refresh data. Besides, it won’t let you automate data import on a schedule.

So, if you need to synchronize multiple sheets of an Excel Workbook with Google Sheets, we recommend you create separate XLSX files for each sheet and reference data to the main Workbook.

Excel stored on Dropbox to Google Sheets

Dropbox is another popular cloud storage where you can store your XLSX files. With Coupler.io, you can import Excel data from Dropbox to Google Sheets as well. 

The setup flow is the same: Title => Source => Destination => Settings (or Schedule if you use the web version)

The only difference is that you need to enter a shared link of your Excel file on Dropbox into the “CSV URL” field.

How to get a shared link of a Dropbox XLSX file?
  • Point your XLSX file on Dropbox and click “Share”.
  • Find the field “Anyone with this link can edit the file” and click “Copy link”.

Example of a shared link of a Dropbox Excel file

https://www.dropbox.com/scl/fi/qbenokvp1u3xf845cb994/Test-data.xlsx?dl=0&rlkey=zzjgx0uf629reuwibu0af8h5i

Insert this link into the “CSV URL” field and you can import your Excel data.

Using the same flow, you can import CSV from OneDrive and Dropbox to Google Sheets.

Do I need to sync Excel with Google Sheets?

If you manipulate refreshable datasets in Excel, you should definitely connect your Workbook to Google Sheets. With Coupler.io, you can customize the data refresh schedule, as well as specify the range of data to import. This is quite useful for reporting and sharing your data.

Besides, if your Excel Workbook contains heavy calculations and advanced formulas not supported by Google Sheets, you may want to import values to Google Sheets as an online source. 

What you can do is test the flow with Coupler.io and decide whether it is what you need. This won’t take much time, and has no cost from your side. Your feedback is highly welcome, so feel free to fill out this form. Good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free