How to add a complex formula in exceljs - JavaScript

I'm currently working on a project that requires to export data in multiple xlsx sheets. In one of them, I need to add formula in cells that verified some conditions before adding some data from the first sheet. Here is one of them:

Ref !== null ? worksheet2.getCell('D2').value ={ formula: `IF('dataSheet'!${Ref.address}<>"";'dataSheet'!${Ref.address};"")`, result:Ref.value} : worksheet2.getCell('D2').value = '';

Ref here is a cell in the first sheet containing the "ref" label.

The problem with this formula (or any "IF" formula) is that I get this error when I open the excel document: We found a problem with some content in test.xlsx. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

And when I do, it recovers the whole document except all of the IF formulas. I also have some VLOOKUP formula that don't work either.

Simple formula such as A1+A2 works but that's basically it.

I also tested other common formula like SUM or AVERAGE, but they did not work either. I tried with or without an "=" before the formula with no success. I tried simple formula such as formula: '=IF(A1=0;10;0)' with no success. I read that you sometime need to add the result, and as you can see in the first part, it did not work.

I also tried another package, XLSX with a IF and a SUM formula but the same problem appears all over again.

Last but not least, as I work in a french company all the formula are written in french so IF is SI.. I first though it could be a miscommunication between the exceljs language use and the one in Excel options but even after changing it and formula to english, it still shows me the same message.

This method will work.

worksheet.getCell('D2').value = { formula: 'IF(datasheet!A1<>"",datasheet!B1,"")'};

Some formula example in here

This demo.js will create test.xls

const Excel = require('exceljs');

const workbook = new Excel.Workbook();

const data_sheet = workbook.addWorksheet("datasheet")
const formula_sheet = workbook.addWorksheet("formula")


// set data_sheet data
data_sheet.getCell('A1').value = 'ref';

data_sheet.getCell('B1').value = 1;

// set formula data
formula_sheet.getCell('A1').value = 2;
formula_sheet.getCell('A2').value = 3;
formula_sheet.getCell('A3').value = { formula: 'SUM(A1,A2)'};

formula_sheet.getCell('D2').value = { formula: 'IF(datasheet!A1<>"",datasheet!B1,"")'};

const fileName = './test.xlsx';
workbook.xlsx
    .writeFile(fileName)
    .then(() => {
        console.log('file created');
    })
    .catch(err => {
        console.log(err.message);
    });

In datasheet,

In formula

The D2 cell's formula will create

=IF(datasheet!A1<>"",datasheet!B1,"")

And display

1

due to datasheet's A1 is ref and B1 is 1.