Why This Matters for Your Evaluation Agent
Your AI agent becomes truly powerful when it can read data from your existing spreadsheets. This guide shows you how to connect your Python-based evaluation agent to Google Sheets and Excel files, enabling automated indicator tracking, report generation, and data quality review directly from your M&E data sources.

Two Approaches Covered: This tutorial covers both Google Sheets (cloud-based) and Excel (desktop files) integration, so you can choose the method that fits your workflow.

Instructions:

1. Go to the Google Cloud Console and create a new project (e.g., “EvalAgent”)
2. Navigate to APIs & Services → Library and enable the Google Sheets API
3. Go to APIs & Services → Credentials and click Create Credentials → Service Account
4. Name your service account and assign the Editor role
5. Click on the service account, go to Keys → Add Key → JSON and download the credentials file
6. Rename the downloaded file to credentials.json and place it in your project folder

Important: Share your Google Sheet with the service account email address (found in the credentials.json file) to allow your agent to access it.

import gspread
from google.oauth2.service_account import Credentials
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = Credentials.from_service_account_file('credentials.json', scopes=SCOPES)
client = gspread.authorize(creds)
# Open by title
sheet = client.open('Your Indicator Data Sheet').sheet1
# Read all data
data = sheet.get_all_values()
# Read as dictionaries
records = sheet.get_all_records()
print(f"Loaded {len(records)} indicator records")

# Update a single cell
sheet.update_acell('F2', 'On Track')
# Update a range
sheet.update([[85, 92, 78]], 'B2:D2')
# Batch update
sheet.batch_update([{
    'range': 'B2:B10',
    'values': [[95], [82], [91]]
}])

import openpyxl
import pandas as pd
# Using openpyxl directly
wb = openpyxl.load_workbook('indicator_data.xlsx')
sheet = wb['Sheet1']
data = []
for row in sheet.iter_rows(min_row=2, values_only=True):
    data.append(row)
# Using pandas (easier)
df = pd.read_excel('indicator_data.xlsx')
records = df.to_dict('records')

# Update cells
sheet['D2'] = 'On Track'
sheet['E2'] = 95
# Add new row
sheet.append(['Indicator 4', 100, 92, 'On Track', 92])
# Save
wb.save('indicator_data_updated.xlsx')

import os
import gspread
import pandas as pd
from google.oauth2.service_account import Credentials
from dotenv import load_dotenv
import anthropic
load_dotenv()
def load_data(source='google_sheets', file_path=None):
    """Load indicator data from Google Sheets or Excel."""
    if source == 'google_sheets':
        SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
        creds = Credentials.from_service_account_file('credentials.json', scopes=SCOPES)
        client = gspread.authorize(creds)
        sheet = client.open('M&E Indicator Tracker').sheet1
        return sheet.get_all_records()
    else:
        df = pd.read_excel(file_path)
        return df.to_dict('records')
def evaluate_indicators(data):
    """AI agent to analyze indicator performance."""
    client = anthropic.Anthropic(api_key=os.getenv('ANTHROPIC_API_KEY'))
    
    prompt = f"""
    You are an M&E specialist analyzing indicator data:
    {data}
    
    Provide:
    1. Overall performance summary
    2. Off-track indicators (below 80% achievement)
    3. Three improvement recommendations
    4. Data quality concerns
    """
    
    response = client.messages.create(
        model="claude-3-sonnet-20240229",
        max_tokens=1000,
        temperature=0.3,
        messages=[{"role": "user", "content": prompt}]
    )
    return response.content[0].text
if __name__ == "__main__":
    data_source = 'google_sheets'
    indicators = load_data(source=data_source)
    print(f"Loaded {len(indicators)} indicators")
    report = evaluate_indicators(indicators)
    print(report)

Master AI Agents for M&E Workflows

The AI Agents for Evaluators Certificate teaches you to build practical, reliable AI agents for real M&E workflows—including spreadsheet integration, validation, and reporting.

Course Features: 32 lectures · Lifetime access · Certificate included · 241 students enrolled

Enroll Now – $249 Lifetime Access

Bundle with AI in M&E course and save 30%