Google Apps Script UrlFetchApp OpenAI API example: Sheets integration template

OPENAI_API_KEY

var SYSTEM_PROMPT_PREPEND = 'You are a search engine marketing expert. Please review the following keyword search intent and propose an article based on it:';  

var SYSTEM_PROMPT_APPEND = 'Write your new article proposal in the form of a synopsis that could directly brief a content writer. Your response should be in plain text.';

// API configuration
var API_URL = 'https://api.openai.com/v1/chat/completions';
var MODEL_VERSION = 'gpt-5.4-mini';
var BATCH_SIZE = 10; // Number of rows to process per batch

// Maximum tokens configuration
var MAX_TOTAL_TOKENS = 4096;
var DEFAULT_MAX_COMPLETION_TOKENS_OUTPUT = 8000; //100 tokens ~= 75 words
var DEFAULT_MAX_COMPLETION_TOKENS_SUMMARY = 150;

function callOpenAIResponses(input) {
  const apiKey = getOpenAIKey();
  const url = 'https://api.openai.com/v1/responses';

  const payload = {
    model: MODEL_VERSION,
    instructions: SYSTEM_PROMPT_PREPEND + SYSTEM_PROMPT_APPEND,
    input: input,
    max_output_tokens: DEFAULT_MAX_COMPLETION_TOKENS_OUTPUT,
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + apiKey
    },
    payload: JSON.stringify(payload)
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());
    const output = data.output_text.trim();

    if (isValidResponse(output)) {
      return output;
    } else {
      logUniqueError('Invalid Response', 'Input: ' + input + ', Output: ' + output);
      return 'Error';
    }
  } catch (error) {
    logUniqueError('API Error', 'Input: ' + input + ', Error: ' + error.message);
    return 'Error';
  }
}

var API_URL = 'https://api.openai.com/v1/responses';

// System prompts for callGPT functions
var SYSTEM_PROMPT_PREPEND = 'You are a search engine marketing expert. Please review the following keyword search intent and propose an article based on it:'; //Prepend a role and instructions for tasking the GPT
var SYSTEM_PROMPT_APPEND = 'Write your new article proposal in the form of a synopsis that could directly brief a content writer. Your response should be in plain text.'; // Additional prompt to append if needed

// System prompts for GPTsummarise functions
var SUMMARY_PROMPT_PREPEND = 'You are a British English editor. Please review the following text:'; //Prepend a role and instructions for tasking the GPT
var SUMMARY_PROMPT_APPEND = 'Summarize in plain text as a bullet point list of the most important points and use relevant emojis to highlight key points while staying true to the original content.'; // Additional prompt to append if needed

// Retrieve the API key securely from Project Settings > Script Properties
function getOpenAIKey() {
  return PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
}

// API configuration
var API_URL = 'https://api.openai.com/v1/chat/completions';
var MODEL_VERSION = 'gpt-5.4-mini';
var BATCH_SIZE = 10; // Number of rows to process per batch

// Maximum tokens configuration
var MAX_TOTAL_TOKENS = 4096;
var DEFAULT_MAX_COMPLETION_TOKENS_OUTPUT = 8000; //100 tokens ~= 75 words
var DEFAULT_MAX_COMPLETION_TOKENS_SUMMARY = 150;

function getMaxCompletionTokens(prompt, defaultMaxCompletionTokens) {
  const promptTokens = estimateTokens(prompt);
  return Math.min(defaultMaxCompletionTokens, MAX_TOTAL_TOKENS - promptTokens);
}

// Utility function to estimate token count
function estimateTokens(text) {
  // Simple estimation: 1 token per 4 characters (average for English text)
  return Math.ceil(text.length / 4);
}

function callOpenAIChat(input) {
  const apiKey = getOpenAIKey();
  const url = API_URL;

  const prompt = SYSTEM_PROMPT_PREPEND + input + SYSTEM_PROMPT_APPEND;
  const maxCompletionTokens=getMaxCompletionTokens(prompt, DEFAULT_MAX_COMPLETION_TOKENS_OUTPUT);

  const payload = {
    model: MODEL_VERSION,
    messages: [
      { role: 'system', content: SYSTEM_PROMPT_PREPEND + SYSTEM_PROMPT_APPEND },
      { role: 'user', content: input }
    ],
    max_completion_tokens:maxCompletionTokens,
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': `Bearer ${apiKey}`
    },
    payload: JSON.stringify(payload)
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const json = response.getContentText();
    const data = JSON.parse(json);
    const output = data.choices[0].message.content.trim();
    
    if (isValidResponse(output)) {
      return output;
    } else {
      logUniqueError('Invalid Response', `Input: ${input}, Output: ${output}`);
      return 'Error';
    }
  } catch (error) {
    logUniqueError('API Error', `Input: ${input}, Error: ${error.message}`);
    return 'Error';
  }
}

