How to do local testing of an ADK agent that writes to a Google Sheet before deploying to Cloud Run

1. Introduction

Overview

In this post, you'll build a Gemini agent using the Google Agent Development Kit (ADK) and FastAPI. Prior to deployment, you will configure Application Default Credentials (ADC) to test locally using the same service account as the Cloud Run service identity.

To communicate with Google Sheets, your application needs an OAuth Access Token with the appropriate scope to access the spreadsheet. You'll learn how to obtain this access token when running locally and on Cloud Run with ADC:

• Locally: ADC finds the credentials, as generated by the gcloud auth application-default command. You can find more information here
• On Cloud Run: ADC uses the Metadata Server to get credentials. You can find more information here.

Terminology Note:

You may be more familiar with the terms "assuming an identity" or "assuming the same permissions." In Google Cloud, impersonating a service account lets an authenticated principal access whatever the service account can access. Only authenticated principals with the appropriate permissions can impersonate service accounts. You can read more here https://docs.cloud.google.com/iam/docs/service-account-overview#impersonation

What you'll learn

• How to configure Application Default Credentials (ADC) for your local environment
• How to create an ADK agent that can read and write to a Google Sheet
• How to deploy the agent to Cloud Run

2. Setup and Requirements

Prerequisites

• You are logged into the Cloud Console.
• You have previously deployed a Cloud Run service. For example, you can follow the deploy a Cloud Run service to get started.

Set Environment Variables

You can set environment variables that will be used throughout this codelab.

You can find your Sheet ID in the URL of your Google Sheet when you copy the Share link, e.g. https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit?usp=sharing

PROJECT_ID=<YOUR-PROJECT-ID>
REGION=<YOUR_REGION>
SPREADSHEET_ID=<YOUR_SPREADSHEET_ID>
SA_NAME=sheet-agent-sa
SERVICE_ACCOUNT_EMAIL=$SA_NAME@$PROJECT_ID.iam.gserviceaccount.com

Enable Required Google Cloud APIs

gcloud services enable \
  sheets.googleapis.com \
  aiplatform.googleapis.com \
  artifactregistry.googleapis.com \
  cloudbuild.googleapis.com \
  run.googleapis.com \
  logging.googleapis.com \
  --project=$PROJECT_ID

Create Service Account

First, create the Service Account with the following command.

gcloud iam service-accounts create $SA_NAME \
    --description="Service account for spreadsheet agent codelab" \
    --display-name="Spreadsheet Agent Service Account" \
    --project=$PROJECT_ID

Next, grant the Vertex AI User role to the Service Account (needed for Gemini models on Vertex AI)

gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:$SERVICE_ACCOUNT_EMAIL" \
    --role="roles/aiplatform.user"

Grant your gcloud identity permission to impersonate the Service Account (needed for local development). You can see your gcloud identities by running gcloud auth list

USER_EMAIL=$(gcloud config get-value account)

gcloud iam service-accounts add-iam-policy-binding $SERVICE_ACCOUNT_EMAIL \
    --member="user:$USER_EMAIL" \
    --role="roles/iam.serviceAccountTokenCreator" \
    --project=$PROJECT_ID

Note: Lastly, you'll need to give your service account Editor access to your sheet, just like you'd share a Google Sheet with a coworker.

1. Open the Google Sheet in the browser.
2. Click the Share button.
3. Paste the Service Account email $SERVICE_ACCOUNT_EMAIL and grant it Editor access.

3. Create the app

First, create a directory for the source code and cd into that directory.

mkdir local-adk-sheets-codelab && cd $_

Then, create a main.py file with the following content:

import os
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from google.auth import default
from googleapiclient.discovery import build
from google.adk.agents.llm_agent import Agent
from google.adk.runners import InMemoryRunner

# 1. Environment and Global Google Sheets Client Setup (ADC)
SPREADSHEET_ID = os.environ.get("SPREADSHEET_ID")
if not SPREADSHEET_ID:
    raise ValueError("SPREADSHEET_ID environment variable is missing. Please set it before running.")

credentials, _ = default(scopes=['https://www.googleapis.com/auth/spreadsheets'])
sheets_api = build('sheets', 'v4', credentials=credentials).spreadsheets()

# 2. Tools
def read_spreadsheet(range_name: str) -> dict:
    """Reads values from the Google Spreadsheet for a given range.
   
    Args:
        range_name: The A1 notation or R1C1 notation of the range to retrieve, e.g., 'Sheet1!A1:D10'.
    """
    return sheets_api.values().get(spreadsheetId=SPREADSHEET_ID, range=range_name).execute()

def update_spreadsheet(range_name: str, values: list[list[str]]) -> dict:
    """Updates the Google Spreadsheet range with the specified grid of values.
   
    Args:
        range_name: The A1 notation or R1C1 notation of the range to update, e.g., 'Sheet1!C2'.
        values: A list of lists of strings representing the grid of values to write.
    """
    return sheets_api.values().update(
        spreadsheetId=SPREADSHEET_ID,
        range=range_name,
        valueInputOption="USER_ENTERED",
        body={"values": values}
    ).execute()

