How to automatically record a daily history of values in a Google Spreadsheet

Google Spreadsheet does a great job of recording a change history for edits that you make. But what if you want to have a spreadsheet with values that change based on some formula, and you would like to keep a history of those values. For example, perhaps you keep a spreadsheet for your spending budget, or your investment portfolio. You could open the spreadsheet each day and copy the values into a new sheet, but it would be nice to automate this. Fortunately this is fairly simple using Google Apps Script.

Let's suppose you have a spreadsheet with various tabs and values that you want to keep. The simplest way to get started is to create a new sheet, which we will call "History". Each row in this new sheet will represent the values that you want to save each day, with a new row for each day. So column A will be the date, and columns B onwards will be values. In this history sheet the first row will be headings for your values, and the second row will be the current values. So cell B2 will be some formula that points at other cells on other sheets, and so is C2, D2 etc. for as many values as you want to save each day. Your new history sheet might look something like this

new "History" sheet in your Google Spreadsheet

Now we will write a function that will copy this row 2 to a new empty row in the spreadsheet and put in the current date in the first column. With the spreadsheet open click Tools > Script Editor and pick Blank Project. This should create one file called Code.gs with an empty function like this

function myFunction() {
  
}

Highlight this whole function and replace it with the following code

function recordHistory() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("History");

  var source = sheet.getRange("A2:D2");

  var values = source.getValues();

  values[0][0] = new Date();

  sheet.appendRow(values[0]);

};

Notice the values in quotation marks. On line 3 is the name of your history sheet (I just called mine History, but you can change this here). On line 4 you'll see A2:D2 which refers to the cells that contain the "current" values. Then on line 6 we set the value that will be written out in the first column of the new row to the current date. Once you've entered this function, click save. You'll be prompted to name the project, and you can call it something like "Record History".

Now let's test it before we schedule it to run automatically. In the toolbar above the function is a pull down menu which should say "recordHistory". This is the name you gave the function. Once that is selected, click the black "play" triangle button. You should see a message at the top saying that the function is running. You will then be asked to authorize the function. Click Authorize and you will be warned that the app wants to view and manage your spreadsheets. Click Accept, and the function will finish running. Your spreadsheet should now look something like this

History sheet with the first historical record

Each time this function runs it will write out a new row like the one shown above in row 3. So as the "current" values change, they will get recorded. You can see this by clicking the play button a few more times in the Script Editor. Now let's schedule this function to run automatically each day. Alongside the play button on the toolbar you should see a clock icon with the tooltip "Current project's triggers...". Click this. There should be no triggers setup currently, so click the link to add one now.

By default it should setup one for your new recordHistory function to run when the spreadsheet opens. But you probably want it to run at a certain time each day instead. So change the pull down list which currently says "From Spreadsheet" to "Time-Driven". Then pick the schedule that you want it to run on. For example, you might pick "Day Timer" and "8pm to 9pm". This will make it run once a day at some point between 8pm and 9pm each day. It should look something like this.

A trigger which will run recordHistory once each day between 8pm-9pm

Now click save to create this new trigger and you can close the tab with the script editor. Your spreadsheet will now automatically create a new row in the history sheet each day and record the "current" values. In this way you can see how the various values change over time. 

One caveat: this approach only works for values and formulas that don't need the spreadsheet to be open. For example, if your values are calculated using the GoogleFinance() function then the above approach will not work. The problem is that GoogleFinance() only executes when the spreadsheet is open. So when your trigger runs unattended the values will not be set. There is a fairly straightforward way to work around this using the Google Apps Script FinanceApp service, which has equivalent capabilities to the GoogleFinance() function. If that is what you're trying to do then read my article on "How to record daily portfolio values in a Google Spreadsheet".