Three Ways To Use AI Tools With Google Sheets

Although AI has been around for decades, it’s only really been available to the public in its current form for the past year or so. And with the rise of these generative AI tools, like ChatGPT and Google Bard, AI is more accessible than ever.

But where does AI fit with Google Sheets?

And how do we use AI tools with Google Sheets?

In this post we’re going to look at how to use the most popular AI tools with Google Sheets. You’ll see how easy it is get started and learn that AI isn’t rocket science. It’s simply another useful tool that can save you time when you work with Google Sheets.

Specifically, we’ll look at three ways to use AI tools with Google Sheets:

1. Chatbots like ChatGPT or Bard
2. Google Sheets Add-Ons
3. Via the API and Apps Script (advanced)

1. Use Chatbot AI Tools with Google Sheets

A chatbot is a software application that aims to mimic human conversation.

The de facto leader of the space is OpenAI’s ChatGPT tool, which has grabbed most of the headlines (and users) since it was announced last year.

Since we’re talking about connecting AI tools with Google Sheets, the other tool we’ll look at is Google Bard.

However, there are plenty of other Large Language Model chatbots out (e.g. Anthropic’s Claude, Jasper AI, or Microsoft Bing to name a few) so feel free to explore them too.

Set up

👉 Sign up for a ChatGPT account here.

👉 Sign up for Google Bard with your Google account here.

Writing Prompts

Once you have an account, you can enter your prompts and have conversations with the tool.

A prompt is a short piece of text that tells the model what to do.

For example, a prompt might be “Write a love poem in the voice of a pirate”. The model then uses the prompt to generate text that is relevant to the task.

For ChatGPT, the prompt window looks like this:

Write your prompts in the input box indicated by the red arrow.

And for Google Bard:

Writing good prompts is part art, part science but there are a few quick and easy rules that you should follow:

• Provide context, e.g. “a Google Sheets formula” versus “a formula”
• Be specific, e.g. “data in cell A1” versus “data”
• Provide example inputs, e.g. “the input is a text string”
• Include clear instructions, e.g. “the formula should sort the data from highest to lowest”
• Provide example outputs, e.g “the output should be a single numeric value”

Think about how you would give the same set of instructions to a colleague to complete this task. Do they have enough details?

Using ChatGPT and Bard with Google Sheets

Once you have an account, there are a loads of practical use cases in Sheets.

1. Explain formulas
2. Create formulas
3. Troubleshoot broken formulas
4. Clean data
5. Categorize data
6. Generate datasets
7. Complete missing data
8. Analyze data
9. Explain Apps Script code
10. Write Apps Script to automate sheets

This is just the start. The list goes on and on!

Let’s look at a couple of examples in detail:

Creating Google Sheets formulas with ChatGPT

If you work with data in Google Sheets, then you’ve inevitably spent time tidying up datasets. Things like removing duplicates, fixing broken dates, extracting data from strings, etc.

Many of the these tasks are either manual (and therefore very slow) or require expertise to write complex formulas.

For example, to extract data we can use the powerful REGEX formulas but they are some of the most challenging formulas to use.

The reason they’re so challenging is because they require knowledge of regular expressions.

So it’s a shame if you can’t use them because of the high barrier to use.

It’s an ideal situation to ask for help from an AI tool. We can give a detailed prompt to ChatGPT or Bard, with an example of the problem:

“What is the Google Sheets formula to extract a phone number from a text string? An example string is “My phone number is (123)-456-7890.”

Then the AI tool can generate the formula for us to copy into our Sheet:

Writing Apps Script with AI Chatbots

Recently, I wanted to add a “toast” popup to my Apps Script project. A toast popup provides reassurance to the user that the script is doing something:

It’s been a while since I previously used a toast method so I needed to look up the syntax.

Instead of searching the documentation or doing a Google search, I asked ChatGPT to generate the code for me:

The AI chatbot tools are extremely good at writing short automation scripts.

In fact, I’ve found myself turning to these AI tools for every coding project to get me started.

It’s much quicker to use the AI chatbot to write 10 lines of code than it is for me to look up the documentation and figure it out myself.

See more examples of how you can use AI in Google Sheets.

2. Use AI via Google Sheets Add-Ons

Google Sheets add-ons are third-party programs that bring additional custom functionality to your Sheets.

There are a number of Google Sheets add-ons that bring AI technology directly into your Google Sheets.

There are too many to showcase in this post so I’ll just share two that I’ve been using, with examples of what you can do with each.

GPT for Sheets and Docs

The GPT for Sheets and Docs add-on lets you use ChatGPT directly within Google Sheets.

It has a library of useful AI-powered formulas that can perform data cleaning and analysis tasks for you. In addition, you can summarize, translate, write, and perform other generative AI tasks inside Sheets.

To use GPT for Sheets and Docs, you need to:

1. Install the add-on
2. Grab a free ChatGPT API key from OpenAI (where to find your API key)
3. Open the add-on sidebar from Google Sheets
4. Add your OpenAI key to the sidebar
5. Use the GPT formulas in your Sheet in the same way you use any other formulas

In the following example, I used a GPT formula to complete the missing data:

These AI-powered formulas are a powerful way to work with data in Sheets.

Coefficient Add-On

The other AI tool that I’ve been using inside of Google Sheets is GPT Copilot from Coefficient.

In a similar fashion to the GPT for Sheets add-on above, the Coefficient add-on has a bunch of GPT-powered formulas that do an impressive variety of data tasks. But, it also has an AI-powered pivot table builder and chart builder.

Select your data range, then use the add-on to build a pivot table for you:

Although it can’t build super complex pivot tables (yet!), it’s still a useful tool that makes pivot tables more accessible. And given that they’re arguably the most useful feature in spreadsheets, but also one of the most challenging, that’s a good thing.

3. Use AI Tools with Google Sheets via API and Apps Script

OpenAI has an API (Application Programming Interface) which allows you to connect your own applications directly to the same GPT models that power ChatGPT.

This lets you bring the power of the AI models into your application directly, rather than go through the chatbot interface.

We use Apps Script — a coding language that extends the functionality of Google Sheets — to connect to the API.

To get started with the OpenAI GPT API, you need to sign up for an account (if you haven’t already) and create a new API Key. This key is like a password for your application to access the API, so keep it secret.

We can use the Chat completions API endpoint to access GPT models programmatically.

We supply an input message and the API returns a model-generated message as the output.

Here’s a bare bones Apps Script template to call the OpenAI API (you’ll need to include your own API key to get this to work):

function callOpenAPI() {

  // your API key
  const apiKey = 'YOUR_API_KEY_HERE';

  // API endpoint
  const url = 'https://api.openai.com/v1/chat/completions';

  // GPT settings in here
  const data = {
    model: 'gpt-3.5-turbo',
    messages: [{"role": "user", "content": "What's the weather like in Washington DC today?"}]
  };

  // configure the API request to OpenAI
  const options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(data),
    'headers': {
      Authorization: 'Bearer ' + apiKey,
    },
  };

  // call API
  const response = UrlFetchApp.fetch(url, options);

  // Send the API request 
  const result = JSON.parse(response.getContentText());
  console.log(result);

}

And here’s a simple example application that calls the OpenAI API to analyze data. It supplies a prompt with data and the model returns a summary of that data:

---

Disclosure: Some of the links in this post are affiliate links, meaning I’ll get a small commission (at no extra cost to you) if you sign up.