# Gscript for static date revision

**URL:** <https://community.glideapps.com/t/gscript-for-static-date-revision/37419>\
**Category:** Ask for Help\
**Created:** [January 19, 2022, 6:34pm UTC](https://community.glideapps.com/t/gscript-for-static-date-revision/37419 "2022-01-19T18:34:55Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [January 19, 2022, 6:34pm UTC](https://community.glideapps.com/t/gscript-for-static-date-revision/37419/1 "2022-01-19T18:34:55Z")

</div>

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.

```auto
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.

 ![Screen Shot 2022-01-14 at 11.14.59 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/6/8/68d3db9b5c91da48a9b40ef86964f495f903e3b8.png)

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 20, 2022, 2:53am UTC](https://community.glideapps.com/t/gscript-for-static-date-revision/37419/2 "2022-01-20T02:53:49Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 20, 2022, 3:24am UTC](https://community.glideapps.com/t/gscript-for-static-date-revision/37419/3 "2022-01-20T03:24:27Z")

</div>

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:

```auto
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++;
  });

```

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [January 20, 2022, 3:39am UTC](https://community.glideapps.com/t/gscript-for-static-date-revision/37419/4 "2022-01-20T03:39:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [January 20, 2022, 4:02am UTC](https://community.glideapps.com/t/gscript-for-static-date-revision/37419/5 "2022-01-20T04:02:49Z")

</div>

Even better. Thank you.

---

<div class="post-metadata">

**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\
**Post date:** [January 21, 2022, 4:03am UTC](https://community.glideapps.com/t/gscript-for-static-date-revision/37419/6 "2022-01-21T04:03:40Z")

</div>

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.