Google Sheets (Google Finance) - Get stock price once and track

Question

I would like to, upon data being entered into a new row (from external sheet - date, time, stock ticker), use google finance to get the current price of the stock into a cell and then no longer update the price in that cell. In the cell next to it, I want to track the price and in the cell next to that, I want to track the high, since creation (the highest value of the 'current price' cell).

Here is my sheet https://docs.google.com/spreadsheets/d/1EUU1bZnIfBatNI8H9pPk202PCD1g8wWXt5M0wx6S-jM/edit?usp=sharing

All I've got so far is a high value tracker that runs on the first row only for the right cells. Embarrasingly enough I can't figure out how to apply it to the entire columns.

So in summary, date time and stock will be entered into column A B and C. When that happens, I want to get the current price of the stock in C, and have that number no longer update. In D I want the price to be tracked, like Google Finance normally functions, and in E, the highest value of the D, in the same row.

That's the goal. Any help would be very much appreciated :)

Answer 1

All I've got so far is a high value tracker that runs on the first row only for the right cells. Embarrasingly enough I can't figure out how to apply it to the entire columns.

The code bound to your spreadsheet is

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("D2:E2");
  var values = range.getValues()[0];
  range.setValues([[values[0], Math.max(values[0], values[1])]]);
}

What does this code do?

• It sets the range to work with always to D2:E2 - no matter what the actual edited range is
• It works only with the first row of the range (range.getValues()[0])
• It compares the 0 and 1 columns of the range (e.g. columns D and E) and assigns the value of column D back to column D (is it necessary?) and the higher of the two values to column E

How to modify your code?

It is not quite clear from your description how column D is populated and what you want to do with column F, but to give your general advice:

• If you want your function to run on all rows:

Modify your range and expand it until the last row. Subsequently loop through all rows:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("D2:E"+lastRow);
  for (var i = 0; i < lastRow-1; i++){
    var values = range.getValues()[i];
    range.setValues([[values[0], Math.max(values[0], values[1])]]);
  }
}

• If you want you code to run only on the currently edited row: Use the event object e.range to find out which is the edited row and work on this row:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = e.range;
  var row = range.getRow();
  var values = sheet.getRange(row, 4, 1, 2).getValues()[0];
  range.setValues([[values[0], Math.max(values[0], values[1])]]);
}

Note: getRange(row, 4, 1, 2) is the notation to get the range starting with the defined row and column 4 (D), 1 row long and two columns wide, see here.

IMPORTANT: If your sheet is being populated automatically from an external sheet - the onEdit trigger will not work for you (it only fires on manual, human-made edits). In this case you will need a workaround as described here.