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. I'll write about that another time, but feel free to ask questions below in the meantime.

65 comments:

  1. Enjoyed the article, do you have any further information on the googlefinance() workaround?

    Want to program a sheet to record a portfolio value at the close of each trading day...

    ReplyDelete
    Replies
    1. Thanks for the comment. I've just made a new post which explains how to do that and provides some sample code.

      Delete
  2. Thanks for posting this. I have been looking for this functionality for quite some time. However, your code may need to be updated. I tried your code and it would only put in the date. So, after a little trial and error and some research. I used the getRange(row, column, numRows, numColumns) version of getRange(). Then it worked perfectly. Thanks again!

    https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(String)

    ReplyDelete
    Replies
    1. Thanks for the comment. Glad you were able to get it to work. Not sure why the form of getRange() in the post didn't work for you. I use it in exactly that form in a spreadsheet that runs nightly. But the important thing is you got it working.

      Delete
  3. Great! Thank you very much for sharing this article!

    ReplyDelete
  4. This is great! Thanks. I have been trying it? Are you sure GoogleFinance() does not execute when the spreadsheet is not open? I have been using it, and it seems to update itself. Strange. Thoughts?

    ReplyDelete
    Replies
    1. How are you telling that the function is working when the spreadsheet is closed? As soon as you open the spreadsheet it will execute. But if you are running a script from a timed trigger, and it executes while the spreadsheet is closed, then in my experience the cell is #NA. The workaround is to use the FinanceApp service from within Google Apps Script. But maybe something has changed?

      Delete
    2. yes i am running it with a timed trigger. i can tell it is updating because the figures are updated each day. i have never had a n/a value. Strange.

      Delete
    3. plus it works for values scaped by googlefinance() and importhtml()

      Delete
    4. That's great. I wonder if something changed with the transition to New Google Sheets. Either way, thanks for letting me know it works.

      Delete
    5. the only thing to check is whether any of the values are simply copied over from the previous day's value, without being updated. but most of mine seem to update. interesting to hear how it works for others.

      Delete
    6. Just wanted to drop in and say thanks for the article. It does seem to be working for my spreadsheet that utilizes a ton of GOOGLEFINANCE() calls. This technique you covered a) worked flawlessly b) was very clear (to me at least) to understand. Thanks!

      Delete
  5. Made some adjustments by doing the following

    var sheet = ss.getSheetByName("XXXX");
    var source = sheet.getRange("E1:E14");
    var values = source.getValues();
    values[0][0] = new Date();
    var historicalData = ss.getSheetByName("YYYYY");
    var newData = [];
    for (var key in values) {
    if (values.hasOwnProperty(key)) {
    newData.push(values[key][0]);
    }
    }
    historicalData.appendRow(newData);

    var values = source.getValues(); is returning something like
    [[xx], [yy], [zz]].
    Hence appendRow will only append to the first cell of each row.
    appendRow takes in the following param,
    sheet.appendRow(["a man", "a plan", "panama"]);

    Hope this helps to whoever needs it.

    ReplyDelete
    Replies
    1. I've tried the original code as well as zeen tan's suggestions and I still only get the date and time returned in the first column. The current cde I'm using is:

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Account");
      var source = sheet.getRange("B7");
      var values = source.getValues();
      values[0][0] = new Date();
      var historicalData = ss.getSheetByName("EOD Data");
      var newData = [];
      for (var key in values) {
      if (values.hasOwnProperty(key)) {
      newData.push(values[key][0]);
      }
      }
      historicalData.appendRow(newData);
      };

      Any suggestions on how to get this to work?

      Delete
    2. Hi Darren - I'm not sure what your script is trying to do in the portion where you iterate over key in values. At that step values is a 2 dimensional area with only 1 value in it which is the current date.

      Your script first reads cell B7 from the Account sheet into values[0][0] and then overwrites this with the current date. It then writes this out to the EOD Data sheet, since the for iterator is effectively doing nothing.

      Which cells from the Account sheet are you hoping to write to the EOD Data sheet? If it is just the value from B7 then the following code should work

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Account");
      var source = sheet.getRange("B7");
      var value = source.getValue(); // getValue() not getValues()
      var historicalData = ss.getSheetByName("EOD Data");
      historicalData.appendRow([new Date(), value]);
      };

      Hope this helps.

      Delete
  6. Thanks you so much for this! I have one problem, the time displayed in column A seems to be in a different time zone from mine (I'm in GMT +1, the chart seems to be showing GMT +8). Do you know how to change this?

    Thanks again!

    ReplyDelete
    Replies
    1. There are two places that you need to check. The first is the time zone of the spreadsheet, the second is the time zone of the script. For the first, click File > Spreadsheet settings... from the spreadsheet. For the second, go into the Script editor (Tools > Script editor...) and then click File > Project Properties. In each case you should see a Time Zone setting that you can adjust. Hope this helps.

      Delete
    2. Thank you, it was the spreadsheet time zone! Also, I saw in the comments above that some people seemed to get the sheet to auto update with GoogleFinance. How has this worked for you? Do I need to use the GoogleFinance funktion or does it work without it?

      Delete
    3. You're welcome. I use the FinanceApp approach that I write about in this other article http://www.gadgetsappshacks.com/2014/01/how-to-record-daily-portfolio-values-in.html but it does sound like - from what others have reported here - that the GoogleFinance approach will work fine. I haven't gone back and tried it again since others indicated that it worked, so I can't confirm it. I can confirm that the FinanceApp approach works.

      Delete
  7. Okay, I will try it out and report back! It got it working fine but when I try to add another set of data for another graph, beside the first one (in your example above your data is in columns A-D, now I'm trying to make a new code for columns E-H) the code copies the data to the first columns (A-D) instead of E-H. This is my code:

    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("History");
    var source = sheet.getRange("E3:H3");
    var values = source.getValues();
    values[0][0] = new Date();
    sheet.appendRow(values[0]);
    };

    Do you know how i get it to paste the new data in column E-H?

    Thank you for your quick replies!

    ReplyDelete
  8. You could replace the last 2 lines with

    var row = ["","","","","",new Date()].concat(values[0]);
    sheet.appendRow(row);

    ReplyDelete
  9. Hi again!

    I have been adding different data sets beside each other in a sheet and everything works nicely thanks to you, except that the data imported doesn't want to sit on the same line. So I have one set of data A-B and one C-G that is automatically imported once a day, but they only fill every other row. Do you know a way around this?

    ReplyDelete
  10. Hi Alex,

    Thanks for the code.

    Your original code is working fine for me. I am not using digits but as time stamped comments. Each time I enter new data, it is copied below the previous time stamped data. Eg I enter details into one cell, this is then replicated with the date in A17 and the comments in B17. The next data I input will put the latest info in row 18 and then tomorrow any new updates will be transferred in 19 and on and on.

    How would we go about having the new data placed above the most recent data ie that my information will always be entered in row 17 and all historical data will push down a row so that the oldest is at the bottom not the top?

    Thx

    ReplyDelete
    Replies
    1. In the sample code above you could replace the appendRow line with something like

      sheet.insertRowBefore(17);
      sheet.getRange("A17:D17").setValues(values);

      Delete
  11. Exactly what I've been looking for -- thanks so much! The one change I made was to edit the date output since I don't want the full time stamp:

    function tracker(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Tracker");
    var source = sheet.getRange("A2:E2");
    var values = source.getValues();
    values[0][0] = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy");
    sheet.appendRow(values[0]);
    }

    ReplyDelete
    Replies
    1. Thanks. I'm glad it worked for you. I tend to just output the full timestamp and adjust the format of the column to only show the date. But sometimes it seems to forget that formatting with new rows, so your change looks like a good one that I should make in my own script.

      Delete
  12. Alex, is there a way you can use this as a tab within a single sheet?
    thanks

    ReplyDelete
    Replies
    1. I'm not exactly sure I understand what you mean, but this can certainly be one tab (sheet) without a single spreadsheet. That's how I use it.

      Delete
  13. Hi, I tried to use the script to get the value from "Portfolio!B4" and record it on the sheet "Record". I have the following script after reading the article and the comments, yet I have only dates on the first column.

    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Record");
    var source = sheet.getRange("B4");
    var value = source.getValue(); // getValue() not getValues()
    var historicalData = ss.getSheetByName("Portfolio");
    historicalData.appendRow([new Date(), value]);
    };
    Any idea?

    ReplyDelete
    Replies
    1. Your script is reading from Record!B4 not Portfolio!B4 - and is writing to Portfolio. You should reverse where you have 'Record' and 'Portfolio'

      Delete
  14. HI Alex, I have been using this script over two months now successfully. Nevertheless, since two days ago, I've been getting " #NA" on the cells that correspond to the new data. I have tried several fixes but it doesn't work. Any ideas? Thanks a lot for the help.

    ReplyDelete
    Replies
    1. How are the values that you are recording the daily history of being calculated? Are you using any formulas to calculate the values? For example, if you are using the GoogleFinance function, then you would get #NA in the history sheet, because that function only calculates the values when the spreadsheet is opened. Have you changed anything about the way the values are calculated in your spreadsheet that perhaps uses a new formula?

      Delete
  15. Hi Alex, thanks a lot!
    Any way to record a column not a row?

    ReplyDelete
    Replies
    1. So you want to take a column of values, and record it as a new column in a history sheet? Yes, it's do-able, although not as straightforward because Google Apps Script doesn't have an "appendColumn" method like it does for "appendRow". So you have to use getValues() and setValues() and also use getLastColumn() to work out which column on the History sheet you need to write into.

      Delete
    2. If the values you want to record are in the first column (A2:A) and you want to "append" them in a new column then the following should work...

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("History");
      var source = sheet.getRange("A2:A");
      var values = source.getValues();
      values[0][0] = new Date(); sheet.getRange(1,sheet.getLastColumn()+1,values.length,1).setValues(values);
      };

      Delete
  16. Hi Alex,

    How do i append a range. I changed the getRange("A2:D2") to getRange("B2:R50") but it still returns the first row only.

    Please help!

    ReplyDelete
    Replies
    1. The script is using appendRow() which only appends a single row. If you want to append a range you'll need to use setValues() and this will require using either getLastRow() or getLastColumn() to work out where you want the range written. See my comment above about writing out a new column of values each time - which uses this function and hopefully that will help.

      Delete
    2. Hi Alex, to continue with this conversation, is there any way to select a range from one sheet and write it out at the end of a different sheet?

      Delete
    3. You'll probably find it useful to refer to the Google Apps Script reference guide for the objects Sheet and Range
      https://developers.google.com/apps-script/reference/spreadsheet/sheet
      https://developers.google.com/apps-script/reference/spreadsheet/range

      If you wanted to always read row 2 of a sheet named "Input" and append it as a new row in the sheet named "Output" then you would do something like this:

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var inputSheet = ss.getSheetByName("Input");
      var source = inputSheet.getRange("A2:D2");
      var values = source.getValues();
      values[0][0] = new Date();
      var outputSheet = ss.getSheetByName("Output");
      outputSheet.appendRow(values[0]);
      };

      Delete
  17. Hey Alex, great article!

    I tried to append a range and insert the output in a new sheet but only the first row is appearing.

    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var inputSheet = ss.getSheetByName("Sheet1");
    var source = inputSheet.getRange("A25:G27");
    var values = source.getValues();
    values[0][0] = new Date();
    var outputSheet = ss.getSheetByName("database");
    outputSheet.appendRow(values[0]);
    };

    I can see that it's because appendRow only appends a single row, but I'm lost on how I can append the whole range. Any suggestions?

    ReplyDelete
    Replies
    1. Thanks.

      Yes, replace outputSheet.appendRow(values[0]); with...

      outputSheet.getRange(outputSheet.getLastRow()+1,1,values.length,values[0].length).setValues(values);

      Delete
  18. Hi Alex
    trying to run your script I always get the following error:
    "TypeError: Cannot call method "getRange" of null. (line 5, file "Code")"

    Cells B2 to D2 contain numbers imported from other sheets with IMPORTRANGE and the numbers appear correctly in the cells.

    Regarding cell A2 I tried to leave it empty, to write "Current", to write TODAY(), but whatever I try get always the same error.

    What am I doing wrong?

    ReplyDelete
    Replies
    1. It sounds from the error message that the variable 'sheet' is null. This would happen if the call to getSheetByName failed. The most likely cause of this is you have a typo in your sheet name. Perhaps you have "History" in the script, but the sheet name is something else?

      Delete
    2. Yes, you are right, the name of the whole spreadsheet was correct. But it was the name of the single sheet that I left as it was Sheet1. But when I changed this name it worked.

      So really many thanks for posting this guide and also for taking the time to personally help me.

      Happy New Year

      Delete
  19. Could you explain what the last two lines do?
    values[0][0] = new Date();
    sheet.appendRow(values[0]);

    ReplyDelete
    Replies
    1. values[0] is an array containing the contents of the first row fetched (in this case A2:D2). values[0][0] is the first cell in that row. The two lines replace the first cell with the current date, and then append a new row to the spreadsheet with those values.

      So basically the script takes the values from A2:D2, replaces the first value with the current date, and appends a new row to the sheet with those values.

      Delete
  20. I am trying to use your script to record every day the "Total Shares Outstanding (millions)" value that today is 273 on this page http://www.nasdaq.com/symbol/gld/institutional-holdings together with other similar data on other pages. Is it possible to do that?

    ReplyDelete
    Replies
    1. If you're using IMPORTXML or IMPORTHTML then probably not, because of the caveat at the end of the article "this approach only works for values and formulas that don't need the spreadsheet to be open". Unfortunately in the case of IMPORTXML and IMPORTHTML I believe the values are only populated when a user has the spreadsheet open. I wrote another article about how to work around this in the case that you were using the GOOGLEFINANCE function: http://www.gadgetsappshacks.com/2014/01/how-to-record-daily-portfolio-values-in.html

      In the case of information you're trying to read from a web page you'll need to use the UrlFetchApp service in Google Apps Script instead of the FinanceApp service. You can find the service documentation here: https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app

      Delete
    2. Thanks, but I would probably die before being able to sort something working out of this.

      Would you be willing to write the code for me for money?

      Delete
    3. I'm pretty busy, but if you can post your email address here, or get a message to me through twitter, then I'll see if I can help.

      Delete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Hi Alex,

    Thank you for posting the scripts. It is really benefit for dummy users like me who has no experience in writing scripts

    I do have a question though - can both spreadsheet be combined together? i.e. Other Data and History contents and formulas are shown in one spreadsheet? I try to do it in one spreadsheet "MAR17" as per below however the data is not captured.

    Looking forward to your reply and Thank you in advance.


    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Mar17");
    var source = sheet.getRange("AG19:AH19");
    var values = source.getValues();
    values[0][0] = new Date();
    sheet.appendRow(values[0]);
    };



    Thank you
    IRvin

    ReplyDelete
    Replies
    1. Both "Other Data" and "History" are sheets (aka tabs) in the same spreadsheet in my article. Not separate spreadsheets. But perhaps you are asking if they can both be in the same sheet of the same spreadsheet? If so, then yes, and the script wouldn't change. The script is written to take row 2 on the History sheet (tab) and duplicate it as a new row. Row 2 can contain anything you want. It doesn't need to refer to another sheet (the "Other Data" in my example).

      Delete
    2. Thank you Alex, yes I was meaning to combine both sheets (aka tabs) together.

      One more question - can I request the value recorded in a specific cell, e.g. AG20:AH20 for the time stamp and value? Thank you

      cheers
      Irvin

      Delete
  23. So rather than appending a new row, you just want the timestamp and value to always be written in AG20:AH20? Yes, that's possible, although obviously wouldn't give you a history - only the last values. But you would replace this line

    sheet.appendRow(values[0]);

    with

    sheet.getRange("AG20:AH20").setValues(values);

    ReplyDelete
    Replies
    1. Hi Alex,

      Sorry I meant to say the values written to a range of cell starting from AG20:AH20 and next AG21:AH21, AG22:AH23 and so on. Sorry for not being so clear about the question on the first hand. Thank you

      cheers
      Irvin

      Delete
    2. It's possible, but starting to get kinda complicated. Might be easier to have a separate sheet that you append a row to (as the script above does), and then in the cells where you want the data to appear you just include references to the separate sheet.

      Delete
    3. That's good idea! Love it! Thank you Alex!

      Delete
  24. Alex I cant get your script to do what I need it to do.
    My data is on a sheet called Daily Sheet, the rows I want to pull data from are B22 through E22. I want to take that data each day and record it on a sheet that is called Month Total, with each day getting a new row just like you explain. Somewhere Im not getting a sheet name right because it wont fill into new sheet except data

    ReplyDelete
    Replies
    1. If you reply with your code, and the names of the two sheets perhaps I can help.

      Delete
    2. Im using your script exactly my data sheet is "Daily" I want it to record the data each day to "Month Total"

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Daily");
      var source = sheet.getRange("B22:E22");
      var values = source.getValues();
      values[0][0] = new Date();
      sheet.appendRow(values[0]);
      };

      Delete
    3. Try this...

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Daily");
      var source = sheet.getRange("B22:E22");
      var values = source.getValues();
      values[0][0] = new Date();
      var sheet = ss.getSheetByName("Month Total");
      sheet.appendRow(values[0]);
      };

      Delete
    4. Yes sir that was perfect thanks for the help!

      Delete