Formula Parse error when I programatically add a formula with apps script-CodePudding

I work in widget where the user can add datas in specific row. In the Modal Dialog, he can type the row where i want add data and select data to add. The data is linked to an other data with a macro and i want to add too this value.

This is my server side's script :

function include(filename){
   return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function widget() {
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getActiveSheet();
  const ui = SpreadsheetApp.getUi();
  var widget;
  widget = HtmlService.createTemplateFromFile("widget.html").evaluate();
  ui.showModalDialog(widget, "Add new Row");
  widget.setWidth(600);
  widget.setHeight(600);
}

function getData(){
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getSheetByName("BDD");
  var services = feuille.getRange("A2:A").getValues().filter(d =>d[0] !== "");
  return services.map(d => "<option>"   d[0]   "</option>").join("")
}

function ajoutLigne(row,data) {
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getActiveSheet();
  index = feuille.getActiveRange().getRow();
  feuille.insertRowBefore(row);
  feuille.getRange("A" row).setValue(data);
  feuille.getRange("B" row).setValue("=RECHERCHEV(A" row ";BDD!A:B;2;FAUX)");
}

This is my widget's HTML page :

<!DOCTYPE html>
 <html>
   <head>
     <base target="_top">
     <?!= include('JavaScript'); ?>
   </head>
   <body>
     <p>Row : <input type="text" id="row"></p>
     <br>
     <p><i>Select data :</i>
       <select id="data" name="data"  required>
         <option disabled selected>Choose ...</option>
         <?!=getData()?>
       </select>
     </p>

     <input type="button"  value="SUBMIT" onclick="addRow();">
     <input type="button"  value="CLOSE" onclick="google.script.host.close();">

   </body>
 </html>

And this is my JavaScript's page :

<script>
   function addRow(){
     var row = document.getElementById("row").value;
     var data = document.getElementById("data").value;
     google.script.run.ajoutLigne(row,data);
   }
</script>

When i launch my widget and sumbit datas, it's correctly reported in my Sheet's tab. But the macro generate an error:

formula parse error

and I don't understand why. The syntax is the same to the previous row. And if i modify 2 times the macro (first time to change one thing and second time to write the initial macro), it's work....

If anyone know the answer to this problem, I would be grateful. Thank you for advance for your help (this is the link of my Sheet).

CodePudding user response：

When I saw your question, the formula is put using the following script.

feuille.getRange("B" row).setValue("=RECHERCHEV(A" row ";BDD!A:B;2;FAUX)");

But, when I saw your script included in your sample Spreadsheet, the formula is put using the following script.

feuille.getRange("B" row).setValue("=VLOOKUP(A" row ";BDD!A:B;2;FAUX)");

And, from your sample Spreadsheet and your comments, in the current stage, I thought that you might try to use VLOOKUP.

If my understanding is correct, how about the following modification?

From:

feuille.getRange("B" row).setValue("=VLOOKUP(A" row ";BDD!A:B;2;FAUX)");

To:

feuille.getRange("B" row).setFormula("=VLOOKUP(A" row ";BDD!A:B;2;FALSE)");

Reference:

setFormula(formula)