exceljs: Data Validation with type 'list', the formulae can't be dynamic
How can be the formulae of data validation for the type ‘list’ dynamic?
Here is my code snippet:
sheet.dataValidations.add('M2:M4999', { type: 'list', allowBlank: false, formulae: [ arrayToTargetFormulae(array) ], showErrorMessage: true, errorStyle: 'error', error: 'The value Valid', });
The function arrayToTargetFormulae takes an array and covert it.
Here is the code for the function arrayToTargetFormulae:
function arrayToTargetFormulae(arr) { let str = '\'"'; str += arr.join(', '); str += '"\''; console.log(str); return str; }
The array values after convert will be one string like ‘“Male,Female”’
About this issue
- Original URL
- State: open
- Created 4 years ago
- Comments: 23
Yes, I solved it by adding the dynamic content to another sheet (on one column) and set the state of that sheet to “very hidden” so no one can edit or change that hidden sheet or its content. Then, i added the “dataValidations” for the column as follows:
sheetStudents.dataValidations.add('L2:L9999', { type: 'list', allowBlank: false, formulae: ['=Levels!$A$2:$A$9999'], showErrorMessage: true, errorStyle: 'error', error: 'Please select a valid value from the list', });Note:
formulae: ['=Levels!$A$2:$A$9999']is the hidden sheet name which contains the dynamic data. Also, A is the column name in that sheet.this solution solve my requirement