How to Build a Personal Finance Tracker with Google Apps Script

Yes, you can build a useful personal finance tracker with Google Apps Script without setting up a separate database or paid service. This guide uses Google Sheets as the data store and Apps Script to create the workbook structure, add transactions through a menu, and generate a monthly income-and-expense summary.

The result is intentionally simple: you control the categories and data, while the script removes repetitive setup and reporting work. It is suitable for personal budgeting, household expenses, or a small shared finance log. It is not intended to replace accounting software, investment reporting, tax records, or bank-grade security.

What this personal finance tracker will do

The tracker has three sheets:

• Transactions: One row per income or expense.
• Categories: Your editable list of income and expense categories.
• Dashboard: A selected month’s income, expenses, net cash flow, and spending by category.

Apps Script adds a custom Finance Tracker menu with actions to initialize the workbook, enter a transaction, and refresh the dashboard. You can still edit the Transactions sheet directly when importing or pasting several records.

Important: Do not paste bank passwords, card numbers, full account numbers, or authentication tokens into this spreadsheet. Use short account labels such as “Checking,” “Credit Card,” or “Cash.”

Before you start

1. Create a new Google Sheet at sheets.new.
2. Give it a descriptive name, such as Personal Finance Tracker.
3. Open Extensions > Apps Script.
4. Remove any starter code from the editor and paste the script below into the default Code.gs file.
5. Save the project, then return to the spreadsheet and reload it.

The first time you use a menu action, Google will ask you to authorize the script. Review the requested spreadsheet permission and authorize it only if you trust the code and own the spreadsheet.

How the tracker is organized

Each transaction has a date, type, category, description, amount, account, and optional notes. The amount is always entered as a positive number. The Type column determines whether the amount contributes to income or expenses. This avoids accidental double negatives, such as entering an expense as both “Expense” and -25.

Column	Example	Purpose
Date	2025-03-15	When the transaction occurred
Type	Expense	Income or Expense
Category	Groceries	Used for category totals
Description	Weekly shopping	Human-readable detail
Amount	84.50	Positive transaction value
Account	Checking	Optional account label
Notes	Family grocery trip	Optional context

Paste the Apps Script code

Replace the contents of Code.gs with this code. The setupFinanceTracker function creates the sheets and sample category lists. The script does not create fake transactions or connect to your bank.

const SHEETS = {
  transactions: 'Transactions',
  categories: 'Categories',
  dashboard: 'Dashboard'
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Finance Tracker')
    .addItem('Set up workbook', 'setupFinanceTracker')
    .addItem('Add transaction', 'addTransaction')
    .addItem('Refresh dashboard', 'refreshDashboard')
    .addToUi();
}

function setupFinanceTracker() {
  const ss = SpreadsheetApp.getActive();
  const transactions = getOrCreateSheet_(ss, SHEETS.transactions);
  const categories = getOrCreateSheet_(ss, SHEETS.categories);
  const dashboard = getOrCreateSheet_(ss, SHEETS.dashboard);

  if (transactions.getLastRow() === 0) {
    transactions.appendRow([
      'Date', 'Type', 'Category', 'Description',
      'Amount', 'Account', 'Notes'
    ]);
  }
  transactions.setFrozenRows(1);
  transactions.getRange('A:A').setNumberFormat('yyyy-mm-dd');
  transactions.getRange('E:E').setNumberFormat('#,##0.00');

  if (categories.getLastRow() === 0) {
    categories.getRange('A1:B1').setValues([['Expense Categories', 'Income Categories']]);
    categories.getRange('A2:A9').setValues([
      ['Housing'], ['Utilities'], ['Groceries'], ['Transport'],
      ['Health'], ['Entertainment'], ['Shopping'], ['Other']
    ]);
    categories.getRange('B2:B5').setValues([
      ['Salary'], ['Freelance'], ['Interest'], ['Other Income']
    ]);
    categories.setFrozenRows(1);
  }

  if (dashboard.getRange('A1').getValue() === '') {
    dashboard.getRange('A1:B1').setValues([['Report month', new Date()]]);
    dashboard.getRange('B1').setNumberFormat('yyyy-mm');
  }

  [transactions, categories, dashboard].forEach(sheet => {
    sheet.autoResizeColumns(1, Math.max(sheet.getLastColumn(), 2));
  });
  refreshDashboard();
  SpreadsheetApp.getUi().alert('Finance tracker is ready. Use the Finance Tracker menu to add a transaction.');
}

