Script to create Table of Contents

Google Sheets Show & Tell
1

This is a script that creates a custom function in Google Sheets that generates a list of Sheet Names in a workbook along with links to each sheet and a count of rows in each sheet. The name of the custom function it creates is ‘Sheetnames.’ I use it to create a Table of Contents for my Tiller workbook which has 43 tabs.

When entered into a cell in Google Sheets, it creates a list with three columns:

1 Names of all the sheets in the workbook.
2. Active links to all of the sheets in the workbook.
3. A count of the number of rows in each sheet.

Why I use this custom function: By default, Sheets includes a tab in the sheet list at the bottom of the Sheets window but it requires scrolling if the workbook has a lot of sheets, it’s not alphabetical and I wanted a count of rows in each sheet because having too many rows in one of my sheets caused the only glitch I’ve ever had with Tiller & Google Sheets.

The format of the function is =Sheetnames(argument 1, argument 2) where:

Argument 1 determines if the resulting list will include the sheet containing the function. For example, if the function is used on a sheet named TOC (for table of contents). A value of 0 will return all sheets in the workbook. A value of -1 will return all sheets except the one with the one containing the function – TOC in my example.

Argument 2 determines if Hidden Sheets should be included in the list of sheets. “false” will include the hidden sheets. “true” will not include hidden sheets in the list.

These are the eight ways the function can be entered:

To Get … Unsorted Sorted Alpha
All Sheets =SHEETNAMES(0,true) =Sort(SHEETNAMES(0,true))
All but TOC =SHEETNAMES(-1,true) =Sort(SHEETNAMES(-1,true))
All Non Hidden Sheets =SHEETNAMES(0,false) =Sort(SHEETNAMES(0,false))
All but TOC and Not Hidden =SHEETNAMES(-1,false) =Sort(SHEETNAMES(-1,false))

The arguments let you control if you want to include TOC in the list and if you want to include Hidden sheets in the list. Wrapping the function with SORT lets you sort the list alphabetically.

To install this script in your Google Sheets Tiller workbook:

  • from the Google Sheets menu, choose Extensions > AppsScript.
  • Create a new script.
  • Replace the default code with the code below.
  • Save.

Then, create a new sheet. Mine is named TOC but it can be any name. In any cell, enter one form of the function such as =Sort(sheetnames(0,true)). This form will generate a sorted list of sheets in your Tiller workbook, including the sheet with the function and including sheets that are hidden.

Be patient… it takes up to a minute or two to finish. Finally – here’s the code.

/**

  • This function will return a list of sheet names from the current workbook.
  • @param {number} option Argument must be -1, 0, or 1.
  • @return A list of sheet names from the current workbook.
  • returns active links to each sheet
  • returns count of rows in each sheet
  • @customfunction
    */
function SHEETNAMES(option = 0, includeHidden = false) {
  var sheetNames = [];
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetUrlPrefix = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/edit#gid=";

  for (var i = 0; i < sheets.length; i++) {
    if ((option === 0 && (includeHidden || !sheets[i].isSheetHidden())) ||
        (option === -1 && SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName() != sheets[i].getName() && (includeHidden || !sheets[i].isSheetHidden())) ||
        (option === 1 && SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId() === sheets[i].getSheetId() && (includeHidden || !sheets[i].isSheetHidden()))) {
      var rowCount = sheets[i].getLastRow(); // Get the count of rows with data
      sheetNames.push([sheets[i].getName(), sheetUrlPrefix + sheets[i].getSheetId(), rowCount]);
    }
  }

  if (sheetNames.length === 0 && option === 1) {
    var currSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    if (includeHidden || !currSheet.isSheetHidden()) {
      sheetNames.push([currSheet.getName(), sheetUrlPrefix + currSheet.getSheetId(), currSheet.getLastRow()]);
    }
  }

  if (sheetNames.length === 0) {
    throw "No matching sheets found. Check the options or sheet visibility.";
  }

  return sheetNames;
}
1 Like
2

Thanks for sharing @ScottC. I can see you are deep into managing the size and metadata of your presumably very large Google spreadsheet.

I moved this topic over to Show & Tell so others will find it more easily.

1 Like