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 Excel ?
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:
- Automatically upload his offline Excel files to his Cloud storage solution.
- 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 by clicking the button below.
Sheetgo
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
Upload to your Cloud storage
Sheetgo automatically connects to 3 different Cloud storage solutions. In this example, however, you’ll see how to automatically upload your offline Excel files to Google Drive:
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.
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.
Conclusion
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.
If you’d like to learn more about the various features in Google Sheets, why not take a look at our blog post on How to create macros in Google Sheets.