function addTransaction() {
  const ui = SpreadsheetApp.getUi();
  const answers = [
    ['Date (YYYY-MM-DD)', Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd')],
    ['Type (Income or Expense)', 'Expense'],
    ['Category', ''],
    ['Description', ''],
    ['Amount', ''],
    ['Account (optional)', ''],
    ['Notes (optional)', '']
  ];

  const values = answers.map(([label, defaultValue]) => {
    const result = ui.prompt(label, 'Enter a value:', ui.ButtonSet.OK_CANCEL);
    if (result.getSelectedButton() !== ui.Button.OK) {
      throw new Error('Transaction entry was cancelled.');
    }
    return result.getResponseText().trim() || defaultValue;
  });

  const date = new Date(values[0]);
  const type = values[1].toLowerCase();
  const amount = Number(values[4]);
  if (Number.isNaN(date.getTime()) || !['income', 'expense'].includes(type)) {
    throw new Error('Use a valid date and enter Type as Income or Expense.');
  }
  if (!Number.isFinite(amount) || amount <= 0) {
    throw new Error('Amount must be a number greater than zero.');
  }

  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEETS.transactions);
  sheet.appendRow([
    date,
    type.charAt(0).toUpperCase() + type.slice(1),
    values[2], values[3], amount, values[5], values[6]
  ]);
  refreshDashboard();
  ui.alert('Transaction added and dashboard refreshed.');
}

function refreshDashboard() {
  const ss = SpreadsheetApp.getActive();
  const dashboard = ss.getSheetByName(SHEETS.dashboard);
  const transactions = ss.getSheetByName(SHEETS.transactions);
  if (!dashboard || !transactions) {
    throw new Error('Run Set up workbook first.');
  }

  const monthValue = dashboard.getRange('B1').getValue() || new Date();
  const monthDate = new Date(monthValue);
  if (Number.isNaN(monthDate.getTime())) {
    throw new Error('Dashboard cell B1 must contain a valid date.');
  }
  const year = monthDate.getFullYear();
  const month = monthDate.getMonth();
  const rows = transactions.getLastRow() > 1
    ? transactions.getRange(2, 1, transactions.getLastRow() - 1, 7).getValues()
    : [];

  let income = 0;
  let expenses = 0;
  const byCategory = {};
  rows.forEach(row => {
    const date = row[0];
    const type = String(row[1]).toLowerCase();
    const category = String(row[2] || 'Uncategorized');
    const amount = Number(row[4]);
    if (!(date instanceof Date) || Number.isNaN(amount)) return;
    if (date.getFullYear() !== year || date.getMonth() !== month) return;
    if (type === 'income') income += amount;
    if (type === 'expense') {
      expenses += amount;
      byCategory[category] = (byCategory[category] || 0) + amount;
    }
  });

  const categoryRows = Object.entries(byCategory)
    .sort((a, b) => b[1] - a[1]);
  dashboard.getRange('A3:B7').setValues([
    ['Income', income],
    ['Expenses', expenses],
    ['Net cash flow', income - expenses],
    ['Transaction count', rows.filter(row => {
      const date = row[0];
      return date instanceof Date && date.getFullYear() === year && date.getMonth() === month;
    }).length],
    ['', '']
  ]);
  dashboard.getRange('A9:B9').setValues([['Expense category', 'Amount']]);
  dashboard.getRange('A10:B100').clearContent();
  if (categoryRows.length) dashboard.getRange(10, 1, categoryRows.length, 2).setValues(categoryRows);
  dashboard.getRange('B3:B5').setNumberFormat('#,##0.00');
  dashboard.getRange('B10:B100').setNumberFormat('#,##0.00');
  dashboard.autoResizeColumns(1, 2);
}

function getOrCreateSheet_(ss, name) {
  return ss.getSheetByName(name) || ss.insertSheet(name);
}

Initialize the workbook

1. Click Save in Apps Script.
2. Return to the spreadsheet and reload the browser tab.
3. Open the new Finance Tracker menu.
4. Choose Set up workbook.
5. Authorize the script when prompted.

You should now see the three sheets. Open Categories and replace the example categories with labels that match your real spending. Keep expense categories in column A and income categories in column B. The current script does not enforce those lists with dropdowns, so consistent spelling matters when the dashboard groups transactions.

Screenshot needed: Capture the Google Sheet after setup, showing the Transactions, Categories, and Dashboard tabs plus the Finance Tracker custom menu. This helps readers confirm that initialization worked.

Add and review transactions

Choose Finance Tracker > Add transaction. The script asks for each field in sequence. Enter the amount as a positive number, such as 42.75. Use Income or Expense for the type. After validation, the row is appended to Transactions and the dashboard is refreshed.

For larger imports, paste rows directly below the header in Transactions. Dates must be recognized as dates by Sheets, the Type value must be Income or Expense, and Amount must be numeric. After importing, choose Refresh dashboard.

Choose a month for the dashboard

Open Dashboard and change cell B1 to any date in the month you want to review. For example, entering 2025-03-01 selects March 2025. Then choose Finance Tracker > Refresh dashboard.

The report reads all transaction rows in one batch, filters them by year and month, and writes the totals back in one operation. The result includes:

• Total income for the selected month
• Total expenses for the selected month
• Net cash flow, calculated as income minus expenses
• Number of transactions in that month
• Expense totals sorted from largest to smallest category

How the Apps Script works

The custom menu

onOpen() runs when the spreadsheet opens and adds menu items. It does not process financial data by itself. If the menu is missing after editing the code, reload the spreadsheet or run onOpen manually from the Apps Script editor.

Validation before saving

addTransaction() checks the date, transaction type, and amount before writing a row. This is deliberately basic validation. It prevents the most common input errors but does not verify that a category exists or that an account balance matches your bank.

Batch processing

refreshDashboard() uses getValues() once, calculates the report in memory, and writes the results back in batches. This is more maintainable and generally more efficient than reading and writing individual cells inside a loop.

Visual plan: A simple workflow diagram can show the data path: Add transaction menu → Transactions sheet → monthly filter → Dashboard summary.

Test the tracker safely

Use a few clearly labeled test rows before entering your real records:

1. Add an expense of 25 in a category such as Groceries.
2. Add income of 100 in a category such as Freelance.
3. Set Dashboard!B1 to the test rows’ month.
4. Refresh the dashboard.
5. Confirm that Expenses is 25, Income is 100, and Net cash flow is 75.
6. Delete the test rows after verifying the result.

Also test a transaction from a different month. It should remain in Transactions but should not affect the selected monthly report.

Common problems and fixes

The Finance Tracker menu does not appear

Reload the spreadsheet after saving the project. If it still does not appear, open Apps Script, select onOpen, click Run, and then return to the spreadsheet. You may need to authorize the project first.

The dashboard shows zero

Check that Dashboard!B1 contains a real date, not text that only looks like a date. Then check the transaction date, Type spelling, and Amount column. A transaction in another month is intentionally excluded.

Categories are split unexpectedly

“Dining,” “dining,” and “Dining ” can become separate labels depending on how the data was entered. Standardize category names before refreshing. For a more controlled version, add data validation dropdowns to the Category column using the Categories sheet.

The script stops with a permission or authorization error

The script needs access to the spreadsheet it is bound to. Run a menu action from the spreadsheet, review the authorization prompt, and confirm that you are signed into the intended Google account. In a shared file, the account that runs the script and the account that owns the file can affect authorization and trigger behavior.

The report becomes slow as the sheet grows

Archive old transactions into a separate spreadsheet or add a date range limit if the workbook becomes very large. Avoid adding a time-driven trigger that refreshes unnecessarily often. Apps Script executions are subject to service limits that can change, so check Google’s current Apps Script quotas if you plan to process substantial data.

Optional improvements

• Dropdowns: Add data validation for Type, Category, and Account to reduce inconsistent entries.
• Charts: Create a chart from Dashboard!A9:B100 to visualize spending by category.
• Recurring bills: Add a separate Bills sheet with due dates and payment status. If that is your main requirement, the Subscription & Recurring Bills Tracker is a more specialized starting point.
• Monthly snapshots: Copy dashboard results to a history sheet if you need to preserve reports after changing B1.
• Web interface: Replace the prompt-based entry flow with an Apps Script HTML Service form if several people need a friendlier input screen.
• Bank imports: Import CSV files manually rather than storing bank credentials in Apps Script. Automatic bank synchronization usually requires a separate, security-reviewed financial data provider.

Security and maintenance considerations

Keep the spreadsheet’s sharing settings restricted to people who need access. A Google Sheet is convenient, but it is not a specialized accounting vault. Review editors periodically, avoid publishing the sheet or script as an unrestricted web app, and do not place secrets in the source code.

Make a copy of the spreadsheet before changing the data structure or code. If you rename a sheet or column, update the corresponding constants and ranges. A simple change log in a Notes sheet can also make it easier to understand why categories or calculations changed.

Frequently asked questions

Can Apps Script connect directly to my bank?

Not safely by default. Apps Script can call external APIs, but a bank connection requires an appropriate provider, authentication flow, privacy review, and handling for duplicate or pending transactions. Manual CSV import is a safer first version for a personal tracker.

Can I use this tracker for multiple people?

Yes, if everyone follows the same categories and account-label conventions. Restrict spreadsheet access and decide whether users may edit all transactions. For stronger separation, give each person a separate input sheet or separate spreadsheet and consolidate approved data.

Can I automate a monthly email report?

Yes. A time-driven trigger could run a report function and use GmailApp to send the result, but that adds email permissions and requires careful handling of personal financial information. Start with manual refreshes until the totals are reliable.

Does the script calculate account balances?

No. It calculates monthly income, expenses, net cash flow, and category totals. It does not reconcile transactions against a bank statement or calculate opening and closing balances by account.

Next step

Set up the workbook, customize Categories, and test it with a few temporary rows. Once the monthly totals match your expectations, remove the test data and begin recording real transactions consistently. The value of this tracker comes less from complex automation than from using the same categories and review routine every month.