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".

153 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
    2. The above does exactly what I need it to do, but I'd love it if instead of placing the date it was pulled at 0,0, if it could place the date on every line pulled in column A, every time I run the report... does that make sense?

      Delete
    3. Not sure what you mean. The above script puts the current date on every line in column A. So when it adds row 2 the date will be in A2, then when it adds row 3 the date will be in A3, etc. Maybe share an example of what you want it to look like.

      Delete
    4. I'm running this on a whole range (outputSheet.getRange...ect) but it is only placing the date at at the top left cell of the pulled range every time I run it. I'd like it to place that date in the leftmost cell of every row for the whole range. Sorry - I'm lacking the terminology to explain it better!

      Delete
    5. I'd need to see the code, but if you were doing something like

      var values = sheet.getRange("A1:E5").getValues();
      values[0][0] = new Date();
      sheet.getRange("A1:E5").setValues(values);

      Then this would certainly only put the date in the top left cell. You'd need to replace the second line with

      for (var i=0; i<values.length; i++) values[i][0] = new Date();

      To make it put the date in the leftmost cell of every row.

      Delete
    6. That's perfect! I can tweak from here. Thanks so much for you code and the help.

      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
  25. Hi,
    I' using a sheet you pull data form the web. Financial data.
    It has a timer so the sheet updates every minute,
    but I want to create a columns with the historical data every hour.
    While the sheet is closed (when I'm away from the pc), and save it in she same sheet in a different tab.
    It is possible to do this?

    Thanks in advance.

    ReplyDelete
    Replies
    1. Yes, as long as the financial data is being updated in the sheet even when the sheet is closed then the script in this article should work for creating an hourly record of the values. You'll just setup the trigger to run hourly instead of daily.

      Delete
    2. Alex - I am attempting to pull just two points of data and record them to a a second sheet for the history, including time stamps. What Im looking for is something like;
      Current Date | $Data | $Data |
      however what I'm getting is the following;
      Current Date | $Data | Duplicate Data

      I'm using the following code. function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Tracker");
      var source = sheet.getRange("B14");
      var value = source.getValue(); // getValue() not getValues()
      var historicalData = ss.getSheetByName("History");
      historicalData.appendRow([new Date(), value, value]);
      };

      Can you help me with the adjustments I need to make?

      Delete
    3. Your script is only reading from Tracker!B14 and then it's writing that same value twice. So if Tracker!B14 contains 99 then you're outputting:
      Current Date | 99 | 99

      Is that what you want? Assuming that you're trying to read from two places in the Tracker sheet then you need to do something like

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Tracker");
      var source1 = sheet.getRange("B14");
      var value1 = source1.getValue();
      var source2 = sheet.getRange("C12");
      var value2 = source2.getValue();
      var historicalData = ss.getSheetByName("History");
      historicalData.appendRow([new Date(), value1, value2]);
      };

      Delete
  26. I am saving daily prices for a few stocks and I've used your script to add a line and save the data and it is working well, however, I only want it to run on business days(not weekends or holidays). Otherwise I believe I will end up with duplicate lines of the same price for Friday, Saturday and Sunday. Do you know how to set the timer to business days or a line of script that would recognize that a date has already been used?

    ReplyDelete
    Replies
    1. Triggers can't be set to only run on business days. But you could add some code at the top of the function which checks the current day and returns immediately if it is Saturday or Sunday. For example:

      var d = new Date();
      var day = d.getDay();
      if (day==0 || day==6) return; // 0=Sunday, 1=Monday, 2=Tuesday, etc.

      Insert this as the first 3 lines of the recordHistory() function and it will skip Saturday and Sunday.

      Delete
    2. I'll try it out. Thanks for your help!

      Delete
    3. Hi Alex - thanks for your help in the past. One more question. Is there a way to get the scrip to skip holidays? For instance, it copied Sept 4 (Labor Day) and I'd like it to skip the major holidays since the stock market is closed.Thanks!!

      Delete
    4. I can't think of any simple way. You could add a sheet with a list of dates you want the script to skip. Then at the beginning of recordHistory() have the script read all the dates and compare the current date against each of them. You'd need to use getYear(), getMonth() and getDate() on both the current date and the dates you read from the sheet to compare their values.

      Delete
  27. Was wondering if there is a simple way to add a function that copies formulas in other columns to populate new added row? The issue I'm having is that my formulas are in F3 & G3 and once data is updated and a new row is added, formulas are now in row 4 and I have to manually copy them to row 3.
    Any help will be greatly appreciated!
    Thanks
    /Roger

    function update() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("LIST10");
    var source = sheet.getRange("J2:N2");
    var values = source.getValues();
    values[0][0] = new Date();
    var outputSheet = ss.getSheetByName("LIST10");
    outputSheet.insertRowBefore(3);
    outputSheet.getRange("A3:E3").setValues(values);

    };

    ReplyDelete
    Replies
    1. If you add the following at the end of your function it will copy the formulas from F4:G4 to F3:G3. Is that what you want?

      var formulas = sheet.getRange("F4:G4").getFormulas();
      sheet.getRange("F3:G3").setFormulas(formulas);

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

    ReplyDelete
  29. I'm trying to have the formulas update each new row added so that the formulas in F3:G3 always takes the values from the new row added (A3:E3).
    The above code seemed to work at first but after looking closer, the formulas take values from lower rows (first row added after update).

    Thanks for your help.
    /Roger

    ReplyDelete
    Replies
    1. So there are formulas in F3:G3 that you want copied to each row that gets inserted before row 3? That's what that code ought to do as long as you put it at the end of the function. If you want to share a copy of your spreadsheet I could take a quick look.

      Delete
  30. Hello Chapman. Is it possible to copy data from sheet to another weekly ,but on a New Sheet every week?

    This is what I have:

    function Copy() {

    var sss = SpreadsheetApp.openById('');
    var ss = sss.getSheetByName('White Attendance');
    var range = ss.getRange('A:L');
    var data = range.getValues();

    var tss = SpreadsheetApp.openById('');
    var ts = tss.getSheetByName('Attendance History'); Sheet tab name
    ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

    }

    ReplyDelete
    Replies
    1. Do you mean to a new spreadsheet each week, or to a new sheet (tab) in the same spreadsheet each week? Either is possible. What you have above copies to the same sheet in the same spreadsheet.

      Delete
    2. Thanks for your timely response.
      I would like to create new sheet (tab) each week inside the 'Attendance History' spread sheet.

      Delete
    3. I'm going to assume that the script is inside the spreadsheet which you want to copy from, and that the data is in cells A:L of the sheet (tab) named "White Attendance". I'm also assuming that the ID of the spreadsheet named "Attendance History" is "X123456789"

      function Copy() {
      var sss = SpreadsheetApp.getActive();
      var source = sss.getSheetByName("White Attendance");
      var data = source.getRange("A:L").getValues();

      var tss = SpreadsheetApp.openById("X123456789");
      var target = tss.insertSheet("Week " + (1+tss.getNumSheets()));
      target.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

      }

      Delete
  31. Thanks! The script runs but stops at line 7. I don't understand the ts:

    target.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

    it reurtns this error: ReferenceError: "ts" is not defined

    ReplyDelete
    Replies
    1. Sorry, the line should be

      target.getRange(target.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

      Delete
  32. Nice script thanks Alex. A question, sometimes it seems the API I am using has some sort of error and pulls data as #NAME? instead of a number (this only happens about 1 in 10 times). Nevertheless when it does happen, it breaks everything.

    How would I code it so that the script checks the values[0][1] (this would be col B correct?) is an integer, and only then writes the values? If it's not an integer, it should either keep trying to run until the API works and an integer is called, or it should instead just skip completely.

    Thanks

    ReplyDelete
  33. Thank you for you script, work very well. But is there any way make the append row method start from second column, not first. My thank in advance !

    ReplyDelete
    Replies
    1. If there's already content in the first column, then appendRow is always going to insert below the lowest row that has any content. You'll need to write a lot more code to read all the values and find the first empty cell in column 2 and then write to that point. It's certainly doable but not trivial. You'll basically be using getValue() and setValue().

      Delete
  34. Amazing Script.
    I had slightly different requirement.
    I want to record of cell range only one of its cell(for example A4 in range A1 to A10) is not empty.
    Resultant cell range should only change if A4 if not empty if for instance A4 goes empty or blank nothing should happen to result cell. My motive here is to keep latest non empty value of cell.

    Would appreciate any kind of help or advice.
    Using Google Sheets.

    ReplyDelete
    Replies
    1. If you want this to happen whenever cell A4 is updated, then that's a little out of scope for this article. You should google "google apps script onedit" and read about onEdit triggers. If this is something that will happen on a schedule, using time based triggers as in this article, then it could be done with many of the same methods shown in this article. Something like...

      var value = sheet.getRange("A4").getValue();
      if (value) sheet.getRange("A1:A10").setValue(value);

      Delete
  35. Hi Alex, great script! In my case I am using IMPORTHTML formula for the data updates, but I am not seeing the data getting updated so I guess I need to force somehow for the time based trigger to update the IMPORTHTML cells, can this script be extended easily to achieve this?

    ReplyDelete
    Replies
    1. Unfortunately IMPORTHTML is another example of a Google Sheets function which requires the spreadsheet to be open to update. So it's the same problem I describe in the last paragraph (starting "One caveat") in the article.

      If you're trying to read the values from another spreadsheet and record them into the current spreadsheet then you'll need to do something like

      function recordHistory() {
      var ss1 = SpreadsheetApp.openById("XXX"); // replace XXX with spreadsheet ID
      var sheet1 = ss1.getSheetByName("Source");
      var source = sheet1.getRange("A2:E2");
      var values = source.getValues();

      var ss2 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss.getSheetByName("History");
      values[0][0] = new Date();
      sheet2.appendRow(values[0]);
      };

      Delete
  36. Thank you Alex, I am reading the source data in a range A2:A8, so I guess I need to "transpose" the result for the "sheet2.appendRow(values[0]);" to work, what would be the right script code for this? Cheers!

    ReplyDelete
    Replies
    1. Well there's other more elegant ways, but the simplest is probably

      sheet2.appendRow([values[0][0],values[1][0],values[2][0],values[3][0],values[4][0],values[5][0],values[6][0],values[7][0]]);

      Delete
    2. It just occurred to me that it is just as simple to setup a staging sheet and transpose the source sheet data to get it into a row for the original script to work as is! Thanx

      Delete
    3. Correct. As long as you don't use IMPORTHTML to build the staging sheet. Good luck.

      Delete
  37. I misunderstood: I thought the SpreadsheetApp.openById("XXX") part would allow us to get the IMPORTHTML to automatically update the values by the script, but it does not seem to work. Are there any workarounds to get the IMPORTHTML data updated with a script timer trigger?

    ReplyDelete
    Replies
    1. Yes, I had miss-read IMPORTHTML (reading from a website) for IMPORTRANGE (reading from a spreadsheet) in your comment when I wrote my first reply.

      To import from a website in a script you'll need to use something like

      var url = "http://somewebsite.com/somepage.html";
      var response = UrlFetchApp.fetch(url);
      var html = response.getContentText();

      But extracting the specific portions of the html you want is not trivial and nothing for which I can provide a simple script. If the html happens to be well-formatted (unlikely) then you could try using the Google Apps Script XmlService.parse() function. If it's not then you'll need to resort to using some kind of regular expression together with the javascript String match() method (Google "javascript string match" for examples).

      Delete
  38. I found a script that worked well so far to automatically refresh IMPORTHTML (and other IMPORT..) formulas, hope it adds value to this thread:

    function RefreshImports() {
    var lock = LockService.getScriptLock();
    if (!lock.tryLock(5000)) return; // Wait up to 5s for previous refresh to end.

    var id = "[YOUR SPREADSHEET ID]";
    var ss = SpreadsheetApp.openById(id);
    var sheet = ss.getSheetByName("[SHEET NAME]");
    var dataRange = sheet.getDataRange();
    var formulas = dataRange.getFormulas();
    var content = "";
    var now = new Date();
    var time = now.getTime();
    var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
    var re2 = /((\?|&)(update=[0-9]*))/gi;
    var re3 = /(",)/gi;

    for (var row=0; row<formulas.length; row++) {
    for (var col=0; col<formulas[0].length; col++) {
    content = formulas[row][col];
    if (content != "") {
    var match = content.search(re);
    if (match !== -1 ) {
    // import function is used in this cell
    var updatedContent = content.toString().replace(re2,"$2update=" + time);
    if (updatedContent == content) {
    // No querystring exists yet in url
    updatedContent = content.toString().replace(re3,"?update=" + time + "$1");
    }
    // Update url in formula with querystring param
    sheet.getRange(row+1, col+1).setFormula(updatedContent);
    }
    }
    }
    }

    // Done refresh; release the lock.
    lock.releaseLock();

    // Show last updated time on sheet somewhere
    sheet.getRange(7,2).setValue("Rates were last updated at " + now.toLocaleTimeString())
    }

    Source: https://stackoverflow.com/questions/33872967/periodically-refresh-importxml-spreadsheet-function

    ReplyDelete
  39. That RefreshImports() function looks very interesting. Thanks for posting it.

    ReplyDelete
  40. I am using your original function to record the data of stocks from google finance:
    function recordHistrory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("History");
    var source = sheet.getRange("A2:LY2");
    var values = source.getValues();
    values[0][0] = new Date();
    sheet.appendRow(values[0]);

    };
    but the data is not updating fast and is coping the previous row data often the sample of the recorded data is hereunder:
    Date & time ABAN ABB ABFRL ACC
    10/24/2017 12:02:12 187.8 1356 148 1791
    10/24/2017 9:15:41 182.95 1333.8 147.1 1786.7
    10/24/2017 9:20:41 183.05 1347 147.25 1782
    10/24/2017 9:25:41 183.05 1347 147.65 1782
    10/24/2017 9:30:41 183.05 1347 147.5 1782
    10/24/2017 9:35:41 184.35 1347.95 147.8 1789.95
    10/24/2017 9:40:41 184.35 1347.95 148.05 1789.95
    10/24/2017 9:45:41 184.35 1348 148.15 1796.05
    10/24/2017 9:50:41 184.35 1348 148.05 1796.05
    10/24/2017 9:55:41 184.35 1344.65 147.7 1794
    10/24/2017 10:00:41 184.15 1344.45 147.25 1793.85
    10/24/2017 10:05:40 184.15 1344.45 147.95 1793.85
    10/24/2017 10:10:41 184.15 1344.45 148.6 1793.85
    10/24/2017 10:16:27 184.15 1344.45 148.4 1793.85
    10/24/2017 10:21:27 184.6 1345.5 148.45 1804.9
    10/24/2017 10:26:27 184.6 1345.5 148.3 1804.9
    10/24/2017 10:31:27 184.6 1345.5 148.45 1804.9
    10/24/2017 10:36:27 184.6 1345.5 148.25 1804.9
    10/24/2017 10:41:27 184.6 1345.5 148.2 1804.9
    10/24/2017 10:46:27 184.6 1355.1 148.2 1797.35
    10/24/2017 10:51:27 184.6 1355.1 148.15 1797.35
    10/24/2017 10:56:27 184.6 1355.1 148.05 1797.35
    10/24/2017 11:01:27 184.4 1356.9 147.25 1797.7
    10/24/2017 11:06:27 184.4 1356.9 147.85 1797.7
    10/24/2017 11:11:27 184.4 1356.9 148 1797.7
    10/24/2017 11:16:27 184.4 1356.9 148 1797.7
    10/24/2017 11:21:27 184.6 1363.45 147.85 1794.75

    Please help me so that it doesn't repeat the previous row and every time it should take the data of first row (fetched from google finance).

    ReplyDelete
    Replies
    1. If row A2:LY2 is using the GOOGLEFINANCE() function then it will only update when someone has the spreadsheet open. Read the last paragraph in the article that starts "One caveat".

      Delete
  41. then please suggest me the solution of my problem. I want to record the live 5 minute data of stocks in excel or google spreadsheet.

    ReplyDelete
    Replies
    1. See this article http://www.gadgetsappshacks.com/2014/01/how-to-record-daily-portfolio-values-in.html

      Delete
  42. Sir, sorry to disturb you again & again but I have not got the answer yet. my question is that even if I keep opened the google spreadsheet and use the google finance function to get updated stock values, I noticed that the stock values were changing continuously but the history of recorded values at 5 minute interval were the same as of last row often. what changes in your script can be done so that I get the latest value in the new row.

    ReplyDelete
    Replies
    1. As I said, and the original article says in the last paragraph, you can't use this script with the GOOGLEFINANCE() function. If you want to record a regular history of stock prices, then you need to use the script in the other article that I reference (and that is referenced in the last paragraph of this article). It doesn't use the GOOGLEFINANCE() function, because that approach simply won't work for what you are trying to do.

      Delete
  43. Hi Alex,

    I'm trying to create a column to capture the SUM of all my stocks stored in cell A1 automatically at the end of every month in another cell (e.g : Jan 31st store value of A1 in B1, Feb 28th A1 value in C1 etc.. ).

    Any idea how do i go about it ?

    ReplyDelete
    Replies
    1. It's easier if you can store the values into a new row rather than a column. Because then you can use the appendRow() function that this post describes. There is no corresponding appendCol() function. I would start by creating a new History sheet and putting the value that you want saved each month in A1 on that sheet, then using the script to create a new row on that sheet with that value. It can be triggered with a time based trigger that runs on the 1st of each month.

      If you still need the values arranged across in columns, then you can just use the Google Sheets TRANSPOSE() function. For example, if the values are currently written on the History sheet in B2, B3, B4 and you want them on some other sheet in B1, C1, D1 then you put =TRANSPOSE(History!B2:B) into the cell B1.

      Delete
  44. Can we capture other type of prices, OPEN CLOSE HIGH LOW other than only close values?

    ReplyDelete
    Replies
    1. The FinanceApp service used to support this, but has been deprecated. You would either need to write a script that scrapes a finance web site for this information, or find a REST service and interface with that. The article linked from the last paragraph in this article describes using FinanceApp but the sample code linked from it has been updated to use the Google Finance web site to get prices. That code could be modified to extract the open/close/high/low.

      Delete
  45. This script is exactly what I needed for my project, thank you! Where could I look for more information about re-starting the process of numbering my new sheets every day? I am pulling 5 minute snapshots of data to manipulate but I would like to re-start the process of filling my tabs every 24 hours so I can link my dashboard to always pull from pre-determined sheets.

    For example, I have 144 sheets named PAGE1, PAGE2, PAGE3, etc that reflect 24 hours of data. I would like the script to re-populate PAGE1 on data pull 145 instead of creating PAGE145. I'm essentially looking for a workaround in lieu of building and maintaining a database. Any advice is appreciated and thanks again for this post!

    ReplyDelete
    Replies
    1. It sounds like you have a script running every 10 minutes and you want it to write to PAGE1 the first time it runs (on a given day) then PAGE2, then PAGE3, ... up to PAGE144, and then have it loop back around to PAGE1 at the beginning of the next day.

      There's a couple of approaches you could take. One would be to use script properties. These let you set and get a property which persists across executions. So you could keep a counter which you increment each time until it reaches 145 and then reset it back to 1.
      https://developers.google.com/apps-script/reference/properties/properties-service

      The other approach is to calculate which page you should be on using the current hour and minute of the day. Something like

      var d = new Date();
      var h = d.getHours();
      var m = d.getMinutes();
      var num = (h*12) + Math.floor(m/5);
      var name = "PAGE" + num;

      The former (script properties) is probably the most reliable, because Google doesn't guarantee execution time consistency (can theoretically be +- 15 minutes). Although in practice I've always found it very reliable.

      Delete
  46. Hi,

    I need a script that can take values from several rows and copy them to columns in one row with date added in the first column.

    Let's say I have a spreadsheet like this:

              A          B
    1 | Value X | 100 |
    2 | Value Y | 150 |
    3 | Value Z | 200 |

    I want to copy these values to another sheet, like this and add date:
                 A                B             C             D
    1 |      Date      | Value X | Value Y | Value Z |
    2 | 01.01.2018 |    100    |    150    |    200    |

    I've currently modified your script like this:

    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Sheet X");
    var source = sheet.getRange("B1");
    var sheet = ss.getSheetByName("Sheet Y");
    var value = source.getValues();
    var row = [new Date()].concat(value[0]);
    sheet.appendRow(row)
    };

    Which gives me:

            A       B
    1 | Date | 100 |

    I'm stuck and need yout help, thanks!

    ReplyDelete
    Replies
    1. You're mostly there.

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Sheet X");
      var source = sheet.getRange("B1:B3");
      var sheet = ss.getSheetByName("Sheet Y");
      var value = source.getValues();
      sheet.appendRow([new Date(),value[0][0],value[1][0],value[2][0]])
      };

      Delete
    2. Thanks so much for the quick reply!

      Delete
    3. So I've run into another problem. I'm now trying to get the new new row on top, and push older rows down. I've read the explanation further up in the comment section, but I can't to get it right.

      function recordHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Portefølje");
      var source = sheet.getRange("E5:E12");
      var sheet = ss.getSheetByName("Kurshistorikk");
      var value = source.getValues();
      sheet.insertRowBefore(6);
      sheet.getRange("A6:K6").setValues(["",Utilities.formatDate(new Date(), "GMT+01", "dd.MM.yyyy 'kl.' HH"),value [0][0],value[1][0],value[2][0],value[3][0],value[4][0],value[5][0],value[6][0],value[7][0]]);

      I get an error saying "Cannot convert Array to Object. I've been searching all over for a solution but I cannot seem to find any. Are you able to help? Thank you!

      Delete
    4. While appendRows() expects an array of values, setValues() expects an array of arrays. So you just need to wrap the array with one more set of square brackets:

      sheet.getRange("A6:K6").setValues([["",Utilities.formatDate(new Date(), "GMT+01", "dd.MM.yyyy 'kl.' HH"),value [0][0],value[1][0],value[2][0],value[3][0],value[4][0],value[5][0],value[6][0],value[7][0]]]);

      Delete
  47. Hi Alex, thanks for continuing to answer this thread after so long. I've looked at every example you have here and after many hours of testing, still just get the date stamp as a results. I was wondering if you could comment each line, so I understand what each function does?
    Sheet data is coming from: "Actuals"
    Cell data is coming from on actuals: L12
    Sheet I want this cell copied to: "Log"

    my code:
    function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Actuals");
    var source = sheet.getRange("L12");
    var values = source.getValues();
    values[0][0] = new Date();
    var sheet = ss.getSheetByName("Log");
    sheet.appendRow(values[0]);
    };

    Like I said, when I run that, I just get the datestamp. The value from cell Actuals!L12 doesn't get carried over.
    Thoughts?



    ReplyDelete
    Replies
    1. For the script to work the way it's written you need to provide a range rather than a single cell where you currently have L12. The script replaces the left most cell in the range you provide with the timestamp, and leaves the rest intact. So since you only want the value from L12 then you should pass "M12:L12". The script reads both M12 and L12 values, and will then replace the value from M12 with a timestamp before appending the row. Give it a try (i.e. replace "L12" in your script with "M12:L12") and it should work.

      Delete
    2. !! Thank you! That worked. Had to go the other direction and use K12:L12 (not M), but now it works great!.
      The key part I wasn't understanding was the range component, but I get it now.
      Again, thank you for continuing this thread for so long.

      Delete
    3. Yes, you're right, I should have said K12:L12. Got my letters mixed up. Anyway, glad you got it working. Thanks for the comment.

      Delete
  48. Hi Alex, I'm new at this script thing but am making progress thanks to your post.The code adapted from a few examples above is doing what I want it to do. The one issue I can't resolve is the time stamp that I don't want. I've tried to format the column as you suggested but that doesn't work. I've tried the other ideas on here but really don't know what to change.
    this is the code i'm using

    function GAIN() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("SCOTIA");
    var source = sheet.getRange("K18");
    var value = source.getValue(); // getValue() not getValues();
    var historicalData = ss.getSheetByName("GAIN");
    historicalData.appendRow([new Date(), value]);
    };

    SCOTIA is the sheet I'm getting the data from(K18) and GAIN is my new sheet.

    Thanks for any help.

    ReplyDelete
    Replies
    1. If you don't want the timestamp, then you can just remove the "new Date()," bit. So the appendRow call will look like

      historicalData.appendRow([value]);

      Delete
    2. I copied and pasted that Alex and now I get no data in column B and my date shows as 1903-11-03.

      Delete
    3. I don't know what data you are reading from SCOTIA!K18 but it's getting written into the A column which you presumably have formatted as a date column. So that 1903-11-03 is whatever it read from SCOTIA!K18 formatted as a date. If you want it written to column B then just insert "", into the appendrow line. So make it something like

      historicalData.appendRow(["",value]);

      Delete
  49. Sorry to keep bothering you Alex, when I changed it to that I got my data back in column B but no date at all in column A.

    ReplyDelete
    Replies
    1. I'm really confused about what you want. You started by saying that you were getting a time stamp that you didn't want. I showed you how to remove it. What is it you want in column A?

      Delete
    2. Column A should be the date only with no time stamp, column B is the data that I'm pulling from SCOTIA sheet.

      Delete
    3. Well you can just go back to the original code you wrote and reformat column A to only show a date in the spreadsheet (select column A and click Format > Number > Date).

      If you really need the column to actually only contain a date then you can replace "new Date()" in the original code you had with

      Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy")

      But unless you re-format the column as well it will likely to continue to show as a timestamp.

      Delete
  50. Success! It works! Thank you very much Alex!

    ReplyDelete
  51. Hello Alex, thank you for your post, it helped me a lot !
    I have an issue though, the value are linked to API that stops actualizing value whether I launch the time trigger
    Have not found anything in the comments,
    Do you have a clue ?

    ReplyDelete
    Replies
    1. I'm not exactly clear what you're saying, but I think you mean that the value which you are reading from is being put there by an API. And that when you run the script from a time trigger, there is no value visible. This sounds like the problem I describe in the final paragraph of the article (beginning "one caveat"). If so, then this script won't work for you. You will need to find a way to read the value from the API directly in the script. Depending on the API you might investigate using UrlFetchApp.fetch(url) to get the value rather than trying to read it from the spreadsheet.

      Delete
  52. Thanks for this post Alex. It has helped me with what I have been trying to implement for some time.

    Cheers,
    D

    ReplyDelete
  53. Hello Alex, this is an awesome guide. I've been following some comments and I see this is still active so I was wondering how I might be able to get specific values at various points in the sheet (example B11, B13, and E15) without the values in between and print to another sheet.

    This is a huge help, thank you very much!

    ReplyDelete
  54. Well that's the idea of the script in the article. You just need to make the History sheet so that cell B2 points to cell B11 on whatever source sheet, an cell C2 points to cell B13 an cell D2 points to E15. So let's say those cells and values that you want to read are on a sheet call Data, then you put =Data!B11 into B2 on History. Similarly for the others. Then the script just works as-is. That's how I use it myself.

    ReplyDelete
  55. Hi Alex!

    I’m looking to solve this:

    I collect via an IMPORTXML function several values form different web sites and store them in a column, I also need to keep not only the actual value, but also the MIN a MAX values through history.

    So let’s say I have a ActualValue in Cell A1 and HistorialMin in Cell C1 and HistoricalMax in CellD1… How could I auto update the Min and Max values?

    ReplyDelete
    Replies
    1. If you used the script in this article to keep a history of the value in cell A1, then you could just make C1 and D1 calculate the MIN and MAX of the column of values on the history sheet. If you need it to be more dynamic then you'll need to explore using an "On Edit trigger".

      Delete
  56. Hi Alex!
    Is there any way to modify this script so that it transfers the results of a column that is being edited daily to a log sheet at the end of every day?
    Basically what I'm trying to accomplish is this: There is one column in particular that users are editing throughout the day. At the end of the day, it will either read "yes" or "no" for each user. I have a script that auto-clears the entire column at 11 PM every night. What I would like is a script that auto-copies the final content of the column to the next empty column of a log sheet at 10 PM, before it clears. The first column of the log sheet is the names of the users (so I can identify whose results/changes I'm looking at) and the first row needs to be a date stamp. I feel like I'm close to having what I want but just can't quite figure it out.
    Any help would be very much appreciated!
    Thanks,
    Effe

    ReplyDelete
  57. If the column you want to copy is Data!H:H and you want to copy it to the next empty column on a sheet called Log then you would execute this code

    var ss = SpreadsheetApp.getActive();
    var source = ss.getSheetByName("Data");
    var values = source.getRange("H:H").getValues();
    var target = ss.getSheetByName("Log");
    target.getRange(1,sheet.getLastColumn()+1,values.length,values[0].length).setValues(values);

    Hopefully that's enough to get you there.

    ReplyDelete
  58. Dear Alex Chapman,

    I read your articles and it helped me a lot!
    I modified your script a little for what is useful for my google spreadsheet but i constantly get the error #num!
    The Date is working.

    A3 = goog
    A4 = ibm

    How could I solve this problem?
    A little help would be appreciated a lot!
    Greetings,
    Eli


    This is only the part I changed, I still have the other functions beneath my script you wrote.
    Is it solvable or is it something else?


    function recordDaHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Stocks");
    var source = sheet.getRange("A3:A4");
    var values = source.getValues();
    var historicalData = ss.getSheetByName("DailyData");
    var result = new Array(3);
    result[0] = new Date();
    for (var i=1; i<3; i++) {
    var price = lookupTicker(values[0][i]);
    Logger.log('The stock %s is trading at %s', values[0][i], price);
    result[i] = price * values[1][i];
    }
    historicalData.appendRow(result);
    };

    ReplyDelete
    Replies
    1. Which cells have the quantities for goog and ibm? B3 and B4? If so, then you need:

      function recordDaHistory() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Stocks");
      var source = sheet.getRange("A2:B4"); // CHANGED
      var values = source.getValues();
      var historicalData = ss.getSheetByName("DailyData");
      var result = new Array(3);
      result[0] = new Date();
      for (var i=1; i<3; i++) {
      var price = lookupTicker(values[i][0]); // CHANGED
      result[i] = price * values[i][1]; // CHANGED
      }
      historicalData.appendRow(result);
      };

      Delete
  59. Is there a way to sort the sheet AUTOMATICALLY by date LIVE that the newest info is inserted into a newly created "ROW 2" which pushes the older info down?

    ReplyDelete
    Replies
    1. You could add sheet.sort(1,false); on the line after the appendRow to make it sort by the first column (date) in descending order.

      Delete