Gscript for static date revision

[gp9293]

Darren solved this for me with the script below. New problem arises when I run the script again, even though the condition current >=target is met, the new timestamp does not appear. The timestamp cell should only be locked if the timestamp is there. The script is behaving as if the cell is locked and hence, it does not write the timestamp to it. Any thoughts appreciated.

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.

[Darren_Murphy]

@gp9293

The timestamp cell should only be locked if the timestamp is there.

Which timestamp? (the sample script I gave you referenced two).
The logic in the script I gave you is as follows:

• For each row in the sheet, check if the time lock has not been set (is empty)
• If it is empty, then
  – compare the current price to the target price
  – If the current price is equal to or greater than the target, then take the last update time and write that to the time lock column
• If the time lock is not empty, then ignore the row

If the above is wrong, can you please re-write so that it correctly reflects your desired behaviour, then I’ll help you adjust the code.

[Darren_Murphy]

Okay, I just reread this a couple of times and I think I get it.

This…

@Darren_Murphy

For each row in the sheet, check if the time lock has not been set (is empty)

…should actually be
“For each row in the sheet, check if the time lock has not been set (is empty) AND the last update time is not empty”

yes?
If so, all you need to do is adjust the for loop to be as follows:

data.forEach(function (row) {
    var time_lock = row[time_lock_index];
    var last_update = row[last_update_index];
    if (time_lock === '' && last_update != '') {
      var current = row[current_index];
      var target = row[target_index];
      if (current >= target) {
        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++;
  });

[gp9293]

I rebuilt all of the data and retested several times. It works. Must have been something in the data formatting that got tweaked after the first time I ran the script. Thank you again.

[gp9293]

Even better. Thank you.