Скрипт содержания G Sheets

Question

Для книг Google с листами, состоящими из нескольких листов, я создаю таблицу содержания, в которой перечислены все листы в книге, чтобы упростить использование для пользователей. Я искал надстройку, макрос или скрипт, которые могут ускорить процесс. Никаких кубиков. Есть идеи, как автоматизировать процесс создания нового листа, который перечисляет имена всех других листов (одно имя листа на ячейку), а затем автоматически связывает ячейку с этим листом?

Answer 1

Сценарий :

function SHEETLIST() {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1 ) ;
  out[0] = [ "NAME" , "#GID" ];
  for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = 
  [sheets[i-1].getName() , sheets[i-1].getSheetId() ];
  return out
}
catch( err ) {
  return "#ERROR!" }}

формула:

=SHEETLIST()

превратить названия листов в активные гиперссылки:

=ARRAYFORMULA(HYPERLINK("#gid="&
 QUERY(INDEX(SHEETLIST();;2); "offset 1"); 
 QUERY(INDEX(SHEETLIST();;1); "offset 1")))

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

=ARRAYFORMULA(IFNA(VLOOKUP(A1, HYPERLINK("#gid="&
 QUERY(INDEX(SHEETLIST();;2); "offset 1"); 
 QUERY(INDEX(SHEETLIST();;1); "offset 1")); 1; 0)))

где A1 - ваша "поисковая ячейка"

Answer 2

Просто чтобы пояснить, это отличное решение, за исключением того, что оно не будет обновляться, когда вы добавляете новые вкладки или переименовываете/переупорядочиваете текущие вкладки. Единственное решение, которое я смог найти, это создать простой триггер флажка.

Сначала я добавил флажок на вкладке «Оглавление». Затем я добавил скрипт выше, за исключением того, что вместо SHEETLIST() в первой строке я добавил номер ячейки флажка - например, SHEETLIST(B3).

Затем я помещаю формулу; в котором заполняются два столбца данных — Имя и #GID. В другой ячейке (где достаточно места для перечисления всех вкладок) я помещаю верхнюю = ФОРМУЛА МАССИВА... (не та, что с ВПР), но, опять же, вместо пустой скобки после SHEETLIST() я ввожу номер ячейки моего флажка в оба места, как показано ниже:

       =ARRAYFORMULA(HYPERLINK("#gid="&
  QUERY(INDEX(SHEETLIST(B3);;2); "offset 1"); 
  QUERY(INDEX(SHEETLIST(B3);;1); "offset 1")))

Затем я скрыл столбцы с данными NAME и #GID, чтобы вы могли видеть только оглавление с гиперссылками.

Теперь, всякий раз, когда я обновляю свои вкладки, я просто устанавливаю флажок, и все перезагружается. Не полностью автоматический/динамический, но лучшее решение, которое я смог найти.