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

Question

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\")");

rotabor · Accepted Answer · 2025-07-15 13:58:11Z

0

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\")

edited Jul 15 at 13:58

answered Jul 15 at 13:40

rotabor

5,6072 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeanne Perreault Jul 15 at 13:45

If I put the French formula in setFormulaLocal instead of the English formula, I get even more errors, such as "Can't find name" and "',' expected"

Jeanne Perreault Jul 15 at 13:52

Does not work either, in French or English, with setFormula

Jeanne Perreault Jul 15 at 14:25

Oh! Seems to be working now, with the code you sent. Thank you so much for your help, really appreciate it

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related