Building a Family Expense Tracker with GAS & Telegram 💰

💸 Zero-Cost Full-Stack Finance App

Google Apps Script + Sheets + Telegram + Gemini AI — Hosted free, forever

📋 Table of Contents

Overview
🎯 Key Features
🏗️ System Architecture
- Technology Stack
- Project Structure
🚀 Getting Started
⚡ Backend: Google Apps Script
🤖 Telegram Bot Integration
⚛️ Frontend: React + Vite
- GAS ↔ Frontend Bridge
🧠 AI Analysis with Gemini
- Gemini API Setup
📊 Google Sheets Structure
🚢 Build & Deploy
📱 Daily Usage
🔧 Customization
- Categories
🐛 Troubleshooting
🎓 Best Practices
🚀 Next Steps & Enhancements
📚 Resources & References
- 🔗 Official Documentation
- 📖 Learning Resources
🎉 Conclusion
- Key Takeaways

Overview

Managing family finances shouldn't require expensive software or complex infrastructure. This guide walks you through building a complete expense tracking system that's entirely free to host, using Google's ecosystem and Telegram for notifications.

The app lets you record expenses in ~8 seconds from your phone, get real-time Telegram alerts, view a rich dashboard with charts, and even leverage Gemini AI to analyze your spending habits — all powered by Google Apps Script with Google Sheets as the database.

📝

Quick Entry

Record expenses in ~8 seconds — amount, category, person, done!

📊

Rich Dashboard

5-tab dashboard: Overview, Budget, Goals, Assets, AI Analysis

🤖

Telegram Bot

Real-time notifications, daily/weekly summaries, inline keyboards

🧠

AI Insights

Gemini 2.5 Flash Lite — 6 quick analyses + free-form Q&A

🏦

Asset Tracking

Gold, savings, stocks, real estate — net worth at a glance

💰

100% Free

No servers, no bills — Google Apps Script + Sheets + Telegram

🎯 Key Features

Feature	Description
📝 Expense Form	Mobile-first interface, record in ~8 seconds
📊 Dashboard	5 tabs: Overview · Budget · Goals · Assets · AI
🏦 Asset Management	Track gold, savings, stocks, real estate — P&L, maturity dates, net worth
🤖 Telegram Bot	HTML-formatted messages, inline keyboard buttons — instant alerts, daily/weekly summaries
🎯 Goals & Debts	CRUD savings goals + debt management with progress tracking
💰 Budget Tracking	Income, savings, bills, category budgets — spending velocity monitoring
📂 Category Management	Add/edit/delete spending categories from the app
⚙️ Settings	Toggle Telegram, manage system configuration
📈 Auto Reports	Monthly / Quarterly / Yearly — auto-generated on the 1st of each month
📱 PWA-like	Add to Home Screen, dark/light theme, mobile-first design
🧠 AI Analysis	Gemini-powered: monthly summary, savings tips, trend forecast, financial health score

🏗️ System Architecture

🏗️ System Architecture — Hover to Explore

📱 User

⚡ Google Apps Script

📊 Google Sheets

🤖 Telegram Bot

🧠 Gemini AI

⚡ Vite + React

Flow: User → React PWA → google.script.run → GAS Backend → Google Sheets
+ GAS Triggers → Telegram Bot notifications + Gemini AI analysis

Technology Stack

⚛️ React 18

Frontend UI

⚡ Vite

Build tooling

📊 Recharts

Charts & graphs

🔧 Google Apps Script

Backend (V8)

📋 Google Sheets

Database

🤖 Telegram Bot API

Notifications

🧠 Gemini 2.5 Flash

AI analysis

📦 Clasp

GAS deployment

Project Structure

family-expense/
├── src/                              ← React source (local dev)
│   ├── main.jsx                      ← Entry point
│   ├── App.jsx                       ← Router (hash-based: #form | #dashboard)
│   ├── styles/globals.css            ← CSS variables & base
│   ├── utils/
│   │   ├── constants.js              ← Categories, budget, colors, sample data
│   │   ├── formatters.js             ← fmt(), fmtK(), pct()
│   │   ├── gas.js                    ← Bridge GAS ↔ local dev (mock in dev)
│   │   └── gemini.js                 ← Gemini AI client (cache, rate-limit)
│   └── components/
│       ├── shared.jsx                ← Card, ProgressBar, StatCard, Tooltip
│       ├── ExpenseForm/              ← Daily expense entry form
│       ├── Dashboard/                ← Dashboard with 5 tabs
│       │   ├── OverviewTab.jsx       ← Tab 1: Overview (trend, pie, recent)
│       │   ├── BudgetTab.jsx         ← Tab 2: Budget (cashflow, budget vs actual)
│       │   ├── GoalsTab.jsx          ← Tab 3: Savings goals & debts
│       │   ├── AssetsTab.jsx         ← Tab 4: Assets (gold, savings, stocks)
│       │   └── AITab.jsx             ← Tab 5: AI analysis (Gemini)
│       └── Management/               ← CRUD panel (goals, debts, categories, etc.)
│
├── gas/                              ← Pushed to Google Apps Script
│   ├── Code.js                       ← Backend: API + reports + triggers + Telegram
│   ├── appsscript.json               ← GAS config + OAuth scopes
│   ├── .clasp.json                   ← CLASP config (scriptId)
│   └── index.html                    ← ⭐ Built file from Vite (all-in-one)
│
├── index.html                        ← Vite entry
├── vite.config.js                    ← Vite + singlefile plugin
└── package.json

How It Works

Data Flow

🚀 Getting Started

Setup Tools

Create GAS

Google Sheet

Build & Deploy

Telegram Bot

Done!

Prerequisites

📋Before You Begin

Make sure you have the following installed:

Node.js ≥ 18 (for Vite and Clasp)
npm (comes with Node.js)
A Google Account (for Sheets and Apps Script)
A Telegram Account (for the bot)
Optionally: a Gemini API key from Google AI Studio for AI features

Step 1: Install Dependencies

npm
yarn

# Clone or create the project
mkdir family-expense && cd family-expense
npm init -y

# Install frontend dependencies
npm install react react-dom recharts

# Install dev dependencies
npm install -D vite @vitejs/plugin-react vite-plugin-singlefile

# Install Clasp globally (Google Apps Script CLI)
npm install -g @google/clasp

# Login to Clasp
clasp login

mkdir family-expense && cd family-expense
yarn init -y

yarn add react react-dom recharts
yarn add -D vite @vitejs/plugin-react vite-plugin-singlefile

npm install -g @google/clasp
clasp login

Enable Apps Script API

Before using Clasp, you must enable the Apps Script API at: https://script.google.com/home/usersettings

Step 2: Configure Vite

The key innovation here is using vite-plugin-singlefile to bundle the entire React app into a single HTML file that Google Apps Script can serve:

vite.config.js
import { defineConfig } from 'vite';
import react from '@vitejs/plugin-react';
import { viteSingleFile } from 'vite-plugin-singlefile';

export default defineConfig({
  plugins: [react(), viteSingleFile()],
  build: {
    outDir: 'gas',          // Build directly into the gas/ folder
    emptyOutDir: false,      // Don't delete Code.js & appsscript.json!
    assetsInlineLimit: 100000000,  // Inline everything
    cssCodeSplit: false,
    rollupOptions: {
      output: { manualChunks: undefined },
    },
  },
});

Why Single File?

Google Apps Script can only serve HTML files via HtmlService. By bundling React + CSS + JS into one index.html, we can serve a full SPA from GAS with zero external hosting.

Step 3: Create Google Sheet & Apps Script Project

From Existing Sheet
Create New with Clasp

Go to Google Sheets → Create a new spreadsheet
Copy the Sheet ID from the URL: https://docs.google.com/spreadsheets/d/{SHEET_ID}/edit
Open Extensions → Apps Script → copy the Script ID from the URL
Create the Clasp config:

gas/.clasp.json
{
  "scriptId": "PASTE_YOUR_SCRIPT_ID_HERE",
  "rootDir": "."
}

# Enable Apps Script API first: https://script.google.com/home/usersettings
mkdir gas && cd gas
clasp create --type standalone --title "Family Expense Tracker"

Then create a Google Sheet separately and note its Sheet ID.

Step 4: GAS Configuration

gas/appsscript.json
{
  "timeZone": "Asia/Ho_Chi_Minh",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/script.scriptapp",
    "https://www.googleapis.com/auth/calendar"
  ],
  "webapp": {
    "access": "ANYONE_ANONYMOUS",
    "executeAs": "USER_DEPLOYING"
  }
}

Scope	Purpose
`spreadsheets`	Read/write Google Sheets data
`script.external_request`	Call Telegram & Gemini APIs
`script.scriptapp`	Manage time-based triggers
`calendar`	Reserved for future calendar integration

⚡ Backend: Google Apps Script

This is the heart of the application — a single Code.js file that handles everything.

Configuration & Constants

gas/Code.js
// ════════════════════════════════════════════════════════════════
//  FAMILY EXPENSE TRACKER — Google Apps Script Backend
// ════════════════════════════════════════════════════════════════

const CONFIG = {
  // ⚠️ Paste your Google Sheet ID here
  SPREADSHEET_ID: 'YOUR_SHEET_ID_HERE',
  SHEETS: {
    RAW_DATA:  'RawData',       // Raw transactions
    MONTHLY:   'BaoCao_Thang',  // Monthly reports
    QUARTERLY: 'BaoCao_Quy',    // Quarterly reports
    YEARLY:    'BaoCao_Nam',    // Yearly reports
    GOALS:     'MucTieu_TietKiem', // Savings goals
    DEBTS:     'QuanLy_No',     // Debt management
    ASSETS:    'TaiSan',        // Assets tracking
    BUDGET:    'NganSach',      // Budget configuration
    SETTINGS:  'CaiDat',        // System settings
    CONFIG:    'CauHinh',       // Categories & config
  },
  TIMEZONE: 'Asia/Ho_Chi_Minh',
};

// Helper: open spreadsheet by ID
function getSpreadsheet_() {
  return SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
}

// Helper: read secrets from Script Properties (never hardcode!)
function getSecret_(key) {
  return PropertiesService.getScriptProperties().getProperty(key) || '';
}

Security First

Never hardcode API tokens or secrets in your source code! Use GAS Script Properties to store:

TELEGRAM_BOT_TOKEN
TELEGRAM_CHAT_ID
GEMINI_API_KEY

Go to GAS Editor → Project Settings ⚙️ → Script Properties → Add each key/value pair.

Web App Entry Point

gas/Code.js — doGet()
function doGet() {
  return HtmlService.createHtmlOutputFromFile('index')
    .setTitle('Family Expense Tracker')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
    .addMetaTag('viewport', 'width=device-width, initial-scale=1, user-scalable=no');
}

This single function serves your entire React app. When Vite builds the project, it outputs gas/index.html which is then served by GAS.

Adding Expenses

gas/Code.js — addEntry()
function addEntry(entry) {
  const ss = getSpreadsheet_();
  const sheet = getOrCreateSheet_(ss, CONFIG.SHEETS.RAW_DATA, [
    'Timestamp', 'Date', 'Month', 'Year', 'Quarter',
    'Person', 'Type', 'Group', 'Category', 'Full Category',
    'Amount (K)', 'Amount (VND)', 'Note',
  ]);

  // Support custom timestamp from frontend
  var ts = entry.timestamp ? new Date(entry.timestamp) : new Date();
  if (isNaN(ts.getTime())) ts = new Date();

  var month = parseInt(Utilities.formatDate(ts, CONFIG.TIMEZONE, 'M'), 10);
  var year  = parseInt(Utilities.formatDate(ts, CONFIG.TIMEZONE, 'yyyy'), 10);
  var quarter = 'Q' + Math.ceil(month / 3);

  sheet.appendRow([
    ts,
    Utilities.formatDate(ts, CONFIG.TIMEZONE, 'dd/MM/yyyy'),
    month, year, quarter,
    entry.person,
    entry.type === 'expense' ? 'Expense' : 'Income',
    catInfo.group,
    entry.category,
    catInfo.fullName,
    entry.amount,
    entry.amount * 1000,
    entry.note || '',
  ]);

  // Send Telegram notification
  notifyTelegram_(entry, ts);

  return { success: true };
}

Helper: Get or Create Sheet

gas/Code.js — getOrCreateSheet_()
function getOrCreateSheet_(ss, name, headers) {
  var sheet = ss.getSheetByName(name);
  if (!sheet) {
    sheet = ss.insertSheet(name);
    if (headers && headers.length) {
      sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
      sheet.getRange(1, 1, 1, headers.length)
        .setFontWeight('bold')
        .setBackground('#4CAF50')
        .setFontColor('white');
      sheet.setFrozenRows(1);
    }
  }
  return sheet;
}

System Setup Function

Run this once after deploying to create all sheets and triggers:

gas/Code.js — SETUP_HE_THONG()
function SETUP_HE_THONG() {
  setupGoals();      // Create savings goals sheet
  setupAssets();     // Create assets sheet
  setupBudget();     // Create budget sheet
  setupSettings();   // Create settings sheet
  setupAllTriggers(); // Install time-based triggers

  Logger.log('✅ SYSTEM READY!');
  Logger.log('📊 RawData         — Raw transactions');
  Logger.log('📈 BaoCao_Thang    — Monthly reports');
  Logger.log('📈 BaoCao_Quy      — Quarterly reports');
  Logger.log('📈 BaoCao_Nam      — Yearly reports');
  Logger.log('🎯 MucTieu_TietKiem — Savings goals');
  Logger.log('📋 QuanLy_No       — Debt management');
  Logger.log('🏦 TaiSan          — Assets');
  Logger.log('💰 NganSach        — Budget config');
  Logger.log('⚙️ CaiDat          — Settings');
}

Time-Based Triggers

gas/Code.js — setupAllTriggers()
function setupAllTriggers() {
  // Remove existing triggers to avoid duplicates
  ScriptApp.getProjectTriggers().forEach(function(t) {
    ScriptApp.deleteTrigger(t);
  });

  // Monthly/Quarterly/Yearly reports — 1st of each month, 8 AM
  ScriptApp.newTrigger('generateAllReports')
    .timeBased().onMonthDay(1).atHour(8).create();

  // Update savings goals progress — Sundays, 8 PM
  ScriptApp.newTrigger('updateGoalsProgress')
    .timeBased().onWeekDay(ScriptApp.WeekDay.SUNDAY).atHour(20).create();

  // Telegram daily summary — Every day, 9 PM
  ScriptApp.newTrigger('telegramDailyReminder')
    .timeBased().everyDays(1).atHour(21).create();

  // Telegram weekly summary — Sundays, 8 PM
  ScriptApp.newTrigger('telegramWeeklySummary')
    .timeBased().onWeekDay(ScriptApp.WeekDay.SUNDAY).atHour(20).create();

  Logger.log('✅ All triggers installed');
}

⏰Automatic Triggers

Trigger	Schedule	Function
Monthly/Quarterly/Yearly reports	1st of month, 8 AM	`generateAllReports()`
Update goals progress	Sunday, 8 PM	`updateGoalsProgress()`
Telegram daily summary	Every day, 9 PM	`telegramDailyReminder()`
Telegram weekly summary	Sunday, 8 PM	`telegramWeeklySummary()`

🤖 Telegram Bot Integration

This is one of the most powerful features — a fully interactive Telegram bot that sends rich notifications with inline keyboard buttons.

Step 1: Create Your Bot (~2 minutes)

Open Telegram → search for @BotFather → send /newbot
Choose a name (e.g., Family Expense Bot)
Choose a username (e.g., family_expense_bot)
Copy the token that BotFather gives you

Step 2: Get Your Chat ID (~1 minute)

Open Telegram → search for @userinfobot or @RawDataBot → send /start
Copy your Chat ID (a number)

Step 3: Store Credentials Securely

Via GAS Editor (Recommended)
Via Code (One-time)

Open GAS Editor → Project Settings (⚙️ icon on the left sidebar)
Scroll to Script Properties → Edit script properties
Add these properties:

Property	Value
`TELEGRAM_BOT_TOKEN`	Token from BotFather
`TELEGRAM_CHAT_ID`	Your Chat ID
`GEMINI_API_KEY`	API key from Google AI Studio

Click Save script properties

Create a temporary function in GAS Editor, run it once, then delete it:

function _initSecrets() {
  setupSecrets({
    TELEGRAM_BOT_TOKEN: '123456:ABC-DEF1234...',
    TELEGRAM_CHAT_ID: '123456789',
    GEMINI_API_KEY: 'AIza...'
  });
}

gas/Code.js — setupSecrets()
function setupSecrets(secrets) {
  var props = PropertiesService.getScriptProperties();
  if (secrets.TELEGRAM_BOT_TOKEN) props.setProperty('TELEGRAM_BOT_TOKEN', secrets.TELEGRAM_BOT_TOKEN);
  if (secrets.TELEGRAM_CHAT_ID) props.setProperty('TELEGRAM_CHAT_ID', secrets.TELEGRAM_CHAT_ID);
  if (secrets.GEMINI_API_KEY) props.setProperty('GEMINI_API_KEY', secrets.GEMINI_API_KEY);
  Logger.log('✅ Secrets saved to Script Properties');
}

Sending Messages

gas/Code.js — sendTelegram_()
function sendTelegram_(text, opts) {
  var token = getSecret_('TELEGRAM_BOT_TOKEN');
  var chatIds = getAllChatIds_();
  if (!token || chatIds.length === 0) return false;

  var anySuccess = false;
  chatIds.forEach(function(chatId) {
    try {
      var url = 'https://api.telegram.org/bot' + token + '/sendMessage';
      var payload = {
        chat_id: chatId,
        text: text,
        parse_mode: 'HTML',
        disable_web_page_preview: true,
      };
      if (opts && opts.reply_markup) {
        payload.reply_markup = opts.reply_markup;
      }

      var res = UrlFetchApp.fetch(url, {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify(payload),
        muteHttpExceptions: true,
      });

      if (res.getResponseCode() === 200) anySuccess = true;
      // Auto-remove blocked chats
      if (res.getResponseCode() === 403) removeChatId_(chatId);
    } catch (e) {
      Logger.log('❌ Telegram error: ' + e.message);
    }
  });
  return anySuccess;
}

Inline Keyboard Buttons

gas/Code.js — buildInlineKeyboard_()
function buildInlineKeyboard_(buttons) {
  return { inline_keyboard: buttons };
}

// Usage example:
sendTelegram_('💸 <b>Expense:</b> 150K\n🛒 Food · 👤 Thanh', {
  reply_markup: buildInlineKeyboard_([[
    { text: '📊 This Month', callback_data: '/status' },
    { text: '📋 Report', callback_data: '/report' }
  ]])
});

Webhook Handler for Bot Commands

gas/Code.js — doPost() + handleTelegramWebhook_()
function doPost(e) {
  try {
    var body = JSON.parse(e.postData.contents);
    if (body.update_id !== undefined) {
      return handleTelegramWebhook_(body);
    }
  } catch(err) {}
  return ContentService.createTextOutput('OK');
}

function handleTelegramWebhook_(update) {
  // Handle inline button presses
  if (update.callback_query) {
    return handleCallbackQuery_(update.callback_query);
  }

  var msg = update.message;
  if (!msg || !msg.text) return ContentService.createTextOutput('OK');

  var chatId = String(msg.chat.id);
  var text = msg.text.trim();
  var firstName = msg.from.first_name || '';
  var reply = '', replyMarkup = null;

  if (text === '/start') {
    addChatId_(chatId);  // Auto-register for notifications
    reply = '🎉 <b>Hello ' + escapeHtml_(firstName) + '!</b>\n\n'
      + '✅ Subscribed to expense notifications.\n\n'
      + '📋 <b>Commands:</b>\n'
      + '/status — Today\'s summary\n'
      + '/report — Monthly report\n'
      + '/stop — Unsubscribe\n';
    replyMarkup = buildInlineKeyboard_([[
      { text: '📊 Status', callback_data: '/status' },
      { text: '📋 Report', callback_data: '/report' }
    ]]);
  } else if (text === '/status') {
    reply = buildQuickStatus_();
    replyMarkup = buildInlineKeyboard_([[
      { text: '📋 Report', callback_data: '/report' },
      { text: '📊 Sheet', url: getSheetUrl_() }
    ]]);
  } else if (text === '/report') {
    reply = buildQuickMonthSummary_();
  }

  // Send reply to the user who sent the command
  if (reply) {
    sendDirectReply_(chatId, reply, replyMarkup);
  }
  return ContentService.createTextOutput('OK');
}

Setting Up the Webhook

gas/Code.js — setupTelegramWebhook()
function setupTelegramWebhook(webAppUrl) {
  var token = getSecret_('TELEGRAM_BOT_TOKEN');
  if (!token) { Logger.log('❌ No bot token'); return; }

  var url = 'https://api.telegram.org/bot' + token + '/setWebhook';
  var res = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({ url: webAppUrl }),
    muteHttpExceptions: true,
  });
  Logger.log('Webhook setup: ' + res.getContentText());
}

After deploying your web app, run:

setupTelegramWebhook('https://script.google.com/macros/s/YOUR_DEPLOY_ID/exec');

Notification Types

🔔Telegram Message Types

Trigger	When	Content	Inline KB
New expense	Real-time	💸 150K · 🛒 Food · 👤 Thanh + budget progress	[📊 This Month]
`telegramDailyReminder`	9 PM daily	Today's total + budget alerts + remaining	[📊 Sheet] [📋 Report]
`telegramWeeklySummary`	Sunday 8 PM	Week total + vs last week + top 3 + debts due	[📊 Sheet]
`telegramBudgetAlert`	Manual	Categories at ≥80% / 100% budget	[📊 Sheet] [📋 Details]
`telegramMonthlyReport`	1st of month	Last month summary + pie chart	[📊 Sheet]

Telegram Helper Functions

gas/Code.js — Utility functions
// Format amount (K) for Telegram: 1500 → "1.5M", 150 → "150K"
function formatK_(val) {
  var n = Number(val) || 0;
  if (Math.abs(n) >= 1000) return (n / 1000).toFixed(1).replace(/\.0$/, '') + 'M';
  return n.toLocaleString('vi-VN') + 'K';
}

// Unicode progress bar for budget tracking
function progressBar_(pct, len) {
  len = len || 10;
  var p = Math.min(100, Math.max(0, pct));
  var filled = Math.round(p / 100 * len);
  var bar = '';
  for (var i = 0; i < filled; i++) bar += '▰';
  for (var i = 0; i < len - filled; i++) bar += '▱';
  return bar + ' ' + Math.round(p) + '%';
}

// Escape HTML for Telegram parse_mode: HTML
function escapeHtml_(text) {
  return String(text).replace(/&/g, '&amp;')
    .replace(/</g, '&lt;').replace(/>/g, '&gt;');
}

Testing the Bot