# 3. Agent Definition
MODEL_NAME = os.environ.get("GEMINI_MODEL", "gemini-3.1-flash-lite")
root_agent = Agent(
    name="spreadsheet_agent",
    model=MODEL_NAME,
    instruction="""You are a helpful spreadsheet assistant.
    You can read and write to the user's Google Spreadsheet using the tools provided.
    Always use the appropriate tools when the user asks you to read or update a spreadsheet.
    When updating a range, make sure the values parameter is a list of lists (e.g. [[value]]).
    Be concise and helpful in your responses.""",
    description="An agent that can read and write to a specific Google Spreadsheet.",
    tools=[read_spreadsheet, update_spreadsheet]
)

# 4. FastAPI Application Setup
app = FastAPI()

class ChatRequest(BaseModel):
    prompt: str

@app.post("/chat")
async def chat_endpoint(request: ChatRequest):
    try:
        async with InMemoryRunner(app_name="sheets_agent_app", agent=root_agent) as runner:
            events = await runner.run_debug(request.prompt, quiet=True)
           
            # Extract the final response text
            texts = [
                "".join(p.text for p in e.content.parts if p.text)
                for e in reversed(events) if e.content and e.content.parts
            ]
            return {"response": next((t for t in texts if t), "No response from agent.")}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

Next, create a requirements.txt file with the following content:

fastapi>=0.100.0
uvicorn>=0.22.0
google-adk>=1.27.1
google-auth
google-api-python-client

4. Test the service locally

gcloud auth application-default login --impersonate-service-account=$SERVICE_ACCOUNT_EMAIL

GOOGLE_GENAI_USE_VERTEXAI=1 GOOGLE_CLOUD_PROJECT=$PROJECT_ID SPREADSHEET_ID=$SPREADSHEET_ID uvicorn main:app --host 0.0.0.0 --port 8080

Now, test your agent from another terminal:

curl -X POST http://localhost:8080/chat \
    -H "Content-Type: application/json" \
    -d '{"prompt": "Update spreadsheet '$SPREADSHEET_ID' at range Sheet1!A1 with the value hello world"}'

The server should respond: {"response":"The spreadsheet at Sheet1!A1 has been updated with the value \"hello world\"."}

Note: Make sure to delete the hello world value from your spreadsheet before you test from Cloud Run, so you'll know that it works from Cloud Run.

5. Deploy to Cloud Run

Now that you know your service account has the appropriate permissions to access the spreadsheet, you will now deploy to Cloud Run, using the service account as the service identity.

gcloud run deploy local-adk-sheets-codelab \
    --source . \
    --region=$REGION \
    --set-env-vars GOOGLE_GENAI_USE_VERTEXAI=1,GOOGLE_CLOUD_PROJECT=$PROJECT_ID,GOOGLE_CLOUD_LOCATION=global,SPREADSHEET_ID=$SPREADSHEET_ID \
    --service-account $SERVICE_ACCOUNT_EMAIL \
    --no-allow-unauthenticated \
    --project=$PROJECT_ID

When the deployment finishes, send a prompt to your Cloud Run service.

# Retrieve the Service URL
SERVICE_URL=$(gcloud run services describe local-adk-sheets-codelab --region $REGION --format 'value(status.url)')

curl -X POST $SERVICE_URL/chat \
    -H "Authorization: bearer $(gcloud auth print-identity-token)" \
    -H "Content-Type: application/json" \
    -d '{"prompt": "Update spreadsheet '$SPREADSHEET_ID' at range Sheet1!A1 with the value hello world"}'

You should receive a response of {"response":"OK. I've updated cell A1 in Sheet1 with \"hello world\"."}

Now open your Google Sheet. You'll see the value "hello world" in the first cell.

6. Congratulations!

Congratulations for completing the codelab!

What's next?

We recommend exploring the following documentation to expand your agent's capabilities:

• Explore advanced agent workflows using the Google Agent Development Kit (ADK)
• Learn more best practices for Building AI solutions on Cloud Run.
• Understand how to securely Authenticate to Google Cloud APIs from Cloud Run.

What we've covered

• How to configure Application Default Credentials (ADC) for your local environment
• How to create an ADK agent that can read and write to a Google Sheet
• How to deploy the agent to Cloud Run

7. Clean up

To avoid inadvertent charges, (for example, if this Cloud Run function is inadvertently invoked more times than your monthly Cloud Run invokement allocation in the free tier), you can either delete the Cloud Run service or delete the project.

To delete a Cloud Run service, go to Cloud Run in the Cloud Console at https://console.cloud.google.com/run/ and delete the service local-adk-sheets-codelab you created in this codelab.

If you choose to delete the entire project, you can go to https://console.cloud.google.com/cloud-resource-manager, select the project you created in Step 2, and choose Delete. If you delete the project, you'll need to change projects in your Cloud SDK. You can view the list of all available projects by running gcloud projects list.