Building an AI-Powered Expense Dashboard with Gemini and Google Sheets

This post is my submission for DEV Education Track: Build Apps with Google AI Studio.

What I Built

I set out to build an intelligent expense tracking dashboard that could automatically parse financial information from natural language text using Google's Gemini AI. The key prompts I used focused on extracting structured expense data from unstructured text like "Lunch with client $45.20, taxi ride $15.00" and converting it into organized spreadsheet entries. I utilized Gemini's structured output capabilities with JSON schemas to ensure consistent data extraction, integrated Google Sheets API for real-time data synchronization, and implemented OAuth 2.0 authentication for secure access to user data.

Demo

Live Demo: View the app in AI Studio

The dashboard features:

• AI-Powered Text Processing: Paste any expense-related text and watch Gemini automatically extract structured data
• Real-Time Analytics: Instant spending summaries, category breakdowns, and visual charts
• Google Sheets Integration: Automatic synchronization with your personal spreadsheets
• Debt Tracking: Separate tracking for IOUs and debts with due dates
• Export Capabilities: Download your data as CSV files

My Experience

Working through the Google AI Studio track was incredibly rewarding and opened my eyes to the power of structured AI outputs. Here are my key takeaways:

What I learned:

• Structured Output is Game-Changing: Using Gemini's JSON schema capabilities transformed how I think about AI integration. Instead of parsing free-form text responses, I could get perfectly structured data every time.
• OAuth Integration: Implementing secure Google authentication required careful attention to token management, refresh logic, and user consent flows.

What was surprising:

• Gemini's Context Understanding: The AI consistently understood relative dates ("yesterday", "last week") and inferred appropriate expense categories even from minimal context.
• Error Handling Importance: Building robust error handling for AI responses was crucial - the AI occasionally returned unexpected formats that needed graceful fallbacks.
• User Experience Impact: The combination of AI + familiar tools (Google Sheets) created an incredibly intuitive user experience that felt both powerful and approachable.

The most exciting part was seeing how Gemini could transform messy, real-world text into clean, structured data that users could immediately use for financial planning. This project demonstrated that the future of productivity apps lies in intelligent automation that works with existing workflows rather than replacing them entirely.

How to Set Up Locally

Prerequisites

• Node.js (v16 or higher)
• A Google Cloud Platform account
• A Gemini API key

1. Clone and Install

# Download the project Google AI Studio
cd gemini-expense-dashboard
npm install

2. Create Google Cloud Project

Step 1: Create a New Project

1. Go to the Google Cloud Console
2. Click "Select a project" → "New Project"
3. Enter a project name (e.g., "Expense Dashboard")
4. Click "Create"

Step 2: Enable Required APIs

1. Navigate to "APIs & Services" → "Library"
2. Search for and enable:
   • Google Sheets API
   • Google Drive API (for file access)

Step 3: Create OAuth 2.0 Client ID

1. Go to "APIs & Services" → "Credentials"
2. Click "Create Credentials" → "OAuth client ID"
3. If prompted, configure the OAuth consent screen:
   • Choose "External" user type
   • Fill in app information:
     • App name: "Expense Dashboard"
     • User support email: your email
     • Developer contact: your email
   • Add your domain to authorized domains
4. Set up the OAuth client:
   • Application type: Web application
   • Name: "Expense Dashboard Client"
   • Authorized JavaScript origins:
     • http://localhost:3000
   • Authorized redirect URIs:
     • http://localhost:3000 (for development)
5. Click "Create" and copy the Client ID

Step 4: Add Test Users

1. In the OAuth consent screen, scroll to "Test users"
2. Click "Add users"
3. Add email addresses of users who will test the application
4. Save the configuration

3. Create Your Google Sheet

1. Create a new Google Sheet
2. Set up the headers in Sheet1 (for expenses):
   • Column A: ID
   • Column B: Date
   • Column C: Description
   • Column D: Category
   • Column E: Amount
3. Set up the headers in Sheet2 (for debts):
   • Column A: ID
   • Column B: Creditor
   • Column C: Amount
   • Column D: Due Date
   • Column E: Description
4. Copy the Sheet ID from the URL: https://docs.google.com/spreadsheets/d/[SHEET_ID]/edit

4. Configure the Application

Update Configuration File

Edit config.ts with your credentials:

// Your Google Sheet ID (found in the URL of your Google Sheet)
export const SPREADSHEET_ID = 'your-spreadsheet-id-here';

// Sheet names for expenses and debts
export const SHEET_NAME = 'Sheet1';
export const DEBTS_SHEET_NAME = 'Sheet2';

// Your OAuth 2.0 Client ID from Google Cloud Console
export const OAUTH_CLIENT_ID = 'your-client-id-here.apps.googleusercontent.com';

// Your Gemini API Key
export const GEMINI_API_KEY = 'your-api-key';

5. Run the Application

# Start the development server
npm run dev

# The app will be available at http://localhost:3000

6. First-Time Setup

1. Open the application in your browser
2. Click "Sign in with Google"
3. Grant the necessary permissions
4. Start adding expenses by pasting text like:
   • "Lunch with client $45.20, taxi ride $15.00"
   • "Coffee $4.50, parking $8.00"

Troubleshooting

• OAuth errors: Ensure your Client ID is correct and the domain is authorized
• Sheets API errors: Verify the API is enabled and you have access to the spreadsheet
• Gemini API errors: Check that your API key is valid and has sufficient quota