This Office Scripts script can be used in Excel to produce a report on workbook structure and objects

WorkbookReport.ts

function main(
  workbook: ExcelScript.Workbook,
  outputRange: string = "A1",
  newSheetName: string = "Sheet Report"
) {
  const sheets = workbook.getWorksheets();

  // Create or clear the report sheet
  let thisSheet = workbook.getWorksheet(newSheetName);
  if (thisSheet) {
    thisSheet.getUsedRange()?.clear();
  } else {
    thisSheet = workbook.addWorksheet(newSheetName);
  }

  // Generate the sheet-level report data
  const sheetNames: (string | boolean | number)[][] = [
    [
      "Name",
      "Used Range",
      "Is Protected",
      "Charts",
      "Worksheet-scoped Names",
      "Pivot Tables",
      "Visibility",
    ],
    ...sheets.map((ws) => {
      if (ws.getName() !== thisSheet.getName()) {
        const usedRange = ws.getUsedRange();
        return [
          ws.getName(),
          usedRange.getAddressLocal(),
          ws.getProtection().getProtected(),
          ws.getCharts().length,
          ws.getNames().length,
          ws.getPivotTables().length,
          ws.getVisibility(), // Visibility status
        ];
      }
      return null; // Exclude the report sheet itself
    }).filter((row) => row !== null) // Remove nulls from the report sheet exclusion
  ];

  // Calculate the totals for charts and pivot tables by summing columns in sheetNames
  const totalCharts = sheetNames.slice(1).reduce((sum, row) => sum + (row[3] as number), 0);
  const totalPivotTables = sheetNames.slice(1).reduce((sum, row) => sum + (row[5] as number), 0);

  // Workbook Overview Table
  const workbookOverview: (string | number)[][] = [
    ["Name", "Sheets", "Workbook-scoped Names", "Charts", "Pivot Tables"],
    [
      workbook.getName(),
      sheets.length - 1,
      workbook.getNames().filter((n) => n.getVisible()).length,
      totalCharts,
      totalPivotTables
    ]
  ];

  // Insert the workbook overview at the top of the report
  thisSheet
    .getRange(outputRange) // The requested starting cell
    .getResizedRange(1, workbookOverview[0].length - 1) // Workbook table is always 2 rows
    .setValues(workbookOverview); // Write the workbook overview

  // Insert the worksheet report just below the workbook overview
  thisSheet
    .getRange(outputRange)
    .getOffsetRange(workbookOverview.length + 1, 0) // skip over the workbook table
    .getResizedRange(sheetNames.length - 1, sheetNames[0].length - 1) // Resizes the range to fit the worksheet report
    .setValues(sheetNames); // Writes the worksheet report
}