What AI Can Do For You As A Google Sheets User. Is The Hype Justified?

In this post, we’re going to take a 40,000 ft view of the AI landscape and see how you can use it alongside Google Sheets to boost your productivity.

And, at the end of the post, I’ll answer the question about whether I think the AI hype is justified.

Before looking at what AI can do with Google Sheets though, let’s start with a quick AI 101 to ensure we’re up to speed with the lingo.

Green Tea Image generated by Dall-E
Image generated by Dall-E, a deep learning model, using the prompt “a cup of tea with a green potion inside with thin swirling vapors rising out of it”

AI 101

Artificial Intelligence, often abbreviated as AI, is an extraordinary and rapidly evolving field of computer science that aims to create intelligent machines capable of simulating human-like cognitive abilities.

At its core, AI mimics human intelligence by using algorithms and models to process vast amounts of data, identify patterns, and make decisions or predictions based on the information acquired.

Machine learning is a core technology in AI that allows systems to learn from data without explicit programming.

Deep learning is a specific subfield of machine learning that uses artificial neural networks with multiple layers to model and process complex patterns in data.

A neural network is a computational model designed to process and learn from data by passing information through interconnected building blocks, similar to how our brain cells work together.

Natural Language Processing (NLP) is a specialized field within AI that focuses on enabling machines to understand, interpret, and generate human language in a way that is both meaningful and contextually relevant.

A Large Language Model (LLM) is a type of artificial intelligence model that is designed to understand, generate, and manipulate human language. It is characterized by its massive size and the ability to process and analyze vast amounts of text data. LLMs are based on deep learning techniques, particularly neural networks with multiple layers. ChatGPT and Google Bard are examples of LLMs.

(Thanks to ChatGPT and Google Bard for helping write this introduction section.)

The AI + Sheets Landscape

Over the past year, a vast number of AI tools have been launched (it’s a hot industry 🔥). It’s overwhelming!

This list is a good place to start. These are the AI tools that I’ve been using in conjunction with Sheets.

AI Models

ChatGPT
Google Bard

There are many other models (e.g. LLaMA from Facebook) but ChatGPT and Bard are the most widely used.

So far, I’ve had better results with ChatGPT than Google Bard and so the majority of the examples below are shown using ChatGPT.

AI-powered Add-Ons

There are lots of third-party add-ons that bring the functionality of AI into your Sheets. Primarily, they use the GPT models from OpenAI. Search for “GPT” or “AI” in the Marketplace.

Recently, I’ve been using the Coefficient add-on, which has a GPT-copilot feature, and you’ll see this tool in some of the workflow examples below.

Existing AI feature in Sheets

Of course, don’t forget that Google has a basic AI-powered feature built into Sheets called Explore.

You can find it in the bottom right corner of your Sheets. Use it to suggest pivot tables and charts based on your data.

How You Can Use AI In Sheets

Here are some ways to use AI tools with Google Sheets to maximize your productivity:

  1. Explain Formulas
  2. Troubleshoot Formulas
  3. Create Formulas
  4. Create Pivot Tables
  5. Create Charts
  6. Clean Data
  7. Retrieve Data
  8. Complete Missing Data
  9. Generate Fake Test Data
  10. Analyze Data
  11. Extract Data
  12. Explain Code
  13. Lookup Code
  14. Write Code
  15. Generate Regular Expressions
  16. Convert Datatypes

Let’s see these in more detail.

1. Explain Formulas

Spreadsheet users are notoriously bad at documenting how their formulas work. Once you combine a few functions together with lots of range references, it becomes very difficult to read.

There is no good way to add comments to formulas (only a hacky way!).

So one of the only ways to understand complex formulas is to peel them apart using the Onion Framework, but this is time consuming.

But this is changing.

Now, we can use AI tools like ChatGPT or Bard to explain formulas to us. They do a great job, even with complex formulas involving new functions.

Consider this example, which uses the modern LAMBDA style formulas. ChatGPT provides a thorough explanation:

ChatGPT explaining a complex formula in Google Sheets

We can take this a step further and build a formula explainer directly into our Sheet, using Apps Script to connect to the OpenAI GPT API and ask it to explain our formula:

