How to Use ChatGPT in Google Sheets with Apps Script

Google Sheets does not include an official =CHATGPT() formula. You can install a third-party add-on, but that means trusting its permissions, pricing and data handling. This guide takes the more transparent route: you will create a small Google Apps Script function, connect it directly to the OpenAI Responses API, and keep control of the prompt and API key.

What you will build

At the end, a sheet cell can call a custom function such as:

=AI_TEXT(A2, "Rewrite this product description in plain English. Return one paragraph.")

The first argument supplies the cell value. The second supplies the instruction. The script sends both to OpenAI and places the returned text in the formula cell. This is suitable for small, deliberate jobs such as classifying feedback, cleaning short descriptions or drafting a summary. It is not a free unlimited feature, and it should not be used with confidential data.

Before you start

• Use a Google account that can edit the spreadsheet.
• Create an OpenAI API key in your own OpenAI Platform project.
• Set a project budget or usage alert before processing a large sheet.
• Remove personal, financial, medical or client-confidential data unless you have a lawful and approved reason to process it.

A ChatGPT subscription and OpenAI API billing are separate. Having ChatGPT Plus does not automatically include API credit. Never paste an API key into a spreadsheet cell: anyone who can view that cell can copy it.

Step 1: open Apps Script

1. Create or open a Google Sheet.
2. Select Extensions → Apps Script.
3. Delete the sample myFunction code.
4. Paste the complete script below and click Save.

Google Sheets custom functions are written in JavaScript, so a Python, PHP, Java or .NET tab would not be runnable inside this editor. For this project, Apps Script is the correct language rather than a decorative multi-language example.

Step 2: add the complete working script

const OPENAI_MODEL = 'gpt-5.6-luna';

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AI Tools')
    .addItem('Save OpenAI API key', 'saveOpenAIKey')
    .addItem('Remove OpenAI API key', 'removeOpenAIKey')
    .addToUi();
}

function saveOpenAIKey() {
  const ui = SpreadsheetApp.getUi();
  const result = ui.prompt(
    'Save OpenAI API key',
    'Paste the key for your own OpenAI Platform project. It is stored in your Apps Script user properties, not in a sheet cell.',
    ui.ButtonSet.OK_CANCEL
  );

  if (result.getSelectedButton() !== ui.Button.OK) return;

  const key = result.getResponseText().trim();
  if (!key.startsWith('sk-')) {
    ui.alert('That does not look like an OpenAI API key. Nothing was saved.');
    return;
  }

  PropertiesService.getUserProperties().setProperty('OPENAI_API_KEY', key);
  ui.alert('API key saved for this Google user and script.');
}

function removeOpenAIKey() {
  PropertiesService.getUserProperties().deleteProperty('OPENAI_API_KEY');
  SpreadsheetApp.getUi().alert('API key removed.');
}

/**
 * Sends one cell value and an instruction to the OpenAI Responses API.
 *
 * @param {string} value Text to process, normally a cell reference such as A2.
 * @param {string} instruction A precise instruction, normally an absolute cell reference such as $D$1.
 * @return {string} Model output or a readable error.
 * @customfunction
 */
