/** * Weekly billing automation for a (fictional) cleaning company. * * Expected tabs in the spreadsheet: * "Raw Jobs" - pasted export from the scheduling app * "Rates" - client, standard_rate, deep_rate * "Cleaner Pay" - cleaner, pay_rate_hourly, flat_job_pay_pct * * Output tabs (rebuilt from scratch on every run, so the run is idempotent): * "Clean Jobs", "Client Invoices", "Cleaner Payouts", "Needs Review", "Summary" * * Business rules (kept identical to verify.py): * - Duplicate job_id rows are dropped (first one wins) and logged. * - Client names are matched to the Rates list ignoring case, outer * whitespace and repeated spaces; the canonical spelling is used. * - Dates are accepted as YYYY-MM-DD or MM/DD/YYYY. * - Hourly jobs (Standard / Deep clean): billed = hours x rate_per_hour. * If the rate is blank, the client's price-list rate is used and logged. * A completed hourly job with blank/zero hours is NOT billed; it is flagged. * - Flat jobs (Turnover / Move-out): billed = flat_fee. * - Cleaner pay: hourly = hours x pay_rate_hourly; flat = flat_fee x flat_job_pay_pct. * - Cancelled: $25 fee if notes contain "late cancel", otherwise $0; cleaner paid $0. * - No-show: billed $0, paid $0, flagged. * - Money is rounded to cents per job (half up), then summed. */ var SHEET_RAW = 'Raw Jobs'; var SHEET_RATES = 'Rates'; var SHEET_PAY = 'Cleaner Pay'; var SHEET_CLEAN = 'Clean Jobs'; var SHEET_INVOICES = 'Client Invoices'; var SHEET_PAYOUTS = 'Cleaner Payouts'; var SHEET_REVIEW = 'Needs Review'; var SHEET_SUMMARY = 'Summary'; var LATE_CANCEL_FEE_CENTS = 2500; var TRIGGER_HANDLER = 'runWeeklyBilling'; // --------------------------------------------------------------------------- // Menu and triggers // --------------------------------------------------------------------------- function onOpen() { SpreadsheetApp.getUi() .createMenu('Billing') .addItem('Load demo data', 'loadDemoData') .addItem('Run weekly billing', 'runWeeklyBilling') .addSeparator() .addItem('Set up weekly auto-run (Mon 7am)', 'setupWeeklyTrigger') .addItem('Remove auto-run', 'removeWeeklyTrigger') .addToUi(); } /** Creates one weekly trigger (Monday 7am). Existing ones are deleted first. */ function setupWeeklyTrigger() { deleteBillingTriggers_(); ScriptApp.newTrigger(TRIGGER_HANDLER) .timeBased() .onWeekDay(ScriptApp.WeekDay.MONDAY) .atHour(7) .create(); alertQuietly_('Auto-run is on: billing will run every Monday around 7am.'); } /** Deletes the weekly trigger(s). */ function removeWeeklyTrigger() { var n = deleteBillingTriggers_(); alertQuietly_(n ? 'Auto-run removed.' : 'There was no auto-run to remove.'); } function deleteBillingTriggers_() { var n = 0; ScriptApp.getProjectTriggers().forEach(function (t) { if (t.getHandlerFunction() === TRIGGER_HANDLER) { ScriptApp.deleteTrigger(t); n++; } }); return n; } /** Shows a popup when run from the menu; silently skipped in a time trigger. */ function alertQuietly_(msg) { try { SpreadsheetApp.getUi().alert(msg); } catch (e) { Logger.log(msg); } } // --------------------------------------------------------------------------- // Main entry point // --------------------------------------------------------------------------- function runWeeklyBilling() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tz = ss.getSpreadsheetTimeZone(); var raw = readTable_(ss, SHEET_RAW); var rates = readTable_(ss, SHEET_RATES); var pay = readTable_(ss, SHEET_PAY); var result = computeBilling_(raw, rates, pay, tz); writeCleanJobs_(ss, result.clean); writeTable_(ss, SHEET_INVOICES, ['week_start', 'client', 'jobs', 'hours', 'total_billed'], result.invoices, { text: [0, 1], integer: [2], decimal: [3], money: [4], widths: [110, 260, 70, 80, 120] }); writeTable_(ss, SHEET_PAYOUTS, ['week_start', 'cleaner', 'jobs', 'hours', 'total_pay'], result.payouts, { text: [0, 1], integer: [2], decimal: [3], money: [4], widths: [110, 180, 70, 80, 120] }); writeTable_(ss, SHEET_REVIEW, ['week_start', 'job_id', 'issue', 'what_the_script_did'], result.issues, { text: [0, 1, 2, 3], widths: [110, 90, 330, 520] }); writeSummary_(ss, result.summary, tz); var status = sendSummaryEmail_(ss, result, tz); ss.getSheetByName(SHEET_SUMMARY).getRange('I3').setValue(status); SpreadsheetApp.flush(); } // --------------------------------------------------------------------------- // Pure billing logic (no Sheets calls, so it can be unit tested) // --------------------------------------------------------------------------- /** * @param {Object[]} raw rows of Raw Jobs as {header: value} * @param {Object[]} rates rows of Rates * @param {Object[]} pay rows of Cleaner Pay * @param {string} tz spreadsheet time zone (used if a cell is a Date object) * @return {{clean:Object[], invoices:Array[], payouts:Array[], issues:Array[], summary:Array[]}} */ function computeBilling_(raw, rates, pay, tz) { var rateMap = {}; rates.forEach(function (r) { var name = collapse_(r.client); if (name) rateMap[norm_(name)] = { name: name, std: toNum_(r.standard_rate), deep: toNum_(r.deep_rate) }; }); var payMap = {}; pay.forEach(function (r) { var name = collapse_(r.cleaner); if (name) payMap[norm_(name)] = { name: name, hourly: toNum_(r.pay_rate_hourly), pct: toNum_(r.flat_job_pay_pct) }; }); var issues = []; // [week_start, job_id, issue, action] var clean = []; var seen = {}; function addIssue(wk, jid, issue, action) { issues.push([wk || '', jid, issue, action]); } raw.forEach(function (r) { var jid = String(r.job_id === undefined || r.job_id === null ? '' : r.job_id).trim(); var pd = parseDate_(r.date, tz); // {iso, nonstd} or null var wk = pd ? weekStart_(pd.iso) : ''; if (jid === '') { addIssue(wk, '', 'Missing job_id', 'Row excluded'); return; } if (seen[jid]) { addIssue(wk, jid, 'Duplicate job_id', 'Duplicate row removed; first occurrence kept'); return; } seen[jid] = true; if (!pd) { addIssue('', jid, 'Unreadable date', 'Row excluded (value was "' + String(r.date).trim() + '")'); return; } if (pd.nonstd) { addIssue(wk, jid, 'Date in non-standard format', 'Parsed "' + String(r.date).trim() + '" as ' + pd.iso); } var clientRaw = String(r.client === undefined || r.client === null ? '' : r.client); var rate = rateMap[norm_(clientRaw)]; if (!rate) { addIssue(wk, jid, 'Unknown client', 'Row excluded; add "' + clientRaw.trim() + '" to the Rates tab'); return; } var clientNameChanged = clientRaw !== rate.name; if (clientNameChanged) { addIssue(wk, jid, 'Client name spelling normalized', 'Changed "' + clientRaw + '" to "' + rate.name + '"'); } var serviceKey = norm_(r.service); var serviceLabels = { 'standard clean': 'Standard clean', 'deep clean': 'Deep clean', 'turnover': 'Turnover', 'move-out': 'Move-out' }; if (!serviceLabels[serviceKey]) { addIssue(wk, jid, 'Unknown service', 'Row excluded (value was "' + String(r.service).trim() + '")'); return; } var statusKey = norm_(r.status); var statusLabels = { 'completed': 'Completed', 'cancelled': 'Cancelled', 'no-show': 'No-show' }; if (!statusLabels[statusKey]) { addIssue(wk, jid, 'Unknown status', 'Row excluded (value was "' + String(r.status).trim() + '")'); return; } var cleaner = collapse_(r.cleaner); var payInfo = payMap[norm_(cleaner)] || null; if (payInfo) cleaner = payInfo.name; var hours = toNum_(r.hours); var rateRow = toNum_(r.rate_per_hour); var flat = toNum_(r.flat_fee); var flags = []; if (pd.nonstd) flags.push('date format converted'); if (clientNameChanged) flags.push('client name normalized'); var billed = 0, paid = 0, rateUsed = '', outHours = null; if (statusKey === 'cancelled') { flags.push('cancelled'); if (String(r.notes === undefined || r.notes === null ? '' : r.notes).toLowerCase().indexOf('late cancel') !== -1) { billed = LATE_CANCEL_FEE_CENTS; flags.push('late cancel fee'); } } else if (statusKey === 'no-show') { flags.push('no-show'); addIssue(wk, jid, 'No-show', 'Billed $0 and paid $0; flagged for follow-up'); } else { if (!payInfo) { addIssue(wk, jid, 'Unknown cleaner', 'Billed normally; cleaner pay set to $0'); flags.push('unknown cleaner'); } if (serviceKey === 'standard clean' || serviceKey === 'deep clean') { if (hours === null || hours <= 0) { addIssue(wk, jid, 'Missing or zero hours on completed hourly job', 'Not billed; excluded from invoices and payouts until hours are confirmed'); return; } if (rateRow === null || rateRow <= 0) { rateRow = serviceKey === 'standard clean' ? rate.std : rate.deep; addIssue(wk, jid, 'Missing rate_per_hour', 'Rate filled from price list (' + rateRow.toFixed(2) + '/hr); billed normally'); flags.push('rate filled from price list'); } rateUsed = rateRow; billed = toCents_(hours * rateRow); if (payInfo) paid = toCents_(hours * payInfo.hourly); outHours = hours; } else { if (flat === null || flat <= 0) { addIssue(wk, jid, 'Missing flat_fee on flat-fee job', 'Not billed; excluded from invoices and payouts until the fee is confirmed'); return; } billed = toCents_(flat); if (payInfo) paid = toCents_(flat * payInfo.pct); outHours = (hours !== null && hours > 0) ? hours : 0; } } clean.push({ job_id: jid, date: pd.iso, week_start: wk, client: rate.name, property: String(r.property === undefined || r.property === null ? '' : r.property).trim(), cleaner: cleaner, service: serviceLabels[serviceKey], status: statusLabels[statusKey], hours: outHours, rate_used: rateUsed, flat_fee: flat === null ? '' : flat, billed: billed, paid: paid, flags: flags.join('; ') }); }); // Deterministic ordering clean.sort(function (a, b) { return cmp_(a.date + '|' + a.job_id, b.date + '|' + b.job_id); }); issues.sort(function (a, b) { return cmp_(a[0], b[0]) || cmp_(a[1], b[1]) || cmp_(a[2], b[2]); }); // Aggregates (integer cents / hundredths of an hour) var inv = {}, pout = {}, weeks = {}; clean.forEach(function (j) { var done = j.status === 'Completed'; var h = done ? Math.round((j.hours || 0) * 100) : 0; var ki = j.week_start + '\u0001' + j.client; var kp = j.week_start + '\u0001' + j.cleaner; var ti = inv[ki] = inv[ki] || { wk: j.week_start, name: j.client, jobs: 0, hours: 0, amt: 0 }; var tp = pout[kp] = pout[kp] || { wk: j.week_start, name: j.cleaner, jobs: 0, hours: 0, amt: 0 }; if (done) { ti.jobs++; ti.hours += h; tp.jobs++; tp.hours += h; } ti.amt += j.billed; tp.amt += j.paid; var w = weeks[j.week_start] = weeks[j.week_start] || { jobs: 0, billed: 0, paid: 0, issues: 0 }; if (done) w.jobs++; w.billed += j.billed; w.paid += j.paid; }); issues.forEach(function (i) { if (weeks[i[0]]) weeks[i[0]].issues++; }); function toRows(map) { return Object.keys(map).map(function (k) { return map[k]; }) .filter(function (t) { return t.jobs > 0 || t.amt > 0; }) .sort(function (a, b) { return cmp_(a.wk, b.wk) || cmp_(a.name, b.name); }) .map(function (t) { return [t.wk, t.name, t.jobs, t.hours / 100, t.amt / 100]; }); } var summary = Object.keys(weeks).sort().map(function (wk) { var w = weeks[wk]; return [wk, w.jobs, w.billed / 100, w.paid / 100, (w.billed - w.paid) / 100, w.issues]; }); return { clean: clean, invoices: toRows(inv), payouts: toRows(pout), issues: issues, summary: summary }; } // --------------------------------------------------------------------------- // Small helpers // --------------------------------------------------------------------------- function collapse_(v) { return String(v === undefined || v === null ? '' : v).replace(/\s+/g, ' ').trim(); } function norm_(v) { return collapse_(v).toLowerCase(); } function cmp_(a, b) { return a < b ? -1 : (a > b ? 1 : 0); } /** Parses a number from a cell; blank or unreadable -> null. */ function toNum_(v) { if (v === undefined || v === null) return null; if (typeof v === 'number') return isNaN(v) ? null : v; var s = String(v).replace(/[$,]/g, '').trim(); if (s === '') return null; var n = Number(s); return isNaN(n) ? null : n; } /** Dollars to whole cents, half up. The tiny epsilon guards against float noise. */ function toCents_(dollars) { return Math.round(dollars * 100 + 1e-7); } function pad2_(n) { return (n < 10 ? '0' : '') + n; } /** * Accepts a Date object (when Sheets converted the cell), YYYY-MM-DD or MM/DD/YYYY. * @return {{iso:string, nonstd:boolean}|null} */ function parseDate_(v, tz) { if (Object.prototype.toString.call(v) === '[object Date]' && !isNaN(v.getTime())) { return { iso: Utilities.formatDate(v, tz, 'yyyy-MM-dd'), nonstd: false }; } var s = String(v === undefined || v === null ? '' : v).trim(); var y, m, d, nonstd = false, mt; if ((mt = /^(\d{4})-(\d{2})-(\d{2})$/.exec(s))) { y = +mt[1]; m = +mt[2]; d = +mt[3]; } else if ((mt = /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/.exec(s))) { m = +mt[1]; d = +mt[2]; y = +mt[3]; nonstd = true; } else { return null; } var t = new Date(Date.UTC(y, m - 1, d)); if (t.getUTCFullYear() !== y || t.getUTCMonth() !== m - 1 || t.getUTCDate() !== d) return null; return { iso: y + '-' + pad2_(m) + '-' + pad2_(d), nonstd: nonstd }; } /** Monday of the week containing the ISO date (string in, string out). */ function weekStart_(iso) { var p = iso.split('-'); var t = new Date(Date.UTC(+p[0], +p[1] - 1, +p[2])); var dow = (t.getUTCDay() + 6) % 7; // Monday = 0 t.setUTCDate(t.getUTCDate() - dow); return t.getUTCFullYear() + '-' + pad2_(t.getUTCMonth() + 1) + '-' + pad2_(t.getUTCDate()); } function addDays_(iso, n) { var p = iso.split('-'); var t = new Date(Date.UTC(+p[0], +p[1] - 1, +p[2])); t.setUTCDate(t.getUTCDate() + n); return t.getUTCFullYear() + '-' + pad2_(t.getUTCMonth() + 1) + '-' + pad2_(t.getUTCDate()); } // --------------------------------------------------------------------------- // Sheet I/O and formatting // --------------------------------------------------------------------------- /** Reads a tab into an array of {lowercased_header: value}. */ function readTable_(ss, name) { var sh = ss.getSheetByName(name); if (!sh) throw new Error('Missing tab: "' + name + '". Please add it before running.'); var values = sh.getDataRange().getValues(); if (values.length < 1) return []; var header = values[0].map(function (h) { return String(h).trim().toLowerCase(); }); var out = []; for (var i = 1; i < values.length; i++) { var row = values[i]; if (row.join('').trim() === '') continue; // skip fully blank rows var obj = {}; header.forEach(function (h, c) { obj[h] = row[c]; }); out.push(obj); } return out; } /** Returns the sheet emptied of values, formats and banding (creates it if needed). */ function resetSheet_(ss, name) { var sh = ss.getSheetByName(name) || ss.insertSheet(name); sh.getBandings().forEach(function (b) { b.remove(); }); sh.clear(); sh.setFrozenRows(0); return sh; } /** * Generic table writer. * opts: text/integer/decimal/money = column indexes (0-based); widths = pixel widths. */ function writeTable_(ss, name, header, rows, opts) { opts = opts || {}; var sh = resetSheet_(ss, name); var nCols = header.length; var nRows = rows.length; sh.getRange(1, 1, 1, nCols).setValues([header]); if (nRows > 0) { // Text columns first, so Sheets does not turn "2026-10-26" into a date. (opts.text || []).forEach(function (c) { sh.getRange(2, c + 1, nRows, 1).setNumberFormat('@'); }); (opts.integer || []).forEach(function (c) { sh.getRange(2, c + 1, nRows, 1).setNumberFormat('0'); }); (opts.decimal || []).forEach(function (c) { sh.getRange(2, c + 1, nRows, 1).setNumberFormat('0.00'); }); (opts.money || []).forEach(function (c) { sh.getRange(2, c + 1, nRows, 1).setNumberFormat('$#,##0.00'); }); sh.getRange(2, 1, nRows, nCols).setValues(rows); } styleTable_(sh, nCols, Math.max(nRows, 1), opts.widths); return sh; } function styleTable_(sh, nCols, nRows, widths) { var head = sh.getRange(1, 1, 1, nCols); head.setFontWeight('bold').setBackground('#1f3a5f').setFontColor('#ffffff').setVerticalAlignment('middle'); sh.setFrozenRows(1); if (nRows > 0) { sh.getRange(1, 1, nRows + 1, nCols) .applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY, true, false); // applyRowBanding recolours the header; restore our header style. head.setBackground('#1f3a5f').setFontColor('#ffffff').setFontWeight('bold'); } if (widths) { widths.forEach(function (w, i) { sh.setColumnWidth(i + 1, w); }); } } function writeCleanJobs_(ss, clean) { var header = ['job_id', 'date', 'week_start', 'client', 'property', 'cleaner', 'service', 'status', 'hours', 'rate_used', 'flat_fee', 'billed_amount', 'cleaner_pay', 'flags']; var rows = clean.map(function (j) { return [j.job_id, j.date, j.week_start, j.client, j.property, j.cleaner, j.service, j.status, j.hours === null ? '' : j.hours, j.rate_used, j.flat_fee, j.billed / 100, j.paid / 100, j.flags]; }); writeTable_(ss, SHEET_CLEAN, header, rows, { text: [0, 1, 2, 3, 4, 5, 6, 7, 13], decimal: [8], money: [9, 10, 11, 12], widths: [80, 95, 95, 250, 230, 100, 110, 90, 60, 80, 80, 110, 110, 330] }); } function writeSummary_(ss, summary, tz) { var header = ['week_start', 'jobs', 'billed', 'paid', 'gross_margin', 'issues']; var sh = writeTable_(ss, SHEET_SUMMARY, header, summary, { text: [0], integer: [1, 5], money: [2, 3, 4], widths: [110, 70, 120, 120, 120, 70] }); // Run information sits to the right of the table (columns H:I). var meta = [ ['Last run', Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd HH:mm:ss z')], ['Rows read (Raw Jobs)', ss.getSheetByName(SHEET_RAW).getLastRow() - 1], ['Email status', 'pending'] ]; sh.getRange('H1:I3').setNumberFormat('@').setValues(meta.map(function (r) { return [r[0], String(r[1])]; })); sh.getRange('H1:H3').setFontWeight('bold'); sh.setColumnWidth(7, 30); sh.setColumnWidth(8, 170); sh.setColumnWidth(9, 380); } // --------------------------------------------------------------------------- // Email (owner only) // --------------------------------------------------------------------------- /** * Picks the most recent complete week (its Sunday is before today). If the data * has no such week (for example old demo data), the latest week in the data is used. */ function pickReportWeek_(summary, tz) { if (!summary.length) return null; var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd'); var weeks = summary.map(function (r) { return r[0]; }); var complete = weeks.filter(function (w) { return addDays_(w, 6) < today; }); return complete.length ? complete[complete.length - 1] : weeks[weeks.length - 1]; } function money_(n) { return '$' + Number(n).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ','); } function htmlEscape_(s) { return String(s).replace(/&/g, '&').replace(//g, '>'); } /** Sends ONE email, only to the person running the script. Returns a status string. */ function sendSummaryEmail_(ss, result, tz) { try { var to = Session.getActiveUser().getEmail() || Session.getEffectiveUser().getEmail(); if (!to) return 'Not sent: no email address available for the script owner'; var week = pickReportWeek_(result.summary, tz); if (!week) return 'Not sent: no billing data'; var th = 'style="text-align:left;padding:6px 10px;background:#1f3a5f;color:#fff;font-family:Arial,sans-serif;font-size:13px"'; var td = 'style="padding:6px 10px;border-bottom:1px solid #ddd;font-family:Arial,sans-serif;font-size:13px"'; var tdr = 'style="padding:6px 10px;border-bottom:1px solid #ddd;font-family:Arial,sans-serif;font-size:13px;text-align:right"'; function table(title, nameHeader, rows, amountHeader) { var html = '

' + title + '

' + ''; var total = 0; rows.forEach(function (r) { total += r[4]; html += ''; }); html += '
' + nameHeader + 'JobsHours' + amountHeader + '
' + htmlEscape_(r[1]) + '' + r[2] + '' + Number(r[3]).toFixed(2) + '' + money_(r[4]) + '
Total' + money_(total) + '
'; return html; } var inv = result.invoices.filter(function (r) { return r[0] === week; }); var pays = result.payouts.filter(function (r) { return r[0] === week; }); var weekIssues = result.issues.filter(function (r) { return r[0] === week; }).length; var body = '
' + '

Billing run finished for the week of ' + week + '.

' + table('Client totals', 'Client', inv, 'Billed') + table('Cleaner totals', 'Cleaner', pays, 'Pay') + '

' + weekIssues + ' item(s) need review for this week (' + result.issues.length + ' across all weeks). See the "Needs Review" tab.

' + '

Sent automatically to the spreadsheet owner only.

'; MailApp.sendEmail({ to: to, subject: 'Weekly billing summary - week of ' + week, htmlBody: body }); return 'Sent to ' + to + ' (week of ' + week + ')'; } catch (e) { Logger.log('Email failed: ' + e); return 'Email failed: ' + e.message; } }