Using AI in Google Sheets can save users time and give them insights they may not otherwise have discovered.
In a January 2024 Fast Company article, Kristina Behr, VP of Product Management For Collaboration Apps, Google Workspace, pointed out that a spreadsheet novice can tap into AI to become a power user.
There are generally three ways to use generative AI in Google Sheets. These are not mutually exclusive.
- App Scripts manually added to individual Sheets
- Third-party AI add-ons from Google Workspace Marketplace
- Native AI functionality in Google Sheets using Duet AI
Duet AI for Google Workspace Enterprise is a paid add-on from Google for organizations using Google Workspace.
Duet AI functionality is available to consumer Gmail users via Google Labs.
It won’t be long until Google Sheets users can routinely make natural language requests natively in a spreadsheet rather than wrestling with formula syntax and chart creation.
In this post, we’ll look at some of the practical use cases for AI in Sheets.
Organize and Track Data
The initial release of Google’s Duet AI lets a user write a prompt requesting that Sheets generate a table for tracking any information.
A message reads, “Soon, Help me organize will be able to accurately edit and analyze content in your spreadsheet.”
In the meantime, several third-party apps will serve this function.
Standard AI Prompts
Many of the prompts you’re accustomed to entering directly into AI apps like ChatGPT or Google’s Gemini can be run inside a Sheet.
The benefit of running these prompts in Google Sheets is that you can reference cells and automatically populate adjacent cells with the AI replies to save them.
An example of a formula based on an Apps Script that connects to OpenAI is:
=AI_GPT4(“Create an outline for an article on the topic of “&A2&”. The article should target the persona “&B2&”.”)
Cell A2 contains the topic in the following example, and cell B2 contains the target persona.
Prompt for Chart Creation
AI can speed up the chart creation process. Here are the steps we took
- Install a Google Workspace Marketplace AI add-on called Sheet Copilot
- Open the Sheet Copilot side panel
- Enter an OpenAI API key
- Create a Google Sheet from Google Search Console data
- Have an exchange like the following in the side panel
Initial Prompt: Please create a pie chart of the top 10 queries in terms of impressions
Sheet Copilot: To create a pie chart of the top 10 queries by impressions, we’ll extract the relevant data from the ‘Queries’ sheet, sort it to find the top 10 queries and then create the pie chart. We’ll review the chart for accuracy and make adjustments if needed. Does this plan work for you? If so, I’ll start with step 1.
Reply: This plan works.
This was the resulting chart.
Analyze Sales Figures
We used AI to create a fictional Sheet showing 2023 sparking water case sales by Region, Month, and Flavor.
We then had the following exchange in the add-on’s side panel.
Initial Prompt: What is the average Orange Flavor sales for April for all regions?
Sheet Copilot: To find the average Orange Flavor sales for April across all regions, we’ll first filter the data to include only April entries. Then, we’ll extract the Orange Flavor sales for April and calculate their average. I’ll use a formula to achieve this. If this plan works for you, I’ll proceed with executing the formula on your ‘2023 Sales’ sheet.
Reply: This plan works.
Sheet Copilot: The average Orange Flavor sales for April across all regions is approximately 764,187 units.
In a more complex example, we used a high-level prompt, simply asking the AI to ‘analyze the data’ we had imported into the sheet from the California Department of Water Resources for Folsom Lake water levels from 2020-2023.
The AI came back with various helpful figures and visualizations, including this chart that shows the number of days each year that recreational boats could travel at over 5 MPH on the lake.
Analyze Survey Results
AI can not only analyze survey and poll results, but it can create stats.
With AI, your prompt can be as simple as the “analyze the data” prompt referenced above — no prompt engineering is required.
Generate Formulas
Using Google Sheets formulas can be tedious and time-consuming.
Users must find the appropriate Google Sheets formulas for their task, read the formula documentation, and go through trial and error before getting the result.
Some AIs can automatically generate the right formula if you explain what you are trying to accomplish in plain language.
Populate the Website Traffic column in Sheet 1 by doing a VLOOKUP on Sheet 2 based on a URL column in Sheet 2 with matching URLs.
Autocomplete
Google Sheets has a built-in autocomplete feature when you add a formula to the top of a new column.
Teach AI to create, transform, or extract text by showing a few examples and letting it autocomplete the rest.
Typical repetitive workflow automation starts with AI Prompts, switches to Autocomplete once you have at least 3-5 examples, and results in a custom AI model.
Create Custom AI Models
A third-party app lets users train custom, task-specific AI models on spreadsheet data. The model can be used with Autocomplete to automate repetitive work in bulk.
Custom AI models are significantly better, faster, and cheaper at executing the tasks they’re trained on, even compared to the best foundational LLM models.
Forecasting and Predictions
AI models can use historical data within Sheets to forecast future trends, demand, sales, and other critical business metrics. This can help with planning and decision-making processes.
Initial Prompt: Based on 2023 trends, please predict 2024 sales based on expected 10% growth in sparkling water sales across brands
Sheet Copilot: To predict 2024 sparkling water sales with a 10% growth, we’ll extract 2023 sales data, calculate 2024 predictions with a 10% increase, and store this in a new sheet. We’ll format the new sheet for clarity and can create a visualization to compare 2023 sales with 2024 predictions. Do you approve of this plan or want to make any adjustments before proceeding?
Reply: I approve of this plan.
This was the resulting chart. The numbers on the x-axis were not to scale. The flavor bars were not adjacent.
Because there are some general limitations with spreadsheet chart rendering, it’s possible to get more impactful visualizations by exporting a CSV file, uploading it to an LLM, and requesting a chart.
Prompt: Please use the data in the CSV file to create a bar chart visualization of unit sales by region and flavor. The bars representing each region’s flavors should be rendered in the color of the respective fruit. The Unit Sales axis should be in 1000s.
Here is the resulting chart.
Another CSV export file use case
A short-term alternative to installing Google Sheets add-ons or waiting for improved native AI functionality is to export data from one or more sheets to CSV files and then upload them to an LLM such as ChatGPT with an accompanying prompt.
The following prompt worked exactly as expected. We imported the resulting CSV file back into the same Google Sheet from which we had downloaded two CSV files.
Add-on App Caveat
For analysis, third-party AI add-ons for Google Sheets like Sheet Copilot extract data from Google Sheets into an LLM like ChatGPT.
Users should be aware their data is being taken off Google’s platform and consider the security implications for sensitive data.
Once Google offers spreadsheet editing analysis natively within the Google platform and offers equivalent functionality to add-ons, it may be difficult for third-party apps to compete.