Как добавить оглавление в электронную таблицу Google Таблиц?

Question

Я пытаюсь добавить оглавление в Google Таблицы: просто хочу включить список всех листов внутри документа в виде интерактивных ссылок (получилось более 150 листов).

Я заставил его работать, но это сложнее, чем хотелось бы, и у меня возникают вопросы о пользовательских функциях в Google Таблицах.

Вот что у меня есть, конечно же 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 
}

Используя каждую формулу, я получаю:

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

И тогда я могу использовать HYPERLINK формула для получения ссылок: =hyperlink(concatenate("#gid=",A2), B2).

Так что работает.

Однако я попытался сделать все за один проход, вот так:

/**
 * 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 
}

Но, как было отмечено в комментариях к коду функции, это не работает.

Мои вопросы:

1. Тот факт, что вы не можете использовать встроенные функции внутри пользовательских функций Google Таблиц / скриптов Google Apps, действительно где-либо задокументирован? (Я бы сэкономил пару часов своей жизни, если бы знал.)

2. Есть ли способ, чтобы пользовательская функция возвращала интерактивные гиперссылки?

Я подозреваю, что использую Rangeмог бы это сделать, но мне они не нравятся (и тот факт, что вышеупомянутое все еще работает, делает это менее стимулом для обучения). Есть ли способ сделать так, чтобы пользовательская функция просто оценивала формулу, взятую из другого столбца?

ПРИМЕЧАНИЕ: я не хочу использовать макрос. Я хочу использовать решение, которое автоматически обновляется при вставке новых листов.

---

Я понимаю, что здесь есть аналогичный вопрос с очень полезным ответом. Это не совсем ответ на мои вопросы, но его немного проще использовать, чем мое текущее решение.

Answer 1

Q1: То, что вы не можете использовать встроенные функции...

Если под встроенными функциями вы имели в виду их, то нет, что-то, предназначенное для работы "внутри" приложения, наверняка не будет доступно в сценариях, написанных на JavaScript, если только специально не указано как сервисы / глобальные объекты (это не так).

Однако вы можете использовать строгое подмножество встроенных сервисов Google. Обратитесь к этому руководству, чтобы определить, что можно, а что нельзя.

Первоначально я думал, что вы буквально имели в виду использование встроенных функций, что, по-видимому, не так.

Q2: Есть ли способ, чтобы пользовательская функция возвращала интерактивные гиперссылки?

Нет, пользовательская функция не используется. Формулы должны быть установлены черезsetFormula() метод, который требует авторизации и не включен в белый список для использования с пользовательскими функциями.

Q3: Я хочу использовать решение, которое автоматически обновляется при вставке новых листов.

Ответ на этот вопрос зависит от того, что означает "когда вставляются новые листы":

1. Пользователь вставляет новые листы вручную: используйте устанавливаемый onChange вызывать

Сначала установите onChange триггер, который запустит функцию с именем createTableOfContents при выстреле:

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

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

    builder.create();
}

Затем объявите свой createTableOfContentsкак ты предпочитаешь. Я создал образец в демонстрационных целях:

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

Обратите внимание, что в примере не рассматривается уменьшение диапазона из-за удаления листа, но вы можете взять его отсюда:

2. Листы вставляются через скрипт: запустить createTableOfContents из функции, которая вставляет листы.

Я не хочу использовать макрос

Другой ответ - не марко. Макросы образуют подмножество функций, написанных в Google Apps Script, должны быть объявлены в манифесте и вызываются с помощью сочетаний клавиш.

Ссылки

1. setFormulas() ссылка
2. offset() ссылка

---

Кредит за предложение принадлежит TheMaster:

Вместо того, чтобы устанавливать HYPERLINK формулу, вы можете использовать setRichTextValues() метод и RichTextValueBuilder класс из встроенного сервиса. Требуется небольшое изменение:

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

Есть много способов пойти и много разных вещей, которые могут вам понадобиться.

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