Sure. Let’s see how I can format this nicely. I couldn’t see how to attach scripts so I just pasted them below with Headers for each one. Ugh, hope it’s enough to copy each one separately.
BTW you have to create that Recurring Transactions sheet by hand first:
Recurring Transactions sheet layout:
Row 1 = instruction text (skipped)
Row 2 = column headers
Row 3+ = data
I don’t have a script to create that, although I could have. Hmm.
Scripts:
-
calculateBudgetWithRecurring.js — builds the Budget with Recurring sheet
-
setupCategoryDropdown.js — syncs the Category dropdown
-
suggestRecurringTransactions.js — auto-detects recurring bills
Triggers needed:
suggestRecurringTransactions has no trigger — it’s manual only (run it, review the staging sheet, then run commitSuggestedRecurring()).
The onCategoriesEdit one is easy to miss since it’s an installable trigger (not a simple one), so whoever sets this up needs to manually register it in Apps Script → Triggers.
calculateBudgetWithRecurring.js
/**
@file calculateBudgetWithRecurring.js
@description Calculates budget analysis including recurring bills for the current month.
Creates/updates the “Budget with Recurring” sheet with:
- Upcoming unpaid bills for the remainder of the current month
- Monthly summary (budget vs actual vs recurring)
- Category-by-category breakdown sorted descending by Safe to Spend
(most overbudget first)
- Safe to Spend column with a red → yellow → green color scale
- Monthly budget gauge chart next to the Monthly Summary section
@schedule Daily (automated trigger)
@version 2026-03-04 v2.9
Change Log:
2026-03-04 v2.9 - Adjusted gauge offsetX to 29px (½ inch minus a small nudge left).
2026-03-04 v2.8 - Shifted gauge right by 36px (½ inch). Center-justified label.
2026-03-04 v2.7 - Locked gauge size to 150x150 (confirmed perfect fit).
2026-03-04 v2.6 - Fixed label appearing twice: setValue on a multi-cell range sets
each cell individually. Now merges D+E before setValue so the text
appears exactly once.
2026-03-04 v2.5 - Raised gauge anchor by one row so it starts on the “Total
Upcoming Bills (This Month)” row instead of the blank row below it.
2026-03-04 v2.4 - Moved gauge anchor to col D (left-aligned). Moved dollar label
to safeToSpendRow+1 (was writing into Safe to Spend row causing
duplicate display and being 1 row too high).
2026-03-04 v2.3 - Shrunk gauge to 140x140. Moved dollar label up one row (to Safe
to Spend row), left-aligned starting at col D, no merge needed.
2026-03-04 v2.2 - Repositioned gauge to fit between “Total Upcoming Bills (This Month)”
and the row below “Safe to Spend”, right-aligned to the “Days Until”
column (col E). Anchor at col 6 with negative offsetX so chart’s
right edge aligns flush with col E’s right edge.
2026-03-03 v2.1 - Added monthly budget % gauge chart next to the Monthly Summary
section. Gauge shows (actual + upcoming) / budget so it reflects
projected total month spend. Chart data in hidden off-screen col 7.
2026-02-28 v2.0 - Sorted Category Budget Summary descending by Safe to Spend
(most overbudget first). Replaced per-cell font color logic with
a smooth red → yellow → green color scale conditional format rule.
2026-02-01 v1.0 - Initial version.
*/
// =============================================================================
// MAIN FUNCTION
// =============================================================================
/**
- Calculates budget analysis including recurring bills for the current month.
- @throws {Error} If required sheets (Recurring Transactions, Transactions, Categories) are missing
- @throws {Error} If current month’s budget column cannot be found in Categories sheet
*/
function calculateBudgetWithRecurring() {
// ===== CONFIGURATION =====
const COLORS = {
headerPrimary: ‘#4285f4’,
headerSuccess: ‘#34a853’,
headerWarning: ‘#f39c12’,
totalsRow: ‘#fbbc04’,
positive: ‘#27ae60’,
negative: ‘#e74c3c’,
white: ‘#ffffff’,
black: ‘#000000’,
scaleMin: ‘#e74c3c’, // Red — most overbudget
scaleMid: ‘#ffeb3b’, // Yellow — at zero / breakeven
scaleMax: ‘#27ae60’ // Green — most underbudget
};
const HEADER_ROW_INDEX = 1;
const DATA_START_ROW = 2;
// ========================
const ss = SpreadsheetApp.getActiveSpreadsheet();
const recurringSheet = ss.getSheetByName(‘Recurring Transactions’);
const transactionsSheet = ss.getSheetByName(‘Transactions’);
const categoriesSheet = ss.getSheetByName(‘Categories’);
const outputSheet = ss.getSheetByName(‘Budget with Recurring’) || ss.insertSheet(‘Budget with Recurring’);
if (!recurringSheet) throw new Error(‘Required sheet “Recurring Transactions” not found.’);
if (!transactionsSheet) throw new Error(‘Required sheet “Transactions” not found.’);
if (!categoriesSheet) throw new Error(‘Required sheet “Categories” not found.’);
const today = new Date();
const currentMonth = today.getMonth();
const currentYear = today.getFullYear();
// ── Recurring Transactions ──────────────────────────────────────────────────
const recurringData = recurringSheet.getDataRange().getValues();
const recurringHeaders = recurringData[HEADER_ROW_INDEX];
const descCol = recurringHeaders.indexOf(‘Description’);
const categoryCol = recurringHeaders.indexOf(‘Category’);
const amountCol = recurringHeaders.indexOf(‘Amount’);
const freqCol = recurringHeaders.indexOf(‘Frequency’);
const dayTypeCol = recurringHeaders.indexOf(‘Day Type’);
const monthsCol = recurringHeaders.indexOf(‘Months’);
const dayCol = recurringHeaders.indexOf(‘Day of Month’);
const startDateCol = recurringHeaders.indexOf(‘Start Date’);
const endDateCol = recurringHeaders.indexOf(‘End Date’);
// ── Transactions ────────────────────────────────────────────────────────────
const transData = transactionsSheet.getDataRange().getValues();
const transHeaders = transData[0];
const transDateCol = transHeaders.indexOf(‘Date’);
const transDescCol = transHeaders.indexOf(‘Description’);
const transAmountCol = transHeaders.indexOf(‘Amount’);
const transCategoryCol = transHeaders.indexOf(‘Category’);
// ── Categories / Budgets ────────────────────────────────────────────────────
const categoriesData = categoriesSheet.getDataRange().getValues();
const catHeaders = categoriesData[0];
const catNameCol = 0;
const targetDate = new Date(currentYear, currentMonth, 1);
const currentMonthHeader = Utilities.formatDate(targetDate, Session.getScriptTimeZone(), ‘MMM yyyy’);
let budgetCol = -1;
for (let i = 0; i < catHeaders.length; i++) {
const header = catHeaders[i];
if (header instanceof Date &&
header.getFullYear() === currentYear &&
header.getMonth() === currentMonth) {
budgetCol = i;
break;
}
}
if (budgetCol === -1) {
throw new Error(
Could not find budget column for ${currentMonthHeader} in Categories sheet. +
Please ensure there is a column with a date header for the current month.
);
}
const budgets = {};
for (let i = 1; i < categoriesData.length; i++) {
const catName = categoriesData[i][catNameCol];
const budget = parseFloat(categoriesData[i][budgetCol]) || 0;
if (catName && budget > 0) budgets[catName] = budget;
}
if (Object.keys(budgets).length === 0) {
Logger.log(Warning: No budgets found for ${currentMonthHeader}. Check your Categories sheet.);
}
// ── Actual Spending by Category (current month only) ────────────────────────
const actualSpending = {};
for (let i = 1; i < transData.length; i++) {
const transDate = new Date(transData[i][transDateCol]);
const amount = parseFloat(transData[i][transAmountCol]) || 0;
const category = transData[i][transCategoryCol];
if (transDate.getMonth() === currentMonth &&
transDate.getFullYear() === currentYear &&
category) {
if (!actualSpending[category]) actualSpending[category] = 0;
if (amount < 0) {
actualSpending[category] += Math.abs(amount);
} else if (amount > 0) {
actualSpending[category] -= amount;
}
}
}
// ── Process Recurring Transactions ─────────────────────────────────────────
const upcomingBills = ;
const categoryUpcoming = {};
for (let i = DATA_START_ROW; i < recurringData.length; i++) {
const row = recurringData[i];
const description = row[descCol];
const category = row[categoryCol];
const amount = parseFloat(row[amountCol]) || 0;
const frequency = row[freqCol];
const dayType = row[dayTypeCol];
const months = row[monthsCol];
const day = row[dayCol];
const startDate = row[startDateCol] ? new Date(row[startDateCol]) : null;
const endDate = row[endDateCol] ? new Date(row[endDateCol]) : null;
if (!description || !frequency) continue;
if (endDate && endDate < today) continue;
const expectedDates = generateExpectedDates(
frequency, dayType, months, day, currentMonth, currentYear, startDate
);
for (const expectedDate of expectedDates) {
const isPaid = checkIfPaid(
transData, transDateCol, transDescCol, transAmountCol, description, expectedDate
);
if (!isPaid) {
const daysUntil = Math.ceil((expectedDate - today) / (1000 * 60 * 60 * 24));
upcomingBills.push({ description, category, amount, expectedDate, daysUntil });
if (!categoryUpcoming[category]) categoryUpcoming[category] = 0;
categoryUpcoming[category] += amount;
}
}
}
Logger.log(Found ${upcomingBills.length} unpaid recurring bill(s) for ${currentMonthHeader});
// ── Totals ──────────────────────────────────────────────────────────────────
let totalBudget = 0;
let totalActual = 0;
let totalUpcomingAll = 0;
let totalRemaining = 0;
for (const category in budgets) {
const budget = budgets[category];
const actual = actualSpending[category] || 0;
const upcoming = categoryUpcoming[category] || 0;
const remaining = budget - actual - upcoming;
totalBudget += budget;
totalActual += actual;
totalUpcomingAll += upcoming;
totalRemaining += remaining;
}
// ── Build Output Array ──────────────────────────────────────────────────────
const output = [
[‘Budget Analysis with Recurring Bills - ’ + currentMonthHeader, ‘’, ‘’, ‘’, ‘’],
[‘As of: ’ + Utilities.formatDate(today, Session.getScriptTimeZone(), ‘MMM dd, yyyy HH:mm’), ‘’, ‘’, ‘’, ‘’],
[’’, ‘’, ‘’, ‘’, ‘’],
[‘Upcoming Recurring Bills (Rest of Month)’, ‘’, ‘’, ‘’, ‘’],
[‘Description’, ‘Category’, ‘Amount’, ‘Expected Date’, ‘Days Until’]
];
let totalUpcoming = 0;
const endOfMonth = new Date(currentYear, currentMonth + 1, 0);
upcomingBills
.filter(bill => bill.expectedDate <= endOfMonth)
.sort((a, b) => a.expectedDate - b.expectedDate)
.forEach(bill => {
totalUpcoming += bill.amount;
output.push([bill.description, bill.category, bill.amount, bill.expectedDate, bill.daysUntil]);
});
output.push([‘’, ‘’, ‘’, ‘’, ‘’]);
output.push([‘Total Upcoming Bills (This Month)’, ‘’, totalUpcoming, ‘’, ‘’]);
output.push([‘’, ‘’, ‘’, ‘’, ‘’]);
output.push([‘MONTH SUMMARY’, ‘’, ‘’, ‘’, ‘’]);
output.push([‘Total Budget’, ‘’, totalBudget, ‘’, ‘’]);
output.push([‘Total Actual Spent’, ‘’, totalActual, ‘’, ‘’]);
output.push([‘Total Upcoming Bills’, ‘’, totalUpcomingAll, ‘’, ‘’]);
output.push([‘Safe to Spend’, ‘’, totalRemaining, ‘’, ‘’]);
output.push([‘’, ‘’, ‘’, ‘’, ‘’]);
output.push([‘’, ‘’, ‘’, ‘’, ‘’]);
output.push([‘Category Budget Summary’, ‘’, ‘’, ‘’, ‘’]);
output.push([‘Category’, ‘Budget’, ‘Actual Spent’, ‘Upcoming Bills’, ‘Safe to Spend’]);
const categoryRows = ;
for (const category in budgets) {
const budget = budgets[category];
const actual = actualSpending[category] || 0;
const upcoming = categoryUpcoming[category] || 0;
const remaining = budget - actual - upcoming;
categoryRows.push([category, budget, actual, upcoming, remaining]);
}
categoryRows.sort((a, b) => a[4] - b[4]);
categoryRows.forEach(row => output.push(row));
output.push([‘’, ‘’, ‘’, ‘’, ‘’]);
output.push([‘TOTALS’, totalBudget, totalActual, totalUpcomingAll, totalRemaining]);
// ── Write to Sheet ──────────────────────────────────────────────────────────
outputSheet.clear();
outputSheet.clearConditionalFormatRules();
outputSheet.getRange(1, 1, output.length, 5).setValues(output);
// ── Formatting ──────────────────────────────────────────────────────────────
outputSheet.getRange(‘A1:E1’)
.setFontWeight(‘bold’).setFontSize(14)
.setBackground(COLORS.headerPrimary).setFontColor(COLORS.white);
outputSheet.getRange(‘A4:E4’)
.setFontWeight(‘bold’)
.setBackground(COLORS.headerSuccess).setFontColor(COLORS.white);
outputSheet.getRange(‘A5:E5’)
.setFontWeight(‘bold’)
.setBorder(false, false, true, false, false, false, COLORS.black, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
const summaryStartRow = 6 + upcomingBills.filter(b => b.expectedDate <= endOfMonth).length + 3;
outputSheet.getRange(summaryStartRow, 1, 1, 5)
.setFontWeight(‘bold’)
.setBackground(COLORS.headerWarning).setFontColor(COLORS.white);
outputSheet.getRange(summaryStartRow, 4, 1, 2).setBackground(null);
outputSheet.getRange(summaryStartRow + 1, 1, 5, 3).setNumberFormat(‘$#,##0.00’);
const safeToSpendRow = summaryStartRow + 4;
outputSheet.getRange(safeToSpendRow, 1, 1, 3)
.setBackground(‘#ffeb3b’).setFontWeight(‘bold’).setFontSize(12);
outputSheet.getRange(safeToSpendRow, 3, 1, 1)
.setFontColor(totalRemaining >= 0 ? COLORS.positive : COLORS.negative)
.setFontSize(14).setFontWeight(‘bold’);
const categorySummaryHeaderRow = summaryStartRow + 7;
outputSheet.getRange(categorySummaryHeaderRow, 1, 1, 5)
.setFontWeight(‘bold’)
.setBackground(COLORS.headerSuccess).setFontColor(COLORS.white);
outputSheet.getRange(categorySummaryHeaderRow + 1, 1, 1, 5)
.setFontWeight(‘bold’)
.setBorder(false, false, true, false, false, false, COLORS.black, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
outputSheet.getRange(output.length, 1, 1, 5)
.setFontWeight(‘bold’).setBackground(‘#ffeb3b’).setFontColor(COLORS.black);
outputSheet.getRange(output.length, 2, 1, 4).setNumberFormat(‘$#,##0.00’);
const upcomingBillsCount = upcomingBills.filter(b => b.expectedDate <= endOfMonth).length;
if (upcomingBillsCount > 0) {
outputSheet.getRange(6, 3, upcomingBillsCount, 1).setNumberFormat(‘$#,##0.00’);
outputSheet.getRange(6, 4, upcomingBillsCount, 1).setNumberFormat(‘mmm dd’);
}
const totalUpcomingRow = 6 + upcomingBillsCount + 1;
outputSheet.getRange(totalUpcomingRow, 3, 1, 1).setNumberFormat(‘$#,##0.00’);
const numCategories = categoryRows.length;
outputSheet.getRange(categorySummaryHeaderRow + 2, 2, numCategories + 1, 4).setNumberFormat(‘$#,##0.00’);
const safeToSpendStartRow = categorySummaryHeaderRow + 2;
const colorScaleRange = outputSheet.getRange(safeToSpendStartRow, 5, numCategories, 1);
const colorScaleRule = SpreadsheetApp.newConditionalFormatRule()
.setGradientMinpoint(COLORS.scaleMin)
.setGradientMidpointWithValue(COLORS.scaleMid, SpreadsheetApp.InterpolationType.NUMBER, ‘0’)
.setGradientMaxpoint(COLORS.scaleMax)
.setRanges([colorScaleRange])
.build();
const existingRules = outputSheet.getConditionalFormatRules();
existingRules.push(colorScaleRule);
outputSheet.setConditionalFormatRules(existingRules);
outputSheet.autoResizeColumns(1, 5);
outputSheet.setColumnWidth(4, 110);
// ── Gauge Chart: projected month spend vs budget ────────────────────────────
renderBudgetGauge_(outputSheet, totalUpcomingRow, safeToSpendRow, totalActual + totalUpcomingAll, totalBudget);
Logger.log(‘Budget analysis complete’);
}
// =============================================================================
// GAUGE CHART
// =============================================================================
/**
- Renders a monthly budget % gauge positioned between the "Total Upcoming Bills
- (This Month)" row and the row below “Safe to Spend”, right-aligned to col E
- (“Days Until”).
- Positioning strategy:
-
- Anchor row : totalUpcomingRow + 1 (blank row just below Total Upcoming Bills)
-
- Anchor col : 4 (col D) with offsetX 0 → left edge of gauge aligns with col D
-
-
- offsetY : 2 → small top padding inside the anchor row
- The vertical span from totalUpcomingRow+1 through safeToSpendRow+1 is always
- exactly 6 rows, so GAUGE_HEIGHT is sized to fill that space comfortably.
- Chart data written to hidden off-screen col 7 (G).
- @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - The output sheet
- @param {number} totalUpcomingRow - Sheet row of “Total Upcoming Bills (This Month)”
- @param {number} safeToSpendRow - Sheet row of “Safe to Spend”
- @param {number} projectedSpend - totalActual + totalUpcomingAll
- @param {number} totalBudget - Total monthly budget
*/
function renderBudgetGauge_(sheet, totalUpcomingRow, safeToSpendRow, projectedSpend, totalBudget) {
const GAUGE_WIDTH = 150;
const GAUGE_HEIGHT = 150;
const pct = totalBudget > 0 ? Math.round((projectedSpend / totalBudget) * 100) : 0;
const dataCol = 7; // col G — safely off the 5-col content area
Logger.log(Budget gauge: $${Math.round(projectedSpend)} projected of $${Math.round(totalBudget)} = ${pct}%);
// Write gauge data to off-screen column
sheet.getRange(totalUpcomingRow, dataCol, 1, 2).setValues([[‘Label’, ‘Value’]]);
sheet.getRange(totalUpcomingRow + 1, dataCol, 1, 2).setValues([[‘Mo. Budget %’, pct ]]);
sheet.hideColumns(dataCol, 2);
const chartRange = sheet.getRange(totalUpcomingRow, dataCol, 2, 2);
// Remove existing charts to avoid duplicates on re-run
sheet.getCharts().forEach(c => sheet.removeChart(c));
// Anchor at col 4 (D) with offsetX=0 so chart’s left edge aligns with col D.
const chart = sheet.newChart()
.setChartType(Charts.ChartType.GAUGE)
.addRange(chartRange)
.setOption(‘min’, 0)
.setOption(‘max’, 150)
.setOption(‘greenFrom’, 0)
.setOption(‘greenTo’, 99)
.setOption(‘yellowFrom’, 99)
.setOption(‘yellowTo’, 101)
.setOption(‘redFrom’, 101)
.setOption(‘redTo’, 150)
.setOption(‘width’, GAUGE_WIDTH)
.setOption(‘height’, GAUGE_HEIGHT)
.setPosition(totalUpcomingRow, 4, 23, 2)
.build();
sheet.insertChart(chart);
// Dollar label — one row below Safe to Spend, cols D+E merged for full display
const labelRow = safeToSpendRow + 1;
sheet.getRange(labelRow, 4, 1, 2).merge()
.setValue(‘Projected: $’ + Math.round(projectedSpend).toLocaleString() + ’ of $’ + Math.round(totalBudget).toLocaleString())
.setFontSize(8)
.setFontColor(‘#666666’)
.setHorizontalAlignment(‘center’)
.setVerticalAlignment(‘middle’);
}
// =============================================================================
// HELPER FUNCTIONS
// =============================================================================
/**
- Generates expected dates for a recurring transaction based on frequency and rules.
- @param {string} frequency - ‘Monthly’, ‘Quarterly’, ‘Yearly’, etc.
- @param {string} dayType - ‘Exact Day’ or ‘Last Day of Month’
- @param {string|number} months - Comma-separated month numbers for quarterly/yearly
- @param {number} day - Day of month (1-31)
- @param {number} currentMonth - Current month (0-11)
- @param {number} currentYear - Current year (YYYY)
- @param {Date} startDate - Optional start date for the recurring transaction
- @returns {Date} Array of expected dates for the current period
*/
function generateExpectedDates(frequency, dayType, months, day, currentMonth, currentYear, startDate) {
const dates = ;
if (frequency === ‘Monthly’) {
let expectedDay = day;
if (dayType === ‘Last Day of Month’) {
expectedDay = new Date(currentYear, currentMonth + 1, 0).getDate();
}
const expectedDate = new Date(currentYear, currentMonth, expectedDay);
if (!startDate || expectedDate >= startDate) dates.push(expectedDate);
} else if (frequency === ‘Quarterly’) {
const quarterMonths = months
? months.toString().split(‘,’).map(m => parseInt(m.trim()) - 1)
: ;
if (quarterMonths.includes(currentMonth)) {
let expectedDay = day;
if (dayType === ‘Last Day of Month’) {
expectedDay = new Date(currentYear, currentMonth + 1, 0).getDate();
}
const expectedDate = new Date(currentYear, currentMonth, expectedDay);
if (!startDate || expectedDate >= startDate) dates.push(expectedDate);
}
} else if (frequency === ‘Yearly’) {
const yearMonth = months ? parseInt(months) - 1 : null;
if (yearMonth === currentMonth) {
let expectedDay = day;
if (dayType === ‘Last Day of Month’) {
expectedDay = new Date(currentYear, currentMonth + 1, 0).getDate();
}
const expectedDate = new Date(currentYear, currentMonth, expectedDay);
if (!startDate || expectedDate >= startDate) dates.push(expectedDate);
}
} else if (frequency === ‘Biweekly’) {
// TODO: Implement biweekly logic if needed
} else if (frequency === ‘Weekly’) {
// TODO: Implement weekly logic if needed
}
return dates;
}
/**
- Checks if a recurring bill has already been paid by searching transactions.
- Matches on description (partial, case-insensitive) within ±3 days of expected date.
- @param {Array} transData - Transaction data from sheet
- @param {number} dateCol - Column index for transaction date
- @param {number} descCol - Column index for transaction description
- @param {number} amountCol - Column index for transaction amount
- @param {string} description - Description to search for
- @param {Date} expectedDate - Expected date of the bill
- @returns {boolean} True if a matching transaction was found
*/
function checkIfPaid(transData, dateCol, descCol, amountCol, description, expectedDate) {
const DAYS_WINDOW = 3;
const threeDaysMs = DAYS_WINDOW * 24 * 60 * 60 * 1000;
for (let i = 1; i < transData.length; i++) {
const transDate = new Date(transData[i][dateCol]);
const transDesc = String(transData[i][descCol] || ‘’);
const dateDiff = Math.abs(transDate - expectedDate);
if (dateDiff <= threeDaysMs &&
transDesc &&
(transDesc.toLowerCase().includes(description.toLowerCase()) ||
description.toLowerCase().includes(transDesc.toLowerCase()))) {
return true;
}
}
return false;
}
setupCategoryDropdown.gs
/**
@file setupCategoryDropdown.gs
@description Syncs the Category dropdown in the “Recurring Transactions” sheet
with the actual category names from the “Categories” sheet.
Excludes categories marked as “Hide from Reports”.
Recurring Transactions sheet layout:
Row 1 = instruction text (skipped)
Row 2 = column headers
Row 3+ = data
Applies a strict dropdown (invalid values rejected) to the
entire Category column in the data rows.
onCategoriesEdit() watches the Categories sheet and auto-syncs
the dropdown whenever column A (name) or "Hide from Reports"
is edited. Must be installed as an installable trigger:
Apps Script → Triggers → Add Trigger
Function: onCategoriesEdit
Event source: From spreadsheet
Event type: On edit
@schedule Auto via installable onEdit trigger + manual menu option
@version 2026-03-02 v1.2
Change Log:
2026-03-02 v1.2 - Added onCategoriesEdit() installable trigger function.
Auto-syncs dropdown when Categories col A or “Hide from
Reports” column is edited.
2026-03-01 v1.1 - Added standard JSDoc header and change log.
Now excludes categories marked “Hide from Reports”.
2026-02-26 v1.0 - Initial version.
*/
/**
const sheet = e.range.getSheet();
if (sheet.getName() !== ‘Categories’) return;
// Only react to edits in col A (category name) or “Hide from Reports” col
const col = e.range.getColumn();
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const hideCol = headers.indexOf(‘Hide from Reports’) + 1; // 1-based, 0 if not found
if (col !== 1 && (hideCol === 0 || col !== hideCol)) return;
setupCategoryDropdown();
}
/**
- Syncs the Category dropdown in the “Recurring Transactions” sheet with
- category names from the “Categories” sheet. Excludes hidden categories.
- Can be run manually from the Extensions menu or called by onCategoriesEdit().
*/
function setupCategoryDropdown() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const categoriesSheet = ss.getSheetByName(‘Categories’);
const recurringSheet = ss.getSheetByName(‘Recurring Transactions’);
if (!categoriesSheet || !recurringSheet) {
Logger.log(‘Required sheets not found’);
return;
}
// — Pull category names, excluding “Hide from Reports” —
const categoriesData = categoriesSheet.getDataRange().getValues();
const catHeaders = categoriesData[0];
const hideColIndex = catHeaders.indexOf(‘Hide from Reports’);
const categoryNames = ;
for (let i = 1; i < categoriesData.length; i++) {
const catName = categoriesData[i][0];
const hidden = hideColIndex !== -1 ? categoriesData[i][hideColIndex] : false;
if (catName && catName !== ‘’ && !hidden) {
categoryNames.push(catName);
}
}
if (categoryNames.length === 0) {
Logger.log(‘No categories found in the Categories sheet.’);
return;
}
// — Find “Category” column in Recurring Transactions —
// Row 1 (index 0) = instructions, Row 2 (index 1) = real headers
const allRows = recurringSheet.getDataRange().getValues();
const recurringHeaders = allRows[1];
const categoryColIndex = recurringHeaders.indexOf(‘Category’);
if (categoryColIndex === -1) {
Logger.log(‘Category column not found in Recurring Transactions (checked row 2).’);
return;
}
const categoryCol = categoryColIndex + 1;
// — Apply dropdown validation to data rows (row 3 onward) —
const lastRow = recurringSheet.getLastRow();
const dataRowCount = lastRow - 2;
if (dataRowCount < 1) {
Logger.log(‘No data rows found in Recurring Transactions.’);
return;
}
const range = recurringSheet.getRange(3, categoryCol, dataRowCount, 1);
const rule = SpreadsheetApp.newDataValidation()
.requireValueInList(categoryNames, true)
.setAllowInvalid(false)
.build();
range.setDataValidation(rule);
Logger.log(
'Category dropdown applied to ’ + dataRowCount + ’ row(s) ’ +
‘with ’ + categoryNames.length + ’ categories.’
);
}
suggestRecurringTransactions
/**
@file suggestRecurringTransactions.gs
@description Analyzes 12 months of transaction history to detect likely monthly
recurring bills that are not already tracked in the “Recurring
Transactions” sheet. Writes candidates to a staging sheet
(“Suggested Recurring”) with checkboxes for approval. A second
function, commitSuggestedRecurring(), reads approved rows and appends
them to the “Recurring Transactions” sheet.
Run suggestRecurringTransactions() manually, review the staging sheet,
check the boxes you want to keep, then run commitSuggestedRecurring()
(or click the Commit button if you add one to the sheet).
@schedule As needed (manual)
@version 2026-03-01 v1.2
Change Log:
2026-03-01 v1.2 - Added standard Change Log block. Fixed @version format (had trailing description).
2026-02-27 v1.1 - Removed yearly recurrence detection; monthly detection only.
2026-02-27 v1.0 - Initial version.
*/
// =============================================================================
// CONFIGURATION
// =============================================================================
/** How many months of transaction history to analyze. */
const SRT_LOOKBACK_MONTHS = 12;
/**
- A transaction description must appear in at least this many distinct
- calendar months to be considered “monthly recurring”.
- 4 out of 12 avoids flagging one-off annual purchases while still catching
- bills that occasionally post a day or two outside the expected month.
*/
const SRT_MONTHLY_MIN_MONTHS = 4;
/**
- For monthly candidates, the median amount across occurrences must stay
- within this percentage of the mean. Catches subscriptions with small
- price adjustments while filtering out irregular purchases.
- 0.25 = amounts must be within ±25% of the mean.
*/
const SRT_AMOUNT_VARIANCE_THRESHOLD = 0.25;
/** Categories to ignore entirely (these are never recurring bills). */
const SRT_SKIP_CATEGORIES = [‘Transfer’, ‘Income’, ‘Paycheck’, ‘Direct Deposit’, ‘’];
/** Staging sheet name. */
const SRT_STAGING_SHEET = ‘Suggested Recurring’;
const SRT_COLORS = {
headerPrimary : ‘#4285f4’,
headerMonthly : ‘#34a853’,
rowEven : ‘#f8f9fa’,
rowOdd : ‘#ffffff’,
committed : ‘#d5f4e6’,
skipped : ‘#fce8e6’,
white : ‘#ffffff’,
black : ‘#000000’
};
// =============================================================================
// MAIN — SUGGEST
// =============================================================================
/**
- Scans 12 months of Transactions, detects monthly recurring patterns,
- filters out anything already in Recurring Transactions, and writes candidates
- to the “Suggested Recurring” staging sheet with checkboxes.
*/
function suggestRecurringTransactions() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const transSheet = ss.getSheetByName(‘Transactions’);
const recurSheet = ss.getSheetByName(‘Recurring Transactions’);
const categoriesSheet = ss.getSheetByName(‘Categories’);
if (!transSheet) { Logger.log(‘ERROR: Transactions sheet not found.’); return; }
if (!recurSheet) { Logger.log(‘ERROR: Recurring Transactions sheet not found.’); return; }
// ── 1. Load existing recurring descriptions (de-dupe check) ──────────────
const existingDescriptions = _loadExistingRecurring(recurSheet);
Logger.log('Existing recurring descriptions: ’ + existingDescriptions.size);
// ── 2. Load valid category names for the dropdown later ──────────────────
const validCategories = _loadCategories(categoriesSheet);
// ── 3. Load & bucket transactions by description ──────────────────────────
const buckets = _bucketTransactions(transSheet);
Logger.log('Unique descriptions found: ’ + Object.keys(buckets).length);
// ── 4. Detect monthly candidates ──────────────────────────────────────────
const candidates = _detectMonthly(buckets, existingDescriptions);
Logger.log('Monthly candidates: ’ + candidates.length);
if (candidates.length === 0) {
Logger.log(‘No new recurring transactions detected. Either everything is already tracked, or no clear monthly patterns were found in the last 12 months.’);
return;
}
// ── 5. Write staging sheet ────────────────────────────────────────────────
_writeStagingSheet(ss, candidates, validCategories);
Logger.log(‘SUCCESS: Found ’ + candidates.length + ’ monthly candidate(s). Review the "’ + SRT_STAGING_SHEET + ‘" sheet, check boxes, then run commitSuggestedRecurring().’);
}
// =============================================================================
// MAIN — COMMIT
// =============================================================================
/**
- Reads approved rows (checkbox = TRUE) from the staging sheet and appends
- them to the “Recurring Transactions” sheet. Marks committed rows green
- and unchecked rows red so the staging sheet reflects what happened.
*/
function commitSuggestedRecurring() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const stagingSheet = ss.getSheetByName(SRT_STAGING_SHEET);
const recurSheet = ss.getSheetByName(‘Recurring Transactions’);
if (!stagingSheet) {
Logger.log(‘ERROR: No staging sheet found. Run suggestRecurringTransactions() first.’);
return;
}
if (!recurSheet) {
Logger.log(‘ERROR: Recurring Transactions sheet not found.’);
return;
}
const data = stagingSheet.getDataRange().getValues();
const headers = data[1]; // Row 2 is the real header row (row 1 = instructions)
// Column indices in staging sheet (0-based)
const colApprove = 0; // A: Checkbox
const colDesc = 1; // B: Description
const colCategory = 2; // C: Category
const colAmount = 3; // D: Amount
const colFreq = 4; // E: Frequency
const colDayType = 5; // F: Day Type
const colMonths = 6; // G: Months
const colDay = 7; // H: Day of Month
const colNotes = 8; // I: Notes (contains detection reasoning)
// Recurring Transactions sheet layout (row 2 = real headers, data from row 3)
const recurData = recurSheet.getDataRange().getValues();
const recurHeaders = recurData[1]; // 0-based row 1 = spreadsheet row 2
const rDescCol = recurHeaders.indexOf(‘Description’);
const rCatCol = recurHeaders.indexOf(‘Category’);
const rAmtCol = recurHeaders.indexOf(‘Amount’);
const rFreqCol = recurHeaders.indexOf(‘Frequency’);
const rDayTypeCol = recurHeaders.indexOf(‘Day Type’);
const rMonthsCol = recurHeaders.indexOf(‘Months’);
const rDayCol = recurHeaders.indexOf(‘Day of Month’);
const numRecurCols = recurHeaders.length;
let committed = 0;
let skipped = 0;
// Data rows in staging sheet start at index 2 (spreadsheet row 3)
for (let i = 2; i < data.length; i++) {
const row = data[i];
const approved = row[colApprove];
const rowNum = i + 1; // 1-based spreadsheet row
if (approved === true) {
// Build a new row for Recurring Transactions (all columns, blanks for unused)
const newRow = new Array(numRecurCols).fill('');
if (rDescCol >= 0) newRow[rDescCol] = row[colDesc];
if (rCatCol >= 0) newRow[rCatCol] = row[colCategory];
if (rAmtCol >= 0) newRow[rAmtCol] = row[colAmount];
if (rFreqCol >= 0) newRow[rFreqCol] = row[colFreq];
if (rDayTypeCol >= 0) newRow[rDayTypeCol] = row[colDayType];
if (rMonthsCol >= 0) newRow[rMonthsCol] = row[colMonths];
if (rDayCol >= 0) newRow[rDayCol] = row[colDay];
recurSheet.appendRow(newRow);
stagingSheet.getRange(rowNum, 1, 1, 9).setBackground(SRT_COLORS.committed);
committed++;
} else {
stagingSheet.getRange(rowNum, 1, 1, 9).setBackground(SRT_COLORS.skipped);
skipped++;
}
}
// Re-apply category dropdown to newly added rows in Recurring Transactions
if (committed > 0) {
try { setupCategoryDropdown(); } catch(e) { /* non-fatal if function not present */ }
}
Logger.log('SUCCESS: Added to Recurring Transactions: ’ + committed + ’ | Skipped: ’ + skipped);
}
// =============================================================================
// DETECTION HELPERS
// =============================================================================
/**
- Loads transaction history into a map keyed by description.
- Each entry contains an array of { month, amount, date } objects.
- Skips transfers, income, and uncategorized junk.
- Uses getDisplayValues() to avoid timezone drift on date cells.
- @param {GoogleAppsScript.Spreadsheet.Sheet} transSheet
- @returns {Object.<string, Array<{month: string, amount: number, date: Date, category: string}>>}
*/
function _bucketTransactions(transSheet) {
const cutoff = new Date();
cutoff.setMonth(cutoff.getMonth() - SRT_LOOKBACK_MONTHS);
cutoff.setHours(0, 0, 0, 0);
// Use getDisplayValues() to avoid UTC timezone issues with date cells
const display = transSheet.getDataRange().getDisplayValues();
const raw = transSheet.getDataRange().getValues();
const headers = raw[0];
const dateCol = headers.indexOf(‘Date’);
const descCol = headers.indexOf(‘Description’);
const amountCol = headers.indexOf(‘Amount’);
const categoryCol = headers.indexOf(‘Category’);
const buckets = {};
for (let i = 1; i < raw.length; i++) {
const category = String(raw[i][categoryCol] || ‘’).trim();
if (SRT_SKIP_CATEGORIES.includes(category)) continue;
// Parse date from display value (M/d/yyyy format from Tiller)
const dateStr = display[i][dateCol];
const dateParts = dateStr.split('/');
if (dateParts.length < 3) continue;
const txDate = new Date(
parseInt(dateParts[2]),
parseInt(dateParts[0]) - 1,
parseInt(dateParts[1])
);
if (isNaN(txDate.getTime()) || txDate < cutoff) continue;
const amount = parseFloat(String(raw[i][amountCol]).replace(/[$,]/g, ''));
if (isNaN(amount) || amount >= 0) continue; // expenses are negative in Tiller
const desc = String(raw[i][descCol] || '').trim();
if (!desc) continue;
const monthKey = txDate.getFullYear() + '-' + String(txDate.getMonth() + 1).padStart(2, '0');
if (!buckets[desc]) buckets[desc] = [];
buckets[desc].push({
month : monthKey,
amount : Math.abs(amount),
date : txDate,
category : category
});
}
return buckets;
}
/**
- Detects monthly recurring candidates from the transaction buckets.
- A description qualifies if:
-
- It appears in >= SRT_MONTHLY_MIN_MONTHS distinct calendar months
-
- The amounts are consistent (within SRT_AMOUNT_VARIANCE_THRESHOLD of mean)
-
- It’s not already in Recurring Transactions
- @param {Object} buckets
- @param {Set} existingDescriptions
- @returns {Array} Candidate rows ready for the staging sheet
*/
function _detectMonthly(buckets, existingDescriptions) {
const candidates = ;
for (const desc in buckets) {
if (_isAlreadyTracked(desc, existingDescriptions)) continue;
const entries = buckets[desc];
const distinctMonths = [...new Set(entries.map(e => e.month))];
if (distinctMonths.length < SRT_MONTHLY_MIN_MONTHS) continue;
const amounts = entries.map(e => e.amount);
if (!_amountsAreConsistent(amounts)) continue;
const medianAmount = _median(amounts);
const category = _mostCommonCategory(entries);
const typicalDay = _medianDayOfMonth(entries);
candidates.push({
description : desc,
category : category,
amount : Math.round(medianAmount * 100) / 100,
frequency : 'Monthly',
dayType : 'Exact Day',
months : '',
day : typicalDay,
notes : 'Seen in ' + distinctMonths.length + ' of last ' + SRT_LOOKBACK_MONTHS + ' months. Typical day: ' + typicalDay + '.'
});
}
// Sort by amount descending (biggest bills first)
candidates.sort((a, b) => b.amount - a.amount);
return candidates;
}
// =============================================================================
// STAGING SHEET WRITER
// =============================================================================
/**
- Creates or replaces the “Suggested Recurring” staging sheet and writes
- all candidates with checkboxes, formatted by frequency group.
- Sheet layout:
- Row 1 : Instructions banner
- Row 2 : Column headers
- Row 3+ : Data rows (one per candidate)
- @param {GoogleAppsScript.Spreadsheet.Spreadsheet} ss
- @param {Array} candidates
- @param {string} validCategories
*/
function _writeStagingSheet(ss, candidates, validCategories) {
// Delete and recreate for a clean slate
const existing = ss.getSheetByName(SRT_STAGING_SHEET);
if (existing) ss.deleteSheet(existing);
const sheet = ss.insertSheet(SRT_STAGING_SHEET);
// ── Column widths ─────────────────────────────────────────────────────────
sheet.setColumnWidth(1, 60); // A: Checkbox
sheet.setColumnWidth(2, 220); // B: Description
sheet.setColumnWidth(3, 140); // C: Category
sheet.setColumnWidth(4, 90); // D: Amount
sheet.setColumnWidth(5, 100); // E: Frequency
sheet.setColumnWidth(6, 130); // F: Day Type
sheet.setColumnWidth(7, 70); // G: Months
sheet.setColumnWidth(8, 100); // H: Day of Month
sheet.setColumnWidth(9, 340); // I: Notes
// ── Row 1: Instructions ───────────────────────────────────────────────────
sheet.getRange(‘A1:I1’).merge();
sheet.getRange(‘A1’)
.setValue(‘
Check the boxes for transactions you want to add to Recurring Transactions, then run commitSuggestedRecurring().’)
.setFontWeight(‘bold’)
.setBackground(SRT_COLORS.headerPrimary)
.setFontColor(SRT_COLORS.white)
.setFontSize(11)
.setWrap(true)
.setVerticalAlignment(‘middle’);
sheet.setRowHeight(1, 40);
// ── Row 2: Column headers ─────────────────────────────────────────────────
const headers = [‘Add?’, ‘Description’, ‘Category’, ‘Amount’, ‘Frequency’, ‘Day Type’, ‘Months’, ‘Day of Month’, ‘Detection Notes’];
sheet.getRange(2, 1, 1, headers.length)
.setValues([headers])
.setFontWeight(‘bold’)
.setBackground(‘#e8eaf6’)
.setBorder(false, false, true, false, false, false, SRT_COLORS.black, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
// ── Data rows ─────────────────────────────────────────────────────────────
let dataRow = 3;
// Monthly group header
sheet.getRange(dataRow, 1, 1, 9).merge();
sheet.getRange(dataRow, 1)
.setValue(‘
Monthly Recurring Candidates’)
.setFontWeight(‘bold’)
.setFontColor(SRT_COLORS.white)
.setBackground(SRT_COLORS.headerMonthly)
.setFontSize(11);
sheet.setRowHeight(dataRow, 32);
dataRow++;
candidates.forEach((c, idx) => {
const rowBg = idx % 2 === 0 ? SRT_COLORS.rowEven : SRT_COLORS.rowOdd;
// Checkbox in column A
sheet.getRange(dataRow, 1).insertCheckboxes().setValue(false);
// Data columns B-I
sheet.getRange(dataRow, 2, 1, 8).setValues([[
c.description,
c.category,
c.amount,
c.frequency,
c.dayType,
c.months,
c.day,
c.notes
]]);
sheet.getRange(dataRow, 1, 1, 9).setBackground(rowBg).setVerticalAlignment('middle');
sheet.getRange(dataRow, 4).setNumberFormat('$#,##0.00'); // Amount column
sheet.setRowHeight(dataRow, 28);
dataRow++;
});
// ── Category dropdown on column C (data rows only) ────────────────────────
if (validCategories.length > 0) {
const firstDataRow = 4; // after instructions + headers + first group header
const numDataRows = candidates.length;
if (numDataRows > 0) {
const catRule = SpreadsheetApp.newDataValidation()
.requireValueInList(validCategories, true)
.setAllowInvalid(true) // allow invalid so pre-filled category isn’t rejected
.build();
// Apply to all rows in the data range (covers all candidate rows)
sheet.getRange(3, 3, dataRow - 3, 1).setDataValidation(catRule);
}
}
// ── Day Type dropdown on column F ─────────────────────────────────────────
const dayTypeRule = SpreadsheetApp.newDataValidation()
.requireValueInList([‘Exact Day’, ‘Last Day of Month’], true)
.setAllowInvalid(false)
.build();
sheet.getRange(3, 6, dataRow - 3, 1).setDataValidation(dayTypeRule);
// ── Frequency dropdown on column E ────────────────────────────────────────
const freqRule = SpreadsheetApp.newDataValidation()
.requireValueInList([‘Monthly’, ‘Quarterly’, ‘Yearly’, ‘Biweekly’, ‘Weekly’], true)
.setAllowInvalid(false)
.build();
sheet.getRange(3, 5, dataRow - 3, 1).setDataValidation(freqRule);
// Freeze header rows
sheet.setFrozenRows(2);
// Activate the new sheet so the user sees it
ss.setActiveSheet(sheet);
Logger.log('Staging sheet written with ’ + candidates.length + ’ candidate(s) starting at row 3, last data row: ’ + (dataRow - 1));
}
// =============================================================================
// UTILITY HELPERS
// =============================================================================
/**
- Loads existing recurring transaction descriptions from the Recurring
- Transactions sheet (row 1 = instructions, row 2 = headers, row 3+ = data).
- Returns a Set of lowercase descriptions for fast lookup.
- @param {GoogleAppsScript.Spreadsheet.Sheet} recurSheet
- @returns {Set}
*/
function _loadExistingRecurring(recurSheet) {
const data = recurSheet.getDataRange().getValues();
const headers = data[1]; // row 2 (0-based index 1)
const descCol = headers.indexOf(‘Description’);
const result = new Set();
for (let i = 2; i < data.length; i++) {
const desc = String(data[i][descCol] || ‘’).trim().toLowerCase();
if (desc) result.add(desc);
}
return result;
}
/**
- Loads valid category names from the Categories sheet (column A, skipping header).
- @param {GoogleAppsScript.Spreadsheet.Sheet|null} categoriesSheet
- @returns {string}
*/
function _loadCategories(categoriesSheet) {
if (!categoriesSheet) return ;
const data = categoriesSheet.getDataRange().getValues();
const result = ;
for (let i = 1; i < data.length; i++) {
const name = String(data[i][0] || ‘’).trim();
if (name) result.push(name);
}
return result;
}
/**
- Returns true if the description (or a close partial match) already exists
- in the Recurring Transactions sheet.
- @param {string} desc
- @param {Set} existingDescriptions
- @returns {boolean}
*/
function _isAlreadyTracked(desc, existingDescriptions) {
const lower = desc.toLowerCase();
for (const existing of existingDescriptions) {
if (lower === existing || lower.includes(existing) || existing.includes(lower)) {
return true;
}
}
return false;
}
/**
- Returns true if the array of amounts is “consistent” — meaning the
- standard deviation is within SRT_AMOUNT_VARIANCE_THRESHOLD of the mean.
- @param {number} amounts
- @returns {boolean}
*/
function _amountsAreConsistent(amounts) {
if (amounts.length === 0) return false;
const mean = amounts.reduce((s, v) => s + v, 0) / amounts.length;
if (mean === 0) return false;
const stdDev = Math.sqrt(amounts.reduce((s, v) => s + Math.pow(v - mean, 2), 0) / amounts.length);
return (stdDev / mean) <= SRT_AMOUNT_VARIANCE_THRESHOLD;
}
/**
- Returns the median value from an array of numbers.
- @param {number} arr
- @returns {number}
*/
function _median(arr) {
const sorted = […arr].sort((a, b) => a - b);
const mid = Math.floor(sorted.length / 2);
return sorted.length % 2 !== 0 ? sorted[mid] : (sorted[mid - 1] + sorted[mid]) / 2;
}
/**
- Returns the median day-of-month across all transaction entries.
- @param {Array<{date: Date}>} entries
- @returns {number}
*/
function _medianDayOfMonth(entries) {
return Math.round(_median(entries.map(e => e.date.getDate())));
}
/**
- Returns the most frequently occurring category in a set of transaction entries.
- @param {Array<{category: string}>} entries
- @returns {string}
*/
function _mostCommonCategory(entries) {
const freq = {};
entries.forEach(e => { freq[e.category] = (freq[e.category] || 0) + 1; });
return Object.keys(freq).reduce((a, b) => freq[a] >= freq[b] ? a : b, ‘’);
}
/**
- Formats a Date as M/d/yyyy for notes/logging.
- @param {Date} d
- @returns {string}
*/
function _fmtDateSrt(d) {
return (d.getMonth() + 1) + ‘/’ + d.getDate() + ‘/’ + d.getFullYear();
}
