function onOpen(e) {
const ui = SpreadsheetApp.getUi();
ui.createAddonMenu()
.addItem("🔄 Generate Tab Links", "runMenuScript")
.addToUi();
}
function onInstall(e) {
onOpen(e); // ensure menu shows up after install
}
function runMenuScript() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let menuSheet = ss.getSheetByName("Menu");
if (!menuSheet) {
menuSheet = ss.insertSheet("Menu");
}
const allSheets = ss.getSheets();
menuSheet.clear();
menuSheet.clearFormats();
if (menuSheet.getMaxRows() > 1) {
menuSheet.deleteRows(2, menuSheet.getMaxRows() - 1);
}
if (menuSheet.getMaxColumns() > 1) {
menuSheet.deleteColumns(2, menuSheet.getMaxColumns() - 1);
}
let row = 1;
for (const sheet of allSheets) {
const name = sheet.getName();
const gid = sheet.getSheetId();
const formula = `=HYPERLINK("#gid=${gid}", "${name}")`; // ← fixed here
const cell = menuSheet.getRange(row, 1);
cell.setFormula(formula);
const bgColor = sheet.getRange("A1").getBackground();
cell.setBackground(bgColor);
const richText = cell.getRichTextValue();
if (richText) {
const style = SpreadsheetApp.newTextStyle()
.setForegroundColor("#000000")
.setUnderline(false)
.build();
const styled = richText.copy()
.setTextStyle(0, name.length, style)
.build();
cell.setRichTextValue(styled);
}
row++;
}
menuSheet.autoResizeColumn(1);
SpreadsheetApp.getUi().alert("✅ Tab links generated in sheet: 'Menu'");
}