---
title: "Generate Google Sheets Formulas with AI - Giacomo Melzi"
description: "Describe the formula you need in plain English and let AI write it in Google Sheets. A free Apps Script function and an OpenAI key, set up in 5 minutes."
url: https://giacomomelzi.com/generate-google-sheets-formulas-automatically/
language: en
alternates:
  it: https://giacomomelzi.com/it/formule-google-sheets-con-ai/
author: "Giacomo Melzi"
date: 2022-12-14
updated: 2026-11-02
---
# Generate Google Sheets Formulas Automatically with AI

Published 14 December 2022 · [AI](https://giacomomelzi.com/category/ai/), [Automation](https://giacomomelzi.com/category/automation/), [Google Sheets](https://giacomomelzi.com/category/google-sheets/)

> **⚡ Updated November 2026**: the original code stopped working when OpenAI retired its old models. This version uses OpenAI's current API, keeps your API key out of the code, and costs a fraction of a cent per formula.

We've all been there: eyes glued to the spreadsheet, completely stumped, trying to figure out the right formula to get the job done.

And how the hell did Index / Match work? 😩

If you follow me here or on [LinkedIn](https://linkedin.com/in/giacomomelzi), you know I love spreadsheets. I'm one of the few weirdos who enjoy writing formulas and scripts.

Still, one day I was stuck on a formula for more than 20 minutes. Out of frustration, I asked myself: why not let AI write the formulas for me?

Funny thing, it worked surprisingly well! 🤯

So I made **\=WhatTheFormula(), a formula (duh) that writes formulas from a plain English description.**

You describe what the formula should do, and the AI comes back with a formula ready to use. No more guesswork.

Here are a few examples 👇

[Demo video](https://giacomomelzi.com/media/posts/generate-google-sheets-formulas-automatically/what-the-formula-demo.mp4)

## Doesn't Google Sheets have AI built in now?

It does, on some plans. Google Sheets now has an `=AI()` function (also called `=GEMINI()`), included with eligible paid Google Workspace and Google AI plans. It's great for working on text: summarising, classifying, drafting.

\=WhatTheFormula() does something different:

- **It gives you the formula itself**, not a one-off answer. You paste it in, and it keeps recalculating as your data changes.
- **It works on any Google account**, including free ones. You only pay OpenAI for what you use.
- **You see how it's done**, so next time you might not need it 😉

The steps are 5:

1) Create an OpenAI API key
2) Paste the code into your Google Sheet
3) Save your API key in the script's settings
4) Describe the formula you need
5) Paste the result as a value and add the =

Let's break them down.

## 1) Create an OpenAI API key

- Sign in or create an account on the [OpenAI platform](https://platform.openai.com/)
- Open the [API keys page](https://platform.openai.com/api-keys) and click **Create new secret key**
- Copy the key and keep it somewhere safe: you'll need it in step 3

OpenAI's API is prepaid: add a few dollars of credit under **Billing** before you start. Each formula costs a fraction of a cent, so a few dollars last a very long time.

## 2) Paste the code into your Google Sheet

- Open a Google Sheet
- Go to **Extensions → Apps Script**
- Delete what's in the editor, paste the code below 👇 and click **Save**

```js
/**
 * Writes a Google Sheets formula from a description in plain English.
 *
 * @param {string} description What the formula should do, in plain English.
 * @param {string} separator Optional: ";" if your spreadsheet separates arguments with semicolons.
 * @return The formula, without the leading "=".
 * @customfunction
 */
function WhatTheFormula(description, separator = ',') {
  const MODEL = 'gpt-6.1-terra'; // any current OpenAI model works here

  const key = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
  if (!key) return 'Add your OpenAI key in Project Settings → Script properties (OPENAI_API_KEY)';
  if (!description) return 'Describe what the formula should do';

  const response = UrlFetchApp.fetch('https://api.openai.com/v1/responses', {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + key },
    muteHttpExceptions: true,
    payload: JSON.stringify({
      model: MODEL,
      reasoning: { effort: 'low' }, // fast enough for Google's 30-second limit on custom functions
      instructions:
        'You write Google Sheets formulas. Reply with one formula only: no "=" at the start, ' +
        'no explanation, no code block. Separate the function arguments with ' + separator,
      input: String(description),
    }),
  });

  const data = JSON.parse(response.getContentText());
  if (data.error) return 'OpenAI error: ' + data.error.message;

  const message = data.output.find((item) => item.type === 'message');
  const formula = message.content.find((part) => part.type === 'output_text').text;
  return formula.trim().replace(/^`+|`+$/g, '').replace(/^=/, '').trim();
}
```

## 3) Save your API key in the script's settings

The key doesn't go in the code: that way you can share or copy the code without giving it away.

- Still in Apps Script, click the gear icon (**Project Settings**) on the left
- Scroll down to **Script properties** and click **Add script property**
- Property: `OPENAI_API_KEY`. Value: your key
- Click **Save script properties**, then go back to your Google Sheet and refresh the page

Heads-up: anyone with **edit** access to this spreadsheet can open Apps Script and see the key. Use it in your own sheets, or create a separate key with a low credit balance for shared ones.

## 4) Describe the formula you need

You're almost good to go! Hurray! 😄

Type =WhatTheFormula() with your instructions in quotes:

\=WhatTheFormula("Translate cell A1 from English to Spanish")

Or write the instructions in a cell and point to it, like in the video: \=WhatTheFormula(C7)

Some regions separate arguments with semicolons instead of commas (e.g. SUM(A1;B1)). The default is commas, so if that's your case you don't have to do anything. If you need semicolons, add ";" as a second argument:

\=WhatTheFormula("some instructions";";")

## 5) Paste the result as a value and add the =

Google Sheets shows what a custom function returns as text: it can't turn it into a live formula on its own. So copy the cell and paste it as a value.

You can do it from the menu:

![The Paste special menu in Google Sheets with the Values only option](https://giacomomelzi.com/_astro/paste-values-only.nR_pOObE_Fh39r.jpg)

or with Shift + Cmd + V on a Mac, Ctrl + Shift + V on a PC.

Then add the = in front and press Enter.

That's it! Enjoy 🎉

#### ****Good to know****

- Delete the \=WhatTheFormula() cell when you're done with it: otherwise it runs again (and asks OpenAI again) every time you open the sheet.
- The results aren't always perfect. Be explicit in the description and check the formula before you rely on it. Even when it's not spot on, it's a great starting point!
- Want a different model? Change the `MODEL` line at the top of the code to any model on [OpenAI's models page](https://platform.openai.com/docs/models).

If you liked it, follow me on [LinkedIn](https://linkedin.com/in/giacomomelzi) and subscribe to the newsletter at the bottom of the page!

---

By [Giacomo Melzi](https://giacomomelzi.com/) · [Blog](https://giacomomelzi.com/blog/) · [LinkedIn](https://www.linkedin.com/in/giacomomelzi)