gas/Code.js — telegramTest()
function telegramTest() {
  var ok = sendTelegram_(
    '🔔 <b>Test successful!</b>\n\n'
    + 'Family Expense Bot is connected.\n'
    + '📅 ' + Utilities.formatDate(new Date(), CONFIG.TIMEZONE, 'dd/MM/yyyy HH:mm')
    + '\n\n🧪 ' + progressBar_(75, 10),
    { reply_markup: buildInlineKeyboard_([[
      { text: '📊 Status', callback_data: '/status' },
      { text: '📋 Report', callback_data: '/report' }
    ]]) }
  );
  Logger.log(ok ? '✅ Telegram test OK' : '❌ FAILED — Check BOT_TOKEN and CHAT_ID');
}

⚛️ Frontend: React + Vite

GAS ↔ Frontend Bridge

The magic that connects the React frontend to GAS backend:

src/utils/gas.js
// Bridge: GAS calls in production, mock data in local dev
const isDev = typeof google === 'undefined' || !google.script;

export function callGas(functionName, ...args) {
  if (isDev) {
    console.log(`[DEV] Mock GAS call: ${functionName}`, args);
    return Promise.resolve(getMockData(functionName));
  }

  return new Promise((resolve, reject) => {
    google.script.run
      .withSuccessHandler(resolve)
      .withFailureHandler(reject)
      [functionName](...args);
  });
}

Local Development

When running npm run dev, the app uses mock/sample data instead of real GAS calls. This gives you hot reload and fast iteration without deploying to GAS every time.

Package Scripts

package.json — scripts
{
  "scripts": {
    "dev": "vite",
    "build": "vite build",
    "preview": "vite preview",
    "push": "cd gas && npx @google/clasp push",
    "deploy": "npm run build && npm run push",
    "open": "cd gas && npx @google/clasp open --webapp"
  }
}

Development Workflow

Command	Description
`npm run dev`	Vite dev server with hot reload (uses mock data)
`npm run build`	Build single HTML file into `gas/`
`npm run deploy`	Build + push to Google Apps Script
`npm run open`	Open the deployed web app

🧠 AI Analysis with Gemini

Gemini API Setup

Get a free API key from Google AI Studio and store it in Script Properties as GEMINI_API_KEY.

The AI tab provides 6 quick analyses + free-form Q&A:

Analysis	Description
🏠 Simple Summary	Plain-language explanation anyone can understand
📊 Monthly Analysis	Income/expenses, budget adherence, reduction suggestions
👶 Baby Expenses	Track baby-related spending: formula, diapers, checkups
💡 Savings Tips	Specific, actionable suggestions tailored to your data
📈 6-Month Trend	Trend analysis, forecasting, early warnings
❤️ Financial Health	Score your financial health + priority actions
💬 Free Q&A	Chat with AI about your personal finances

src/utils/gemini.js — Simplified
const GEMINI_MODEL = 'gemini-2.5-flash-lite';
const GEMINI_BASE_URL = 'https://generativelanguage.googleapis.com/v1beta/models';

export async function askGemini(apiKey, prompt, data) {
  const url = `${GEMINI_BASE_URL}/${GEMINI_MODEL}:generateContent?key=${apiKey}`;

  const systemPrompt = `You are a family financial advisor. 
    Analyze the following expense data and provide insights.
    Data: ${JSON.stringify(data)}`;

  const response = await fetch(url, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      contents: [{ parts: [{ text: systemPrompt + '\n\n' + prompt }] }],
    }),
  });

  const result = await response.json();
  return result.candidates?.[0]?.content?.parts?.[0]?.text || 'No response';
}

Free Tier Limits

Gemini 2.5 Flash Lite free tier: ~30 requests/minute, ~1500 requests/day — more than enough for personal use.

📊 Google Sheets Structure

The app uses 10 sheets as its database:

Sheet	Purpose	Auto-Created
`RawData`	Raw transactions (1 row per entry)	✅
`BaoCao_Thang`	Monthly reports	✅
`BaoCao_Quy`	Quarterly reports	✅
`BaoCao_Nam`	Yearly reports	✅
`MucTieu_TietKiem`	Savings goals	✅
`QuanLy_No`	Debt management	✅
`TaiSan`	Assets (gold, savings, stocks, real estate)	✅
`NganSach`	Budget configuration (versioned by month)	✅
`CaiDat`	System settings (Telegram on/off, etc.)	✅
`CauHinh`	Categories & configuration	✅

RawData Schema

Each expense/income entry becomes a single row:

Column	Field	Example
A	Timestamp	`2026-02-10T14:30:00`
B	Date	`10/02/2026`
C	Month	`2`
D	Year	`2026`
E	Quarter	`Q1`
F	Person	`Thanh`
G	Type	`Expense` / `Income`
H	Group	`Essential` / `Wants` / `Growth`
I	Category	`Food`
J	Full Category	`Food (groceries, supermarket, snacks)`
K	Amount (K)	`150`
L	Amount (VND)	`150000`
M	Note	`Weekly groceries`

