Analyze text using natural language with Claude for Google Sheets

You can do a lot more with generative AI (genAI) in Google Sheets than get help writing spreadsheet formulas. Thanks to extensions such as Claude for Sheets, you can also do things like:

• Determine whether text sentiment is positive, negative, or neutral
• Classify text into categories
• Extract email addresses, phone numbers, and other entities from plain text

And you can do this without having to write computer code in a language like Python.

Instead, you only need spreadsheet formulas as simple as:

=claudeExtract("sentiment of positive, negative, or neutral", A2)

Let’s see how to implement some of these use cases with the help of Claude for Sheets, an add-on for Google Sheets offered by Anthropic, maker of the Claude family of large language models (LLMs).

Just remember:

1. LLMs aren’t always accurate. The more important your task, the more effort you should put into checking Claude’s results.
2. Think twice about sending sensitive data to an LLM. If it’s corporate data, follow company policies.
3. Claude for Sheets costs money to use. It’s a pretty trivial amount for moderate amounts of text, but understand the pricing if you’ve got a massive data set. (Although for a lot of data, performance will be slow and you’re probably better off with another solution.) More on pricing in a bit.

Claude for Sheets setup

You’ll need two things in addition to a Google account in order to run Claude for Sheets: an Anthropic API key and the Claude for Sheets extension.

You can get an Anthropic API key by creating a free account. Once you log in, your dashboard home page should show an option to create API keys.

Click the button to create a key and heed the warning to copy your API key (just a string of characters) when it appears — once you close that dialog box, you won’t be able to access it again on the Anthropic website. Store the key securely, just as you would a password.

You’ll likely need to add some money to your Anthropic account before you can use the Claude API. You shouldn’t need much unless you’re uploading a lot of data. (I added $20 months ago and still have more than half left — and I’ve coded public-facing apps using Claude in addition to playing with Sheets.)

To add Claude for Sheets to your Google account, download it from the Google Workspace Marketplace and install it.

You need to set up Claude for Sheets in each spreadsheet where you want to use it. Create a new Google spreadsheet and go to Extensions > Claude for Sheets > Open Sidebar. Then click on the sidebar’s hamburger menu (three horizonal lines), choose Settings, and click on API provider. You want to choose Anthropic. Enter your API key where it says the key is missing.

Next, scroll down and choose a default model. Anthropic offers several different models under the Claude umbrella, and their capabilities and pricing vary considerably.

Haiku is the smallest, fastest, least expensive, and least capable model offered in Claude for Sheets. I’d start there while experimenting.

As of this writing, Haiku costs 80 cents per million tokens in and $4 per million tokens out. One million tokens is roughly 4 million characters or 750,000 English words. The priciest and most powerful model, Opus, is $15 per million input and $75 per million output. The middle model, Claude 3.5 Sonnet, is $3/M in and $15/M out.

Whatever you choose as the default model for a spreadsheet, you can override it by specifying a different model in the formulas you create, as we’ll see in the examples below.

Case 1: Sentiment analysis

Start by entering the text you want to analyze in your A column. I entered the following five sample “review” text blurbs into my spreadsheet’s A column (ReviewText), one item per row:

ReviewText	Sentiment
The new iPad Mini 7 has a great display, speedy response, and is light enough to hold for long periods of time. It’s a significant upgrade from my old (ancient?) iPad 5. My lone nit is that the form factor is thicker and less sleek now due to the change in connectors, but I’m still glad I upgraded.
This desktop computer has a better processor and can handle much more demanding tasks such as running LLMs locally. However, it’s also noisy and comes with a lot of bloatware.
The charger is affordable and does exactly what I want.
This charger doesn’t work like I expected.
This charger seems like a great value – until you try to use it. The connection is flakey and my device often ends up not fully charged overnight.

In column B (Sentiment) — more specifically, in cell B2 next to my first text item, I added the formula

=claudeExtract("sentiment analysis of 'positive', 'negative', or 'neutral’", A2, "claude-3-5-haiku-latest", "temperature", 0)

And then I clicked and dragged the formula down the rest of the rows next to cells with text.

Here’s an explanation of the formula.

=claudeExtract uses the syntax claudeExtract(property, text, model, argumentValuePairs)

Property is defined in the help file as “The property to extract from the text, e.g. ‘phone’.” For this case, I used a property of “sentiment analysis of ‘positive’, ‘negative’, or ‘neutral’” since that’s what I want Claude to extract from my text.

Text is my source cell location. If my first row of text is in cell A2, text value will be cell A2. Clicking and dragging the formula down the rest of the column applies it appropriately to the other text cells.

Although I chose a default model in Claude for Sheets main settings, I specified a model in the formula too so I could add optional arguments at the end of the formula. Google Sheets formulas don’t like it if you skip expected parameters when adding more at the end. Anthropic’s Model names documentation helpfully lists the specific syntax to use in formulas for each of its available models. In this case I chose claude-3-5-haiku-latest — which specifies the latest available version of the Claude 3.5 Haiku model.

