Создание листа Навигация со списком листов гугл таблицы (включая скрытые), сортировка по названию

Навигация по статье

По кнопке открывается гугл документ. Вы можете скачать его и он будет в формате .txt

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

• Создание листа Навигация (если его нет) / обновление листа Навигация
• Граббинг названий листов (включая скрытые)
• Сортировка по правилу: если в начале названия листа стоит цифра, то такие листы находятся сверху списка от большего к меньшему.

Например:
30 КМ ООО Компания 1
29 КМ ООО Компания 2
28 КМ ООО Компания 3.

• Создание гиперссылок на листы (по ссылке можно перейти прямо на лист, независимо от того, скрытый он или нет). Если лист скрытый появляется уведомление "Показать скрытый лист".

Целесообразность скрипта

Описание скрипта

Результат скрипта

function createNavigationSheet() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var allSheets = spreadsheet.getSheets();
var navSheetName = "Навигация";

// Получаем или создаем лист навигации
var navSheet = spreadsheet.getSheetByName(navSheetName);
if (!navSheet) {
navSheet = spreadsheet.insertSheet(navSheetName);
} else {
// Очищаем существующий лист
navSheet.clear();
}

// Устанавливаем заголовки
navSheet.getRange("A1").setValue("Название листа");
navSheet.getRange("B1").setValue("Ссылка");

// Форматируем заголовки
var headerRange = navSheet.getRange("A1:B1");
headerRange.setBackground("#4a86e8")
.setFontColor("white")
.setFontWeight("bold")
.setHorizontalAlignment("center");

// Собираем данные о листах, исключая навигационный лист
var sheetData = [];
for (var i = 0; i < allSheets.length; i++) {
var sheet = allSheets[i];
var sheetName = sheet.getName();

if (sheetName !== navSheetName) {
sheetData.push({
name: sheetName,
sheet: sheet,
id: sheet.getSheetId(),
// Добавляем флаг и числовое значение для сортировки
hasNumber: hasLeadingNumber(sheetName),
numberValue: extractLeadingNumber(sheetName)
});
}
}

// Сортируем по пользовательским правилам
sheetData.sort(function(a, b) {
// Если оба начинаются с чисел
if (a.hasNumber && b.hasNumber) {
// Сортируем по убыванию (от большего к меньшему)
return b.numberValue - a.numberValue;
}

// Если только A начинается с числа - он должен быть выше
if (a.hasNumber && !b.hasNumber) {
return -1;
}

// Если только B начинается с числа - он должен быть выше
if (!a.hasNumber && b.hasNumber) {
return 1;
}

// Если оба без чисел - сортируем по алфавиту
return a.name.localeCompare(b.name);
});

// Заполняем данные
if (sheetData.length > 0) {
for (var i = 0; i < sheetData.length; i++) {
var row = i + 2;
var sheetName = sheetData[i].name;
var sheetId = sheetData[i].id;

// Название листа
navSheet.getRange("A" + row).setValue(sheetName);

// Создаем гиперссылку
var formula = '=HYPERLINK("#gid=' + sheetId + '"; "' + sheetName + '")';
navSheet.getRange("B" + row).setFormula(formula);
}

// Форматируем таблицу
navSheet.setColumnWidth(1, 200);
navSheet.setColumnWidth(2, 200);
navSheet.getRange(2, 1, sheetData.length, 2).setHorizontalAlignment("left");
navSheet.getRange(1, 1, sheetData.length + 1, 2).setBorder(true, true, true, true, true, true);

} else {
navSheet.getRange("A2").setValue("Нет других листов");
}

// Замораживаем заголовок
navSheet.setFrozenRows(1);

// Перемещаем навигационный лист на первое место
spreadsheet.setActiveSheet(navSheet);
spreadsheet.moveActiveSheet(1);
}

// Функция для проверки, начинается ли строка с числа
function hasLeadingNumber(str) {
if (!str || str.length === 0) return false;
var match = str.match(/^(\d+)/);
return match !== null;
}

// Функция для извлечения начального числа из строки
function extractLeadingNumber(str) {
if (!str || str.length === 0) return 0;
var match = str.match(/^(\d+)/);
if (match) {
return parseInt(match[1], 10);
}
return 0;
}

// Альтернативная версия без использования ID листов с новой сортировкой
function createSimpleNavigation() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var allSheets = spreadsheet.getSheets();
var navSheetName = "Навигация";

// Получаем или создаем лист навигации
var navSheet = spreadsheet.getSheetByName(navSheetName);
if (!navSheet) {
navSheet = spreadsheet.insertSheet(navSheetName);
} else {
navSheet.clear();
}

// Устанавливаем заголовки
navSheet.getRange("A1").setValue("Список листов");
navSheet.getRange("A1").setFontWeight("bold").setBackground("#4a86e8").setFontColor("white");

// Собираем данные о листах
var sheetData = [];
for (var i = 0; i < allSheets.length; i++) {
var sheetName = allSheets[i].getName();
if (sheetName !== navSheetName) {
sheetData.push({
name: sheetName,
sheet: allSheets[i],
hasNumber: hasLeadingNumber(sheetName),
numberValue: extractLeadingNumber(sheetName)
});
}
}

// Сортируем по тем же правилам
sheetData.sort(function(a, b) {
if (a.hasNumber && b.hasNumber) {
return b.numberValue - a.numberValue;
}

if (a.hasNumber && !b.hasNumber) {
return -1;
}

if (!a.hasNumber && b.hasNumber) {
return 1;
}

return a.name.localeCompare(b.name);
});

// Заполняем список
if (sheetData.length > 0) {
for (var i = 0; i < sheetData.length; i++) {
var cell = navSheet.getRange("A" + (i + 2));
var sheetName = sheetData[i].name;
cell.setValue(sheetName);

// Добавляем кликабельную ссылку через скрипт
var sheet = sheetData[i].sheet;
if (sheet) {
var richText = SpreadsheetApp.newRichTextValue()
.setText(sheetName)
.setLinkUrl('#gid=' + sheet.getSheetId())
.build();
cell.setRichTextValue(richText);
}
}
} else {
navSheet.getRange("A2").setValue("Нет других листов");
}

// Форматирование
navSheet.setColumnWidth(1, 300);
navSheet.getRange("A:A").setHorizontalAlignment("left");
navSheet.setFrozenRows(1);
}

// Функция для обновления навигации
function updateNavigation() {
createSimpleNavigation(); // Используем простую версию
}

// Функция для создания меню
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Навигация')
.addItem('Обновить список листов', 'updateNavigation')
.addItem('Создать навигацию с ссылками', 'createNavigationSheet')
.addToUi();
}

Скрипт для гугл таблицы "Создание листа Навигация":

Разработка и ведение таблиц в Excel и Гугл таблицах

Самозанятый - Абдуллина Индира Ринатовна, ИНН 025700449805, дата постановки на учёт, в качестве налогоплательщика 12.11.2021
Email для документов: damir.abdullin248@gmail.com, Telegram - https://t.me/mmsenyy27