Skip to content

Instantly share code, notes, and snippets.

@estevecastells
Created January 7, 2025 14:03
Show Gist options
  • Save estevecastells/df3fef32507a13e8fed663f488d530ba to your computer and use it in GitHub Desktop.
Save estevecastells/df3fef32507a13e8fed663f488d530ba to your computer and use it in GitHub Desktop.
/**
* Configuration object for API settings
*/
const CONFIG = {
ANTHROPIC: {
API_KEY: 'API_KEY', // Replace with your Anthropic API key
URL: 'https://api.anthropic.com/v1/messages',
VERSION: '2023-06-01'
},
GEMINI: {
API_KEY: 'API_KEY', // Replace with your Gemini API key
URL: 'https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash:generateContent'
},
OPENAI: {
API_KEY: 'API_KEY', // Replace with your OpenAI API key
URL: 'https://api.openai.com/v1/chat/completions'
}
};
/**
* Enhanced retry configuration with request spreading
*/
const RETRY_CONFIG = {
MAX_RETRIES: 8,
INITIAL_DELAY: 1500,
MAX_DELAY: 20000,
BACKOFF_FACTOR: 2,
// New parameters for request spreading
MIN_JITTER_PERCENT: 0.3, // Minimum jitter multiplier
MAX_JITTER_PERCENT: 1.7, // Maximum jitter multiplier
TIME_WINDOW_MS: 1000, // Time window for spreading requests
// Prime numbers for additional randomization
PRIME_MULTIPLIERS: [2, 3, 5, 7, 11, 13, 17, 19]
};
/**
* Generates a unique request ID using time and random components
* @returns {string} Unique request identifier
*/
function generateRequestId() {
const timestamp = Date.now();
const random = Math.floor(Math.random() * 1000000);
return `${timestamp}-${random}`;
}
/**
* Creates a unique delay based on request ID and retry count
* @param {string} requestId - Unique identifier for the request
* @param {number} retryCount - Current retry attempt number
* @returns {number} Calculated delay in milliseconds
*/
function calculateUniqueDelay(requestId, retryCount) {
// Extract numeric value from request ID
const numericId = parseInt(requestId.split('-')[1]);
// Use prime number for this retry attempt
const primeFactor = RETRY_CONFIG.PRIME_MULTIPLIERS[retryCount % RETRY_CONFIG.PRIME_MULTIPLIERS.length];
// Calculate base delay with exponential backoff
const baseDelay = Math.min(
RETRY_CONFIG.MAX_DELAY,
RETRY_CONFIG.INITIAL_DELAY * Math.pow(RETRY_CONFIG.BACKOFF_FACTOR, retryCount)
);
// Add pseudo-random component based on request ID
const requestIdFactor = (numericId % 100) / 100;
// Calculate jitter range
const jitterRange = RETRY_CONFIG.MAX_JITTER_PERCENT - RETRY_CONFIG.MIN_JITTER_PERCENT;
const jitterFactor = RETRY_CONFIG.MIN_JITTER_PERCENT + (requestIdFactor * jitterRange);
// Combine all factors for final delay
let delay = baseDelay * jitterFactor;
// Add time window spreading using prime factors
delay += (numericId % RETRY_CONFIG.TIME_WINDOW_MS) * primeFactor;
// Add microsecond variation
delay += Math.random();
return Math.floor(delay);
}
/**
* Enhanced retry wrapper for API calls with request spreading
* @param {Function} apiCall - The API call function to retry
* @param {string} errorPrefix - Prefix for error messages
* @returns {Object} - API response or error
*/
function withRetry(apiCall, errorPrefix) {
const requestId = generateRequestId();
let lastError;
let currentDelay = 0;
for (let i = 0; i < RETRY_CONFIG.MAX_RETRIES; i++) {
try {
// Add small initial delay even on first attempt to help spread concurrent requests
if (i === 0) {
const initialSpread = requestId.split('-')[1] % 100;
Utilities.sleep(initialSpread);
}
const response = apiCall();
// Enhanced response validation
if (response.getResponseCode() === 200) {
const content = response.getContentText();
if (!content) {
throw new Error('Empty response received');
}
const json = JSON.parse(content);
if (json.error) {
throw new Error(JSON.stringify(json.error));
}
// Track successful request timing
Logger.log(`${errorPrefix}: Request ${requestId} succeeded after ${i} retries with total delay of ${currentDelay}ms`);
return response;
}
// Handle non-200 responses
throw new Error(`HTTP ${response.getResponseCode()}: ${response.getContentText()}`);
} catch (error) {
lastError = error;
// Enhanced error categorization
const errorString = error.toString().toLowerCase();
// Don't retry on authentication errors
if (errorString.includes('401') || errorString.includes('403')) {
throw new Error(`${errorPrefix}: Authentication failed - check your API key`);
}
// Don't retry on invalid requests
if (errorString.includes('400')) {
throw new Error(`${errorPrefix}: Invalid request - ${error}`);
}
// Calculate unique delay for this request and retry attempt
const delay = calculateUniqueDelay(requestId, i);
currentDelay += delay;
// Enhanced logging
Logger.log(`${errorPrefix}: Request ${requestId} - Attempt ${i + 1} failed. Retrying in ${delay}ms. ` +
`Total delay: ${currentDelay}ms. Error: ${error}`);
// Wait before next retry
Utilities.sleep(delay);
}
}
// Enhanced failure logging
Logger.log(`${errorPrefix}: Request ${requestId} failed after ${RETRY_CONFIG.MAX_RETRIES} attempts. ` +
`Total delay: ${currentDelay}ms. Last error: ${lastError}`);
throw new Error(`${errorPrefix}: All retry attempts failed for request ${requestId}. Last error: ${lastError}`);
}
/**
* Generates text using the Anthropic Claude API
* @param {string} prompt - The input prompt for the API
* @return {string} The generated response
* @customfunction
*/
function generateAnthropicText(prompt) {
const options = {
'method': 'post',
'headers': {
'Content-Type': 'application/json',
'x-api-key': CONFIG.ANTHROPIC.API_KEY,
'anthropic-version': CONFIG.ANTHROPIC.VERSION,
'request-id': generateRequestId() // Add request ID to headers
},
'payload': JSON.stringify({
'model': 'claude-3-5-sonnet-latest',
'max_tokens': 8000,
'messages': [
{
'role': 'user',
'content': prompt
}
]
}),
'muteHttpExceptions': true
};
try {
const response = withRetry(
() => UrlFetchApp.fetch(CONFIG.ANTHROPIC.URL, options),
'Anthropic API Error'
);
const json = JSON.parse(response.getContentText());
return json.content[0].text;
} catch (error) {
Logger.log('Anthropic API Error:', error);
return 'Error: ' + error.toString();
}
}
/**
* Generates text using the Google Gemini API
* @param {string} prompt - The input prompt for the API
* @return {string} The generated response
* @customfunction
*/
function generateGeminiText(prompt) {
const requestId = generateRequestId(); // Generate unique request ID
const url = `${CONFIG.GEMINI.URL}?key=${CONFIG.GEMINI.API_KEY}`;
const requestBody = {
contents: [{
parts: [
{ text: prompt }
]
}],
generationConfig: {
temperature: 0.5,
maxOutputTokens: 3500
},
};
const options = {
method: 'post',
contentType: 'application/json',
headers: {
'x-request-id': requestId // Add request ID to headers
},
payload: JSON.stringify(requestBody),
muteHttpExceptions: true
};
try {
const response = withRetry(
() => UrlFetchApp.fetch(url, options),
'Gemini API Error'
);
const jsonResponse = JSON.parse(response.getContentText());
const generatedText = jsonResponse.candidates[0].content.parts[0].text;
return generatedText;
} catch (error) {
Logger.log('Gemini API Error:', error);
return 'Error: ' + error.toString();
}
}
/**
* Generates text using the OpenAI GPT API
* @param {string} prompt - The input prompt for the API
* @return {string} The generated response
* @customfunction
*/
function generateOpenAIText(prompt) {
const requestId = generateRequestId(); // Generate unique request ID
const options = {
'method': 'post',
'contentType': 'application/json',
'headers': {
'Authorization': 'Bearer ' + CONFIG.OPENAI.API_KEY,
'x-request-id': requestId // Add request ID to headers
},
'payload': JSON.stringify({
model: "gpt-4",
messages: [
{"role": "user", "content": prompt}
],
temperature: 0.4,
max_tokens: 3500,
top_p: 1,
frequency_penalty: 0,
presence_penalty: 0
}),
'muteHttpExceptions': true
};
try {
const response = withRetry(
() => UrlFetchApp.fetch(CONFIG.OPENAI.URL, options),
'OpenAI API Error'
);
const data = JSON.parse(response.getContentText());
return data.choices[0].message.content;
} catch (error) {
Logger.log('OpenAI API Error:', error);
return 'Error: ' + error.toString();
}
}
/**
* Adds a custom menu to easily reload the script
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('AI APIs')
.addItem('Reload Script', 'reloadScript')
.addToUi();
}
/**
* Reloads the script to update any changes
*/
function reloadScript() {
SpreadsheetApp.getActive().toast('Script reloaded successfully!');
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment