1

Good evening, I have a formula in a cell:

=query(IMPORTRANGE("1O9JBXnSpnigfVpMqNHq6nHcaHb7VwdroDgzDqfnD8iM";"'Tableau detail'!$b$3:$c");"select Col2 where (Col1='"&'Feuille 1'!$E$12&"')")

I would like to integrate it into a script with setformula (). I tried with this writing:

formuleNombre.setFormula('=query(IMPORTRANGE("1O9JBXnSpnigfVpMqNHq6nHcaHb7VwdroDgzDqfnD8iM";"'Tableau detail'!$b$3:$c");"select Col2 where (Col1='"&'Feuille 1'!$E$12&"')")');

but it does not work :( I've this error :

formula analysis error.

I tried this one too :

formuleNombre.setFormula('=query(IMPORTRANGE("1O9JBXnSpnigfVpMqNHq6nHcaHb7VwdroDgzDqfnD8iM";"\'Tableau detail\'!$b$3:$c");"select Col2 where (Col1='"&\'Feuille 1\'!$E$12&"')")');

and i've this error

Sign) missing after the argument list. (line 56, file "Code")

Line 56 is the line where there is the formula above..... I do not see at all or where this error is.

Could you help me please.

Cordially.

Rubén
  • 34,714
  • 9
  • 70
  • 166
william
  • 63
  • 11

1 Answers1

1

The reason you are having this error is because of the ' '.

The formula you need to input in the setFormula must be a string, and what you give is:

'=query(
    IMPORTRANGE(
        "1O9JBXnSpnigfVpMqNHq6nHcaHb7VwdroDgzDqfnD8iM";
        "'Tableau detail'!$b$3:$c"
    );
    "select Col2 where (Col1='"&'Feuille 1'!$E$12&"')"
)'

You string is between single quotes, but you have single quotes in it. You'll need to escape all the single quotes of the string, something like.

'=query(IMPORTRANGE("1O9JBXnSpnigfVpMqNHq6nHcaHb7VwdroDgzDqfnD8iM";"\'Tableau detail\'!$b$3:$c");"select Col2 where (Col1=\'"&\'Feuille 1\'!$E$12&"\')")'
Liora Haydont
  • 1,252
  • 1
  • 12
  • 25
  • Hello @liora Haydont I thank you very much, the code works very well. I did not miss much ...... Anyway thank you for your help. cordially. – william Mar 08 '18 at 18:53