Skip to main content

Importing Test Cases from Google Sheets

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.

Unauthenticated Access​

If the Google Sheet is set to be accessible by "anyone with the link," you can directly specify the share URL in your YAML configuration. For example:

prompts:
  - prompt1.txt
  - prompt2.txt
providers:
  - anthropic:messages:claude-3-5-sonnet-20241022
  - openai:chat:gpt-4o
tests: https://docs.google.com/spreadsheets/d/1eqFnv1vzkPvS7zG-mYsqNDwOzvSaiIAsKB3zKg9H18c/edit?usp=sharing

Here's an example sheet. For more information on how to set up the sheet itself, see loading assertions from CSV.

Authenticated Access with Default Application Credentials​

For sheets that are not publicly accessible, you can use authenticated access. This requires setting up Google Default Application Credentials. Here’s how you can configure it:

  1. Install peer dependencies: npm install googleapis

  2. Service Account Setup: Create a service account in your Google Cloud Platform project. Create a JSON key file and download it.

  3. Enable Google Sheets API: Enable the Google Sheets API (sheets.googleapis.com).

  4. Share Sheet: Share the Google Sheet with the email address of your service account ([email protected]) with at least viewer permissions.

  5. Configure Environment: Set the GOOGLE_APPLICATION_CREDENTIALS environment variable to the path of the JSON key file:

export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/service-account-file.json"
  1. Update YAML Configuration: Use the same URL format as in unauthenticated access, but the system will automatically use the authenticated method to access the sheet:
tests: https://docs.google.com/spreadsheets/d/1eqFnv1vzkPvS7zG-mYsqNDwOzvSaiIAsKB3zKg9H18c/edit?usp=sharing

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

For more information on overriding the LLM grader, see the model-graded metrics documentation.

Writing outputs to a Google Sheet​

The outputPath parameter (--output or -o on the command line) supports Google Sheets URLs. In order to write, Default Application Credentials must be configured with a service account that has write access.

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