How to automatically convert Excel to Google Sheets
Spreadsheets of the past were offline desktop files, but today’s cloud-based software allows for location-independent collaboration. And Google Sheets has been leading this innovative front. We can now store these spreadsheets online and share them with colleagues using access controls: allowing different people to view or edit the file. While Google Sheets has covered a lot of ground with these advancements, not everything is hassle-free…yet. For example, data transfer between Google Sheets and Microsoft Excel is not exactly seamless. But here we’ll explain how you can easily convert Excel to Google Sheets.
Why convert Excel to Google Sheets?
Even though Excel and Google Sheets are competitors, your team might still use both of them.
- Some people are used to Excel spreadsheets; others enjoy the collaborative and flexible Google Sheets.
- Working together, and sharing your data with each other, can be difficult. Therefore, it is useful to know how to automatically upload Excel to Google Sheets, and the other way around.
Meet Judith. She’s an economic research analyst and her job requires her to gather insights from data sets. She travels a lot to gather data on the field. Due to the nature of her job, she needs to use different devices to collect data while on the run. Sometimes an iPad. At times, her Thinkpad. And, once she is done with fieldwork, she uses a heavy-duty desktop at her office. Given the convenience of accessibility from anywhere and any device, she uses Google Sheets to store her data.
However, she has associates working at her office who do not travel. And, they use Microsoft Excel for their spreadsheet needs. And everybody in their office, including Judith, uses Microsoft Outlook for email. Judith usually receives individual datasheets from them, and she strings the pieces together. To date, their approach has been the following:
- The associates send Excel files via email.
- Judith downloads them onto her computer.
- She then imports the downloaded Excel spreadsheets to Google Sheets.
That is certainly a time-consuming
How do I automatically upload Excel files to Google Sheets?
In this case, the configuration is a simple three-step procedure:
- If you don’t already use it, install Google Drive.
- Upload the Excel file(s) automatically to your Drive using this back-up and sync software. Note that if you have your Excel files stored in Dropbox, you can access them directly from inside Sheetgo.
- Create an automated connection between the uploaded Excel file and Google Sheets file, using the Sheetgo add-on.
How to automate Excel data import with Sheetgo
While the first step means the associates don’t need to send Excel files via email, remember that you need to have permission to access the file on Google Drive (the correct share setting must be activated). The second step automates the downloading and importing process—saving Judith time!
I. Upload Excel file to Google Drive
Ken is Judith’s associate. He’s got an important data set ready to share with Judith, who will combine this with her existing research data. Here’s what the Excel file looks like:
You can upload this Excel file to Google Drive with the help of Google Drive software on PC/Mac. If you do not have this installed, you can download the software from here and install. If you simply want to use Gmail, choose to download the Personal option, or if you need to use all G Suite applications, choose to download the Business software.
Now, going back to our example, all that Ken has to do is drop the above-mentioned Excel file onto the Google Drive folder on his computer! This can be done by simply adding or dragging the file to Google Drive, or installing the Back-up and Sync software if he wants to automatically upload each file (stored in a particular folder) to his Drive.
And, Ken can grant Judith access to this file by doing this: Right-click on the file name > Choose Google Drive > Click Share.
On the resulting pop-up window, enter Judith’s email address and hit the Send button.
II. Create a connection using the Sheetgo add-on and automatically import Excel data.
Now that Judith has all the dependent tasks done, she follows these next steps to establish a connection and automatically convert Excel to Google Sheets that Ken shared.
(Please note that Sheetgo supports XLS, XLSX, and XLSM formats).
- Open the Google Sheets file that we want to import data to.
- If the Sheetgo add-on isn’t installed already, just click the button below to install Sheetgo in our Google Sheets application.
1. Start Sheetgo and select the file you want to import data from
By installing, a new Google Sheet will automatically be created. Follow the next steps to create the connection between the Excel file and this new sheet, using Sheetgo:
- Go to menu Add-ons > Sheetgo > Start.
- Click on the green + button to create a connection.
- Choose your data source by clicking the Select file(s) button. This leads us to the list of most recently updated files by default. If we don’t see the file we want, we can use the search module to lookup a file within Google Drive. Or we can click on Upload files to directly select a file from our computer.
- Find and select the required Microsoft Excel sheet from our Google Drive. We can recognize Excel sheets easily by their icon.
- Select the source sheet tab that we want to import the data from.
2. Customize the settings as needed, and create a connection
- You can choose to adjust the frequency settings to automate the import on a scheduled basis, but for now, we will leave it as it is.
- Click on the SAVE CONNECTION button.
Sheetgo will now automatically import your Excel data to Google Sheets
Keeping the connection active
We have automated the entire process from uploading the Excel file through to importing data into Google Sheets using the Sheetgo add-on (see the first screenshot). But, how do we ensure Sheetgo always fetches current information? It is very simple! In the example we considered above, all that Ken has to do to ensure that Judith always sees updated data is the following method:
- For making any changes to the file content, Ken can directly open the file from the Google Drive folder on his computer, make changes and save it in the same folder. Doing this will ensure that the changes get synchronized with Google Drive. And therefore, the existing Sheetgo connection will automatically import the most recent Excel data.
Learn how to back up data from Excel file to Google Sheets in the following blog post: Backup data from Excel file to Google Sheets.