Supercharge Your Google Sheets with AI: GENAI Functions

I'm excited to share two subscriber-exclusive custom functions, GENAI() and GENAI_ARRAY() that allow you to integrate AI capabilities directly into your Google Sheets. Imagine having the power of modern AI models like OpenAI's GPT-4, Anthropic's Claude, and Google's Gemini directly inside your spreadsheets—no switching between applications, no copy-pasting, just seamless AI assistance right where your data lives.

With these custom functions, you can:

• Automatically classify datasets (products, customer feedback, support tickets)

• Generate content at scale (product descriptions, email templates, social media posts)

• Summarize data from complex tables, reports, or survey responses

• Translate text across multiple languages directly in your spreadsheets

• Extract structured information from unstructured text

• Create personalized responses based on user data

Prerequisites

API Keys Setup

Before using these functions, you must set your API keys in the Script Properties with the following keys:

• GOOGLE_API_KEY

• OPENAI_API_KEY

• ANTHROPIC_API_KEY

Please refer to my tutorial on setting up API keys for each AI model provider: https://spreadsheet.dev/api-keys-openai-gemini-anthropic-apps-script

Custom Functions

This tutorial assumes that you are familiar with creating and using custom functions in Google Sheets.

How to Implement the GENAI Functions

Ready to add these powerful AI capabilities to your spreadsheets? I'll walk you through how to to implement them in your Google Sheets spreadsheet:

• Visit our exclusive member post to access the code

• Open your Google Sheet and select Extensions > Apps Script from the menu

• Copy the entire code from the member post

• Paste it into the Apps Script editor, replacing any existing code

• Save the project (Ctrl+S or ⌘+S)

• Set up your API keys as described in this tutorial

• Return to your spreadsheet and start using the GENAI custom functions

GENAI()

The GENAI() custom function enables you to generate AI responses based on a prompt and contextual information that you provide. The function handles both single cells and ranges, treating the entire range as context for a single prompt.

Syntax

=GENAI(prompt, range, modelProvider, model)

Parameters

• prompt (required): The text prompt to send to the AI model.

• range (optional): Range of data to provide as context.

• modelProvider (optional): AI provider to use. Options:

• "google" (default) - uses Google Gemini models

• "openai" - uses OpenAI models

• "anthropic" - uses Anthropic Claude models

• model (optional): Specific model name for the chosen provider. If not specified, defaults to:

• Google: "gemini-2.0-flash"

• OpenAI: "gpt-4o"

• Anthropic: "claude-3-haiku-20240307"

Examples

Example 1: Summarize text

=GENAI("Summarize in 5 bullet points. Return only the text, without markdown.", A2)

This sends the content of cell A2 as context to the default Google Gemini model with the prompt "Summarize in 5 bullet points. Return only the text, without markdown.".

Example 2: Translate content

=GENAI("Translate into Spanish",A2,"anthropic", "claude-3-5-sonnet-20241022")

This translates the content of cell A2 to Spanish using Anthropic's Claude Sonnet 3.5 model.

Example 3: Using with complex data

=GENAI("Extract key sales metrics, up to 10 bullet points, no markdown. Double-check the accuracy of the extracted data.", A1:F20, "google", "gemini-2.5-pro-preview-03-25")

This sends an entire sales data table to Google's Gemini 2.5 pro model to extract key metrics.

GENAI_ARRAY()

The GENAI_ARRAY() function processes each cell in a range individually with AI. Unlike GENAI() which treats the entire range as a single context, GENAI_ARRAY() applies the prompt to each non-empty cell in the range individually, returning a corresponding array of responses. It is designed to be used with ArrayFormula. The function optimizes performance by batching multiple items into a single API call.

Syntax

=GENAI_ARRAY(prompt, range, modelProvider, model)

Parameters

Same parameters as GENAI().

Examples

Example 1: Process a column of data with ArrayFormula

=ArrayFormula(GENAI_ARRAY("Categorize this product", A2:A10))

This categorizes each product name in cells A2:A10, returning the results in a corresponding range.

Example 2: Enhance multiple descriptions

=GENAI_ARRAY("Make this product description more engaging", A2:A22, "openai", "gpt-4o")

This improves multiple product descriptions using GPT-4o, with each cell processed individually.

Example 3: Analyze sentiment across responses

=ArrayFormula(GENAI_ARRAY("Rate the sentiment of this customer feedback from 1-5", SurveyResponses!A2:A15, "anthropic"))

This analyzes the sentiment of customer responses using Claude, returning a numerical rating for each.