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 to 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:
- Application – select Google Drive as the source application. Click Continue.
- Source account – click Connect to connect a Google account or select one from the drop down list if you’ve already connected some. Click Continue.
- Spreadsheet – select the Excel file to export from your Google Drive.
- Sheet(s) – select the tab from the chosen Excel file.
Click Jump to Destination Settings.
Note: Optionally, you can select a range to export from the chosen Excel file. To do this, click Continue after you selected a spreadsheet and a sheet.
- Application – select Google Sheets as the source application. Click Continue.
- 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.
- Click Continue to configure the optional parameters, Cell address and Import mode. Refer to the Coupler.io knowledge base to learn more about these parameters.
By the way, you can easily change the destination and get your data, for example, from Google Drive to Excel.
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:
- 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 View Results.
If you need to import CSV from Google Drive to Google Sheets, the flow will be mostly the same. Read our dedicated blog post for 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.
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>
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
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