Startups.js

/**
 * Manages the interactive update of startup data from accelerator portfolios.
 * * Modes of Operation:
 * 1. Selection Mode: Processes accelerators currently selected in the 'accelerators' sheet.
 * 2. Manual Input: Processes a specific accelerator URL typed by the user (must exist in DB).
 * * Key Features:
 * - Crawling: Detects 'Portfolio' or 'Companies' links to scrape the correct page automatically.
 * - Data Merging: Implements N-to-N logic. If a startup already exists, the new accelerator is appended to the list instead of creating a duplicate.
 * - Validation: Cross-references LLM extraction with actual DOM links, with a permissive fallback for sites with anti-bot protection.
 * - Auto-Formatting: Applies text wrapping and fixed column widths for optimal readability.
 */
function update_startups() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const accSheet = ss.getSheetByName('accelerators');
    const startupSheet = ss.getSheetByName('startups');
    const ui = SpreadsheetApp.getUi();

    if (!accSheet || !startupSheet) {
        ui.alert("Error: Missing sheets. Please run Setup first.");
        return ;
    }

    // Determine Target Accelerators via User Input/Selection
    const activeRange = ss.getActiveRange();
    let defaultRangeStr = "";

    const accData = accSheet.getDataRange().getValues();
    
    // If user is on 'accelerators' sheet, propose current selection
    if (activeRange && ss.getActiveSheet().getName() === 'accelerators') {
        defaultRangeStr = activeRange.getA1Notation();
    }

    const response = ui.prompt(
        'Update Startups from Accelerator',
        `Current selection: ${defaultRangeStr}\n\n` +
        `Click OK to process the selected accelerator(s), or type a specific website (e.g., 'https://seedcamp.com').`,
        ui.ButtonSet.OK_CANCEL
    );

    if (response.getSelectedButton() !== ui.Button.OK) return ;

    const input = response.getResponseText().trim();
    
    // If input is a URL/Domain -> Process single site. 
    // If input is empty/Range -> Process rows in sheet.
    let targets = []; // Array of objects: { name, website }

    if (input.includes('http')) {
        // Direct Website Input
        const manualUrlClean = normalize_URL(input);
        let foundAccelerator = null;

        for (let i = 1; i < accData.length; i++) {
            const sheetUrl = normalize_URL(accData[i][0]);
            if (sheetUrl === manualUrlClean) {
                foundAccelerator = {
                    name: accData[i][1],
                    website: sheetUrl
                };
                break;
            }
        }

        if (foundAccelerator) {
            targets.push(foundAccelerator);
        } else {
            ui.alert(`Error: The accelerator "${input}" is not present in the database.`);
            return ; 
        }
    } else {
        // Sheet Range Selection
        let targetRange;
        try {
            // Use input if provided, otherwise default to active selection
            const rangeStr = input === "" ? defaultRangeStr : input;
            if (!rangeStr) {
                ui.alert("Error: No selection or input provided.");
                return ;
            }
            targetRange = accSheet.getRange(rangeStr);
        } catch (e) {
            ui.alert("Error: Invalid range or input.");
            return ;
        }

        // Validate range is in accelerators sheet
        if (targetRange.getSheet().getName() !== 'accelerators') {
             ui.alert("Error: Range must be in the 'accelerators' sheet.");
             return ;
        }

        const startRow = targetRange.getRow();
        const numRows = targetRange.getNumRows();

        // Collect accelerators from the selected rows
        for (let i = 0; i < numRows; i++) {
            const r = startRow + i;
            if (r === 1) continue; // Skip header

            const website = accSheet.getRange(r, 1).getValue(); // Col A
            const name = accSheet.getRange(r, 2).getValue();    // Col B

            if (website) {
                targets.push({ name: name, website: normalize_URL(website) });
            }
        }
    }

    if (targets.length === 0) {
        ui.alert("No valid accelerators found in selection.");
        return ;
    }

    console.log(`[START] Processing ${targets.length} accelerators...`);

    // Pre-load Existing Startups (Global Map for Efficiency)
    const existingMap = new Map();
    const sData = startupSheet.getDataRange().getValues();
    
    for (let i = 1; i < sData.length; i++) {
        const url = normalize_URL(sData[i][0]);
        if (url) {
            existingMap.set(url, {
                rowIndex: i + 1,
                accelerators: sData[i][3] ? sData[i][3].toString() : ""
            });
        }
    }

    let totalNew = 0;
    let totalUpdated = 0;

    for (const target of targets) {
        const accWebsite = target.website;
        const accName = target.name;
        
        // Resolve URL
        const workingAccUrl = resolve_active_url(accWebsite);
        if (!workingAccUrl) {
            console.warn(`[SKIP] Website unreachable: ${accWebsite}`);
            continue;
        }

        console.log(`[ANALYZING] ${accName} (${workingAccUrl})...`);

        // SCRAPING
        let content = fetchWebsiteContent(workingAccUrl);
        if (!content) continue;

        if (content.portfolioUrl) {
            console.log(`[NAVIGATING] Portfolio found: ${content.portfolioUrl}`);
            const portfolioContent = fetchWebsiteContent(content.portfolioUrl);
            if (portfolioContent && portfolioContent.text.length > 500) {
                content = portfolioContent;
            }
        }

        // AI EXTRACTION
        const prompt = `You are a Data Extraction Expert.
            
            CONTEXT:
            - Accelerator Name: "${accName}"
            - Source Website: ${workingAccUrl}
            
            PAGE CONTENT:
            """${content.text}"""

            REAL LINKS FOUND:
            """${content.extractedLinks}"""

            TASK:
            Identify startups funded by "${accName}".
            For each startup, match with a REAL LINK if possible.
            ALSO EXTRACT the short description or "one-liner" pitch if present in the text.
            
            RULES:
            1. Prefer matching a URL from the "REAL LINKS" list.
            2. If description is missing, leave it empty string "".
            3. Return MAX 5 startups.
            
            JSON OUTPUT FORMAT:
            {
                "startups": [
                {
                    "name": "Revox",
                    "website": "https://revox.ai", 
                    "country": "UK",
                    "description": "Voice AI infrastructure to grow your revenue.",
                    "thought": "Found Revox in the list with description next to it."
                }
                ]
            }
        `;

        try {
            const result = call_LLM("Extract startup data.", prompt, true);
            const parsed = JSON.parse(result);
            const candidates = parsed.startups || [];

            console.log(`[AI] Found ${candidates.length} candidates for ${accName}: ${candidates}`);

            candidates.forEach(st => {
                const cleanStUrl = normalize_URL(st.website);
                const finalUrl = resolve_active_url(st.website);
                if (!finalUrl) {
                    if (st.website && st.website.includes('.')) {
                        console.warn(`[WARN] Validation Fallback: ${st.website} (Network fail, Syntax OK).`);
                        finalUrl = st.website;
                    } else {
                        return ;
                    }
                }

                if (existingMap.has(cleanStUrl)) {
                    // MERGE: Update existing record
                    const record = existingMap.get(cleanStUrl);
                    const currentAccs = record.accelerators;

                    // Avoid duplicate accelerator tag
                    if (!currentAccs.includes(accWebsite)) {
                        const newAccList = currentAccs ? `${currentAccs}, ${accWebsite}` : accWebsite;
                        startupSheet.getRange(record.rowIndex, 4).setValue(newAccList);
                        totalUpdated++;
                        record.accelerators = newAccList; // Update map
                        console.log(`[MERGE] Linked ${st.name} to ${accName}`);
                    }
                } else {
                    // INSERT: New record
                    startupSheet.appendRow([
                        finalUrl, 
                        st.name, 
                        st.country || "Europe", 
                        accWebsite, // Foreign Key
                        st.description || ""
                    ]);
                    
                    // Update Map immediately to handle duplicates within same batch
                    existingMap.set(cleanStUrl, { 
                        rowIndex: startupSheet.getLastRow(), 
                        accelerators: accWebsite 
                    });
                    totalNew++;
                    console.log(`[NEW] Added ${st.name}`);
                }
            });

        } catch (e) {
            console.error(`[ERROR] ${accName}: ${e.message}`);
            const errorMsg = e.message.toString().toLowerCase();
            if (errorMsg.includes("429") || errorMsg.includes("quota")) {
                ui.alert("Critical Error: Gemini Quota Exhausted.");
                return ;
            }
            else{
                ui.alert(`Error ${accName}: ${e.message}`);
                return ;
            }
        }
    }

    if (totalNew > 0 || totalUpdated > 0) {
        const lastRow = startupSheet.getLastRow();
        
        if (lastRow > 1) {
            const numRows = lastRow - 1;
            
            const fullDataRange = startupSheet.getRange(2, 1, numRows, 5);
            const nameColumnRange = startupSheet.getRange(2, 2, numRows, 1);
            const valuePropColumnRange = startupSheet.getRange(2, 5, numRows, 1);

            fullDataRange.setWrap(true);
            fullDataRange.setVerticalAlignment("middle");

            nameColumnRange.setNumberFormat("@");
            nameColumnRange.setFontColor("#000000");
            nameColumnRange.setFontLine("none");

            startupSheet.autoResizeColumns(1, 4);
            for (let i = 1; i <= 4; i++) {
                let width = startupSheet.getColumnWidth(i);
                if (width > 300) width = 300; 
                startupSheet.setColumnWidth(i, width + 20); 
            }

            startupSheet.setColumnWidth(5, 450); 
        }
    }

    ui.alert(`Process Complete.\n\nNew Startups: ${totalNew}\nUpdated (Merged): ${totalUpdated}`);
}

