Google Sheets Integration

promptfoo allows you to import eval test cases directly from Google Sheets. This can be done either unauthenticated (if the sheet is public) or authenticated using Google's Default Application Credentials, typically with a service account for programmatic access.

Importing Test Cases from Google Sheets

Public Sheets (Unauthenticated)

For sheets that are accessible via "anyone with the link", simply specify the share URL in your configuration:

# yaml-language-server: $schema=https://promptfoo.dev/config-schema.json
description: 'Public Google Sheet Example'
prompts:
  - 'Please translate the following text to {{language}}: {{input}}'
providers:
  - anthropic:messages:claude-3-5-sonnet-20241022
  - openai:chat:gpt-5
// highlight-start
tests: https://docs.google.com/spreadsheets/d/1eqFnv1vzkPvS7zG-mYsqNDwOzvSaiIAsKB3zKg9H18c/edit?usp=sharing
// highlight-end

The Google Sheet above is structured with columns that define the test cases. Here's a copy of the sheet:

language,input,__expected
French,Hello world,icontains: bonjour
German,I'm hungry,llm-rubric: is german
Swahili,Hello world,similar(0.8):hello world

💡 See our example sheet for the expected format. For details on sheet structure, refer to loading assertions from CSV.

Private Sheets (Authenticated)

For private sheets, you'll need to set up Google's Default Application Credentials:

1. Install Peer Dependencies

   bashCopy

   npm install googleapis
   

2. Set Up Authentication

   • Create a service account in Google Cloud
   • Download the JSON key file
   • Enable the Google Sheets API (sheets.googleapis.com)
   • Share your sheet with the service account email ([email protected]) with at least viewer permissions

3. Configure Credentials

   bashCopy

   export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/service-account-file.json"
   

4. Use the Same URL Format

   yamlCopy

   tests: https://docs.google.com/spreadsheets/d/1eqFnv1vzkPvS7zG-mYsqNDwOzvSaiIAsKB3zKg9H18c/edit?usp=sharing
   

   The system will automatically use authenticated access when the sheet is not public.

Writing Evaluation Results to Google Sheets

The outputPath parameter (--output or -o on the command line) supports writing evaluation results directly to Google Sheets. This requires Default Application Credentials with write access configured.

Basic Usage

prompts:
  - ...
providers:
  - ...
tests:
  - ...
// highlight-start
outputPath: https://docs.google.com/spreadsheets/d/1eqFnv1vzkPvS7zG-mYsqNDwOzvSaiIAsKB3zKg9H18c/edit?usp=sharing
// highlight-end

Targeting Specific Sheets

You have two options when writing results to a Google Sheet:

1. Write to an existing sheet by including the sheet's gid parameter in the URL:

   yamlCopy

   outputPath: https://docs.google.com/spreadsheets/d/1eqFnv1vzkPvS7zG-mYsqNDwOzvSaiIAsKB3zKg9H18c/edit#gid=123456789
   

   💡 To find a sheet's gid, open the sheet in your browser and look at the URL - the gid appears after the #gid= portion.

2. Create a new sheet automatically by omitting the gid parameter. The system will:

   • Create a new sheet with a timestamp-based name (e.g., "Sheet1234567890")
   • Write results to this new sheet
   • Preserve existing sheets and their data

This behavior helps prevent accidental data overwrites while keeping your evaluation results organized within the same Google Sheets document.

Output Format

Results are written with columns for test variables followed by prompt outputs. Prompt columns include the provider in the header using the format [provider] prompt-label. For example, with two providers testing the same prompt:

Using Custom Providers for Model-Graded Metrics

When using Google Sheets for test cases, you can still use custom providers for model-graded metrics like llm-rubric or similar. To do this, override the default LLM grader by adding a defaultTest property to your configuration:

prompts:
  - file://prompt1.txt
  - file://prompt2.txt
providers:
  - anthropic:messages:claude-3-5-sonnet-20241022
  - openai:chat:gpt-5-mini
tests: https://docs.google.com/spreadsheets/d/1eqFnv1vzkPvS7zG-mYsqNDwOzvSaiIAsKB3zKg9H18c/edit?usp=sharing
defaultTest:
  options:
    provider:
      text:
        id: ollama:chat:llama3.3:70b
      embedding:
        id: ollama:embeddings:mxbai-embed-large

For more details on customizing the LLM grader, see the model-graded metrics documentation.