gistfile1.txt

/**
 * Calls the OpenRouter API to perform an action based on an instruction and optional cell range.
 * Uses the Chat Completions endpoint with a default model (e.g., "anthropic/claude-sonnet-4").
 *
 * @param {string} instruction The prompt or instruction for the AI.
 * @param {string|Array<Array<string>>} [range] Optional. A cell range (e.g., A2:C2) or an array of values.
 * @param {string} [model] Optional. Override the default model (e.g., "openai/gpt-4o-mini", "anthropic/claude-4-sonnet").
 * @return The text generated by the OpenRouter API.
 * @customfunction
 */
function XAI(instruction, range, model) {
  // --- Configuration ---
  const API_KEY_PROPERTY = "OPENROUTER_API_KEY"; // Key for storing API key in User Properties
  const DEFAULT_MODEL = "openai/gpt-4o"; // Change to your preferred default
  const API_URL = "https://openrouter.ai/api/v1/chat/completions";
  const MAX_TOKENS = 300;
  const TEMPERATURE = 0.7;
  // ---------------------

  if (!instruction) {
    throw new Error("Instruction parameter is required.");
  }

  const apiKey = PropertiesService.getUserProperties().getProperty(
    API_KEY_PROPERTY
  );
  if (!apiKey) {
    throw new Error(
      "OpenRouter API key not found. Please set it using the 'XAI OpenRouter > Set API Key' menu."
    );
  }

  let rangeContent = "";
  if (range) {
    if (Array.isArray(range)) {
      rangeContent = range.map((row) => row.join(" ")).join("\n");
    } else {
      rangeContent = String(range);
    }
  }

  let userPrompt = instruction;
  if (rangeContent) {
    userPrompt += "\n\n--- Data from Spreadsheet ---\n" + rangeContent;
  }

  const messages = [
    {
      role: "system",
      content:
        "You are an AI assistant integrated into Google Sheets. Respond only with the direct answer or generated text requested by the user. No explanations or filler.",
    },
    { role: "user", content: userPrompt },
  ];

  const payload = {
    model: model || DEFAULT_MODEL,
    messages: messages,
    max_tokens: MAX_TOKENS,
    temperature: TEMPERATURE,
  };

  const options = {
    method: "post",
    contentType: "application/json",
    headers: {
      Authorization: "Bearer " + apiKey,
      "HTTP-Referer": "https://docs.google.com", // Required by OpenRouter
      "X-Title": "Google Sheets AI Integration", // Optional but recommended
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  };

  try {
    const response = UrlFetchApp.fetch(API_URL, options);
    const responseCode = response.getResponseCode();
    const responseBody = response.getContentText();

    if (responseCode === 200) {
      const jsonResponse = JSON.parse(responseBody);
      if (
        jsonResponse.choices &&
        jsonResponse.choices.length > 0 &&
        jsonResponse.choices[0].message &&
        jsonResponse.choices[0].message.content
      ) {
        return jsonResponse.choices[0].message.content.trim();
      } else {
        Logger.log("Unexpected OpenRouter response format: " + responseBody);
        return "Error: Unexpected response format from OpenRouter.";
      }
    } else {
      Logger.log(
        "OpenRouter API Error - Code: " + responseCode + ", Body: " + responseBody
      );
      try {
        const errorJson = JSON.parse(responseBody);
        if (errorJson.error && errorJson.error.message) {
          return "Error: " + errorJson.error.message;
        }
      } catch (e) {}
      return "Error: Failed to call OpenRouter API (Code: " + responseCode + ")";
    }
  } catch (error) {
    Logger.log("Script Error: " + error);
    return "Error: " + error.message;
  }
}

// --- Helper Functions for API Key Management ---

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("XAI OpenRouter")
    .addItem("Set API Key", "setOpenRouterApiKey")
    .addToUi();
}

function setOpenRouterApiKey() {
  const ui = SpreadsheetApp.getUi();
  const promptTitle = "Set OpenRouter API Key";
  const promptMessage =
    "Enter your OpenRouter API key (it will be stored securely per user):";

  const result = ui.prompt(promptTitle, promptMessage, ui.ButtonSet.OK_CANCEL);
  const button = result.getSelectedButton();
  const apiKey = result.getResponseText();

  if (button == ui.Button.OK && apiKey && apiKey.trim() !== "") {
    PropertiesService.getUserProperties().setProperty(
      "OPENROUTER_API_KEY",
      apiKey
    );
    ui.alert("OpenRouter API Key set successfully for your user account.");
  } else {
    ui.alert("API Key setup cancelled.");
  }
}