The one optional argument I added was to set the model’s temperature to 0. An LLM’s temperature tells it how much randomness to use. Should it opt for the most likely result again and again (low temperature) or try to mix things up a bit (higher temperature)?

It’s sometimes helpful to view temperature as a measure of model “creativity.” Higher temperature can be nice when chatting with an LLM, so it doesn’t sound robotic and repetitive. However, for technical tasks, “more likely to be accurate” is a better idea than “be creative,” and that means lower temperatures. Anthropic advises: “For multiple-choice or analytical tasks, you’ll want [temperature] close to 0.”

Note: This ability to set model parameters like temperature is one advantage of using the LLM’s API via a spreadsheet or computer programming instead of a free chatbot without granular temperature control.

Results

Below is Claude’s sentiment analysis on my sample text. I agreed with all of them:

Haiku: Sentiment analysis

ReviewText	Sentiment
The new iPad Mini 7 has a great display, speedy response, and is light enough to hold for long periods of time. It’s a significant upgrade from my old (ancient?) iPad 5. My lone nit is that the form factor is thicker and less sleek now due to the change in connectors, but I’m still glad I upgraded.	positive
This desktop computer has a better processor and can handle much more demanding tasks such as running LLMs locally. However, it’s also noisy and comes with a lot of bloatware.	neutral
The charger is affordable and does exactly what I want.	positive
This charger doesn’t work like I expected.	negative
This charger seems like a great value – until you try to use it. The connection is flakey and my device often ends up not fully charged overnight.	negative

Two important tips:

If you see errors or delays in some cells, you can ask Claude for Sheets to recalculate those by opening the Claude extension’s dropdown menu (Extensions > Claude for Sheets) and clicking on Recalculate all “DEFERRED / THROTTLED / #ERROR! Cells.

To keep results from updating — which might change them and costs more API money — copy and paste the column values only (excluding the formulas) to a new column and then delete the original column with formulas. Anthropic says Claude for Sheets results are cached for a week, so opening the spreadsheet again after a week may cause everything to recalculate.

Case 2: Text classification into categories

For this test, I took a few social media posts about the R programming language (often used for data analysis), genAI tools, and other topics — some modified to try to make the topics slightly less obvious — and asked Claude to categorize them as Data, Generative AI, Security & Privacy, or Other.

I used the same workflow as above, just changing the formula’s property text using natural language instructions:

=claudeExtract("one or more categories among 'Data', 'Generative AI', 'Security & Privacy'. If more than one apply, include all. If none apply, say 'Other'.", A2, "claude-3-5-haiku-latest", "temperature", 0)

Results weren’t bad but were imperfect. The Haiku model didn’t apply both Security & Privacy and Generative AI to the text about LLM vulnerabilities, and it didn’t know that R involves data.

Haiku: Text categorization

Kyle has some great resources for using the GIS data sets he mentions, especially “Analyzing US Census Data: Methods, Maps, and Models in R” and the tidycensus package	data
OpenAI today announced new model 4o — basically 4o preview out of preview — it says is better and faster than the preview version. And multimodal. Also a new “pro” tier for ChatGPT and its 4o model, at an eye-popping $200/month.	generative ai
An Introduction to R is a free online book by several professors (Univ of Aberdeen, Danish Technical Univ) and R practitioners that aims to, well, introduce you to using R. If you teach R, feel free to use some or all of the content in this book [with attribution]”. https://intro2r.com	other
Prompt injection and supply chain vulnerabilities remain the main LLM vulnerabilities but as the technology evolves new risks come to light including system prompt leakage and misinformation. https://www.csoonline.com/article/575497/owasp-lists-10-most-critical-large-language-model-vulnerabilities.html	security & privacy
This story “tracked thousands of US military & intel personnel coming & going from classified sites, incl. NSA hubs & nuclear vaults. We know where they sleep, what they eat, and which brothels they visit. It’s an ocean of blackmail & national secrets within reach of every spy agency in the world.”	data,security & privacy
“Logging on to Bluesky for the first time felt a bit like walking into your apartment after a professional deep clean. The layout is the same, as are the appliances and furniture. But it just felt good to be there. I wanted to hang out.”	other

When I tried with the Sonnet model (by swapping in claude-3-5-sonnet-latest as the model name), it did apply both categories to the LLM security text but also couldn’t recognize the R blurb as being data-related. The Opus model (claude-3-opus-latest) didn’t know R is data-related either.

My conclusion on categorizing text after this and other experiments: Either the text needs to be fairly straightforward about topics, or the prompt should include specific instructions about phrases likely to show up in your data that the LLM doesn’t know how to classify. In other words, experiment a bit and add examples or more detailed explanations when needed. When I added “Text about the R programming language is topic Data.” to the end of my prompt’s property text, Haiku applied “data” to my R-related posts.

Case 3: Entity extraction

I used some sample text to test extracting email addresses and phone numbers. The text was originally generated by a chatbot (Perplexity), and I modified it a bit. I then added that text to a new tab on the spreadsheet, and inserted the following formulas in columns B and C:

=claudeExtract("all phone numbers", A4, "claude-3-5-haiku-latest", "temperature", 0)

