[SOLVED] Why do I get an error by trying to add a formula in a cell in Excel Script?

Why do I get an error by trying to add a formula in a cell in Excel Script?

I'm trying to add a formula to a specific cell in my French Excel, but I get an error when I try to run it. Any ideas on what to change?

Just to add, I got this English formula by using the "Inserting an action" button and it automatically translated my formula in French. The selected sheet is already defined without errors.

// Set range C11 on selectedSheet
    selectedSheet.getRange("C11").setFormulaLocal("=IFERROR(SORT(FILTER('Sheet number two'!A1:I496,ISNUMBER(SEARCH(R4C4,'Sheet number two'!A1:A496))*ISNUMBER(SEARCH(R5C4,'Sheet number two'!B1:B496))*(IF(F4,'Sheet number two'!C1:C496=\"Modèle1\",FALSE)+IF(G4,'Sheet number two'!C1:C496=\"Modèle2\",FALSE))*ISNUMBER(SEARCH(R6C4,'Sheet number two'!E1:E496))),6,-1),\"Aucun résultat\")");

Solution

setFormulaLocal sets the formula for the object, using A1-style references in the language of the user. It seems French function names should be used.

To verify yourself, just enter the formula into a cell then print getFormulaLocal. All details will get clear for you.

Or just use setFormula instead of setFormulaLocal.

It the formula, you mixed A1 and RC reference styles: ISNUMBER(SEARCH(R6C4,. Use the A1 style only.

=IFERROR(
  SORT(
    FILTER('Sheet number two'!A1:I496,
      ISNUMBER(SEARCH(D4,'Sheet number two'!A1:A496))
        *ISNUMBER(SEARCH(D5,'Sheet number two'!B1:B496))
        *(IF(F4,'Sheet number two'!C1:C496=\"Modèle1\",FALSE)
          +IF(G4,'Sheet number two'!C1:C496=\"Modèle2\",FALSE))
        *ISNUMBER(SEARCH(D6,'Sheet number two'!E1:E496))
    ),
  6,-1),
\"Aucun résultat\")