/**
 * Interactive tool to generate AI-powered Value Propositions for selected startups.
 * * Workflow:
 * 1. Detects the active selection to suggest a default range (e.g., "E5").
 * 2. Prompts the user to confirm or modify the target range (e.g., "E5:E10").
 * 3. Validates that the range targets the 'Value Proposition' column in the 'startups' sheet.
 * 4. Iterates through rows, fetching the startup's website content (from Col A).
 * 5. prompts the LLM to write a pitch following the strict schema: "Startup <Name> helps <Target> do <Action> so that <Benefit>".
 * 6. Updates cells in real-time and logs success/failure.
 */
function generate_value_propositions() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getActiveSheet();
    const ui = SpreadsheetApp.getUi();

    // Detect Active Range for Default Recommendation
    const activeRange = ss.getActiveRange();
    let defaultRangeStr = "E2"; // Fallback

    if (activeRange) {
        defaultRangeStr = activeRange.getA1Notation();
    }

    // Ask User for Confirmation
    const response = ui.prompt(
        'Generate Value Propositions',
        `Current selection: ${defaultRangeStr}\n\nClick OK to confirm this range, or type a new one (e.g., E5:E10):`,
        ui.ButtonSet.OK_CANCEL
    );

    if (response.getSelectedButton() !== ui.Button.OK) {
        return ; // User cancelled
    }

    // If user leaves input empty, use the detected default. Otherwise use input.
    let rangeString = response.getResponseText().trim();
    if (rangeString === "") {
        rangeString = defaultRangeStr;
    }

    // Parse and Validate Range
    let targetRange;
    try {
        targetRange = sheet.getRange(rangeString);
    } catch (e) {
        ui.alert(`Error: Invalid range format "${rangeString}". Use A1 notation (e.g., E5).`);
        return ;
    }

    if (targetRange.getSheet().getName() !== 'startups') {
        ui.alert("Error: The range must be in the 'startups' sheet.");
        return ;
    }

    if (targetRange.getColumn() !== 5) {
        ui.alert("Error: Please select cells in the 'Value Proposition' column (Column E).");
        return ;
    }

    const startRow = targetRange.getRow();
    const numRows = targetRange.getNumRows();
    
    if (startRow === 1) {
        ui.alert("Error: Cannot generate value props for the header row.");
        return ;
    }

    console.log(`[START] Processing range: ${rangeString} (${numRows} rows)`);
    let successCount = 0;
    let errorCount = 0;

    // Processing Loop
    for (let i = 0; i < numRows; i++) {
        const currentRow = startRow + i;
        
        const website = sheet.getRange(currentRow, 1).getValue();
        const name = sheet.getRange(currentRow, 2).getValue();
        const targetCell = sheet.getRange(currentRow, 5);

        if (!website) {
            targetCell.setValue("[Error: Missing Website]");
            errorCount++;
            continue ;
        }

        console.log(`[PROCESSING] Analyzing ${name} (${website})...`);

        const websiteData = fetchWebsiteContent(website);

        if (!websiteData || websiteData.text.length < 50) {
            console.warn(`[SKIP] Website unreachable: ${name}`);
            targetCell.setValue("[Error: Website Unreachable]");
            errorCount++;
            continue ;
        }

        const prompt = `You are a Copywriting Expert.
            SOURCE TEXT from ${name}'s website:
            """${websiteData.text.slice(0, 10000)}""" 

            TASK:
            Write a SINGLE sentence value proposition for "${name}" based strictly on the text above.
            
            REQUIRED FORMAT:
            "Startup <Name> helps <Target Audience> do <Core Action> so that <Main Benefit>"

            CONSTRAINTS:
            - Strict format adherence.
            - No markdown.
            - Under 30 words.
        `;

        try {
            let result = call_LLM("Precise copywriter.", prompt, false);
            result = result.replace(/^"|"$/g, '').trim(); 

            targetCell.setValue(result);
            successCount++;
            console.log(`[SUCCESS] Generated for ${name}`);

        } catch (e) {
            const errorMsg = e.message.toString();
            console.error(`[AI ERROR] ${name}: ${errorMsg}`);
            
            if (errorMsg.includes("429") || errorMsg.includes("quota")) {
                targetCell.setValue("[Error: Quota Exceeded]");
                ui.alert("Critical Error: Gemini API quota exhausted. Stopping execution.");
                return ; 
            }
            targetCell.setValue("[Error: AI Generation Failed]");
            errorCount++;
        }
    }
    ui.alert(`Process Completed for ${rangeString}.\n\nGenerated: ${successCount}\nFailed: ${errorCount}`);
}