Hi,
I have this typescript to hide columns where all the cells are empty. But this doesn't work. Could you help me with correcting it?
Thanks.
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook.getActiveWorksheet();
const tableRange = sheet.getUsedRange();
const table = sheet.getRange(tableRange.getRow(), tableRange.getColumn(), tableRange.getLastRow(), tableRange.getLastColumn());
const numRows = table.getNumRows();
const numCols = table.getNumColumns();
//sheet.setColumnWidths(1, numCols, 100); // Reset column widths
// Loop through each column in the table
for (let col = 1; col <= numCols; col++) {
let visibleCount = 0;
let bHide = true;
// Loop through each row in the column's range
for (let row = 1; row <= numRows; row++) {
const cell = table.getCell(row, col);
if (cell.getValue() !== '') {
visibleCount++;
}
}
// If there are any non-empty cells in the column, don't hide it
if (visibleCount > 0) {
bHide = false;
}
// Hide or show the entire column based on the visibility flag
sheet.setColumnHidden(col, bHide);
}
}
Top comments (0)