function callOpenAISummarize(input) {
  const apiKey = getOpenAIKey();
  const url = API_URL;

  const prompt = SUMMARY_PROMPT_PREPEND + input + SUMMARY_PROMPT_APPEND;
  const maxCompletionTokens=getMaxCompletionTokens(prompt, DEFAULT_MAX_COMPLETION_TOKENS_SUMMARY);

  const payload = {
    model: MODEL_VERSION,
    messages: [
      { role: 'system', content: SUMMARY_PROMPT_PREPEND + SUMMARY_PROMPT_APPEND },
      { role: 'user', content: input }
    ],
    max_completion_tokens:maxCompletionTokens,
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': `Bearer ${apiKey}`
    },
    payload: JSON.stringify(payload)
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const json = response.getContentText();
    const data = JSON.parse(json);
    const output = data.choices[0].message.content.trim();

    if (isValidResponse(output)) {
      return output;
    } else {
      logUniqueError('Invalid Response', `Input: ${input}, Output: ${output}`);
      return 'Error';
    }
  } catch (error) {
    logUniqueError('API Error', `Input: ${input}, Error: ${error.message}`);
    return 'Error';
  }
}

function processOutputs() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  let lastProcessedRow = getLastProcessedRow();
  const batchSize = BATCH_SIZE;

  while (lastProcessedRow - 1 < data.length) {
    const end = Math.min(lastProcessedRow + batchSize - 1, data.length);
    const batch = data.slice(lastProcessedRow - 1, end);

    batch.forEach((row, index) => {
      if (row[5] && !row[6]) { // Column F (index 5) is input, Column G (index 6) is output
        const input = row[5];
        const output = callOpenAIChat(input);
        if (output !== 'Error') {
          sheet.getRange(lastProcessedRow + index, 7).setValue(output); // Write the output to column G (index 6)
        }
      }
    });

    lastProcessedRow = end + 1;
    setLastProcessedRow(lastProcessedRow);
    SpreadsheetApp.flush();
  }
}

function processSummaries() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  let lastProcessedRow = getLastProcessedRow();
  const batchSize = BATCH_SIZE;

  while (lastProcessedRow - 1 < data.length) {
    const end = Math.min(lastProcessedRow + batchSize - 1, data.length);
    const batch = data.slice(lastProcessedRow - 1, end);

    batch.forEach((row, index) => {
      if (row[6] && !row[7]) { // Column G (index 6) is output, Column H (index 7) is summary
        const output = row[6];
        const summary = callOpenAISummarize(output);
        if (summary !== 'Error') {
          sheet.getRange(lastProcessedRow + index, 8).setValue(summary); // Write the summary to column H (index 7)
        }
      }
    });

    lastProcessedRow = end + 1;
    setLastProcessedRow(lastProcessedRow);
    SpreadsheetApp.flush();
  }
}

function clearColumn(column) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange(2, column, sheet.getLastRow() - 1, 1); // Start from row 2 to avoid headers
  range.clearContent();
}

function clearInput() {
  clearColumn(6); // Column F (index 6)
}

function clearOutput() {
  clearColumn(7); // Column G (index 7)
}

function clearSummary() {
  clearColumn(8); // Column H (index 8)
}

function makeCustomFunctionsStatic() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const formulas = range.getFormulas();
  const values = range.getValues();

  for (let row = 0; row < formulas.length; row++) {
    for (let col = 0; col < formulas[row].length; col++) {
      if (formulas[row][col].startsWith('=GPT_OUTPUT') || formulas[row][col].startsWith('=GPT_SUMMARY')) {
        sheet.getRange(row + 1, col + 1).setValue(values[row][col]);
      }
    }
  }
}

function resetProgressTracker() {
  const progressTrackerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ProgressTracker');
  progressTrackerSheet.getRange('A2').setValue(2);
}

function getLastProcessedRow() {
  const progressTrackerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ProgressTracker');
  return parseInt(progressTrackerSheet.getRange('A2').getValue());
}

function setLastProcessedRow(row) {
  const progressTrackerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ProgressTracker');
  progressTrackerSheet.getRange('A2').setValue(row);
}

function logUniqueError(type, message) {
  const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Logs');
  const existingErrors = logSheet.getDataRange().getValues();
  const errorIndex = existingErrors.findIndex(row => row[1] === message);
  
  if (errorIndex > -1) {
    // If error message already exists, append the affected row number
    logSheet.getRange(errorIndex + 1, 3).setValue(logSheet.getRange(errorIndex + 1, 3).getValue() + `, ${getLastProcessedRow()}`);
  } else {
    // Log new error with the initial row number
    logSheet.appendRow([new Date(), type, message, getLastProcessedRow()]);
  }
}

function isValidResponse(response) {
  // Basic validation example: ensure non-empty response
  return response && response.length > 20; // Example condition
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('OpenAI Integration ✨')
    .addItem('🤖 Process Outputs', 'processOutputs')
    .addItem('🧑‍🏫 Process Summaries', 'processSummaries')
    .addItem('Clear Input 🫥', 'clearInput')
    .addItem('Clear Output 🫥', 'clearOutput')
    .addItem('Clear Summary 🫥', 'clearSummary')
    .addItem('Make formulas static 🔏', 'makeCustomFunctionsStatic')
    .addItem('Reset Progress Tracker 🔄', 'resetProgressTracker')
    .addToUi();
}

/**
 * Generates a GPT output for the given input.
 * @param {string} input The input text to process.
 * @return {string} The GPT output.
 * @customfunction
 */
function GPT_OUTPUT(input) {
  return callOpenAIChat(input);
}

/**
 * Generates a summary for the given GPT output.
 * @param {string} output The GPT output to summarize.
 * @return {string} The summarized output.
 * @customfunction
 */
function GPT_SUMMARY(output) {
  return callOpenAISummarize(output);
}