Advanced Bright Data AI Research Function for Google Sheets

google-apps-script.js

/**
 * Advanced BrightData AI Research Function for Google Sheets
 *
 * Features:
 * - Multi-source scraping with 5 URLs
 * - Advanced entity extraction and sentiment analysis
 * - Caching for cost savings
 * - Rate limiting
 * - Async mode with callbacks
 * - Progress tracking
 * - Error recovery
 *
 * @customfunction
 */

// ============================================================================
// CONFIGURATION
// ============================================================================

const CONFIG = {
  N8N_WEBHOOK_URL: "https://your-n8n-domain.com/webhook/advanced-brightdata-search",
  API_KEY: "your-api-key-here",
  TIMEOUT: 120000, // 2 minutes
  ENABLE_CACHE: true,
  NOTIFY_EMAIL: "your-email@domain.com",
  SLACK_WEBHOOK: "" // Optional: for direct Slack notifications
};

// ============================================================================
// MAIN FUNCTION
// ============================================================================

/**
 * Advanced AI-powered web research function
 *
 * @param {string} prompt - The cell reference or context
 * @param {string} source - The search query
 * @param {string} [options] - Optional: JSON string with options
 * @return {string} The research result
 * @customfunction
 */
function BRIGHTDATA_ADVANCED(prompt, source, options) {
  if (!prompt || prompt === "") {
    return "❌ Prompt required";
  }

  if (!source || source === "") {
    return "❌ Source required";
  }

  // Parse options
  let opts = {
    language: "English",
    format: "short", // short|detailed|json
    notifyEmail: CONFIG.NOTIFY_EMAIL,
    enableCache: CONFIG.ENABLE_CACHE,
    async: false
  };

  try {
    if (options) {
      opts = { ...opts, ...JSON.parse(options) };
    }
  } catch (e) {
    return "❌ Invalid options JSON: " + e.message;
  }

  // Get cell context
  let cellInfo = getCellInfo();

  // Build payload
  const payload = {
    prompt: prompt,
    source: source,
    language: opts.language,
    notifyEmail: opts.notifyEmail,
    context: {
      spreadsheetId: cellInfo.spreadsheetId,
      sheetName: cellInfo.sheetName,
      cellAddress: cellInfo.cellAddress,
      timestamp: new Date().toISOString(),
      format: opts.format
    },
    options: {
      enableCache: opts.enableCache,
      async: opts.async
    }
  };

  // Make request
  const requestOptions = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
    headers: {
      "Accept": opts.format === "json" ? "application/json" : "text/plain",
      "key": CONFIG.API_KEY
    }
  };

  try {
    const response = UrlFetchApp.fetch(CONFIG.N8N_WEBHOOK_URL, requestOptions);
    const responseCode = response.getResponseCode();

    if (responseCode !== 200) {
      Logger.log("Error response: " + response.getContentText());
      return `❌ Error ${responseCode}: ${getErrorMessage(responseCode)}`;
    }

    const result = response.getContentText();

    // Handle different formats
    if (opts.format === "json") {
      return formatJsonResult(result);
    } else if (opts.format === "detailed") {
      return result; // Full extended summary
    } else {
      return result; // Short main answer
    }

  } catch (error) {
    Logger.log("Exception: " + error.toString());
    return "❌ Connection error: " + error.toString();
  }
}

/**
 * Async version - returns immediately, updates cell when done
 *
 * @param {string} prompt
 * @param {string} source
 * @param {string} [options]
 * @customfunction
 */
function BRIGHTDATA_ASYNC(prompt, source, options) {
  const opts = options ? JSON.parse(options) : {};
  opts.async = true;

  // Call main function with async flag
  const result = BRIGHTDATA_ADVANCED(prompt, source, JSON.stringify(opts));

  return "🔄 Processing... (will update automatically)";
}

/**
 * Get extended details with entities, sentiment, and data tables
 *
 * @param {string} prompt
 * @param {string} source
 * @customfunction
 */
function BRIGHTDATA_DETAILS(prompt, source) {
  const options = {
    format: "detailed",
    language: "English"
  };

  return BRIGHTDATA_ADVANCED(prompt, source, JSON.stringify(options));
}

/**
 * Get JSON output with full structured data
 *
 * @param {string} prompt
 * @param {string} source
 * @customfunction
 */
function BRIGHTDATA_JSON(prompt, source) {
  const options = {
    format: "json",
    language: "English"
  };

  return BRIGHTDATA_ADVANCED(prompt, source, JSON.stringify(options));
}