🚢 Build & Deploy

Build

Push

Deploy

Setup

Test

One-Command Deploy

# Build React app into single HTML + push to GAS
npm run deploy

This runs:

vite build — bundles React into gas/index.html
clasp push — pushes Code.js, index.html, appsscript.json to GAS

Deploy as Web App

Open GAS Editor: cd gas && clasp open
Click Deploy → New deployment
Select Web app
Execute as: Me | Access: Anyone
Click Deploy → Copy the URL

First-Time Setup

In the GAS Editor, run the SETUP_HE_THONG function:

Open GAS Editor
Select SETUP_HE_THONG from the function dropdown
Click Run ▶️
Grant permissions when prompted

This creates all 10 sheets and installs all time-based triggers automatically.

After Each Code Change

npm run deploy

Then in GAS: Deploy → Manage deployments → Edit → select New version → Deploy

Version Management

Each clasp push updates the code, but the deployed version doesn't change automatically. You must create a new version in the deployment settings after each push.

📱 Daily Usage

📱

Open App

Tap the Home Screen icon (PWA-like)

💸

Enter Expense

Amount → Category → Person → Save (~8 sec)

🔔

Get Notified

Instant Telegram confirmation with budget bar

🌙

9 PM Summary

Automatic daily recap with alerts

📊

Weekly Review

Sunday summary with trends and goals

🧠

AI Insights

Ask Gemini to analyze your spending anytime

🔧 Customization

Entity	Get	Add	Update	Delete
Goals	`getGoals()`	`addGoal(goal)`	`updateGoal(goal)`	`deleteGoal(rowIndex)`
Debts	`getDebts()`	`addDebt(debt)`	`updateDebt(debt)`	`deleteDebt(rowIndex)`
Assets	`getAssets()`	`addAsset(asset)`	`updateAsset(asset)`	`deleteAsset(rowIndex)`
Categories	`getCategories()`	`addCategory(cat)`	`updateCategory(cat)`	`deleteCategory(rowIndex)`
Budget	`getBudget()`	—	`updateBudget(budget)`	—
Settings	`getSettings()`	—	`updateSettings(settings)`	—

🐛 Troubleshooting

clasp push fails

Verify .clasp.json has the correct scriptId and rootDir: "."
Make sure Apps Script API is enabled: https://script.google.com/home/usersettings
Run clasp login again if your session expired

App shows loading forever

In GAS deployment settings, make sure access is set to Anyone
Try creating a new deployment instead of editing an existing one

Telegram bot not responding

Verify TELEGRAM_BOT_TOKEN and TELEGRAM_CHAT_ID in Script Properties
Run telegramTest() in GAS Editor — grant permissions if prompted
Check the Execution Log for errors

Telegram webhook not working

Make sure you called setupTelegramWebhook() with the correct Web App URL
The URL should be the deployed web app URL, not the editor URL
Run removeTelegramWebhook() then setupTelegramWebhook(url) to reset

Local dev shows no data

This is normal! npm run dev uses mock/sample data. Real data only appears in the deployed version on GAS.

AI analysis not working

Verify GEMINI_API_KEY in Script Properties
Ensure you're using a valid key from Google AI Studio
Check the browser console for API errors

After deploy, old version still shows

In GAS Editor: Deploy → Manage deployments → Edit → select New version → Deploy. Each clasp push updates code but the deployed version is pinned.

🎓 Best Practices

🔒Security

Never hardcode secrets in source files
Use Script Properties for all tokens/keys
Set web app access to "Anyone" only if needed
Blocked chats are auto-removed from notification list

⚡Performance

GAS has a 6-minute execution limit per function
Batch sheet reads: get full range, filter in memory
Use Utilities.formatDate() with timezone for consistency
Cache Gemini results to reduce API calls

🛠️Development

Use npm run dev for hot reload with mock data
Deploy only when changes are ready: npm run deploy
Always create a new version after deploying
Keep CATEGORY_MAP in sync between frontend and backend

📊Data

Google Sheets has a 10 million cell limit
1 year ≈ 3,000–5,000 rows — plenty of room
Use sheet-based versioning for budget (snapshot per month)
Back up your Sheet periodically

🚀 Next Steps & Enhancements

📸

Receipt OCR

Scan receipts with Google Vision API and auto-fill expenses

👨‍👩‍👧

Multi-User

Family members can register via /start and get their own notifications

📈

Chart Images

Send pie/bar charts via Telegram using quickchart.io

🌐

Multi-Language

Add English/Vietnamese toggle for the entire app

🔄

