Track changes in Google Sheets: How to record historic values
Spreadsheets of today not only give us a way to store data but also provide us graphical tools to be able to identify any inherent trends. Data in Google Sheets can change over time depending on the inputs, formulas, and external data sources. Not surprisingly, the data we usually like to draw trends from is usually time-bound. Therefore, saving historic data is the fundamental step to track changes in Google Sheets.
Why record historic values?
There are several purposes why we would want to track changes in Google Sheets. For example, to preserve laboratory readings, or organizational Key Performance Indicators (KPIs), or location-specific climate profiles. In the interest of this post, let us consider the case of Andrew.
Andrew is a stockbroker. He holds shares of a few companies whose stock prices he wishes to keep track of. Here is how he sees the daily stock information.
Given the nature of his job, he analyzes how these stocks have been doing over a period of time. Hence, he will need the means to store such data on a regular basis. For Andrew to be able to track changes in Google sheets, he would have to copy-paste these values every day to record this information. The reason being that there is no feature native to Google Sheets that allows us to preserve the historic value of a changing variable. Repeating the manual copy-pasting process is not desirable at all.
Solution: Track changes in Google Sheets automatically with Sheetgo
We will use the Sheetgo Add-on’s Append functionality to accomplish this process. For the purpose of this demonstration, we will configure Sheetgo as such that the data from Stock prices today file flows every day into Stock prices – historic data file.
We recommend that the source data sheet (in this case Stock prices today) has a single tab. And also that it contains only the data we would like to record historic values for. Doing this will ensure that we record only the variable(s) that we would like to record and not all the other data that might be on the source Google Sheet.
Following are the steps we take to realize this automation.
1) Start Sheetgo and initiate a connection
If not already installed, we can get the Sheetgo add-on by clicking on the button below.
Having installed Sheetgo on our Google Sheets application, a new Google Sheets file will automatically open. Give it an appropriate name to avoid confusion. In this case: Stock prices – historic data. To start the add-on, we navigate to Add-ons > Sheetgo > Start.
Doing so, Sheetgo will open as a sidebar on the right side of your screen. Click on the + button to start creating the connection.
2) Link the destination file with source sheet with that we need to import the data from
Clicking the Select file(s) button 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. For this example, we select Stock prices today file for Data source, and then for tab choose Prices today.
frequency and create a connection
In the Settings section, enable the Append button to record the historic values. We may choose the import time and frequency with which the data import can happen in the Automatic update section. Since Andrew needs a track of daily information, we enable this button and leave it as it is, i.e. Daily at midnight. If it makes it convenient for us, we can edit the name of the connection with which a new sheet is created in the destination file.
Once done, we now click on the SAVE CONNECTION button to establish a new connection between the source and the destination file. Here’s how the Stock prices – historic data file will look like, immediately after we perform this step.
If we opened the Stock prices – historic data file after the week ended, it should look something like this below:
We can observe that the fresh data is appended below the existing data every day, in accordance with the varying daily input source information. Therefore, this is just a one-time setup that Andrew can configure, and the Sheetgo add-on connection we just created, will automatically track changes in Google Sheets.
The tracking keeps happening automatically and periodically once the appropriate connection is in place. Please note that we can change the frequency with which these data imports happen or even turn off the automatic update altogether.
If you want to see how you can easily track changes in Excel files, take a look at this blog post.