=claudeExtract(“all email addresses", A4, "claude-3-5-haiku-latest", "temperature", 0)

Haiku performed quite well on this one:

Haiku: Phone number and email address extraction

Text	Phone	Email
The team is at marketing@company.com and (914) 737-9938. Headquarters is @ 512 Main St.	914-737-9938	marketing@company.com
John Smith from accounting (j.smith@business.net) will be out of office until Friday. For urgent matters, reach him at 888-555-9012.	888-555-9012	j.smith@business.net
Contact support: help_desk@tech.org or 1-800-555-3456 Technical issues? sarah.tech@company.com For appointments: +1 (444) 867-5309	1-800-555-3456, +1 (444) 867-5309	help_desk@tech.org, sarah.tech@company.com
Meeting notes from yesterday: – Dave (d.wilson@dept.com) will handle the project – Marketing team contact: marketing@company.com / 555.777.8888 – Emergency line: 911-555-0123	555.777.8888, 911-555-0123	d.wilson@dept.com, marketing@company.com
Please forward any invoices to billing@finance.com or fax to (777) 555-0147	777-555-0147	billing@finance.com

Trying to extract company names was tougher, since it’s a lot easier to spot phone number and email address patterns than to know the difference between a company name and a product name.

I tested company and product extraction with some recent headlines and summaries from Computerworld’s home page, asking simply for “companies” and “products” in my formulas. Haiku’s results:

Haiku: Company and product name extraction

Text	Companies	Products
10 steps to smarter Google account security Give yourself some added peace of mind by giving your Google account a thorough set of security reinforcements, both on Android and your desktop.	Google	Google account, Android, desktop
How ChatGPT works with iOS 18.2 iPhones and Siri Apple Intelligence is about to receive its first important update — introducing ChatGPT access.	ChatGPT, iOS, Apple, Siri	ChatGPT, iOS 18.2 iPhones, Siri, Apple Intelligence
OECD: GenAI is affecting jobs previously thought safe from automation Though the technology will likely lead to new jobs, they may not benefit those who lost work due to automation.	OECD	GenAI
Microsoft moves to stop M365 Copilot from ‘oversharing’ data The generative AI assistant can surface sensitive information in over-permissioned files, a growing concern for businesses testing the technology. Microsoft is adding new features to SharePoint and Purview to make it easier to control what the tool can access.	Microsoft	Microsoft 365 Copilot, SharePoint, Purview

It was a bit disappointing that Haiku tagged ChatGPT, iOS, and Siri as companies. Sonnet did better on that one overall but worse on the last listing, adding two products as companies along with Microsoft:

Sonnet: Company and product name extraction

Text	Companies	Products
10 steps to smarter Google account security Give yourself some added peace of mind by giving your Google account a thorough set of security reinforcements, both on Android and your desktop.	Google	Google account
How ChatGPT works with iOS 18.2 iPhones and Siri Apple Intelligence is about to receive its first important update — introducing ChatGPT access.	Apple	iOS 18.2, iPhones, Siri, ChatGPT
OECD: GenAI is affecting jobs previously thought safe from automation Though the technology will likely lead to new jobs, they may not benefit those who lost work due to automation.	OECD	GenAI
Microsoft moves to stop M365 Copilot from ‘oversharing’ data The generative AI assistant can surface sensitive information in over-permissioned files, a growing concern for businesses testing the technology. Microsoft is adding new features to SharePoint and Purview to make it easier to control what the tool can access.	Microsoft, SharePoint, Purview	M365 Copilot, SharePoint, Purview

When I tried the pricey Opus model, it got rows 2-4 correct but added Android as a company on the first row. My conclusion: I either need to give these models better prompts and more examples for a task like this or tolerate some inaccuracies.

However, models continue to improve, and a task that’s beyond their capabilities now may work better a few months down the road.

For the sake of comparison, I gave all this text to OpenAI’s new o1 model in the ChatGPT Plus chatbot and asked it to extract products. Those results were better: o1 correctly identified Google account, Android, ChatGPT, iOS 18.2, iPhones, Siri, M365 Copilot, SharePoint, and Purview as products.

And it was the only model to extract just Google, Apple, and Microsoft as “companies” — OECD, the Organisation for Economic Co-operation and Development, is not technically a company. When I asked o1 to extract both companies and organizations, it responded: Google (company), Apple (company), OECD (organization), and Microsoft (company).

(OpenAI doesn’t have its own extension for Google Sheets, although there are some paid third-party applications.)

Meanwhile, though, if you can tolerate some imperfect results and less than top-speed performance, you can start doing LLM-based natural language processing right within a spreadsheet. As Ethan Mollick, author of Co-Intelligence: Living and Working with AI, advised on Bluesky recently:

“I think firms worrying about AI hallucination should consider some questions:

1) How vital is 100% accuracy on a task?

2) How accurate is AI?

3) How accurate is the human who would do it?

4) How do you know 2 & 3?

5) How do you deal with the fact that humans are not 100%?

Not all tasks are the same.”