promptfoo--promptfoo/site/docs/integrations/google-sheets.md

---
sidebar_label: Google Sheets
description: Import and export LLM test cases with Google Sheets integration. Configure public or authenticated access, write evaluation results, and run model-graded metrics.
---

# 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 title="promptfooconfig.yaml"
# 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:

```csv title="Google 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](https://docs.google.com/spreadsheets/d/1eqFnv1vzkPvS7zG-mYsqNDwOzvSaiIAsKB3zKg9H18c/edit#gid=0) for the expected format. For details on sheet structure, refer to [loading assertions from CSV](/docs/configuration/expected-outputs/#load-assertions-from-csv).

### Private Sheets (Authenticated)

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

1. **Install the authenticated Sheets package**

   ```bash
   npm install @googleapis/sheets
   ```

   `@googleapis/sheets` is an optional runtime dependency. Public-sheet CSV imports do not need it, but private-sheet reads and Google Sheets output writes do.

2. **Set Up Authentication**
   - Create a [service account](https://console.cloud.google.com/iam-admin/serviceaccounts) in Google Cloud
   - Download the JSON key file
   - Enable the [Google Sheets API](https://console.cloud.google.com/apis/library/sheets.googleapis.com) (`sheets.googleapis.com`)
   - Share your sheet with the service account email (`your-service-account@project-name.iam.gserviceaccount.com`) with at least viewer permissions

3. **Configure Credentials**

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

4. **Use the Same URL Format**
   ```yaml
   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

```yaml
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:

   ```yaml
   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:

| language | input       | [openai:gpt-5] Translate | [anthropic:claude-4.5-sonnet] Translate |
| -------- | ----------- | ------------------------ | --------------------------------------- |
| French   | Hello world | Bonjour le monde         | Bonjour monde                           |

## 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:

```yaml
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](/docs/configuration/expected-outputs/model-graded/#overriding-the-llm-grader).