Look out for a tutorial about this project in the future.

Back to contents

2. Troubleshoot Formulas With AI

ChatGPT and Bard can help troubleshooting broken formulas.

Consider this incorrect formula (can you spot the problem?):

=VLOOKUP(A2,E1:F11,2,,false)

Let’s supply the broken formula in our prompt to ChatGPT and ask for a fix:

Troubleshoot Formulas with AI

ChatGPT correctly identified the extra comma causing the error and suggested the correct formula for us. 🎉

=VLOOKUP(A2,E1:F11,2,false)

I haven’t done a lot of testing with this to see how well it does with less obvious errors, but if you’re stuck with a formula, it’s another avenue to explore. If nothing else, it might give you a clue that will let you fix your formula.

Back to contents

3. Create Formulas

The AI tools can create formulas for you too.

ChatGPT and Bard generally give correct answers for simple formulas but it’s hit-and-miss with more complex formula asks.

Again, it pays to be as specific as possible with your prompt.

For example, compare the response generated by this vague prompt “Create a formula that finds the average value of my range”:

Vague ChatGPT Prompt

Now take a look at the response we get from this specific prompt “Create a Google Sheets formula to calculate the average value of data in the range D5:D20

Create Google Sheets Formulas with AI

Now, if you’re reading this site you’re probably comfortable with more advanced formulas than this example above.

So how do the AI models handle complex formulas?

So-so at the moment (July 2023).

Sometimes they work but just as often they give you incorrect responses. You’ll need to test thoroughly and often modify the results.

Think of these AI tools as assistants that give you ideas for your complex formulas. You still need to know what you’re doing.

Here’s an example of ChatGPT getting a complex formula correct (even though the purist in me prefers to see the ArrayFormula on the outside):

Create Formulas with ChatGPT

Notice the specificity in the prompt, which includes example inputs and outputs.

Also notice the “Copy code” button, which lets you easily copy and paste the formula into your Google Sheet.

Back to contents

4. Create Pivot Tables

Google Sheets has had AI-assisted pivot tables for years… they’re hidden in the Explore tab.

Explore in Google Sheets

It’s a great way to get started if you’re not familiar with pivot tables. Go grab a pre-built pivot table and see what insights they give you.

Treat this as a jumping off point to understand pivot tables so you know what they can and can’t do for you.

But this is only the start…

There are Google Sheet add-ons that take the AI-assisted pivot tables a step further.

Here, I use the GPT Copilot feature in the Coefficient Add-on to build a custom pivot table, based on my input prompt “what are the total sales by property type

Coefficient Pivot Table

Back to contents

5. Create Charts

In a similar fashion to the pivot example above, we can use AI to generate charts for us.

Use the Explore tab (button in bottom right of Sheet) to grab some pre-built examples in any Sheet.

Create Charts with Explore in Google Sheets

Or use a tool like Coefficient’s Add-On to build custom charts for you, based on your input prompt:

Create Charts with Coefficient

Back to contents

6. Clean Data

AI tools can assist with data cleaning tasks.

You can copy-paste the data directly into one of the chat tools and ask it to clean the data for you, but that is rather inconvenient.

So I suggest you use one of the ChatGPT powered Google Sheets Add-Ons with AI powered formulas.

For example, we can use the GPTX functions within the Coefficient add-on to fix these issues.

Suppose we have addresses in our data that contain “USA”, “US”, “U.S.A.”, “United States” etc.

First, we extract the country name with the GPTX_EXTRACT function in column B:

=GPTX_EXTRACT(A4:A13,"country name")

It automatically handles the array input and output for us, which is a bonus:

GPTX_EXTRACT function

Then we standardize the country name with the GPTX_FORMAT function in column C (using an alpha-3 code):

=GPTX_FORMAT(B4:B13,"alpha-3")

GPTX_FORMAT function

Finally, we use the built-in SUBSTITUTE function to combine these results into a new address column:

=SUBSTITUTE(A4, B4, "")&C4

SUBSTITUTE Function in Google Sheets

It was quicker to use these GPTX functions to standardize the data than to create rule-based formulas with REGEX functions or other text functions (e.g. FIND, LEFT, MID, etc.).

