1

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.

0

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'
  • Unfortunately when using the code it is being used to work in 7 rows, withe lack of the row restriction && !r[1] then when the status changes it changes the times on all rows. How can I get it to update the values and still have the row constraints? – Robert Feb 7 '18 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.