Building BudgetBuddy: Real-Time Transaction Tracking with Azure Functions & Google Sheets
Brad Malgas
Author
Learn how I built BudgetBuddy, a programmable banking solution that tracks transactions in real-time using JavaScript, Azure Functions, and Google Sheets.
Managing personal finances effectively requires real-time insights into spending habits. To automate this, I built BudgetBuddy—a programmable banking solution that logs every transaction I make, formats the data, and writes it to Google Sheets within seconds. In this post, I'll walk you through how I designed and built this system using JavaScript, Azure Functions, and the Google Sheets API.
Project Overview
BudgetBuddy automates transaction logging using programmable banking capabilities. When I make a purchase, my bank card runs a small script that triggers an Azure Function. This function processes the transaction details and sends them to Google Sheets for structured logging.
How It Works
- Transaction Occurs: When I make a purchase, my bank card executes a JavaScript function.
- Secure API Call: The function securely triggers an Azure Function via an HTTP request.
- Data Processing: The Azure Function extracts key values, formats timestamps, and fills in missing details.
- Logging: The processed transaction is written to Google Sheets via the Google Sheets API.
- Real-Time Insights: The transaction appears in my sheet in under 3 seconds.
Tech Stack & Implementation
1. JavaScript on the Bank Card
My Investec bank card supports programmable banking, allowing JavaScript execution on transactions. Using Fetch API, the script sends an HTTP request to my Azure Function.
fetch('https://my-budgetbuddy.azurewebsites.net/api/logTransaction', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
amount: transaction.amount,
date: transaction.date,
merchant: transaction.merchant,
category: transaction.category
})
});
2. Azure Function for Transaction Processing
The Azure Function processes and formats the transaction details before sending them to Google Sheets.
app.http("logTransaction", {
methods: ["GET", "POST"],
authLevel: "function",
handler: async (request, context) => {
const req = await request.text();
const input = JSON.parse(req);
const transactionData = {
date: input.date,
expense: `${input.merchant?.name ?? "Unknown"} - ${
input.merchant?.city ?? "Unknown"
}`,
amount: input.centsAmount ?? 0,
category: input.merchant?.category ?? "Uncategorized",
};
}
3. Google Sheets Integration
The processed data is sent to Google Sheets via the Google Sheets API.
// Parse the Google service account key
const serviceAccountKey = JSON.parse(
process.env.GOOGLE_SERVICE_ACCOUNT_KEY
);
// Authenticate with Google
context.log("Authenticating with Google...");
const auth = new google.auth.GoogleAuth({
credentials: serviceAccountKey,
scopes: ["https://www.googleapis.com/auth/spreadsheets"],
});
context.log("Authentication successful.");
// Initialize Google Sheets API
const sheets = google.sheets({ version: "v4", auth });
// Append the transaction data to a specific sheet
const spreadsheetId = process.env.SPREADSHEET_ID;
const range = "Sheet1!A1";
context.log("Appending data to Google Sheets:", transactionData);
const response = await sheets.spreadsheets.values.append({
spreadsheetId,
range,
valueInputOption: "RAW",
requestBody: {
values: [
[
transactionData.date,
transactionData.expense,
transactionData.amount / 100,
transactionData.category,
],
],
},
});
context.log("Data appended successfully");
// Send a successful response back to the client
context.res = {
status: 200,
body: "Transaction data successfully recorded!",
};
Challenges & Lessons Learned
- Google Sheets API Complexity: Managing authentication and permissions for API access was initially tricky.
- Data Formatting: Transactions sometimes lacked merchant names or categories, requiring smart defaults.
- Performance Optimization: Keeping the entire process under 3 seconds required efficient API calls and minimal processing overhead.
Future Enhancements
- LLM-Powered Queries: I plan to integrate an AI assistant that analyzes my spending trends and answers questions like "What were my top 3 expenses last month?"
- Automated Categorization: Using machine learning to auto-categorize transactions based on historical patterns.
- Multi-Bank Support: Expanding the solution to work with multiple banking APIs.
ConclusionBudgetBuddy has transformed how I track my spending by eliminating manual transaction logging. This project demonstrates the power of cloud automation, API integrations, and serverless computing in real-world personal finance applications. If you're interested in building something similar, start by exploring Azure Functions and Google Sheets API—it's easier than you think!
An investment in knowledge pays the best interest.
– Benjamin Franklin
Loading reactions…
Loading comments…