SheetAI: Automate Data Processing in Google Sheets with AI

Tired of manually cleaning, classifying, and enriching data Spreadsheets?  SheetAI offers a powerful solution, leveraging the capabilities of AI (specifically OpenAI) to automate these tasks at scale.  This isn't just about individual cell formulas; SheetAI is designed for batch processing, enabling you to transform large datasets with minimal effort.  SheetAI provides a streamlined model for applying AI prompts to your spreadsheet data, generating new columns with the results.

What is SheetAI?

SheetAI is a simple AppScript that enable your Google Sheet integrated with OpenAI/ChatGPT.  SheetAI focuses on applying prompts to entire columns of data.  You define prompts that use placeholders (tokens) representing your data columns, and SheetAI automatically generates new columns containing the AI-processed results. This makes it ideal for tasks like:

• Data Classification: Categorizing data based on descriptions or other attributes.
• Data Enrichment: Adding extra information to your existing data (e.g., extracting sentiment, identifying key entities).
• Data Transformation: Parsing unstructured data into structured formats.

Key Features

• AI-Powered Data Processing: Utilizes OpenAI to classify, enrich, and process spreadsheet data.
• Batch Processing: Handles large datasets efficiently, with support for up to 50,000 requests per batch.
• Automated Column Management: Dynamically creates new columns to store the extracted data from the AI's responses.
• Cost Tracking: Logs token usage (including cached tokens) and provides accurate estimates of OpenAI API costs.
• Customizable Prompts: Define your own prompts and select the OpenAI model to use for each task.

How it Works

SheetAI's workflow revolves around three key sheets:

Config

This sheet stores your OpenAI API key, default model settings (like gpt-4o-mini, temperature, max_tokens, and a seed for reproducibility), a debug flag, and the batch size.

Prompts

Here, you define the prompts that will be applied to your data. Each prompt includes:

• Prompt Name: An Identifier for the prompt.
• Prompt Text: The actual instruction for the AI, using tokens (placeholders in double curly braces, e.g., {{Column Name}}) to represent data from your columns. Note that the prompt should be designed to return JSON.
• Model (Optional): Specify a different model for this specific prompt.
• Active: A flag (1 or 0) to enable or disable the prompt.
• Temperature (Optional): Override the default temperature.
• Max Tokens (Optional): Override the default max tokens.

Data

This is where your raw data resides. The first row must be a header row, defining the column names. You should not remove Status and Batch ID columns. You can put your data from C1 onwards.

Run Sheet

You can execute the prompts with OpenAI Tools Menu appear next to the Help menu. When you run SheetAI, it iterates through the active prompts in the Prompts sheet. For each prompt, it does the following:

1. Replaces the tokens in the Prompt Text with the corresponding values from each row in the Data sheet.
2. Sends the prompt to the OpenAI API.
3. Receives the JSON response from OpenAI.
4. Creates new columns in the Data sheet based on the keys in the returned JSON. For example, if your prompt asks for {"Category": "...", "Sentiment": "..."}, SheetAI will create columns named Prompt Name - Category and Prompt Name - Sentiment.
5. Populates these new columns with the extracted values from the JSON response.

Use Cases

Extracting Information from Unstructured Text: Process a column of company descriptions to extract key services offered by each company – all in one go.

• Job Title Classification: Categorize a list of employees based on their job titles, automatically creating a "Category" column.
• Address Parsing: Take a column containing full addresses and split it into separate "City," "State," and "Country" columns.
• Lead Prioritization: Analyze a batch of LinkedIn messages to identify high-potential contacts.
• Sentiment Analysis: Process hundreds or thousands of customer feedback entries and automatically add a "Sentiment" column (positive, negative, neutral).
• Entity Extraction: Extract names, dates, and locations from a column of text documents.
• Lead Qualification: Analyze sales lead data and add a "Qualified" column (Yes/No).
• Content Summarization: Generate short summaries for a large number of articles or documents.
• Spam Detection: Identify spam messages within a column of customer inquiries.

Setup Instructions

Get the Template: Use the link to make a copy of the SheetAI template. This template includes the necessary Config, Prompts, and Data sheets.

Obtain an OpenAI API Key: Create an API key from the OpenAI website.

• Configure the Config Sheet:
  • Enter your API key in cell B1 (next to API_KEY).
  • Set the DEFAULT_MODEL (e.g., gpt-4o-mini) in cell B2.
  • Adjust other settings (debug, batch size, temperature, max tokens, seed) as needed.
• Define Prompts in the Prompts Sheet:
  • Use the provided headers (Prompt Name, Prompt Text, Model, Active, Temperature, Max Tokens).
  • Write your prompts, using {{Column Name}} tokens to refer to columns in your Data sheet. Remember to format your prompt to return JSON.
  • Set the Active column to 1 for prompts you want to use.
• Prepare Your Data in the Data Sheet:
  • Ensure the first row is a header row with clear column names. A1 and A2 are reserved columns. You can use A3 onwards for your data.
  • Populate the sheet with your data.
• Run SheetAI
  • Click OpenAI Tools -> Run for First 10 Rows (to test your prompts)
  • Click OpenAI Tools -> Run for All Rows (to process the entire dataset)
  • For large datasets, use the batch processing options:
    • Create Batch - This will create a batch with a max number of rows mentioned in the BATCH_SIZE config
    • Check Batch Status - This will fill the current status of batches
    • Check and Process Batch - This will check the batch status and if a batch is finished, it will process it and fill the sheet with the response.

Important Considerations

• API Costs: Using the OpenAI API incurs costs. Monitor your usage and be aware of OpenAI's pricing.
• JSON Formatting: Your prompts must instruct the AI to return valid JSON. This is how SheetAI extracts data to create new columns.
• Column Name Matching: Token names (e.g., {{Company Description}}) must exactly match the column headers in your Data sheet (case-sensitive and including spaces).
• Data Privacy: Avoid sending sensitive data to OpenAI unless you are comfortable with their privacy policies.

Google App Script Authorization

The script requires authorization to run. Review the permissions carefully. It does not send your data to external systems other than the OpenAI API. You can inspect the full source code within Google Sheets (Extensions -> App Script). The detailed instructions on how to authorize the script is available in the README.

Execution Logs and Debugging

SheetAI provides helpful logs for monitoring and troubleshooting

• Execution Log: Shows the prompts sent and the responses received from OpenAI.
• Error Log: Captures any errors encountered during processing.
• Cost Summary: Tracks token usage and estimated costs.
• Batch Status: Monitors the progress of batch processing jobs.

Troubleshooting

Ready to streamline your data processing with AI? Try SheetAI for Free.

Need a demo of how SheetAI works and how you can adopt it to your use cases? Feel free to contact us.