function AI_TEXT(value, instruction) {
  if (value === null || value === undefined || String(value).trim() === '') return '';
  if (!instruction || String(instruction).trim() === '') {
    throw new Error('Add an instruction, for example =AI_TEXT(A2, $D$1).');
  }

  const apiKey = PropertiesService.getUserProperties().getProperty('OPENAI_API_KEY');
  if (!apiKey) {
    throw new Error('Use AI Tools → Save OpenAI API key, then recalculate the formula.');
  }

  const inputText = String(value).slice(0, 6000);
  const task = String(instruction).slice(0, 2000);
  const payload = {
    model: OPENAI_MODEL,
    input: task + '\n\nText to process:\n' + inputText,
    max_output_tokens: 500,
    store: false
  };

  const response = UrlFetchApp.fetch('https://api.openai.com/v1/responses', {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + apiKey },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();
  if (status < 200 || status >= 300) {
    let message = 'OpenAI request failed with HTTP ' + status;
    try {
      const parsedError = JSON.parse(body);
      if (parsedError.error && parsedError.error.message) {
        message += ': ' + parsedError.error.message;
      }
    } catch (error) {
      // Keep the status-only message when the response is not JSON.
    }
    throw new Error(message);
  }

  const data = JSON.parse(body);
  const textParts = (data.output || [])
    .flatMap(item => item.content || [])
    .filter(part => part.type === 'output_text' && part.text)
    .map(part => part.text);

  if (!textParts.length) throw new Error('The API returned no text output.');
  return textParts.join('\n').trim();
}

Step 3: authorize and save the key

1. Return to the spreadsheet and reload the tab.
2. Open AI Tools → Save OpenAI API key.
3. Google will ask you to authorize the bound script. Review the permissions before continuing.
4. Paste the API key when the script asks for it.

The key is stored in Apps Script user properties. It is not written into the grid or printed to logs. Still, do not treat a shared bound script as a secret vault: spreadsheet editors can inspect the attached code, and an organization should use a server-side integration with centrally managed credentials instead.

Step 4: run your first formula

Put this sample text in A2:

delivery was quick but the setup guide made no sense

Put this instruction in D1:

Classify the feedback as Positive, Mixed, or Negative. Return only the label.

Then enter this formula in B2:

=AI_TEXT(A2, $D$1)

A reasonable output is Mixed. Check it yourself rather than assuming every answer is correct. If the category affects a customer, payment or business decision, require human review.

Prompts that produce usable spreadsheet output

A spreadsheet works best with predictable output. State the task, allowed labels, output format and constraints. Compare these two instructions:

For a deeper explanation of constraints and evaluation, use MetaCyberGuru’s guide to writing better AI prompts.

Control cost and spreadsheet performance

Every formula cell can create a separate API request. Copying the formula into 1,000 rows may create 1,000 billable calls and can also hit Google Apps Script execution limits or OpenAI rate limits. Start with five rows, inspect the output, then expand gradually.

• Do not use volatile inputs such as NOW() or RAND(); they can trigger repeated recalculation.
• Paste approved results as values when they no longer need to refresh.
• Keep prompts and source cells short.
• Process ranges in batches for production workflows instead of making one formula call per cell.
• Monitor API usage in the OpenAI dashboard.

Google documents a 30-second completion limit for custom functions. Long prompts or slow requests may therefore fail even when the API itself is working.

Privacy and security checklist

• Never store the API key in a visible cell.
• Use a dedicated OpenAI project key, not a key shared across unrelated applications.
• Revoke the key immediately if it appears in a screenshot, version history or public script.
• Minimize the data sent. Names, emails and account numbers usually are not needed for classification or rewriting.
• Do not send regulated or client-confidential data without the required organizational approval.
• Keep store: false when you do not need response storage, and review OpenAI’s current data-control documentation for your account.

Troubleshooting common errors

Practice project: triage customer feedback

1. Create 10 fictional feedback messages. Do not use real customer data.
2. Define exactly four allowed categories in one instruction cell.
3. Run AI_TEXT for five rows.
4. Manually label the same five rows and compare the results.
5. Record every disagreement and rewrite the instruction once.
6. Run the remaining five rows only after the revised instruction works.

This exercise teaches the important part of AI automation: evaluating a repeatable workflow, not merely producing text. For larger data jobs, continue with the free Python automation course, which covers CSV files, APIs, testing and audit trails.

Official documentation and review rule

• OpenAI API developer quickstart
• OpenAI model guidance
• Google Sheets custom functions
• Google Apps Script Properties service

OpenAI models, pricing and API fields can change. Before copying this into a production sheet, compare the code with the linked official documentation and recheck the project’s usage limits.

Frequently asked questions

Is ChatGPT built into Google Sheets?

No. This tutorial creates a custom Apps Script function that calls the OpenAI API. Third-party add-ons are separate products with their own terms and permissions.

Is this completely free?

Apps Script is available with Google Sheets, subject to Google quotas. OpenAI API usage is separately billed according to the selected model and tokens used.

Can I share the spreadsheet?

You can share it, but treat the bound script and its data flow as part of the document’s security boundary. For team or client use, a server-side service with managed credentials is safer than a personal spreadsheet script.

Why is only JavaScript shown?

Google Apps Script uses JavaScript. Python, PHP, Java and .NET examples would not run inside the Sheets script editor, so they would add length without helping the learner complete this project.