Setup.js

/**
 * Initializes the database structure within the Google Sheet.
 * * Workflow:
 * 1. Defines the schema for 'accelerators' and 'startups' sheets.
 * 2. Creates missing sheets if they don't exist.
 * 3. Checks if headers are missing (empty A1 cell) and populates them.
 * 4. Applies formatting to headers:
 * - Blue background (#1155cc) with White bold text.
 * - Centered alignment and borders.
 * - Freezes the first row.
 * 5. Auto-resizes columns for better readability.
 * 6. Clean-up: Removes the default empty 'Foglio1'/'Sheet1' to keep the workbook clean.
 */
function setup_database() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    const schema = {
        'accelerators' : ['website', 'name', 'country'],
        'startups' : ['website', 'name', 'country', 'accelerator', 'value_proposition']
    };

    for (const sheetName of Object.keys(schema)) {
        let sheet = ss.getSheetByName(sheetName);

        if (!sheet) {
            sheet = ss.insertSheet(sheetName);
            console.log(`[SETUP] Created new sheet: ${sheetName}`);
        }
        else {
            console.log(`[SETUP] Sheet ${sheetName} already exists`);
        }

        const headers = schema[sheetName];
        // Assumes that if cell A1 is empty, the headers are missing
        if (sheet.getRange(1, 1).getValue() === "") {
            const headerRange = sheet.getRange(1, 1, 1, headers.length);  // row, column, number of rows and columns in the range
            headerRange.setValues([headers]);

            headerRange.setFontWeight('bold');
            headerRange.setBackground("#1155cc");
            headerRange.setFontColor("#ffffff");
            headerRange.setHorizontalAlignment("center");
            headerRange.setVerticalAlignment("middle");
            headerRange.setBorder(true, true, true, true, true, true);

            sheet.setFrozenRows(1);  // Freeze the first row

            sheet.autoResizeColumns(1, headers.length);
            for (let i = 1; i <= headers.length; i++) {
                const width = sheet.getColumnWidth(i);
                sheet.setColumnWidth(i, width + 40);
            }

            console.log(`[SETUP] Headers added to ${sheetName}`);
        }
    }

    const defaultSheet = ss.getSheetByName('Foglio1');  // Created by default by Google
    if (defaultSheet && defaultSheet.getLastRow() === 0) {
        ss.deleteSheet(defaultSheet);
    }

    const defaultSheet_en = ss.getSheetByName('Sheet1');  // Created by default by Google
    if (defaultSheet_en && defaultSheet_en.getLastRow() === 0) {
        ss.deleteSheet(defaultSheet_en);
    }

    SpreadsheetApp.getUi().alert('Setup complete!');
}

/**
 * Function that runs automatically when the spreadsheet is opened.
 * * Purpose:
 * Creates a custom menu "Startup Scouting AI" in the Google Sheets UI, providing easy access to all script functionalities.
 * * Menu Items:
 * - Set Up Database: Runs initialization.
 * - Scouting Accelerators: Finds new VCs.
 * - Update Startups: Scrapes portfolios.
 * - Generate Value Propositions: AI writing assistant.
 */
function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('Startup Scouting AI')
        .addItem('Set Up Database', 'setup_database')
        .addSeparator()
        .addItem('Scouting Accelerators', 'scouting_accelerators')
        .addItem('Update Startups', 'update_startups')
        .addItem('Generate value propositions', 'generate_value_propositions')
        .addToUi();
}