// Service wrappers. These functions touch SpreadsheetApp or GmailApp, and they // pass the work they can delegate to the pure rules in Reminders.gs. const RENEWAL_SHEET_NAME_ = 'Renewals'; /** * Creates one Gmail draft per Active renewal due within 14 days that has no * Reminder drafted date, then records today's date in that row. * * @returns {void} */ function draftRenewalReminders() { const sheet = getRenewalSheet_(); const result = applyReminderPlan_( sheet, function (email) { GmailApp.createDraft(email.to, email.subject, email.body); }, new Date(), ); console.log(JSON.stringify(result)); } /** * Reads the sheet, plans reminders, calls createDraft for each one, and marks * each row immediately after its draft exists. * * @param {SpreadsheetApp.Sheet} sheet * @param {function(ReminderEmail): void} createDraft * @param {Date} today * @returns {{drafted: number[], problems: RowProblem[]}} */ function applyReminderPlan_(sheet, createDraft, today) { const values = sheet.getDataRange().getValues(); const problem = headerProblem_(values[0]); if (problem !== '') { throw new Error(problem); } const plan = planReminders_(values.slice(1), today); /** @type {number[]} */ const drafted = []; plan.reminders.forEach(function (email) { createDraft(email); sheet.getRange(email.rowNumber, REMINDER_DRAFTED_COLUMN_).setValue(today); drafted.push(email.rowNumber); }); return { drafted: drafted, problems: plan.problems }; } /** * Simple trigger: clears Reminder drafted when someone edits a Renewal date, * so the next run can draft a reminder for the new date. * * @param {{range: SpreadsheetApp.Range}} e The edit event; only its range is read. * @returns {void} */ function onEdit(e) { resetRemindersAfterEdit_(e, RENEWAL_SHEET_NAME_); } /** * @param {{range: SpreadsheetApp.Range}} e The edit event; only its range is read. * @param {string} sheetName The only sheet whose edits matter. * @returns {void} */ function resetRemindersAfterEdit_(e, sheetName) { const range = e.range; const sheet = range.getSheet(); if (sheet.getName() !== sheetName) { return; } const rows = reminderRowsToReset_(range.getRow(), range.getColumn(), range.getNumRows(), range.getNumColumns()); if (rows) { sheet.getRange(rows.firstRow, REMINDER_DRAFTED_COLUMN_, rows.numRows, 1).clearContent(); } } /** * @returns {SpreadsheetApp.Sheet} */ function getRenewalSheet_() { const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); if (!spreadsheet) { throw new Error('Run this from the Apps Script project bound to the test spreadsheet.'); } const sheet = spreadsheet.getSheetByName(RENEWAL_SHEET_NAME_); if (!sheet) { throw new Error('Add a sheet named Renewals and import renewals.csv into it.'); } return sheet; } /** * Editor smoke test. Writes fixture rows to a temporary sheet in the bound test * spreadsheet, runs the real Sheets code with a recording function in place of * Gmail, checks the results, and deletes the temporary sheet. Creates no drafts. * * @returns {void} */ function smokeTestReminders() { const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); if (!spreadsheet) { throw new Error('Run this from the Apps Script project bound to the test spreadsheet.'); } const today = new Date(2026, 4, 1); const sheet = spreadsheet.insertSheet('Smoke test ' + Date.now()); try { sheet.getRange(1, 1, 4, 5).setValues([ RENEWAL_HEADERS_, ['Ada Studio', 'ada@example.com', new Date(2026, 4, 10), 'Active', ''], ['Lin Bakery', 'lin@example.com', new Date(2026, 5, 30), 'Active', ''], ['Kai Garage', 'not-an-email', new Date(2026, 4, 3), 'Active', ''], ]); /** @type {ReminderEmail[]} */ const sent = []; const result = applyReminderPlan_( sheet, function (email) { sent.push(email); }, today, ); expectEqual_(result.drafted, [2], 'drafted rows'); expectEqual_(result.problems.map(function (p) { return p.rowNumber; }), [4], 'problem rows'); expectEqual_(sent.map(function (email) { return email.to; }), ['ada@example.com'], 'draft recipients'); expectEqual_(sent[0].subject, 'Your subscription renews in 9 days', 'subject'); const marked = sheet.getRange(2, REMINDER_DRAFTED_COLUMN_, 3, 1).getValues(); expectEqual_( marked.map(function (row) { return row[0] instanceof Date ? formatIsoDate_(row[0]) : row[0]; }), ['2026-05-01', '', ''], 'Reminder drafted column', ); const secondRun = applyReminderPlan_(sheet, function () {}, today); expectEqual_(secondRun.drafted, [], 'second run drafts nothing'); sheet.getRange('C2').setValue(new Date(2026, 4, 12)); resetRemindersAfterEdit_({ range: sheet.getRange('C2') }, sheet.getName()); expectEqual_(sheet.getRange('E2').getValue(), '', 'date edit clears Reminder drafted'); console.log('Smoke test passed.'); } finally { spreadsheet.deleteSheet(sheet); } } /** * @param {*} actual * @param {*} expected * @param {string} label * @returns {void} */ function expectEqual_(actual, expected, label) { const actualJson = JSON.stringify(actual); const expectedJson = JSON.stringify(expected); if (actualJson !== expectedJson) { throw new Error('Smoke test failed: ' + label + '. Expected ' + expectedJson + ', got ' + actualJson + '.'); } }