Retrieve only Visible sheets from workbook - OfficeScript

Question

I am attempting to get a list of only the visible sheets in an Excel online workbook. I found the method .getNext({visibleOnly?: boolean}).

However, I don't know how/where to call it in the below code. I attempted to add it by reducing the sheets collection to a visibleSheets only collection.

function main(workbook: ExcelScript.Workbook) {
    let sheets = workbook.getWorksheets();
    let visibleSheets:ExcelScript.Worksheet[] = sheets.getNext({visibleOnly: true});

    visibleSheets.forEach(sheet =>{
        // Toggle auto filter on selectedSheet
        if (visibleSheets.getName() != "SUMMARY") {
        console.log(visibleSheets.getName())
        visibleSheets.getAutoFilter().apply(visibleSheets.getRange("A1"));
        // Apply custom filter on selectedSheet
        visibleSheets.getAutoFilter().apply(visibleSheets.getAutoFilter().getRange(), 0, {filterOn: ExcelScript.FilterOn.custom, criterion1: '<>'});
        };
    });

Answer 1

The usage of getNext in your code is not correct. getVisibility might be the better one to get the visible sheets.

---

Option 1:

function main(workbook: ExcelScript.Workbook) {
    let sheets = workbook.getWorksheets();
    let sheetVis: ExcelScript.Worksheet [] = [];
    sheets.forEach(sheet => {
        if (sheet.getVisibility() === ExcelScript.SheetVisibility.visible) {
            // Your code to manipulate sheet, then you don't need sheetVis
            sheetVis.push(sheet);
        }
    });
    sheetVis.forEach(sht => {
        console.log(sht.getName());
        // Your code to manipulate sheet
    }
    );
}

---

Option 2:

Or you can get the list of visible sheet name.

function main(workbook: ExcelScript.Workbook) {
  let sheets = workbook.getWorksheets();
  let sheetVis = sheets
    .filter(t => t.getVisibility() === ExcelScript.SheetVisibility.visible)
    .map(t => t.getName());
  sheetVis.forEach(sht => {
    console.log(sht);
    // Your code to manipulate sheet
  }
  );
}

---

Option 3:

You can certainly achieve the same outcome using the approach of getNext if preferred.

function main(workbook: ExcelScript.Workbook) {
  let sheetVis = workbook.getFirstWorksheet();
  do {
    console.log(sheetVis.getName());
    sheetVis = sheetVis.getNext(true);
  } while (sheetVis)
}