Skip to main content

​
Overview

SpendWisely George uses Google Sheets as its primary database for expense tracking. The integration is powered by Google Apps Script, which provides:
  • RESTful API via Web App deployment
  • Server-side data processing and calculations
  • Settings persistence using Script Properties
  • Transaction history with automatic balance calculation

​
Setup

1

Create Google Sheet

Create a new Google Sheet with the following structure:
DateDescriptionAmountCategoryType
2024-03-15Coffee200FoodDEBIT
2024-03-15Salary50000IncomeCREDIT
Column Headers (Row 1):
  • Column A: Date
  • Column B: Description
  • Column C: Amount
  • Column D: Category (Food, Transport, Tech, Invest, Income, Misc)
  • Column E: Type (CREDIT or DEBIT)
2

Copy Sheet ID

Extract the Sheet ID from the URL:
https://docs.google.com/spreadsheets/d/SHEET_ID_HERE/edit
Update line 14 in the Apps Script:
const SHEET_ID = "YOUR_SHEET_ID_HERE";
3

Install Apps Script

  1. Open your Google Sheet
  2. Go to Extensions → Apps Script
  3. Delete default code
  4. Paste the entire script from google_script.js
  5. Save the project
4

Deploy Web App

  1. Click Deploy → New deployment
  2. Select type: Web app
  3. Configure:
    • Execute as: Me
    • Who has access: Anyone
  4. Click Deploy
  5. Copy the Web App URL
5

Update Frontend

Paste the Web App URL in index.html at line 306:
const SCRIPT_URL = "https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec";

​
API Endpoints

The Apps Script provides both GET and POST endpoints.

​
GET - Fetch Data

GET {WEB_APP_URL}
Response: 
{
  "balance": 12450.50,
  "expenses": [
    {
      "date": "2024-03-15",
      "description": "Coffee",
      "amount": 200,
      "category": "Food",
      "type": "DEBIT"
    }
  ],
  "budget": 350,
  "privacy": true
}
Implementation: google_script.js:41-74
Returns the last 50 transactions for performance. Adjust startRow calculation on line 55 to change this limit.

​
POST - Add/Modify Data

All POST requests use the same endpoint with different action values: 
POST {WEB_APP_URL}
Content-Type: application/json

​
POST Actions

​
Save Settings

{
  "action": "save_settings",
  "budget": 500,
  "privacy": true,
  "geminiKey": "AIza..."
}
Implementation: google_script.js:82-87
Settings are stored in Script Properties, not in the spreadsheet.

​
Delete Transaction

{
  "action": "delete",
  "description": "Coffee",
  "amount": 200
}
Response: 
{
  "status": "Deleted",
  "balance": 12250.50
}
Implementation: google_script.js:90-103

​
Add Transaction (Manual)

{
  "action": "add",
  "description": "Lunch",
  "amount": 350,
  "category": "Food",
  "type": "DEBIT"
}
Response: 
{
  "status": "Success",
  "balance": 11900.50,
  "parsed": {
    "date": "2024-03-15T10:30:00.000Z",
    "description": "Lunch",
    "amount": 350,
    "category": "Food",
    "type": "DEBIT"
  }
}
Implementation: google_script.js:106-119

​
Process with AI

{
  "action": "process_text",
  "text": "Spent 250 on coffee with friends",
  "mode": "add"
}
See Gemini AI Integration for details.

​
Balance Calculation

The script calculates balance server-side by iterating through all transactions: 
function calculateBalance(sheet) {
  const data = sheet.getDataRange().getValues();
  let bal = 0;
  
  // Start from row 1 to skip headers
  for (let i = 1; i < data.length; i++) {
    const amount = parseFloat(data[i][2]); // Column C
    const type = data[i][4]; // Column E
    
    if (!isNaN(amount)) {
      if (type === 'CREDIT') {
        bal += amount;
      } else {
        bal -= amount; // DEBIT
      }
    }
  }
  return bal;
}
Implementation: google_script.js:22-39
Balance is recalculated on every transaction. For sheets with 1000+ rows, consider caching or indexing strategies.

​
Script Properties

Persistent settings are stored using PropertiesService: 
const SCRIPT_PROP = PropertiesService.getScriptProperties();

// Set properties
SCRIPT_PROP.setProperty("DAILY_BUDGET", "500");
SCRIPT_PROP.setProperty("PRIVACY_MODE", "true");
SCRIPT_PROP.setProperty("GEMINI_KEY", "AIza...");

// Get properties
const budget = SCRIPT_PROP.getProperty("DAILY_BUDGET") || 350;
const privacyRaw = SCRIPT_PROP.getProperty("PRIVACY_MODE");
const privacy = privacyRaw === null ? true : (privacyRaw === "true");
Implementation: google_script.js:11,48-52

​
Frontend Integration

The frontend communicates with Apps Script via fetch: 
const res = await fetch(SCRIPT_URL);
const data = await res.json();

console.log(data.balance); // 12450.50
console.log(data.expenses); // Array of transactions
Implementation: index.html:326-462

​
Transaction Categories

Supported categories with their UI icons:
CategoryIconType
Income💰CREDIT
Food🍔DEBIT
Transport⛽DEBIT
Tech⚡DEBIT
Invest📈DEBIT/CREDIT
Misc🛍️DEBIT
Implementation: index.html:518

​
Permissions

The script requires the following OAuth scopes:
  • https://www.googleapis.com/auth/spreadsheets - Read/write spreadsheet data
  • https://www.googleapis.com/auth/script.scriptapp - Access Script Properties
These are automatically requested when deploying the Web App.

​
Rate Limits

Google Apps Script quotas (as of 2024):
ResourceFree Tier Limit
Script runtime6 min/execution
Triggers90 min/day
URL Fetch calls20,000/day
Spreadsheet reads20,000 cells/day
Spreadsheet writes20,000 cells/day
For high-frequency usage, consider upgrading to Google Workspace with higher quotas.

​
Debugging

​
Enable Logging

Add console logs to the script: 
function doPost(e) {
  console.log("Request body:", e.postData.contents);
  const body = JSON.parse(e.postData.contents);
  console.log("Parsed action:", body.action);
  // ...
}
View logs:
  1. Apps Script Editor → Executions tab
  2. Click on any execution to see logs

​
Test in Apps Script

Create test functions: 
function testGet() {
  const result = doGet();
  console.log(result.getContent());
}

function testPost() {
  const mockEvent = {
    postData: {
      contents: JSON.stringify({
        action: "add",
        description: "Test",
        amount: 100,
        category: "Food"
      })
    }
  };
  const result = doPost(mockEvent);
  console.log(result.getContent());
}

​
Troubleshooting

​
”Not Found” Error

Verify Web App deployment:
  1. Check deployment URL is correct
  2. Ensure “Anyone” has access in deployment settings
  3. Redeploy if you made changes to the script

​
Balance Not Updating

The script recalculates balance on every operation. Check:
  1. Column E (Type) has correct values (CREDIT/DEBIT)
  2. Column C (Amount) contains valid numbers
  3. No extra spaces or formatting issues

​
CORS Errors

Google Apps Script Web Apps automatically handle CORS. If you see CORS errors:
  1. Ensure deployment is set to “Anyone can access”
  2. Check that you’re using the correct Web App URL (not the script editor URL)

Build docs developers (and LLMs) love

Get started for freeTalk to us