Text finder doesn't work with result from formula-CodePudding

I'm working on a small script for Google Sheet.

Basically, I have a formula in my sheet who says -

=IFS(M7="";"";M7>TODAY();"NO";M7<TODAY();"XXX")

And I would like my script find and replace "XXX" value to "YES".

I worked with a textFinder :

var textFinder = SpreadsheetApp.getActive()
  .getSheetByName('DROITS ONIRIM')
  .createTextFinder('XXX')
textFinder.replaceAllWith('OUI');

But It didn't succeed to find the result of the formula, and doesn't replace the text. How could I proceed ?

CodePudding user response：

Try

function myFunction() {
  let ranges = SpreadsheetApp.getActive()
    .getSheetByName('DROITS ONIRIM')
    .createTextFinder("XXX")
    .matchCase(false)
    .matchFormulaText(true)
    .findAll();
  ranges.forEach(function (range) {
    range.setFormula(range.getFormula().replace(/xxx/g,'OUI'));
  });
}

CodePudding user response：

It doesn't work, it continuously processing without doing anything. Just to be sure, I don't want to replace "XXX" in the formula, I want to change the result to "OUI" if the result of the formula is "XXX" (so it can overwrite the formula)