gScript for static date

[gp9293]

I received some good ideas on this before but closed the topic as solved, when I should not have. The issue is the following: my sheet receives updates from an external stock price database (think Yahoo Finance). Prices are updated every 15 minutes. When the price exceeds a target value (hard-coded in another field), I would like write a datetime stamp to a third field. But the datetime stamp cannot change even if the price moves below and above the target. during the course of the day/week. Once the target is hit, game over, static timestamp. Appreciate any help.

[Darren_Murphy]

I remember this. Can you describe the solution that you implemented, and in what way it’s falling down?

[Darren_Murphy]

Here… imagine that your sheet looks like this:

Then the following code should do what you want:

function check_stock_targets() {
  // Replace each of the below with actual column/sheet names
  const SHEET_NAME = 'Stocks'; 
  const SYMBOL = 'Symbol';
  const CURRENT = 'Current Price';
  const LAST_UPDATE = 'Last Update';
  const TARGET = 'Target';
  const TIME_LOCK = 'Target Hit At';

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().shift();
  var symbol_index = headers.indexOf(SYMBOL);
  var current_index = headers.indexOf(CURRENT);
  var last_update_index = headers.indexOf(LAST_UPDATE);
  var target_index = headers.indexOf(TARGET);
  var time_lock_index = headers.indexOf(TIME_LOCK);

  var data = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
  var current_row = 2;

  data.forEach(function (row) {
    var time_lock = row[time_lock_index];
    if (time_lock === '') {
      var current = row[current_index];
      var target = row[target_index];
      if (current >= target) {
        var last_update = row[last_update_index];
        var symbol = row[symbol_index];
        console.log("Target for %s hit at %s, locking", symbol, last_update);
        sheet.getRange(current_row,time_lock_index+1).setValue(last_update).setNumberFormat("yyyy-mm-dd hh:mm");
      }
    }
    current_row++;
  });
}

I would run that as a timed trigger, perhaps once every 5 minutes.

[gp9293]

Looks good. I will try

[gp9293]

Well, I am not surprised Darren. It works perfectly. Thank you.