Google App Script keeps reading an old value from a sheets cell

Question

I've got a script that checks for FOO in any cell of column A, and where there's a match, it will write BAR in the matching row of column B.

If I put FOO in A1, it puts BAR in B1, excellent. But if I remove or type something else into A1, and delete BAR from B1, a few seconds later it automatically puts BAR in B1 again.

I've tried different triggers, such as On edit, On change, On open, but haven't had any luck.

I guess there's a cache going on with the cell values, but I wonder is there a way to force it to read the actual latest values from the cells? To discard any cache it has?

function containsText(cellValue, searchText) {
  return cellValue.indexOf(searchText) !== -1;
}

function updateCellValue() {
  const sheetName = "MySheet";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Sheet ${sheetName} not found!`);
    return;
  }

  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange("A1:A" + lastRow);
  var dataValues = dataRange.getValues();
 
  for (var i = 0; i < dataValues.length; i++) {
    var cellValue = dataValues[i][0];
    if (containsText(cellValue, "FOO")) {
      sheet.getRange("B" + (i + 1)).setValue("BAR");
    }
  }

  SpreadsheetApp.flush();
}

Answer 1

RECOMMENDATION

[Updated]

The following script should be able to detect any instances of "FOO" on your spreadsheet, and should apply "BAR" right beside it. You can check this for your reference:

function onEdit(e) {
      const sheetName = "MySheet";
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
      if (!sheet) {
        Logger.log(`Sheet ${sheetName} not found!`);
        return;
      }
    
      var dataRange = sheet.getRange(1,1, sheet.getLastRow(), 1);
      var dataValues = dataRange.getValues();
    
      for( i = 0; i < dataValues.length; i ++){    
        if (dataValues[i][0] == "FOO"){      
          sheet.getRange(i+1,2,1,1).setValue("BAR");   
        }
        else{
          sheet.getRange(i+1,2,1,1).clear();
        }      
      }
    
      SpreadsheetApp.flush();
    }