Summarizing feedback, drafting reply text, or classifying rows by hand becomes tedious once a sheet grows past a few dozen entries, and this tutorial shows how to hand that work to OpenAI's chat completions endpoint directly from Apps Script.
The script sends a small conversation, a system message describing the assistant's role and a user message containing the actual prompt, to the /v1/chat/completions endpoint and reads the generated reply out of the response.
Authentication uses a Bearer API key stored in Script Properties, the same pattern used for Slack and HubSpot elsewhere in this series, which keeps the key out of the script body and easy to rotate without editing code.
A second function, summarizeFeedbackColumn, shows a practical use of the completion call: it joins an entire feedback column into one prompt and writes the model's summary back into a single cell.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Config | Value | Purpose |
|---|---|---|
| OPENAI_API_KEY | Script property | Bearer token |
| Model | gpt-4o-mini | chat/completions model |
| Sheet | Prompts | Prompt, Response, RanAt |
What it does
callOpenAiCompletion sends a chat payload with a system role, a user role, and a low temperature setting to reduce randomness, then returns just the text content of the model's first choice.
Prerequisites
The response body's choices array contains one or more candidate replies, and this function reads choices[0].message.content, which is the standard location for the generated text in a chat completions response.
Walkthrough
Generate an API key from the OpenAI platform dashboard, store it under OPENAI_API_KEY in Script Properties, and confirm the account has available quota before running the function against real data.
Edge cases
The messages array separates instructions for how the assistant should behave, in the system role, from the actual task content, in the user role, which produces more consistent output than folding both into a single prompt string.
Testing
A non-200 response usually indicates either an invalid API key, an exceeded quota, or a request that violates the model's content policy, and the thrown error includes OpenAI's own error object so the specific cause is visible in the execution log.
Hardening
Call callOpenAiCompletion with a short, unambiguous prompt first to confirm the API key and model name are correct before wiring it into summarizeFeedbackColumn against a full sheet of real data.
Variations
Chat completion calls are billed per token for both the prompt and the generated reply, so a script that runs summarizeFeedbackColumn on a schedule should cap the input length and consider a cheaper model tier for routine, non-critical summaries.
Full code: callOpenAiCompletion()
Store the OpenAI API key in Script Properties, test callOpenAiCompletion with a short prompt, then call summarizeFeedbackColumn against a Feedback sheet.
var OPENAI_API_KEY_PROPERTY = 'OPENAI_API_KEY';
function callOpenAiCompletion(userPrompt) {
var apiKey = PropertiesService.getScriptProperties().getProperty(OPENAI_API_KEY_PROPERTY);
if (!apiKey) throw new Error('Missing OPENAI_API_KEY script property.');
var payload = {
model: 'gpt-4o-mini',
messages: [
{ role: 'system', content: 'You are a concise assistant for spreadsheet automation.' },
{ role: 'user', content: userPrompt }
],
temperature: 0.3
};
var options = {
method: 'post',
contentType: 'application/json',
headers: { Authorization: 'Bearer ' + apiKey },
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
var response = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', options);
var body = JSON.parse(response.getContentText());
if (response.getResponseCode() !== 200) {
throw new Error('OpenAI request failed: ' + JSON.stringify(body));
}
return body.choices[0].message.content.trim();
}
function summarizeFeedbackColumn() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Feedback');
var rows = sheet.getDataRange().getValues().slice(1);
var combinedText = rows.map(function (row) { return row[0]; }).join('\n');
var summary = callOpenAiCompletion('Summarize this customer feedback in three bullet points:\n' + combinedText);
sheet.getRange('C1').setValue(summary);
}- Line 4: Reads the OpenAI API key from Script Properties.
- Line 9: System role message sets the assistant's persistent behavior.
- Line 10: User role message carries the actual prompt content.
- Line 18: Sends the API key with the Bearer authorization scheme.
- Line 27: Reads the generated text from choices[0].message.content.
- Line 32: Joins an entire feedback column into a single prompt.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: call OpenAI chat completions
- 1OpenAI API key generated from the platform dashboard
- 2Key stored under OPENAI_API_KEY in Script Properties
- 3Account quota confirmed before running against real data
- 4Model name in the payload matches an available, correctly spelled model
- 5System message reviewed to match the intended assistant behavior
- 6Short test prompt run before wiring the call into a bulk function
- 7Token usage estimated for any scheduled or high-volume use
Frequently asked questions
The system message sets persistent behavior and tone for the assistant, while the user message carries the specific task, and keeping them separate produces more consistent results across different inputs than concatenating both into one string.
Temperature controls how much randomness the model applies when choosing its next words; a low value like 0.3 produces more predictable, repeatable output, which suits automation better than a high, creative-writing value.
By default the API returns a single candidate reply unless the request explicitly asks for more with the n parameter, so choices[0] is the complete answer in the common case this tutorial covers.
OpenAI returns a 429 response with an error indicating insufficient quota, and the script's error handling surfaces that message directly instead of a generic failure.
Yes, swapping the model field to a smaller, less expensive model is usually enough, since the request and response shape stays identical across most current chat models.
Chunk the feedback rows into smaller batches before joining them into a prompt, and summarize each batch separately if the combined text would otherwise exceed the model's token limit.