How to record data in Excel to Google Sheets historically

Sheetgo logo Sheetgo logo

Automate your spreadsheet tasks with Sheetgo

Recommended for Workspace

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.

How to Record Data in Excel: Refreshed Stock Data Sample

Given the time-sensitivity of the data which changes every day, he would like to keep a record of the historic data. Because of the ease of accessibility, he wishes to store it onto Google Sheets.

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:

  1. Automatically upload his offline Excel files to his Cloud storage solution.
  2. Create a connection between the uploaded Excel file and Google Sheets file, using Sheetgo.

Follow the steps below to see how it works.

how to record data in excel 2

Step 1: Install Sheetgo

Install Sheetgo by clicking the button below.

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.

You may also like…