How to record data in Excel to Google Sheets historically
Storing our data on either internal or external storage devices for backup is now a thing of the past. We are ushered into the era of Cloud computing technology, where we upload the data and access it from any location or device. Without ever having to worry about losing it, that is! Many of you might already be aware that Google Sheets is a cloud-based spreadsheets application. We can use it to store the spreadsheets, open and modify them, and also share them with other users. But, can we use it to record historic data from an Excel file? We can, but not without a little help.
Why record data in
Irvin is an experienced stockbroker. For the purposes of analysis and daily stock tracking, he uses Microsoft Excel. He had written an automated script (macro) on his Excel. Its job is to fetch current stock information of a few companies he invested in, at a designated time of the day. Below is the snapshot of the refreshed stock data of that day.
Given the time-sensitivity of the data which changes every day, he would like to keep a record of the
It can get very challenging to continuously record historic data from Excel file to Google Sheets whenever the stock information changes every day. Since Irvin is usually very busy with other important things, he’d rather not do this backup activity manually. Also, unfortunately for Irvin, there is no native Google Sheets function in place that can take care of this for him.
Automate the backup process with Sheetgo
For this use case, the configuration to record data in Excel historically is going to be a simple two-step procedure. You will have to:
- Install Google’s Backup and sync software and store the Excel file on Google Drive. Note that if you have the Excel file stored on Dropbox, you can ignore this step because you can immediately access your Dropbox Excel file from inside Sheetgo.
- Create a connection between the uploaded Excel file and Google Sheets file, using Sheetgo.
Follow the steps below to see how it works.
Step 1: Install Sheetgo
Install Sheetgo here. Once inside the web app, click on Connect to create the Append connection between your Excel and Google Sheets file.
Sheetgo’s Append feature allows you to track historical data from any spreadsheet to another sheet.
Step 2: Excel as your data source
If you have your Excel files stored in Dropbox, you can immediately access them from inside Sheetgo.
Add to Google Drive
If you do not have them stored in Dropbox, to connect them to your Google Drive automatically.
Rather than moving the Excel files to Google Drive manually, one by one, you can automate this process by using Google’s Backup and Sync software.
Just store the Excel files you want to upload in the same folder on your computer, then select this folder to automatically upload to your Drive using Backup and Sync.
Go to Google Drive > Computers > Select folder.
Select the folder containing your Excel files and right-click to add it to your Drive.
You’re now able to easily access your Excel files in Sheetgo, either from inside your Drive or straight from Dropbox.
Search and find the Excel file and its specific tab you want to export to Google Sheets, and add it as your data source.
Step 3: Google Sheets as your data destination
Let Sheetgo create a new Google Sheets file for you, or select an already existing one from inside your Google Drive.
Scroll down a bit and find the Settings. Here, make sure to enable the Append data button.
Finally, click on Finish and save to create the Append connection that will create a historic track of your Excel entries into the Google Sheets file.
Step 4: Automate the workflow
As a result, you now have a workflow in which your Excel data appends in Google Sheets and creates new entries while saving the historical data.
To schedule automatic updates, click on Automate on the floating bar and set the frequency you want this workflow to update with: hourly, daily, weekly, or monthly.
The data backup keeps happening automatically and periodically once the appropriate connection is in place.
Please note that we can change the frequency with which these data backups happen (hourly, daily, weekly, or monthly) or even turn off the automatic update altogether.