We’ve all been there – eyes glued on the spreadsheet and minds completely stumped, trying to figure out the right Excel/Google Sheets formula to get the job done.
And how the hell did Index / Match work? 😩
If you follow me here or on Linkedin, you know I love spreadsheets. I’m one of the few weirdos who enjoy creating formulas and scripts.
Recently tho, I’ve been stuck for more than 20 min on formula. Out of frustration, I’ve got an idea, and I’ve asked myself…. why not let AI create the formulas for me?
Funny thing, it worked surprisingly well! 🤯
I’ve made =WhatTheFormula(), a formula (duh) to automatically create formulas from a plain English description.
You just have to describe what you need the formula to do, and the AI will come up with an accurate formula ready to use.
You won’t have to waste more time on guesswork!
Here you have a few examples 👇
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!
Want to be more efficient in your work?
Start automating your operations 🚀
Workflows automation doesn’t need to start big, let’s begin with the easy wins! Make the first step to automation