Generate Google Sheets Formulas Automatically with AI

Cool right? Let’s get to the how-to!

The steps are 4:

1) Create an OpenAI account and generate an API key
2) Copy the code and paste it into your Google Sheet
3) Write the instructions for the formula you need to generate
4) Copy the generated formula as a value and paste it into another cell
4+1) Enjoy 😄🎉

Let’s break them down:

1) Create an OpenAI account and generate an API key

• Create an account OpenAI account by clicking here
• Click on your name -> View API keys -> Create a new secret key
• Copy the key and store it somewhere; we’ll need it in the next step.

2) Copy the code and paste it into your Google Sheet

• Open a Google Sheet
• Go to Extensions -> Apps Script
• Copy and Paste the code below 👇

/**
 * Generate a Google Sheets Formula from instructions in plain English 
 *
 * @param {string} prompt describe what you want the formula to do in plain English.
 * @param {string} specify the separator of the formula's arguments. Comma is the default value
 * @return The Google Sheets formula generated from you plain English instructions
 * @customfunction
 */
function WhatTheFormula(prompt,separator =",") {
  const openAiKey = "YOUR OPENAI KEY"
  const config = {
    endpoint: "https://api.openai.com/v1/completions",
    aiConfig: {
      model: "text-davinci-003",
      temperature: 0.1,
      max_tokens: 100,
      top_p: 1,
      best_of: 5,
      frequency_penalty: 0,
      presence_penalty: 0,
    }
  }
  let response;
  try {
    config.aiConfig.prompt = "Create google sheets formula that " + prompt;
    const options = {
      "method": "post",
      "headers": {
        "Authorization": "Bearer " + openAiKey,
        "Content-Type": "application/json"
      },
      "payload": JSON.stringify(config.aiConfig),
    }
    response = UrlFetchApp.fetch(config.endpoint,options)
  } catch(e) {
    response = e
  } 
  const res = JSON.parse(response.getContentText());
  
  let formula = res.choices[0].text
  if (formula.includes("=")) {
    formula = formula.replace("\n=","").replace("\n","").replaceAll(",",separator).trim();
  }
  return formula;
}

• Paste the OpenAI API key where you see const openAiKey = “YOUR OPENAI KEY”. Make sure to put your key between the quotes.
• Save -> Go back to your Google Sheet and refresh the page

3) Add the instructions for the formula you need to generate

You’re almost good to go! Hurray! 😄

Start typing =WhatTheFormula() and add your instructions as text like this:

=WhatTheFormula(“Translate cell A1 from English to Spanish”)

Depending on your region, you may have to add a second parameter to set the separator.

Some regions use semicolons as separators instead of the default comma (e.g., SUM(A1;B1)). The default of this formula is commas, so if that’s your case, you don’t have to do anything. On the other hand, if you need semicolons, just add “;” as a second argument in the formula.

Like this:

=WhatTheFormula(“some instructions”;”;”)

4) Copy the generated formula as a value and paste it into another cell

Given limitations on how google handles custom formulas like this, it’s impossible to have the formula evaluated automatically. You will have to copy and paste as value.

You can do it via the interface:

or Shift + Cmd + B on a Mac
or Ctrl + Shift + V on PC

Remember to add the preceding =

That’s it! Enjoy 🎉

Disclaimer

• Delete the formula when you’re done with it; otherwise, it will recalculate every time you open the Sheet.
• The results are not always perfect. You have to play with the description and be explicit. Tho, even in cases that are not precise, it’s a great starting point!

If you liked it follow me on Linkedin (where I post 2/3 times a week) and consider subscribing to the newsletter by scrolling down to the bottom of the page!