How to add a table of contents to a Google Sheets spreadsheet?

Question

I'm trying to add a table of contents to Google Sheets: simply want to include a list of all sheets inside the document, as clickable links (got 150+ sheets.)

I got it to work, but it's more complicated than I'd like, and leaves me with questions about custom functions in Google Sheets.

Here's what I have, set of course in Tools › Script editor:

/**
 * Returns all the document's sheet IDs.
 *
 * @return
 * @customfunction
 */
function tocid() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var i=0 ; i<sheets.length ; i++)
      out.push( [ sheets[i].getSheetId() ]
    )
  return out 
}

/**
 * Returns all the document's sheet names.
 *
 * @return
 * @customfunction
 */

function toctitle() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var i=0 ; i<sheets.length ; i++)
      out.push( [ sheets[i].getSheetName() ]
    )
  return out 
}

Using each formula, I get:

| Sheet_ID   | Sheet_Title       |
|------------|-------------------|
|  349319062 | Table of Contents |
| 1280378086 | many ou much      |
| …          | …                 |

And then I can use the HYPERLINK formula to get links: =hyperlink(concatenate("#gid=",A2), B2).

So it works.

However, I tried to do it all in one pass, like so:

/**
 * Returns a list of all the document's sheets as hyperlinks.
 *
 * @return
 * @customfunction
 *
 * …unfortunately, can't use built-in functions inside of it, it seems. So instead of hyperlinks, it shows the formula for the hyperlinks.
 */
function toclink() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var i=0 ; i<sheets.length ; i++)
      out.push( [ "=HYPERLINK(\"#gid=" + sheets[i].getSheetId() + "\", \"" + sheets[i].getName() + "\")" ]
    )
  return out 
}

But, as noted in the function's code comments, it does not work.

My questions are:

1. Is the fact that you cannot use built-in functions inside of Google Sheets' custom functions / Google Apps scripts actually documented anywhere? (I'd have saved a couple of hours of my life had I known.)

2. Any way to have the a custom function return clickable hyperlinks?

I suspect using Range might do it, but I'm not comfortable with them (and the fact the above still works makes it less of an incentive to learn). Is there a way to have a custom function just evaluate a formula taken from another column?

NOTE: I don't want to use a macro. I want to use a solution that auto-updates when new sheets get inserted.

---

I realize there's a similar question here, with a very useful answer. This does not exactly answer my questions, but is a bit easier to use than my current solution.

Answer 1

Q1: Is the fact that you cannot use built-in functions...

If by built-in functions you meant these then no, something designed to work "within" the app surely won't be available in scripts written in JavaScript unless specifically exposed as services / globals (they are not).

You can use a strict subset of built-in Google services, though. Refer to this guide to determine which you can and cannot.

Initially, I thought you literally meant using built-in functions, which is apparently not the case

Q2: Any way to have the a custom function return clickable hyperlinks?

No, not using a custom function. Formulas have to be set via setFormula() method which requires authorization and is not whitelisted for use with custom functions.

Q3: I want to use a solution that auto-updates when new sheets get inserted

The answer to that depends on what "when new sheets get inserted" means:

1. User inserts new sheets manually: use installable onChange trigger

First, install an onChange trigger that will run function called createTableOfContents when fired:

function installOnChange() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    const builder = ScriptApp
        .newTrigger("createTableOfContents")
        .forSpreadsheet(ss)
        .onChange();

    builder.create();
}

Then, declare your createTableOfContents as you prefer. I created a sample for demonstration purposes:

const createTableOfContents = () => {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheets = ss.getSheets();

    const tableOfContents = sheets.map(sheet => {
        return [
            sheet.getSheetId(),
            sheet.getSheetName()
        ];
    });

    const withHeaders = [
        ["Sheet Id", "Title"],
        ...tableOfContents
    ];

    const [sh] = sheets;

    const rng = sh.getRange(1, 1, withHeaders.length, withHeaders[0].length);

    rng.setValues(withHeaders).activate();

    const formulas = tableOfContents.map(([id,name]) => {
        return [`=HYPERLINK("#gid=${id}","${name}")`];
    });

    rng.offset(1, 1, tableOfContents.length, 1).setFormulas(formulas);
};

Note that the sample does not deal with range shrink due to sheet deletion, but you can take it from here:

2. Sheets are inserted via script: run createTableOfContents from the function that inserts the sheets.

I don't want to use a macro

What the other answer provides is not a marco. Macros form a subset of functions written in Google Apps Script, have to be declared in manifest and are called via keyboard shortcuts.

References

1. setFormulas() reference
2. offset() reference

---

Credit for the suggestion goes to TheMaster:

Instead of setting a HYPERLINK formula, you can use the setRichTextValues() method and RichTextValueBuilder class from the built-in service. The change required is small:

const links = tableOfContents.map(([id,name]) => {
  const value = SpreadsheetApp.newRichTextValue();

  return [value
    .setText(name)
    .setLinkUrl(`#gid=${id}`)
    .build()];
});

rng.offset(1, 1, tableOfContents.length, 1).setRichTextValues(links);

Answer 2

There's a lot of ways to go and and a lot of different things you might want.

function tableOfContents() {
  const ss=SpreadsheetApp.getActive();
  let sh=ss.getSheetByName('Table Of Contents');
  if(sh) {
    ss.deleteSheet(sh);
    SpreadsheetApp.flush();
  }
  sh=ss.insertSheet('Table of Contents',0);
  sh.clear();  
  let c=[["Table of Contents","","",""],["Item","Name","Link","Hidden"]];
  let shts=ss.getSheets();
  shts.forEach(function(sh,i){
    c.push([i+1,sh.getName(),sh.isSheetHidden()?"":Utilities.formatString('=HYPERLINK("\#gid=%s\","Link")',sh.getSheetId()),sh.isSheetHidden()?"Yes":""]);
  })
  sh.getRange(1,1,c.length,c[0].length).setValues(c).setHorizontalAlignment("center").setFontSize(10);
  sh.getRange(1,1,1,c[0].length).mergeAcross().setFontWeight("bold").setFontSize(12).setBorder(false,false,true,false,false,false);
  sh.getRange(2,1,1,c[0].length).setFontWeight("bold").setFontSize(8).setBorder(true,true,true,true,true,true);
}