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