Introduction

Google Sheets is a powerful place to prototype automations because it’s where work happens: lists, notes, customer rows, and content drafts live there. By combining Google Apps Script with the OpenAI Responses API, you can add five practical, no-code (no external server) ChatGPT-driven automations to a sheet: row summarization, bulk email drafts, tagging/classification, bullet → paragraph expansion, and on-demand translation/tone rewrites. Below you’ll find simple setup steps, explanations, and ready-to-copy Apps Script templates you can paste into a sheet-bound script editor and run. The scripts use UrlFetchApp to call OpenAI’s Responses API; save your API key in Script Properties for safety. (platform.openai.com)

Before you begin — required permissions & safety notes

• Create an OpenAI API key and never place it directly in cells visible to others. Use Apps Script PropertiesService to store it. (platform.openai.com)
• Apps Script calls to external services use UrlFetchApp and require authorization; when installing triggers you’ll grant those scopes. UrlFetchApp requests originate from Google servers (may need allowlisting in restrictive environments). (developers.google.com)
• The examples use the Responses endpoint (POST /v1/responses) and parse its output; the REST response contains output items and text content — code below uses a robust extractor to find text output. (developers.openai.com)

Quick setup (one-time)

1. Open Google Sheets → Extensions → Apps Script.
2. Create a new script file and paste the code blocks below.
3. Run the setup() function once to store your API key (it will prompt for authorization).
4. From the Apps Script editor you can also create installable triggers (some examples include helper code to auto-create triggers). For spreadsheet triggers, use ScriptApp.newTrigger(...).forSpreadsheet(...).onEdit().create() if needed. (developers.google.com)

Common helper functions (paste these first)

// ===== Common helpers =====
function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
}

function callOpenAI(prompt, model='gpt-4o-mini') {
  const apiKey = getApiKey();
  if (!apiKey) throw new Error('Set OPENAI_API_KEY in Script Properties (use setup())');

  const url = 'https://api.openai.com/v1/responses';
  const payload = {
    model: model,
    input: prompt
  };
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + apiKey },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  const res = UrlFetchApp.fetch(url, options);
  const code = res.getResponseCode();
  const text = res.getContentText();
  if (code < 200 || code >= 300) throw new Error('OpenAI request failed: ' + code + ' ' + text);
  const data = JSON.parse(text);
  // Robust extractor for textual outputs
  if (data.output_text) return data.output_text; // SDK convenience (rare in REST)
  if (data.output && data.output.length) {
    for (const item of data.output) {
      if (item.content && item.content.length) {
        for (const c of item.content) {
          if (c.type === 'output_text' && c.text) return c.text;
        }
      }
    }
  }
  // Fallback: stringify whole response
  return JSON.stringify(data);
}

function setup() {
  // Run once: paste your key when prompted
  const key = Browser.inputBox('Paste your OpenAI API key (sk-...)');
  PropertiesService.getScriptProperties().setProperty('OPENAI_API_KEY', key);
  SpreadsheetApp.getUi().alert('API key saved to Script Properties.');
}

Automation 1 — Summarize a row on edit (writes summary in Column B)

Design: When a cell in Column A is edited, generate a one-sentence summary and write it to the same row’s Column B. Use an installable onEdit trigger (recommended — simple triggers have auth limits).

function onEditSummarize(e) {
  try {
    const range = e.range;
    const sheet = range.getSheet();
    if (sheet.getName() !== 'Sheet1') return; // change as needed
    if (range.getColumn() !== 1) return; // only Column A
    const text = range.getValue().toString();
    if (!text) return;

    const prompt = `Summarize the following in one short sentence:\n\n${text}`;
    const out = callOpenAI(prompt);
    sheet.getRange(range.getRow(), 2).setValue(out); // write to Col B
  } catch (err) {
    console.error(err);
  }
}

// Optional helper to create the installable trigger
function createOnEditTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onEditSummarize').forSpreadsheet(ss).onEdit().create();
}

Automation 2 — Bulk generate personalized email drafts (menu-driven)

Design: Add a custom menu “AI Tools” → “Generate Emails” to produce draft emails based on columns: Name (A), Context (B) → Email draft (C).

function onOpen() {
  SpreadsheetApp.getUi().createMenu('AI Tools')
    .addItem('Generate Emails', 'generateEmails')
    .addToUi();
}

function generateEmails() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) { // skip header
    const name = rows[i][0];
    const context = rows[i][1];
    if (!name && !context) continue;
    const prompt = `Write a professional, friendly email to ${name}. Context: ${context}\nInclude greeting and 3-sentence body.`;
    const draft = callOpenAI(prompt);
    sheet.getRange(i+1, 3).setValue(draft);
    Utilities.sleep(500); // gentle pacing to avoid bursts
  }
}

Automation 3 — Tagging & classification (comma-separated tags into Column C)

Design: Ask the model for 3-5 short tags for each text in Column A and write them as CSV into Column C.

function tagRows() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    const text = rows[i][0];
    if (!text) continue;
    const prompt = `Read this and return 3 short tags (comma-separated):\n\n${text}`;
    const tags = callOpenAI(prompt);
    sheet.getRange(i+1, 3).setValue(tags);
    Utilities.sleep(300);
  }
}

Automation 4 — Expand bullet points to a paragraph (user-selected range)

Design: User selects a cell with bullets (or a single cell), runs “Expand Bullets” from the custom menu; script replaces or writes the expanded paragraph beside it.

function expandBullets() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  const text = range.getValue();
  if (!text) { SpreadsheetApp.getUi().alert('Select a cell with bullets.'); return; }
  const prompt = `Expand these bullet points into a coherent 4-sentence paragraph:\n\n${text}`;
  const out = callOpenAI(prompt);
  sheet.getRange(range.getRow(), range.getColumn() + 1).setValue(out); // write to next column
}

Automation 5 — On-demand translate / rewrite for tone (flip a checkbox to run)

Design: When a row’s “Translate?” column (Column D) is set to "Yes" (or checked), rewrite Column A into the target language (Column E) or rewrite in a different tone.

function onEditTranslate(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== 'Sheet1') return;
  // Assume Column D is the trigger (4)
  if (range.getColumn() === 4) {
    const flag = range.getValue().toString().toLowerCase();
    if (flag === 'yes' || flag === 'translate') {
      const row = range.getRow();
      const source = sheet.getRange(row,1).getValue();
      const prompt = `Translate the following into Spanish (concise):\n\n${source}`;
      const out = callOpenAI(prompt);
      sheet.getRange(row,5).setValue(out); // Column E
      // Clear flag or update status
      sheet.getRange(row,4).setValue('done');
    }
  }
}

Cost, rate limits and best practices

• Avoid calling the API on every keystroke; prefer explicit menu actions or a “Process” flag column. Keep sleeps between calls and batch operations to reduce spikes.
• Store the key in Script Properties (used above) rather than inline. Never share the sheet with keys visible. (developers.google.com)

Conclusion

With a handful of Apps Script functions and a safe place to store your OpenAI key, you can turn Google Sheets into a practical, no-code automation hub for summarization, email drafting, tagging, expansion, and translation. Copy the helper functions and the five templates into your script editor, adjust sheet names and columns to match your workflow, and run the setup() function once. For production usage, consider batching, monitoring cost, and handling errors and retries more robustly. If you want, I can adapt any of these templates to your exact sheet layout (tell me the column headers and your desired output style) and provide a single script file you can paste and run.