nobilix · April 9, 2025 15:26
diff --git a/Code.gs b/Code.gs
 // Constants at file root for global access
 const API_KEY = "your_openrouter_api_key"; // Replace with your OpenRouter API key
 const MODEL = "google/gemini-2.5-pro-exp-03-25:free";
 const SYSTEM_PROMPT = `You are a helpful assistant that provides concise, accurate responses. 
 When given data from a spreadsheet, analyze it thoroughly and provide insights that are relevant to the user's prompt. 
 Be direct and focus on the most important information.`;

 /**
 * Creates a custom Google Sheets function that makes LLM completion requests via OpenRouter.
 * 
 * @param {string} prompt - The prompt to send to the LLM.
 * @param {Range} range - Optional range of cells to include in the prompt.
 * @return {string} The LLM response.
 * @customfunction
 */
 function LLM(prompt, range) {
  
  // Process the range if provided
  let contextData = "";
  if (range !== undefined) {
    try {
      // Check if range is a valid range
      // In Google Sheets, a range will typically be a 2D array
      if (Array.isArray(range)) {
        contextData = "\n\nSpreadsheet data:\n" + rangeToString(range);
      } else {
        // If it's not an array, it might be a single cell or an invalid reference
        contextData = "\n\nSpreadsheet data: " + String(range);
      }
    } catch (error) {
      return "Error processing range: " + error.message;
    }
  }
  
  // Combine the prompt with the range data
  const fullPrompt = prompt + contextData;
  
  try {
    // Make the API request to OpenRouter
    const response = makeOpenRouterRequest(fullPrompt, SYSTEM_PROMPT, MODEL);
    return response;
  } catch (error) {
    return "Error: " + error.message;
  }
 }

 /**
 * Converts a range of cells to a formatted string.
 * 
 * @param {Range} range - The range to convert.
 * @return {string} A string representation of the range.
 */
 function rangeToString(range) {
  if (!range || !range.length) {
    return "";
  }
  
  // Handle both 2D arrays and single values
  if (typeof range === "string" || typeof range === "number") {
    return String(range);
  }
  
  // Format the range as a table
  return range.map(row => 
    row.map(cell => String(cell !== null && cell !== undefined ? cell : "")).join("\t")
  ).join("\n");
 }

 /**
 * Makes a request to the OpenRouter API.
 * 
 * @param {string} prompt - The prompt to send.
 * @param {string} systemPrompt - The system prompt.
 * @param {string} model - The model to use.
 * @return {string} The LLM response.
 */
 function makeOpenRouterRequest(prompt, systemPrompt, model) {
  const url = "https://openrouter.ai/api/v1/chat/completions";
  
  const payload = {
    model: model,
    messages: [
      {
        role: "system",
        content: systemPrompt
      },
      {
        role: "user",
        content: prompt
      }
    ]
  };
  
  const options = {
    method: "post",
    contentType: "application/json",
    headers: {
      "Authorization": `Bearer ${API_KEY}`,
      "HTTP-Referer": "https://docs.google.com/spreadsheets", // Required by OpenRouter
      "X-Title": "Google Sheets LLM Function" // Optional but good practice
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  
  try {
    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    
    if (responseCode !== 200) {
      throw new Error(`API returned status code ${responseCode}: ${response.getContentText()}`);
    }
    
    const responseData = JSON.parse(response.getContentText());
    
    // Add more robust error checking
    if (!responseData) {
      throw new Error("Empty response from API");
    }
    
    if (!responseData.choices || !Array.isArray(responseData.choices) || responseData.choices.length === 0) {
      throw new Error("No choices returned in the API response: " + JSON.stringify(responseData));
    }
    
    if (!responseData.choices[0].message || !responseData.choices[0].message.content) {
      throw new Error("Response format is unexpected: " + JSON.stringify(responseData.choices[0]));
    }
    
    return responseData.choices[0].message.content;
  } catch (error) {
    // Return a more descriptive error message
    return `Error: ${error.message}. If this is your first time using this function, please verify your API key is correct.`;
  }
 }
	// Constants at file root for global access
	const API_KEY = "your_openrouter_api_key"; // Replace with your OpenRouter API key
	const MODEL = "google/gemini-2.5-pro-exp-03-25:free";
	const SYSTEM_PROMPT = `You are a helpful assistant that provides concise, accurate responses.
	When given data from a spreadsheet, analyze it thoroughly and provide insights that are relevant to the user's prompt.
	Be direct and focus on the most important information.`;

	/**
	* Creates a custom Google Sheets function that makes LLM completion requests via OpenRouter.
	*
	* @param {string} prompt - The prompt to send to the LLM.
	* @param {Range} range - Optional range of cells to include in the prompt.
	* @return {string} The LLM response.
	* @customfunction
	*/
	function LLM(prompt, range) {

	// Process the range if provided
	let contextData = "";
	if (range !== undefined) {
	try {
	// Check if range is a valid range
	// In Google Sheets, a range will typically be a 2D array
	if (Array.isArray(range)) {
	contextData = "\n\nSpreadsheet data:\n" + rangeToString(range);
	} else {
	// If it's not an array, it might be a single cell or an invalid reference
	contextData = "\n\nSpreadsheet data: " + String(range);
	}
	} catch (error) {
	return "Error processing range: " + error.message;
	}
	}

	// Combine the prompt with the range data
	const fullPrompt = prompt + contextData;

	try {
	// Make the API request to OpenRouter
	const response = makeOpenRouterRequest(fullPrompt, SYSTEM_PROMPT, MODEL);
	return response;
	} catch (error) {
	return "Error: " + error.message;
	}
	}

	/**
	* Converts a range of cells to a formatted string.
	*
	* @param {Range} range - The range to convert.
	* @return {string} A string representation of the range.
	*/
	function rangeToString(range) {
	if (!range \|\| !range.length) {
	return "";
	}

	// Handle both 2D arrays and single values
	if (typeof range === "string" \|\| typeof range === "number") {
	return String(range);
	}

	// Format the range as a table
	return range.map(row =>
	row.map(cell => String(cell !== null && cell !== undefined ? cell : "")).join("\t")
	).join("\n");
	}

	/**
	* Makes a request to the OpenRouter API.
	*
	* @param {string} prompt - The prompt to send.
	* @param {string} systemPrompt - The system prompt.
	* @param {string} model - The model to use.
	* @return {string} The LLM response.
	*/
	function makeOpenRouterRequest(prompt, systemPrompt, model) {
	const url = "https://openrouter.ai/api/v1/chat/completions";

	const payload = {
	model: model,
	messages: [
	{
	role: "system",
	content: systemPrompt
	},
	{
	role: "user",
	content: prompt
	}
	]
	};

	const options = {
	method: "post",
	contentType: "application/json",
	headers: {
	"Authorization": `Bearer ${API_KEY}`,
	"HTTP-Referer": "https://docs.google.com/spreadsheets", // Required by OpenRouter
	"X-Title": "Google Sheets LLM Function" // Optional but good practice
	},
	payload: JSON.stringify(payload),
	muteHttpExceptions: true
	};

	try {
	const response = UrlFetchApp.fetch(url, options);
	const responseCode = response.getResponseCode();

	if (responseCode !== 200) {
	throw new Error(`API returned status code ${responseCode}: ${response.getContentText()}`);
	}

	const responseData = JSON.parse(response.getContentText());

	// Add more robust error checking
	if (!responseData) {
	throw new Error("Empty response from API");
	}

	if (!responseData.choices \|\| !Array.isArray(responseData.choices) \|\| responseData.choices.length === 0) {
	throw new Error("No choices returned in the API response: " + JSON.stringify(responseData));
	}

	if (!responseData.choices[0].message \|\| !responseData.choices[0].message.content) {
	throw new Error("Response format is unexpected: " + JSON.stringify(responseData.choices[0]));
	}

	return responseData.choices[0].message.content;
	} catch (error) {
	// Return a more descriptive error message
	return `Error: ${error.message}. If this is your first time using this function, please verify your API key is correct.`;
	}
	}