Google-sheets – Google script to delete on condition

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I'm trying to fix a script for a Google Spreadsheet, https://docs.google.com/spreadsheets/d/1J3VRBXkx5B3p1fmrVpkI3Y6F5EGns9Vao7FyxOQsSVo/edit?usp=sharing

I wrote to following:

function checkClearData() {
  var s = SpreadsheetApp.getActive().getSheetByName('Operation');
  s.getRange('C2:D').getValues()
  .forEach (function (r, i) {
    if(r[0] == 'Clear' && !r[1]) {
      s.getRange(i + 2, 4).setValue(new Date())
    } 
  })
}

Originally the script adds a timestamp to the cell when the condition 'Clear' is passed through from another page. However sometimes the condition occurs multiple time in a day, but the timestamp doesn't get rewritten, I tried to then change the code to,

function checkClearData() {
  var s = SpreadsheetApp.getActive().getSheetByName('Operation');
  s.getRange('C2:D').getValues()
  .forEach (function (r, i) {
    if(r[0] == 'Clear' && !r[1]) {
      s.getRange(i + 2, 4).setValue(new Date())
    } else {
      s.getRange(i + 2, 4).clearContent();
    }
  })
}

but now when a cell change from Clear to a value all the entries in the column range get cleared.

Is there a constraint I need to add to limit the clearContent(), or rewrite to make the timestamp get rewritten?

I am new at this so all assistance will help.

Best Answer

If you want that the timestamp be rewritten, on the original code instead of the condition

r[0] == 'Clear' && !r[1]

use

r[0] == 'Clear'
Related Topic