Set value on form submission based on previous row-CodePudding

I have a script that triggers on form submission, checks the row before last row and should write a TRUE value to the 9th column of the checked row IF all three cells on its left have a TRUE value.

My problem is that the script always writes a FALSE value, even if the 3 cells on the left are all TRUE.

function onFormSubmit() {
var s = SpreadsheetApp.getActiveSpreadsheet(); 
var rcore = s.getSheetByName("test");
var lastrow = rcore.getLastRow();

var trgt = rcore.getRange(lastrow-1,9);

if(trgt.getValue() === ""){
  if(trgt.offset(0, -3) == "TRUE" && trgt.offset(0, -2) == "TRUE" && trgt.offset(0, -1) == "TRUE"){
    trgt.setValue("TRUE");
  } else {
  trgt.setValue("FALSE");
  }
  }
}

(My language is set to hungarian so that's why you see "IGAZ" for "TRUE" values and "HAMIS" for "FALSE" values)

The 3 TRUE/FALSE values are generated by ARRAYFORMULA. Maybe that is also important

SO FAR I have tried several variations: -tried to change the if to check with offset (0, -4) if its equal to 2 and not check anything else, but still I got FALSE values. -I also tried to check with different if statements but it always gives FALSE. -tried to check what happens if I also offset row to -1

I simply cant get a TRUE value. The last time I was able to get a TRUE value was when there was no other if statement, only one that checks if the cell is empty or not.

CodePudding user response：

As Rubén pointed out, you need to use .getValue() to read the value in a range. You should also use the Boolean values true and false instead of the text strings "TRUE" and "FALSE", like this:

function onFormSubmit() {
  const ss = SpreadsheetApp.getActive();
  const rcore = ss.getSheetByName('test');
  const lastRow = rcore.getlastRow();
  const trgt = rcore.getRange(lastRow - 1, 9);
  if (trgt.getValue() === '') {
    if (trgt.offset(0, -3).getValue() === true && trgt.offset(0, -2).getValue() === true && trgt.offset(0, -1).getValue() === true) {
      trgt.setValue(true);
    } else {
      trgt.setValue(false);
    }
  }
}

...or more concisely:

function onFormSubmit() {
  const rcore = SpreadsheetApp.getActive().getSheetByName('test');
  const trgt = rcore.getRange(rcore.getlastRow() - 1, 9);
  if (trgt.getValue() === '') {
    trgt.setValue(trgt.offset(0, -3, 1, 3).getValues().flat().every(value => value === true));
  }
}

CodePudding user response：

Try this:

function Form11(e) {
  var sh = e.source.getSheetByName("test");
  var trgt = sh.getRange(e.range.rowStart - 1, 6, 1, 4);
  if (trgt.getValues()[0][3] === "") {
    if (trgt.getValues()[0] == [true,true,true,""]) {
      trgt.setValue("TRUE");
    } else {
      trgt.setValue("FALSE");
    }
  }
}