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 Artificial Intelligence (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.

AI 101

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:

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:

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”:

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”

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):

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.

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”

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.

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

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:

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")

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

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

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!

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:

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:

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:

❗️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.

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.

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:

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:

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:

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:

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.

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

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

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:

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.