Recurring Expenses

Auto-add monthly bills (rent, subscriptions) on schedule

📊

Investment Tracking

Auto-fetch gold prices, stock quotes for real-time portfolio value

📚 Resources & References

🔗 Official Documentation

📖 Learning Resources

🎉 Conclusion

🎊

Your Free Finance App is Ready!

You've built a complete expense tracking system with zero hosting costs. Record expenses, track budgets, get AI insights, and receive Telegram notifications — all from your phone.

Key Takeaways

💸

Zero Cost

GAS + Sheets + Telegram = completely free hosting

⚡

Single File

Vite bundles entire React app into one HTML file for GAS

🤖

Smart Notifications

Telegram bot with inline keyboards and rich formatting

🧠

AI-Powered

Gemini analyzes your spending and gives actionable advice

📊

Auto Reports

Time-based triggers generate daily/weekly/monthly summaries

🛠️

Full CRUD

Manage goals, debts, assets, categories — all from the app

Built with ❤️ using Google Apps Script, React, Vite, Telegram & Gemini AI

Last updated: February 2026 | Version 2.7.0

💸 Zero-Cost Full-Stack Finance App

Overview​

Quick Entry

Rich Dashboard

Telegram Bot

AI Insights

Asset Tracking

100% Free

🎯 Key Features​

🏗️ System Architecture​

🏗️ System Architecture — Hover to Explore

Technology Stack​

Project Structure​

How It Works​

Data Flow​

🚀 Getting Started​

Prerequisites​

📋Before You Begin

Step 1: Install Dependencies​

Step 2: Configure Vite​

Step 3: Create Google Sheet & Apps Script Project​

Step 4: GAS Configuration​

⚡ Backend: Google Apps Script​

Configuration & Constants​

Web App Entry Point​

Adding Expenses​

Helper: Get or Create Sheet​

System Setup Function​

Time-Based Triggers​

⏰Automatic Triggers

🤖 Telegram Bot Integration​

Step 1: Create Your Bot (~2 minutes)​

Step 2: Get Your Chat ID (~1 minute)​

Step 3: Store Credentials Securely​

Sending Messages​

Inline Keyboard Buttons​

Webhook Handler for Bot Commands​

Setting Up the Webhook​

Notification Types​

🔔Telegram Message Types

Telegram Helper Functions​

Testing the Bot​

⚛️ Frontend: React + Vite​

GAS ↔ Frontend Bridge​

Package Scripts​

Development Workflow​

🧠 AI Analysis with Gemini​

Gemini API Setup​

📊 Google Sheets Structure​

RawData Schema​

🚢 Build & Deploy​

One-Command Deploy​

Deploy as Web App​

First-Time Setup​

After Each Code Change​

📱 Daily Usage​

Open App

Enter Expense

Get Notified

9 PM Summary

Weekly Review

AI Insights

🔧 Customization​

Categories​

Budget Configuration​

CRUD API Reference​

🐛 Troubleshooting​

🎓 Best Practices​

🔒Security

⚡Performance

🛠️Development

📊Data

🚀 Next Steps & Enhancements​

Receipt OCR

Multi-User

Chart Images

Multi-Language

Recurring Expenses

Investment Tracking

📚 Resources & References​

Overview

🎯 Key Features

🏗️ System Architecture

Technology Stack

Project Structure

How It Works

Data Flow

🚀 Getting Started

Prerequisites

Step 1: Install Dependencies

Step 2: Configure Vite

Step 3: Create Google Sheet & Apps Script Project

Step 4: GAS Configuration

⚡ Backend: Google Apps Script

Configuration & Constants

Web App Entry Point

Adding Expenses

Helper: Get or Create Sheet

System Setup Function

Time-Based Triggers

🤖 Telegram Bot Integration

Step 1: Create Your Bot (~2 minutes)

Step 2: Get Your Chat ID (~1 minute)

Step 3: Store Credentials Securely

Sending Messages

Inline Keyboard Buttons

Webhook Handler for Bot Commands

Setting Up the Webhook

Notification Types

Telegram Helper Functions

Testing the Bot

⚛️ Frontend: React + Vite

GAS ↔ Frontend Bridge

Package Scripts

Development Workflow

🧠 AI Analysis with Gemini

Gemini API Setup

📊 Google Sheets Structure

RawData Schema

🚢 Build & Deploy

One-Command Deploy

Deploy as Web App

First-Time Setup

After Each Code Change

📱 Daily Usage

🔧 Customization

Categories

Budget Configuration

CRUD API Reference

🐛 Troubleshooting

🎓 Best Practices

🚀 Next Steps & Enhancements

📚 Resources & References

🔗 Official Documentation

📖 Learning Resources

🎉 Conclusion

Key Takeaways