Dynamic data within spreadsheets constantly changes according to various inputs, formulas and external data sources we apply. Google Sheet’s cloud-based system is a fantastic tool for dynamic data. This is because of its real-time transfers, which means you receive the latest version of your data no matter what. But what happens when you want to track changes in Google Sheets and keep a record of the historic values from your dynamic data?
There are many instances where you would want to track previous values.
For example, to keep track of stock market prices over time to evaluate share investments or predict future prices. Or perhaps you need to track your organization’s KPIs to determine the next steps of your business strategy. So, how can you track changes in Google Sheets?
Unfortunately, Google Sheets does not yet offer a historical record feature. If you want to keep a log of data changes, you must manually copy and paste your records every time. This is extremely time-consuming and critically reduces the level of efficiency and productivity you’d otherwise have.
However, Sheetgo offers an Append feature to solve this issue. The Append feature in Sheetgo automatically makes a record of all the changes made into a spreadsheet, so you don’t have to worry about a thing.
In this article, let’s explore in more detail what the Append feature is, and how you can track changes in Google Sheets automatically using the Append feature in the Sheetgo add-on.
Track changes in Google Sheets
Why record historical values?
Instead of entering, processing, and storing all your data in one file, a workflow keeps dynamic data safely separated and allows you to create a log of historical changes in one central master sheet. This enables you to:
- Keep a log of data changes at specific time intervals
- Save older versions of your data without copy-pasting to a new sheet
- Automate the entire data recording process
- Generate dashboards and charts to analyze trends over time
The Sheetgo Append feature
The Append feature in Sheetgo will make a record of any data changes within a spreadsheet automatically. Sheetgo created a workflow between your source data (the spreadsheet containing the dynamic data) and a new spreadsheet where your historical values will be recorded.
Sheetgo also gives you the opportunity to set an automatic transfer schedule. This means that you can determine the frequency when your new spreadsheet is updated with changes. With automatic transfers, you won’t ever need to open the spreadsheet to manually enter data again!
Now that you understand what the Append feature in Sheetgo does, let’s go through a step by step on how to Append your spreadsheet data in Google Sheets using the Sheetgo add-on.
How to track changes in Google Sheets
Let’s take the example of the stock market prices to demonstrate how to use the Append feature in the Sheetgo add-on.
In my spreadsheet, I update the same row every day with the latest stock prices for several tech companies.
However, in order to predict future prices and evaluate which companies I should buy shares in, I need to know how these stocks are performing over a period of time.
Instead of manually copy-pasting variables every day, I can create an automated workflow to carry out this task using the Sheetgo add-on.
Step 1: Open the Sheetgo add-on
Install the Sheetgo add-on for Google Sheets by clicking the button below.
Sheetgo add-on
Or, open the Sheetgo add-on directly from inside your Google Sheets file. In your spreadsheet containing your dynamic data, find the navigation menu and click on Add-ons > Sheetgo > Start.
If not yet installed, get the add-on via the Get add-ons option.
As we want to export this data, click Select data under the Export data section.
Make sure you select the correct tab with your dynamic data.
Under Settings, you can adjust features such as transfer formatting to make sure your data is transferred correctly. This includes any headers or data formats, such as dates, currency, or time.
As I have data formatted as a date in our spreadsheet, I will enable the Number formatting feature and make sure I select the correct Date format.
Once completed, press Continue.
2. Connect to a new Google Sheets file
It’s time to connect your spreadsheet to a new file. Under the Send data section, select Google Sheets. We want to create another Google Sheets file where our historical values will be recorded.
Here, you can make changes to your new file name, tab name and destination. To make sure I don’t mix this new sheet up with our original file, I have named it “Stocks (tech) – Historical data”.
3. Append your data
To activate the Append feature, click on the Settings section located underneath the file information. Simply slide the button to enable the Append feature.
You can also adjust the other settings depending on your needs:
- Lock destination tab: Enabling this will prevent any edits made to your destination file. Sheetgo will remove any edits made after each automatic transfer.
- Parse data: Sheetgo will identify and transfer any numbers, text, and formulas in your spreadsheet. If unchecked, Sheetgo will simply transfer your values as raw data instead.
Once finished, press Finish and save.
4. Edit your Automate schedule for data transfers
You should now see your completed connection on the right-hand side of your spreadsheet. Now, we can adjust the automation settings to determine the frequency with which Sheetgo transfers the data from our original file to the new file.
To schedule automatic transfers of your data, select the Automate button.
Once you are happy with your transfer frequency, press Save.
5. Enjoy your new historical value data in Google Sheets!
Your connection is now up and running! When you access your new spreadsheet, it should look something like this:
Your fresh data will append below the existing data entries every day. You can now track changes in Google Sheets automatically. You don’t even need to open a spreadsheet! To adjust the update schedule or switch off automatic updates, just open the add-on and change the settings.
Automatically track changes in Google Sheets with Sheetgo
And there you have it! Using the Sheetgo add-on, you can create a workflow that not only tracks your historical data, but also automatically transfers your data for ultimate efficiency.
Interested in learning more about Sheetgo’s useful features? Discover our Merge function in our blog post on How to merge multiple sheets in Google Sheets.
Alternatively, take a look at our related articles below!
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.
