[Q] Data validation not working on google sheets

[lerg96]

💬 Questions and Help

I have a project where i'm using exceljs to read a xlsx file and adding datavalidation, it works in Microsoft Excel Desktop App but it does not work in google sheets.

First i load the file

await workbook.xlsx.readFile(fullPath);

Then i create a sheet where all data that i need to add to the validator will be

const categoriesWS = workbook.addWorksheet('categories'); categories.forEach((category,index) => { categoriesWS.getCell('A${(index+1)}').value = category.name })

then i use a formula in the data validator

templateWS.dataValidations.add('F2:F9999', { type: 'list', allowBlank: false, formulae: ['=categories!A1:A${categories.length}'], showErrorMessage: true, errorStyle: 'error', error: 'The value Valid', });

at the end i hide that sheet
categoriesWS.state = 'hidden';

am i doing something wrong?

Version:
exceljs: "^4.3.2"