You should reverse where you have 'Record' and 'Portfolio'. |If this possible or am I shooting high? Built-in formulas, pivot tables and conditional formatting options save time and simplify common spreadsheet tasks. Which you could do something like this...var oldValues = sheet.getRange(sheet.getLastRow(),1,1,4).getValues();if ((oldValues[0][1]==values[0][1]) && (oldValues[0][2]==values[0][2]) && (oldValues[0][3]==values[0][3])) return;You'd put that in immediately after the var values = source.getValues(); line.This would work if the values were numbers or text, but you mention that they are dates, which complicates things a little further, because Google Apps Script (which is just JavaScript essentially) doesn't let you test the equality of dates with == because they are objects. You could open up the Version History, select a version of the sheet from a different date, and even revert to that version of the file if you wanted. These let you set and get a property which persists across executions. I would like the script to re-populate PAGE1 on data pull 145 instead of creating PAGE145. Now let's test it before we schedule it to run automatically. Click to share on Twitter (Opens in new window), Click to share on Facebook (Opens in new window), Click to email this to a friend (Opens in new window), Click to share on LinkedIn (Opens in new window), Click to share on Reddit (Opens in new window), Click to share on Pinterest (Opens in new window), Click to share on Tumblr (Opens in new window), How to Skip the First Page Number in Word for Office 365, How to Print in the Middle of the Page in Google Sheets, How to Put Google Sheets Page Numbers at the Top of the Page, How to Transfer More than One Pokemon at a Time in Pokemon Go, How to Remove a Passcode from the iPhone Dropbox App, How to Make a Picture Transparent in Powerpoint 2013, How to Show More Emails Per Page in Gmail, How to Prevent Your iPhone 5 Screen From Automatically Turning Off. So, how this should work: I have on Sheet1 4 columns:Name,Color,Status,Date. The easiest way to get it working would be to insert a column before your current A column (this is where the script will write a date for each new row). Thanks again!https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(String). Any ideas? 4675 Try it now. 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. Possible to capture multiple row? The first is the time zone of the spreadsheet, the second is the time zone of the script. Find the cell whose history you wish to view. Okay, I will try it out and report back! Sign in to review and manage your activity, including things you’ve searched for, … Thanks. How to Use Version History in Google Sheets. So, how this should work: I have on Sheet1 4 columns:Name,Color,Status,Date. If so, then you need:function recordDaHistory() {var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheetByName("Stocks");var source = sheet.getRange("A2:B4"); // CHANGEDvar 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]); // CHANGEDresult[i] = price * values[i][1]; // CHANGED}historicalData.appendRow(result);}; 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? Do you know a way around this? Correct. It doesn't use the GOOGLEFINANCE() function, because that approach simply won't work for what you are trying to do. The SPARKLINE function in Google Sheets allows you to insert these types of charts into a single cell on your spreadsheet. Thank you very much Alex! Once that is selected, click the black "play" triangle button. Then at the beginning of recordHistory() have the script read all the dates and compare the current date against each of them. If you reply with your code, and the names of the two sheets perhaps I can help. 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-serviceThe other approach is to calculate which page you should be on using the current hour and minute of the day. 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. At the end of the day, it will either read "yes" or "no" for each user. Great! 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. Thanks for the comment. 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. That's perfect! Built-in formulas, pivot tables and conditional formatting options save time and simplify common spreadsheet tasks. You started by saying that you were getting a time stamp that you didn't want. Thanks you so much for this! You might be best served trying to find a freelancer who can spend an hour or two writing the script you need. The data on A2,B2 and C2 change continuously, so I need to "lock" the value on A3, B3 and C3 after calculations, even if the value on A2,B2 and C3 changed. How to See Google Sheets Cell History. I have the following script after reading the article and the comments, yet I have only dates on the first column. Any advice is appreciated and thanks again for this post! How to Use Sheets' Query Function to Save Google Form Responses. When a new response is submitted, create a new record in your Google sheet. Glad you figured out an approach. Any idea how do i go about it ? I've tried the other ideas on here but really don't know what to change.this is the code i'm usingfunction 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. It does seem to be working for my spreadsheet that utilizes a ton of GOOGLEFINANCE() calls. 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 triggerHave not found anything in the comments,Do you have a clue ? Glad it works, Dave. This chapter's tutorials will focus on bound scripts—the most common ones in the Google Docs Add … You'd need to replace the second line withfor (var i=0; i