AI

How to Add ChatGPT to Google Sheets

by Ben Hicks Updated:
abstract image for G Sheets ChatGPT integration

Connect ChatGPT API to Sheets vs Apps Script

Adding ChatGPT to Google Sheets combines two powerful tools with nearly limitless uses.  In this guide, I’ll show you how to integrate OpenAI’s GPT model into your spreadsheets, allowing you to generate text, analyze data, and process information right from your cells. 

Because you’re working in the structure of a spreadsheet, you can easily chain prompts together to create generative AI workflows, which can be an incredibly powerful productivity tool. 

Before we begin, you’ll need:

  • An OpenAI API key (get one from platform.openai.com)
  • Billing set up to pay for tokens
  • Basic familiarity with Google Sheets and Apps Scripts

Setting Up the Script

  1. Open a new Google Sheet
  2. Click on “Extensions” in the top menu
  3. Select “Apps Script”
  4. Delete any code in the default Code.gs file
  5. Download the script below and copy/paste into the App Script editor 
  6. Replace your-api-key-here and your-org-id-here with your actual OpenAI API key and Org ID
  7. Save the Apps Script

Click here to download the JS file >

Testing API requests to OpenAI

he testCallOpenAI function from the Apps Script

If you’re having issues or want to monitor the script’s execution:

  1. Use the built-in test function by running testCallOpenAI from the Apps Script editor
  2. This will process the prompt in cell A1 and show:
    • The response in cell B1
    • Detailed status updates in cell C1
    • Execution logs in the Apps Script editor

How to use your new PROMPT function in Sheets

Once you’ve set up the script, there are two ways to use it:

Using the PROMPT Function

  1. In any cell, type =PROMPT(“your prompt here”)
  2. The script will send your prompt to ChatGPT and return the response in that cell
  3. You can reference other cells in your prompt: =PROMPT(“Summarize the data in A1”)
  4. You can reference ranges (A1:A5) and other sheets as well (Sheet1!A1)
Example: =PROMPT("Calculate the average of the numbers in A1:A5")

Voila!! Is calculating an average something you would actually want to do using AI? Absolutely not! This is just an example of how you can include a range of cells in your prompt.

a screenshot of g sheets with chatgpt calculating average from a1:a5

*Very Important Note: The referenced cells or ranges must have the column letter capitalized, e.g. A1 works, a1 will not. 

Basic Use Cases and Prompts

How you use ChatGPT in your Sheet is limited only by your imagination. And your token budget! Later in the article we’ll explore a more complicated use-case: chaining prompts together into a workflow. Before we get there, below are some basic examples of how to use the script. 

  1. Translation
=PROMPT("Translate the text in A1 to French")
  1. Data Analysis
=PROMPT("Analyze the trends in the data from A1:A10 and provide insights")
  1. Content Generation
=PROMPT("Write a product description using the features listed in B1:B5")
  1. Keyword Categorization
=PROMPT("Categorize the keyword in A1 into one of these categories: [list, of, categories]")

Register to get updates from our blog sent to your inbox:

  • This field is for validation purposes and should be left unchanged.

Real-World Example: Keyword List to Ad Campaign

Let’s walk through a practical example of using chained prompts to go from keyword research to a complete SEO landing page strategy. In this scenario we have about 140 keywords related to Meta ads that are completely ungrouped. We want to end up with a page outline and ad copy. 

Scenario: Create an optimized landing page outline and search ad copy for Facebook Ads services from an initial list of ungrouped keywords

This workflow analyzes a large-ish  set of keywords (140 rows) and creates a complete content.

screenshot of G Sheets with data in column A and prompt chains in column B

Step 1: Cluster the keywords into Topics

=PROMPT("As a digital marketing strategist, analyze these keywords from A1:A140 and group them into strategic topic clusters for a comprehensive Facebook Ads marketing campaign. For each cluster: 1) Name the cluster, 2) List the keywords, 3) Identify the primary user intent, 4) Suggest the buying stage.")

Result: Creates 10 strategic clusters including “Facebook Ads Overview,” “Performance & Optimization,” and “Technical Setup & Management,” each with clear user intent.

Step 2: Landing Page Structure

=PROMPT("Based on the most commercial-intent clusters from " & B1 & ", design a focused landing page outline for a digital marketing agency. Keep it concise with just 3-4 main sections. Include: 1) SEO title & meta desc (focus on conversion), 2) H1 plus 3-4 H2s max, 3) Key content points under each H2.")

Result: Generates a structured landing page outline that aligns with the identified user intents and buying stages.

Step 3: Google Ad Copy Creation

=PROMPT("Using our keyword clusters from " & B1 & " and the page structure from " & B2 & ", create Google Search Ad variations. For each main topic create: 1) 3 headlines (30 chars each), 2) Two descriptions (90 chars each), 3) ad extensions. Focus on high CTR and ad rank.")

Result: Produces targeted ad copy that speaks directly to the identified user needs and intents.

You can view the results of this exercise in our Google Sheet > 

*Limitations: The Apps Script has a 5000 character limit on prompts. When chaining them together, that limit is for ALL prompts, not each individually.

Tips, Troubleshooting, and More Information About the Script

Best Practices

  1. API Usage: Be mindful of your API usage. Each prompt counts towards your OpenAI API quota.
  2. Prompt Design: Make your prompts clear and specific. For example:
    • Good: “Translate the text in A1 to Spanish”
    • Better: “Translate the text in A1 to Spanish, maintaining formal tone”
  3. Cell References: When referencing cells, make sure they contain the data you expect.
  4. Performance: For large sheets, consider using batch operations rather than individual cell prompts.
  5. Security: Remember, don’t share your API key and be aware that it will be visible to people with access to the Apps Script. 

Troubleshooting

Common issues we’ve run across are:

  1. #ERROR! in cells
    • Check if your API key is correct
    • Verify cell references exist
    • Look for syntax errors in your prompt
  2. Slow Response Times
    • The script includes automatic retry logic
    • Consider reducing the complexity of your prompts
  3. Formula Parse Errors
    • Make sure to use quotes around your prompt text
    • Use & to concatenate cell references

Tips for Working with Large Datasets

When working with larger datasets (100+ rows), consider these strategies:

  1. Batch Processing
    • Split ranges into smaller chunks (e.g., A1:A50, A51:A100)
    • Focus on identifying top clusters rather than analyzing every keyword
    • Use sampling in your prompts to identify patterns
  1. Modify the Script Configuration to Extend These Limits
const CONFIG = {
  MAX_TOKENS: 4000,        // Increased token limit
  TIMEOUT_SECONDS: 600,    // Extended timeout
}
  1. Prompt Optimization
    • Be specific about output format
    • Request prioritized insights for complicated prompts
    • Focus on actionable outcomes

From plain ol’ Google Sheets to AI-powered workflows

This integration brings the power of ChatGPT directly into your spreadsheets, enabling new possibilities for data processing and content generation. Have some fun with it. The more you use it, the more you’ll find use-cases that can change the way you approach your day-to-day work.

Posted in AI