Created
January 7, 2025 14:03
-
-
Save estevecastells/df3fef32507a13e8fed663f488d530ba to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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