/**
 * Batch research - process multiple queries in parallel
 *
 * @param {Array<Array<string>>} queries - 2D array: [[prompt, source], [prompt, source], ...]
 * @param {string} [options]
 * @return {Array<string>} Results array
 * @customfunction
 */
function BRIGHTDATA_BATCH(queries, options) {
  if (!Array.isArray(queries) || queries.length === 0) {
    return [["❌ Invalid queries array"]];
  }

  const results = [];
  const opts = options ? JSON.parse(options) : {};

  for (let i = 0; i < queries.length; i++) {
    const [prompt, source] = queries[i];

    if (!prompt || !source) {
      results.push(["❌ Missing prompt or source"]);
      continue;
    }

    try {
      const result = BRIGHTDATA_ADVANCED(prompt, source, JSON.stringify(opts));
      results.push([result]);

      // Rate limiting: wait 1 second between requests
      if (i < queries.length - 1) {
        Utilities.sleep(1000);
      }
    } catch (e) {
      results.push([`❌ Error: ${e.message}`]);
    }
  }

  return results;
}

// ============================================================================
// ENTITY EXTRACTION FUNCTIONS
// ============================================================================

/**
 * Extract just the people mentioned
 *
 * @param {string} prompt
 * @param {string} source
 * @return {string} Comma-separated list of people
 * @customfunction
 */
function BRIGHTDATA_PEOPLE(prompt, source) {
  const options = { format: "json" };
  const result = BRIGHTDATA_ADVANCED(prompt, source, JSON.stringify(options));

  try {
    const data = JSON.parse(result);
    return data.entities.people.join(", ");
  } catch (e) {
    return result; // Return original if parsing fails
  }
}

/**
 * Extract just the organizations mentioned
 *
 * @param {string} prompt
 * @param {string} source
 * @return {string} Comma-separated list of organizations
 * @customfunction
 */
function BRIGHTDATA_ORGS(prompt, source) {
  const options = { format: "json" };
  const result = BRIGHTDATA_ADVANCED(prompt, source, JSON.stringify(options));

  try {
    const data = JSON.parse(result);
    return data.entities.organizations.join(", ");
  } catch (e) {
    return result;
  }
}

/**
 * Extract sentiment
 *
 * @param {string} prompt
 * @param {string} source
 * @return {string} Sentiment: positive|neutral|negative
 * @customfunction
 */
function BRIGHTDATA_SENTIMENT(prompt, source) {
  const options = { format: "json" };
  const result = BRIGHTDATA_ADVANCED(prompt, source, JSON.stringify(options));

  try {
    const data = JSON.parse(result);
    return `${data.overallSentiment} (${Math.round(data.confidence * 100)}%)`;
  } catch (e) {
    return result;
  }
}

/**
 * Extract key insights only
 *
 * @param {string} prompt
 * @param {string} source
 * @return {string} Key insights
 * @customfunction
 */
function BRIGHTDATA_INSIGHTS(prompt, source) {
  const options = { format: "json" };
  const result = BRIGHTDATA_ADVANCED(prompt, source, JSON.stringify(options));

  try {
    const data = JSON.parse(result);
    return data.keyInsights.map((insight, i) => `${i + 1}. ${insight}`).join("\n");
  } catch (e) {
    return result;
  }
}

// ============================================================================
// UTILITY FUNCTIONS
// ============================================================================

/**
 * Get current cell information
 */
function getCellInfo() {
  try {
    const sheet = SpreadsheetApp.getActiveSheet();
    const activeCell = sheet.getActiveCell();

    return {
      spreadsheetId: SpreadsheetApp.getActiveSpreadsheet().getId(),
      sheetName: sheet.getName(),
      cellAddress: activeCell.getA1Notation()
    };
  } catch (e) {
    return {
      spreadsheetId: "unknown",
      sheetName: "unknown",
      cellAddress: "unknown"
    };
  }
}

/**
 * Format JSON result for spreadsheet display
 */
function formatJsonResult(jsonString) {
  try {
    const data = JSON.parse(jsonString);

    let output = `ANSWER: ${data.mainAnswer}\n\n`;
    output += `CONFIDENCE: ${Math.round(data.confidence * 100)}%\n`;
    output += `SOURCES: ${data.sourcesUsed}\n`;
    output += `SENTIMENT: ${data.overallSentiment}\n\n`;

    if (data.keyInsights && data.keyInsights.length > 0) {
      output += `INSIGHTS:\n`;
      data.keyInsights.forEach((insight, i) => {
        output += `${i + 1}. ${insight}\n`;
      });
    }

    return output;
  } catch (e) {
    return jsonString; // Return raw if parsing fails
  }
}

/**
 * Get user-friendly error messages
 */
function getErrorMessage(code) {
  const messages = {
    400: "Bad request - check your parameters",
    401: "Unauthorized - check your API key",
    403: "Forbidden - API key may be invalid",
    404: "Webhook not found - check URL",
    429: "Rate limit exceeded - try again later",
    500: "Server error - contact support",
    503: "Service unavailable - try again later",
    504: "Gateway timeout - query took too long"
  };

  return messages[code] || "Unknown error";
}

// ============================================================================
// ADMIN FUNCTIONS
// ============================================================================

/**
 * Test the webhook connection
 */
function testConnection() {
  const testPayload = {
    prompt: "Test",
    source: "Connection test",
    context: { test: true }
  };

  const options = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(testPayload),
    muteHttpExceptions: true,
    headers: {
      "key": CONFIG.API_KEY
    }
  };

  try {
    const response = UrlFetchApp.fetch(CONFIG.N8N_WEBHOOK_URL, options);
    const code = response.getResponseCode();

    if (code === 200) {
      Logger.log("✅ Connection successful!");
      return "✅ Connected";
    } else {
      Logger.log(`❌ Connection failed with code ${code}`);
      return `❌ Error ${code}`;
    }
  } catch (e) {
    Logger.log("❌ Connection error: " + e.toString());
    return "❌ Connection failed";
  }
}

/**
 * Clear cache for a specific query
 */
function clearCache(prompt, source) {
  // This would need to call a separate n8n webhook to clear cache
  // Implementation depends on your n8n setup
  Logger.log("Cache clear requested for: " + prompt + " - " + source);
  return "Cache clear not yet implemented";
}

/**
 * Get usage statistics
 */
function getUsageStats() {
  // This would query your DataTable logs
  // Implementation depends on your n8n setup
  return "Usage stats not yet implemented";
}

/**
 * HTTP endpoint handler for async callbacks
 */
function doPost(e) {
  try {
    const data = JSON.parse(e.postData.contents);

    // Find and update the target cell
    const ss = SpreadsheetApp.openById(data.spreadsheetId);
    const sheet = ss.getSheetByName(data.sheetName);
    const cell = sheet.getRange(data.cellAddress);

    // Update with result
    cell.setValue(data.result);

    return ContentService.createTextOutput(JSON.stringify({
      status: "success",
      message: "Cell updated"
    })).setMimeType(ContentService.MimeType.JSON);

  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({
      status: "error",
      message: error.toString()
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

/**
 * Health check endpoint
 */
function doGet(e) {
  return ContentService.createTextOutput(JSON.stringify({
    status: "alive",
    message: "Advanced BrightData Apps Script is running",
    version: "2.0.0",
    timestamp: new Date().toISOString(),
    features: [
      "Multi-source scraping",
      "Entity extraction",
      "Sentiment analysis",
      "Caching",
      "Rate limiting",
      "Async mode",
      "Batch processing"
    ]
  })).setMimeType(ContentService.MimeType.JSON);
}

// ============================================================================
// EXAMPLE USAGE IN SHEETS
// ============================================================================

/**
 * Example formulas:
 *
 * Basic:
 * =BRIGHTDATA_ADVANCED("Apple Inc", "What is the current stock price?")
 *
 * With language:
 * =BRIGHTDATA_ADVANCED("Tesla", "Who is the CEO?", "{\"language\": \"Hebrew\"}")
 *
 * Detailed format:
 * =BRIGHTDATA_DETAILS("Microsoft", "Latest earnings report")
 *
 * JSON format:
 * =BRIGHTDATA_JSON("Amazon", "Employee count")
 *
 * Extract entities:
 * =BRIGHTDATA_PEOPLE("OpenAI", "Who are the founders?")
 * =BRIGHTDATA_ORGS("AI industry", "Major players")
 * =BRIGHTDATA_SENTIMENT("Tesla stock", "Market sentiment")
 *
 * Batch processing:
 * =BRIGHTDATA_BATCH({{"Apple", "CEO"}, {"Microsoft", "Revenue"}, {"Tesla", "Employees"}})
 *
 * Async mode:
 * =BRIGHTDATA_ASYNC("Complex query", "May take a while", "{\"notifyEmail\": \"me@domain.com\"}")
 */