Google Apps Script UrlFetchApp OpenAI API example: Sheets integration template

Integrate the OpenAI API in Google Sheets with custom Apps Script functions and a template for running tasks in bulk.

ChatGPT works well for quick questions and ideation. But if you're already working in Sheets, running data entry tasks end-to-end in one place tends to be more practical. Integrating the OpenAI API directly lets you manage everything from a single spreadsheet.

This guide and template with Apps Script code was updated October 2026 for the gpt-6-luna model and the Responses API.

Prerequisites

• OpenAI API key

Create a new API key (and Organisation Project) or ask your tech admin to generate a unique one for you.

Save the key somewhere safe and private as soon as it's generated.

A project key can now be given an expiry date, and a project can have a monthly spend limit that stops requests once it's reached.

• G-account to use Google Sheets

Google Sheets is free to use and is possible to get started with another email address provider e.g. Outlook.

Setting up

To get you running quickly, make the template yours by creating a copy, adding your API key and authorising the Apps Script. You could then run the functions with the sample data there (in column F) as an initial test.

1. Make a copy of the updated Sheet template here for gpt-6-luna API model.

2. Next, you'll need to add your OpenAI API key. Go to Extensions > Apps Script > ⚙️ Project Settings > Script Properties > Add script property

For the 'Property' enter:

OPENAI_API_KEY

For 'Value' paste in your secret API key and then 'Save script properties'.

In the general settings above, check that the project time zone matches your local region.

3. Then you'll need to allow the Apps Script in order to connect OpenAI.

Next, go to Extensions > Apps Script > Editor > Debug

From the popup window, click 'Review permissions' and sign into Google if asked to.

Then click 'Advanced' > 'Go to [project name]'

Tick 'Select all' and click 'Continue' to allow securely encrypted cloud computing functions to make API calls to OpenAI.

Configuring the ChatGPT prompts

The script file you'll need to adjust is the 'config.gs' in the Apps Script Files Editor. This is where the main configurations are for writing instructions to ChatGPT. So, all you need to do is locate the "SYSTEM_PROMPT_PREPEND" AND "SYSTEM_PROMPT_APPEND" at the top of the file and swap out what's there between the straight apostrophes with your own prompts.

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.';

This is the structure of what is sent to GPT:

i) Prepended prompt

e.g. "You are a search engine marketing expert. Please review the following keyword search intent and propose an article based on it:"

ii) Sheet data cell(s) - from the 'Input' column F

e.g. "how to train a puppy"

iii) Appended prompt

e.g. "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."

The two prompts are joined and sent as the instructions, with the cell sent as the input. GPT's response then appears in the 'GPT Output' column G.

Generally, it's good prompt engineering practice to prepend a 'role' for the GPT, such as "You are a French translator" and give it a goal such as "You will identify the following excerpt's original language and translate it into French".

Following this "Prepended" prompt, will be data from the main Sheet "Input" column for context.

Then, you can append more specific expectations to your context.

Tip: Microsoft's perfect prompt formula is a good one to keep in mind when prompt engineering: 'goal + context + sources + expectations'. Wherein, a clear goal should be the heart of the prompt. Context is necessary for guiding the response. Sources and Expectations make the response more precise.

Running the custom functions

After saving your customisations to the 'System prompts for callGPT functions', go back to the main sheet and refresh the page.

Add context anywhere in column F: Input.

Now, you can either type the custom formula function "=GPT_OUTPUT" in any cell and use any cell location in the formula builder. Or use the toolbar menu "OpenAI Integration ✨" > "🤖 Process Outputs" to send the prepended/context input/appended prompt and fetch GPT's response.

By using the custom formula, you can use multiple rows of cells to be used as context in the prompt.

By using the custom function "🤖 Process Outputs" from the menu dropdown, you can run prompts per each row in automated batches. The default number of rows in a batch is 10, so you may experience an initial delay before seeing any responses.

The 2nd custom function you probably noticed, is the 'GPT_SUMMARY'/'🧑‍🏫 Process Summaries'. These are handy for summarising long-form responses in the 'GPT output' column G.

Tip: to brighten up your Sheets, type "PRIDE" across the first five columns.

Other useful functions

There's a 'ProgressTracker' tab for automatically recording the Sheet row number processed in case of time outs or if you added new rows of input that you want to process at a later time. Reset it to '2' (discounting the header row #1 in the 'ChatGPT function examples' tab) whenever you clear the columns or edit the prompts and want to restart from row 2.

Tip: use 'Reset Progress Tracker 🔄' in the toolbar menu to do it for you.

The final tab called 'Logs' records any errors you may encounter with OpenAI, such as issues with token limits or restrictions to ChatGPT's capabilities.

What do the custom toolbar menu functions do?

In the 'OpenAI Integration ✨' custom toolbar menu dropdown:

