如何在Google表格电子表格中添加目录? [英] How to add a table of contents to a Google Sheets spreadsheet?

问题描述

我正在尝试向Google表格添加目录:只想在文档中包含所有表格的列表，作为可点击的链接(得到150个) +张.)

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.)

我可以使用它，但是它比我想要的要复杂得多，还给我带来了有关Google表格中自定义功能的问题.

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.

这就是我所拥有的，当然是在Tools › Script editor中设置的:

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      |
| …          | …                 |

然后我可以使用HYPERLINK公式来获取链接:=hyperlink(concatenate("#gid=",A2), B2).

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

因此有效.

但是，我试图一次完成所有操作，就像这样:

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.

我的问题是:

1. 您是否真的可以在任何地方记录的Google表格自定义功能/Google Apps脚本中使用内置功能? (据我所知，我已经节省了几个小时的生命.)

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.)

是否可以使用自定义功能返回可点击的超链接?

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

我怀疑使用Range可能会做到，但是我对它们不满意(并且上述事实仍然有效，这使得学习动机降低了).有没有一种方法可以让自定义函数仅对从另一列获取的公式求值?

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.

推荐答案

问题1:事实是您不能使用内置函数...

如果使用内置函数，则表示这些那么，除非专门暴露为服务/全局变量(不是)，否则设计为在应用程序内部"运行的某些东西肯定不会在用JavaScript编写的脚本中可用.

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).

您可以使用 内置Google服务的严格子集 .请参阅本指南，以确定可以和不能使用的指南.

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

问题2:通过自定义函数返回可点击的超链接的任何方式?

否，未使用自定义功能.必须通过setFormula() 方法来设置公式授权，并且未与自定义功能一起列入白名单.

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.

第三季度:我想使用一种在插入新工作表时自动更新的解决方案

答案取决于何时插入新表"是什么意思

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

1. 用户手动插入新工作表:使用可安装的onChange 触发

首先，安装一个onChange触发器，该触发器将在被触发时运行名为createTableOfContents的函数:

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();
}

然后，根据需要声明createTableOfContents.我创建了一个示例用于演示:

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:

1. 通过脚本插入工作表:从插入工作表的功能运行createTableOfContents.

我不想使用宏

其他答案不是马可.宏是用Google Apps脚本编写的函数的子集，必须在清单中声明，并通过键盘快捷键进行调用.

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.

参考

1. setFormulas() 参考
2. offset() 参考

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

---

建议的信用归TheMaster:

---

Credit for the suggestion goes to TheMaster:

代替设置HYPERLINK公式，您可以使用 setRichTextValues() 方法和 RichTextValueBuilder 内置服务中的类.所需的更改很小:

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);

这篇关于如何在Google表格电子表格中添加目录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！