Back to contents

7. Retrieve Data

Occasionally, you might want datasets to supplement your proprietary data. Or maybe a table to use in a demo.

ChatGPT or Bard can generate data tables for you. Simply type in what you want!

Generate Data with AI

It’s important to check the data carefully before you blindly use it in a professional setting.

We can also use GPT-powered add-ons to generate data for us.

Or, if we’re feeling brave, we could write a script to connect to GPT via the API. Then we ask for data directly from our Sheet, as shown in this example:

Generate Data in Sheets with AI

Look out for a tutorial about this project in the future.

Back to contents

8. Complete Missing Data

AI can find missing data for you, which is a nice time saver.

In this example, we want to categorize companies by their industry but we don’t have industry data.

No problem!

A ChatGPT-powered custom function (from the Coefficient Add-On) fills in the data for me:

Complete missing data with AI

Magic! 🪄

Back to contents

9. Generate Fake Test Data

In sections 7 and 8 above, we saw how AI can retrieve real datasets for you.

But it can also generate fake data that can used for testing purposes or giving a demo, for example.

Again, just ask one of the tools to generate it for you. Here’s an example of ChatGPT creating a table of fictitious companies for us:

Generate fake data with ChatGPT

❗️Note: Watch out for the AI guardrails. These are designed to protect us from fraudulent activity and things like asking for fake addresses can fall foul of that.

AI ethics guardrails

Back to contents

10. Analyze Data

From my own experience, analyzing datasets is still hit-or-miss. The tools sometimes give useful summaries but often the responses contain serious errors! Proceed with caution and check anything you plan to use.

In this example, I wrote an Apps Script that called the OpenAI GPT API with my data and a prompt, and it returned a summary.

Analyze data with GPT API

Look out for a tutorial about this project in the future.

Back to contents

11. Extract Data

The built-in function REGEXEXTRACT works incredibly well, but it has one principal drawback.

You need to understand regular expressions, which are hard!

On the contrary, GPT formulas only require you to know what you want to extract.😎

See this example, which uses Coefficient’s GPTX_EXTRACT function from their add-on:

Extract Data with GPT

Easy peasy!

Back to contents


In addition to helping with standard spreadsheet tasks, like formulas, pivot tables, and data analysis, these AI tools are hugely useful if you’re coding with Google Sheets.

In fact, it’s perhaps when they’re most useful.

Let’s see how:

12. Explain Code

The tools do a good job of explaining snippets of code.

It’s super helpful for both beginners learning to code or experienced coders wanting to quickly understand a snippet of code they’ve grabbed from documentation or Stack Overflow.

For example, here’s ChatGPT explaining what this specific map function does:

ChatGPT to explain code

Back to contents

13. Lookup Code

The other day I wanted to know the code for the toast method in Apps Script.

Ordinarily I would have googled “apps script to show toast”

Instead, I fed this prompt to ChatGPT and it gave me a code snippet instantly, which I could modify for my own needs:

Find code snippet with AI tool

On this occasion, it was quicker than digging through google results or the documentation.

Back to contents

14. Write Code

ChatGPT and Bard can generate useful code snippets.

For specific tasks, it’s like having a programming assistant on your shoulder who can quickly generate code snippets.

In this example, we use ChatGPT to write code that uses the Browser Speech API to read a string out loud in the browser:

Write code with AI

However, it’s still important to understand and check the code that an AI tool writes for you. You need to test it thoroughly enough to know it’s working as you want.

Use it as a tool to speed up your coding workflow rather than expecting it to build your apps from top-to-bottom.

Back to contents

15. Generate Regular Expressions

Regular Expressions, or REGEX for short, is the syntax used inside the Google Sheet REGEX formulas. They let you extract data from text strings, do complex substitutions, and even data validation. They’re hugely helpful when working with text data

However, REGEX is difficult to use. It takes a long time to understand and write REGEX.

It’s a great task for ChatGPT or Bard to help out with.

For best results, describe the operation as specifically as possible in your prompt and include an example input and output.

Write REGEX with ChatGPT

Then, copy and paste the REGEX string into your Google Sheets REGEXEXTRACT function:

REGEX formula in Google Sheets

Even better, ask ChatGPT to generate the formula in one go!

Generate REGEX functions for Google Sheets

Back to contents

16. Convert Datatypes

Sometimes you want to convert one datatype to another. ChatGPT and Bard can do this for you.

You can help the AI tools by giving context in your prompt and set an expectation of what you want.

See this example:

Convert Data Types

Back to contents

Conclusion

As you’ve seen, there are many ways to integrate the current AI tools into your Google Sheets workflows. Some of them work great and will save you time (e.g. researching code, extracting data) whilst others are still not robust enough to rely on (e.g. writing complex formulas or analyzing data).

However, I would encourage you to explore these AI tools and try some of the examples above for yourself. I’m sure you’ll find some beneficial use cases.

This technology is evolving rapidly, so I expect the tools to keep improving in the short term and get better at more complex work.

In the medium-term, I want to see better integration with Sheets. Right now, we use the chat based apps outside of Sheets, so we still have to copy-paste data back and forth, which is annoying and prone to error. The third-party add-ons help reduce some of that but I’d love to see a built-in way to leverage AI technology (presumably Google Bard) inside Sheets.

And finally, I promised you an answer to this question…

Is The AI Hype Justified?

Yes, but.

As a technology optimist, I’m sometimes guilty of getting caught up in the exciting possibilities of new technology. But I’m old enough, and hopefully wise enough now, to know that things rarely pan out as the experts forecast. We still don’t have self-driving cars or a 3-D printer in every home.

That being said, I do think this is different. I do believe this is a generational technological step forward, that will change how we work.

The first software engineers wrote programs with 0’s and 1’s on punch cards. Then came along basic languages that facilitated more complex programs. And after that, high-level languages that are much more user-friendly. They free programmers from worrying about background low-level tasks like memory allocation or garbage collection.

We’re about to jump to the next level, where we build things (whether that’s software or spreadsheet models) using natural language, as you’ve seen in the basic examples above (“create a Google Sheets formula to reverse a string“).

Of course, I haven’t touched on other use cases for AI in this post. Writing formulas and code is only one (small) part of our jobs. And AI can help improve the efficiency of many other tasks like summarizing meeting notes, writing emails or white papers, writing documentation, etc. So it’s likely to affect us all, in some way or another.

Whether you’re convinced by this potential or not, it’s an exciting technology that is important to understand and explore.

If you have other ideas or examples of AI workflows, please share them in the comments below. I’d love to see how you’re using AI inside of Sheets.

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.

15 thoughts on “What AI Can Do For You As A Google Sheets User. Is The Hype Justified?”

  1. Excellent overview of the help we could get from AI in our day-to-day tasks! Is Coefficient the only ChatGPT integration you’re currently using or are there other addons you’d recommend?

  2. Brilliantly useful post, Ben. There are things here that will change the way I work with Sheets – for example creating pivots and formulae that would have been beyond me using just Sheets input. Using them already!

    1. Thanks, Andrew! That’s great to hear. Let me know if you find any good AI + Sheets workflows that I haven’t covered here.

  3. Ben, this is great stuff. Your most in-depth article since I have started following. Using ChatGPT to help me navigate and understand tricky formulas, or even better- making my formulas more simple and manageable has done wonders for my work. Thank you for all that you do!

  4. Thanks for a most useful analysis, Ben! I have found ChatGPT to be invaluable as a tutor when using Apps Script and to be aware of these Sheets extensions that make AI available within the sheet is great!

  5. I am looking to use chatGPT in this way – let’s say I have two columns – 1 with users and the other with phone numbers. Is there a way to ask chatGPT – What is the phone number of this person? Not as a regex but almost as a search?

    1. Hey Adam! Sure, you can give it a try. This is what happened when I tried, using your prompt but with minor modifications to give context:

      ChatGPT solution

  6. Great article with practical tips. Have you come across any AI tools that allow you to convert a Google sheet to another coding language (python, java, etc.)? For example, say you created a quick proof of concept for an application in Google sheets and then you want to build the same app in python without rewriting the code by hand.

Leave a Reply

Your email address will not be published. Required fields are marked *