• '🤖 Process Outputs' takes whatever is in the Input column and sends it along inside your prompt to OpenAI row-by-row and returns ChatGPT's response in the corresponding row (in column G). It processes rows in batches of 10 and skips any row where the Input cell is empty or column G is already filled.
• '🧑‍🏫 Process Summaries' similarly takes whatever is in the GPT output column and sends it along inside your summary prompt to OpenAI row-by-row and returns ChatGPT's response in the corresponding row (in column H). It processes rows in batches of 10 and skips any row where the GPT output cell is empty or column H is already filled.
• 'Clear Input 🫥' deletes everything in column F (in the 'ChatGPT function examples' tab, excluding the heading)
• 'Clear Output 🫥' deletes everything in column G
• 'Clear Summary 🫥' deletes everything in column H
• 'Make formulas static 🔏' overwrites any cell with the custom formulas with the value (ChatGPT's response)
• 'Reset Progress Tracker 🔄' overwrites the row number recorded in the ProgressTracker tab (cell A2). The numbering reflects the Sheet row numbers in the 'ChatGPT function examples' tab.

Tip: remember to Reset the Progress Tracker before running the Process Summaries function or whenever you try a new prompt. You can also manually type in a row number you want to GPT to rerun or continue from.

Adjusting the OpenAI API configuration

In the config.gs file, locate the API and token configuration lines.

// API configuration
var API_URL = 'https://api.openai.com/v1/responses';
var MODEL_VERSION = 'gpt-6-luna';
var REASONING_EFFORT = 'none'; // gpt-6-luna also accepts low, medium, high, xhigh and max
var BATCH_SIZE = 10; // Number of rows to process per batch
var MAX_ATTEMPTS = 4; // Tries per request before an error is logged

// Maximum output tokens (these include reasoning tokens when reasoning is on)
var MAX_OUTPUT_TOKENS_OUTPUT = 8000; //100 tokens ~= 75 words
var MAX_OUTPUT_TOKENS_SUMMARY = 150;

Generally, you may only need to change the OpenAI API model. gpt-6-luna defaults to medium reasoning effort where gpt-5.4-mini defaulted to none, so the template sets REASONING_EFFORT to 'none' to keep the same behaviour. OpenAI's migration notes say to remove the temperature parameter whenever reasoning effort is anything other than 'none'.

Read more about reasoning effort controls.

The output token limits count reasoning tokens as well as the visible answer. With reasoning switched on, both limits need raising. OpenAI recommends reserving at least 25,000 tokens when starting out with reasoning models.

Tip: be mindful of straight (") vs curly (❝) quotation or apostrophe (' vs ’) marks when editing code.

The next section covers more detail on understanding and customising the integration.

Using the Responses API

OpenAI's Responses API (/v1/responses) is the recommended endpoint for new integrations. It replaces the message-based structure of Chat Completions with a simpler instructions + input format. It can handle conversation state server-side.

The template now calls the Responses API. The Chat Completions endpoint (/v1/chat/completions) is still supported and isn't deprecated. If an older copy of the template runs fine, there's no urgency to migrate.

Here's the request the template sends and how it reads the reply:

const payload = {
  model: MODEL_VERSION,
  instructions: instructions,
  input: String(input),
  max_output_tokens: maxOutputTokens,
  reasoning: { effort: REASONING_EFFORT },
  store: false
};

// The reply text sits inside the output array, in items of type 'message'
function getOutputText(data) {
  return (data.output || [])
    .filter(item => item.type === 'message')
    .flatMap(item => item.content)
    .filter(part => part.type === 'output_text')
    .map(part => part.text)
    .join('')
    .trim();
}

The key differences from Chat Completions:

• System prompts go in the instructions parameter rather than a messages array
• User input goes directly in input instead of a message object with role: 'user'
• The reply text sits inside the output array, in items of type 'message', instead of choices[0].message.content. The output_text shortcut in OpenAI's examples only exists in its Python and JavaScript SDKs
• max_output_tokens replaces max_completion_tokens
• Starting with GPT-5.4, tool calling with reasoning is only supported in the Responses API
• Responses are stored for later retrieval by default, for at least 30 days. The template sends store: false to turn that off

To move an older copy of the template to the Responses API, replace the API and token lines in config.gs with the ones shown above.

Then replace callGPT.gs and GPTsummarise.gs with the versions below. The rest of the template (menu, batch processing, progress tracker) works the same way.

Building a custom OpenAI UrlFetchApp integration

You'll see in the template Apps Script project that there are 7 different files:

1. config.gs: contains global configuration settings for the batch function and custom cell formula calls to OpenAI API, including the model and output token limits. Using the GPT functions/scripts will be charged by OpenAI.

// 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/responses';
var MODEL_VERSION = 'gpt-6-luna';
var REASONING_EFFORT = 'none'; // gpt-6-luna also accepts low, medium, high, xhigh and max
var BATCH_SIZE = 10; // Number of rows to process per batch
var MAX_ATTEMPTS = 4; // Tries per request before an error is logged

// Maximum output tokens (these include reasoning tokens when reasoning is on)
var MAX_OUTPUT_TOKENS_OUTPUT = 8000; //100 tokens ~= 75 words
var MAX_OUTPUT_TOKENS_SUMMARY = 150;

// 429 errors that a retry will not fix
var NO_RETRY_CODES = [
  'credit_balance_exhausted',
  'organization_spend_limit_exceeded',
  'project_spend_limit_exceeded',
  'organization_usage_limit_exceeded'
];

2. callGPT.gs: Sends each request to the OpenAI API and returns the response for the 'GPT output' Sheet column. Rate-limit and overload errors are retried, following OpenAI's Retry-After header when it's present.

function callOpenAI(instructions, input, maxOutputTokens) {
  const payload = {
    model: MODEL_VERSION,
    instructions: instructions,
    input: String(input),
    max_output_tokens: maxOutputTokens,
    reasoning: { effort: REASONING_EFFORT },
    store: false
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + getOpenAIKey()
    },
    muteHttpExceptions: true, // Return error responses instead of throwing
    payload: JSON.stringify(payload)
  };

  for (let attempt = 1; attempt <= MAX_ATTEMPTS; attempt++) {
    let response;
    try {
      response = UrlFetchApp.fetch(API_URL, options);
    } catch (error) {
      logUniqueError('API Error', 'Input: ' + input + ', Error: ' + error.message);
      return 'Error';
    }

    const status = response.getResponseCode();
    let data = {};
    try {
      data = JSON.parse(response.getContentText());
    } catch (error) {
      // Leave data empty if the body is not JSON
    }

    if (status === 200) {
      const output = getOutputText(data);
      if (isValidResponse(output)) {
        return output; // Cut short if the output token limit was reached
      }
      const detail = data.status === 'incomplete'
        ? 'Incomplete: ' + data.incomplete_details.reason
        : 'Output: ' + output;
      logUniqueError('Invalid Response', 'Input: ' + input + ', ' + detail);
      return 'Error';
    }

    const code = data.error ? data.error.code : '';
    const message = data.error ? data.error.message : response.getContentText();
    const retryable = status >= 500 || (status === 429 && NO_RETRY_CODES.indexOf(code) === -1);
    if (!retryable || attempt === MAX_ATTEMPTS) {
      logUniqueError('API Error', 'Input: ' + input + ', Status: ' + status + ', Error: ' + message);
      return 'Error';
    }

    // Wait at least as long as Retry-After asks, otherwise back off exponentially
    const headers = response.getHeaders();
    const retryAfter = Number(headers['Retry-After'] || headers['retry-after']);
    const seconds = retryAfter > 0 ? retryAfter : Math.pow(2, attempt);
    Utilities.sleep(seconds * 1000 + Math.random() * 500);
  }
}

// The reply text sits inside the output array, in items of type 'message'
function getOutputText(data) {
  return (data.output || [])
    .filter(item => item.type === 'message')
    .flatMap(item => item.content)
    .filter(part => part.type === 'output_text')
    .map(part => part.text)
    .join('')
    .trim();
}

function callOpenAIChat(input) {
  return callOpenAI(SYSTEM_PROMPT_PREPEND + ' ' + SYSTEM_PROMPT_APPEND, input, MAX_OUTPUT_TOKENS_OUTPUT);
}

3. GPTsummarise.gs: Handles calling the OpenAI API for generating GPT summaries in the 'GPT summarised' Sheet column. You could rework this to perform a different follow up task or make a copy of this to create a third follow up task for GPT to perform.

function callOpenAISummarize(input) {
  return callOpenAI(SUMMARY_PROMPT_PREPEND + ' ' + SUMMARY_PROMPT_APPEND, input, MAX_OUTPUT_TOKENS_SUMMARY);
}

4. sheetFunctions.gs: Contains functions to process outputs, summaries, clear columns, make custom functions static, and reset the Progress Tracker.

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);
}

5. helpers.gs: Contains JS helper functions for progress tracking and error logging.

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
}

6. menuUI.gs: Creates the custom toolbar menu in Google Sheets.

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();
}

7. customFunctions.gs: Contains custom functions for in-cell use (GPT_OUTPUT and GPT_SUMMARY formulas).

/**
 * 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);
}

More considerations

The template covers the core Responses API integration. Other OpenAI API endpoints handle images, audio and embeddings. The same UrlFetchApp pattern applies to each.

Tip: Google Sheets IMAGE function can render web images in your Sheet cell!

You could also integrate other AI APIs or LLMs to compare different AI responses in one place. Look for REST documentation and ask AI to help translate the scripts into Google Apps Script code.

Apps Script handles medium-sized automation well. At scale (thousands of rows or complex chains), Sheets slows down and a different framework makes more sense: Cloud Run functions with BigQuery, or open-source stacks like Vercel AI SDK with PostgreSQL.

Beyond Apps Script, Google tools like Data Studio and BigQuery offer deeper integration options, as do third-party Sheet add-ons.

Google Sheets also has a built-in AI function on eligible Workspace and Google AI plans. It generates text from a prompt and an optional cell range, for up to 350 selected cells at a time.