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!
Synchronize your Excel files stored on OneDrive with Google Sheets
Let’s import data from an Excel Workbook on OneDrive to Google Sheets.
For this we need to install Coupler.io. It’s a Google Sheets add-on that allows you to retrieve data from different sources, including CSV, Xero, QuickBooks, and many more. You can find Coupler.io on the G Suite Marketplace or install it using this direct link.
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.
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”.
<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.
Synchronize your Excel files stored on Dropbox with 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
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
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