/** * Tinkery Satellite Network — signup backend * * 1. Appends submission to the responses sheet. * 2. Sends the submitter a branded welcome email (matching the Tinkery * newsletter design) with links to the CURRENT published newsletter * plus the previous one. Published-page URLs are stable (newest always * at root, prior at /previous/), so when a new newsletter ships there * is no code change required — new signups receive the current version. * * DEPLOY STEPS (one-time): * 1. Open the responses sheet: * https://docs.google.com/spreadsheets/d/1qJSQhU2-WWzrjBnO5JbBh12RhgjKC7VOcFQwsvutbcM/edit * 2. Extensions → Apps Script. * 3. Replace Code.gs with the contents of this file. Save. * 4. Deploy → Manage deployments → Edit the existing "Web app" deployment → * Version: New version → Deploy. (This KEEPS the same /exec URL that the * live form already uses.) * - Execute as: Me (admin@aitinkery.com) * - Who has access: Anyone * 5. First deploy only: Authorize scopes (MailApp, SpreadsheetApp). Approve. */ const SHEET_ID = '1qJSQhU2-WWzrjBnO5JbBh12RhgjKC7VOcFQwsvutbcM'; const SHEET_TAB = 'Sheet1'; const FROM_NAME = 'AI Tinkery'; const REPLY_TO = 'wilsong@stanford.edu'; // Published newsletter URLs — newest at root, previous at /previous/. const NEWSLETTER_URL = 'https://tinkery-newsletter-preview.pages.dev/'; const PREVIOUS_NEWSLETTER_URL = 'https://tinkery-newsletter-preview.pages.dev/previous/'; const NEWSLETTER_SUBJECT = 'Welcome to the Tinkery Network \u2014 here\u2019s the latest newsletter'; // Brand colors (match tinkery-newsletter-preview design). const BRAND_BG = '#f5e9cf'; // cream const BRAND_HEADER = '#153642'; // dark teal const BRAND_ACCENT = '#E8620A'; // tinkery orange const BRAND_INK = '#153642'; const BRAND_MUTED = '#5c6e76'; // ---------- Web app entry points ---------- function doPost(e) { try { const data = JSON.parse(e.postData.contents); const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_TAB) || SpreadsheetApp.openById(SHEET_ID).getActiveSheet(); appendRowByHeader_(sheet, data); try { sendWelcomeNewsletter_(data); } catch (err) { console.error('welcome email failed:', err); } return jsonOut_({status: 'ok'}); } catch (err) { console.error(err); return jsonOut_({status: 'error', message: String(err)}); } } function doGet(e) { return ContentService.createTextOutput('Tinkery Satellite Network backend OK'); } // ---------- Sheet append ---------- function appendRowByHeader_(sheet, data) { const lastCol = sheet.getLastColumn(); const headerValues = lastCol > 0 ? sheet.getRange(1, 1, 1, lastCol).getValues()[0] : []; const flat = Object.assign({}, data); ['aiTools', 'supportInterests', 'participation', 'gradeLevels'].forEach(function(k) { if (Array.isArray(flat[k])) flat[k] = flat[k].join(', '); }); if (headerValues.length && headerValues.some(function(h) { return String(h).trim() !== ''; })) { const row = headerValues.map(function(h) { const key = headerKeyToDataKey_(h); if (key === '__timestamp') return new Date(); return flat[key] != null ? flat[key] : ''; }); sheet.appendRow(row); return; } const order = [ 'fullName','email','jobTitle','schoolName','district','cityState', 'schoolType','gradeLevels','enrollment','numStaff','tinkeryStatus', 'motivation','currentSetup','aiTools','supportInterests','participation', 'willingToMentor','howHeard','additionalNotes' ]; if (sheet.getLastRow() === 0) { sheet.appendRow(['Timestamp'].concat(order)); sheet.setFrozenRows(1); } const row = [new Date()].concat(order.map(function(k) { return flat[k] != null ? flat[k] : ''; })); sheet.appendRow(row); } function headerKeyToDataKey_(header) { const h = String(header).trim().toLowerCase(); if (!h) return ''; if (h === 'timestamp' || h === 'date' || h === 'submitted') return '__timestamp'; const map = { 'name': 'fullName', 'full name': 'fullName', 'email': 'email', 'email address': 'email', 'job title': 'jobTitle', 'title': 'jobTitle', 'school': 'schoolName', 'school name': 'schoolName', 'organization': 'schoolName', 'district': 'district', 'city, state': 'cityState', 'location': 'cityState', 'school type': 'schoolType', 'grade levels': 'gradeLevels', 'grades': 'gradeLevels', 'enrollment': 'enrollment', 'students': 'enrollment', 'staff': 'numStaff', '# staff': 'numStaff', 'tinkery status': 'tinkeryStatus', 'status': 'tinkeryStatus', 'motivation': 'motivation', 'why': 'motivation', 'current setup': 'currentSetup', 'ai tools': 'aiTools', 'tools': 'aiTools', 'support interests': 'supportInterests', 'interests': 'supportInterests', 'participation': 'participation', 'willing to mentor': 'willingToMentor', 'mentor': 'willingToMentor', 'how heard': 'howHeard', 'how did you hear': 'howHeard', 'notes': 'additionalNotes', 'additional notes': 'additionalNotes', 'anything else': 'additionalNotes' }; if (map[h]) return map[h]; return h.replace(/[^a-z0-9]+(.)/g, function(_, c) { return c.toUpperCase(); }); } // ---------- Welcome email ---------- function sendWelcomeNewsletter_(data) { const toEmail = (data.email || '').trim(); if (!toEmail || toEmail.indexOf('@') < 1) return; const firstName = (data.fullName || '').split(' ')[0] || 'there'; const safeFirst = escapeHtml_(firstName); const htmlBody = buildHtmlEmail_(safeFirst); const plainBody = buildPlainEmail_(firstName); MailApp.sendEmail({ to: toEmail, subject: NEWSLETTER_SUBJECT, name: FROM_NAME, replyTo: REPLY_TO, body: plainBody, htmlBody: htmlBody }); } function buildHtmlEmail_(safeFirst) { // Email-safe HTML (table-based layout; inline styles) matching the // tinkery-newsletter-preview design: cream bg, dark teal header with // 🤖 emoji + "AI Tinkery · Stanford" title, serif typography, orange CTA. const serif = "Charter, 'Iowan Old Style', 'Palatino Linotype', Palatino, Georgia, serif"; return '' + '' + '' + '' + '
' + '' + // --- Teal header band (matches newsletter) --- '' + // --- Body --- '' + // --- Footer --- '' + '
' + '
The Tinkery Network
' + '
' + '🤖 AI Tinkery · Stanford' + '
' + '
' + '

Welcome to the Tinkery Network, ' + safeFirst + '!

' + '

Thanks for joining us. Below is the latest Tinkery Network newsletter so you\u2019re caught up on what\u2019s happening at the Tinkery and across the network.

' + '

Click through to read it \u2014 and reply to this email if anything in it sparks a question.

' + // CTA button '
' + 'Read the current newsletter \u2192' + '
' + '

Missed the one before that? Previous issue \u2192

' + '
' + '

See you around,

' + '

Gregory & Karin
' + 'AI Tinkery \u00b7 Stanford Accelerator for Learning

' + '
' + 'You\u2019re receiving this because you signed up for the Tinkery Network at aitinkery.com/network. Reply to this email to reach us directly.' + '
' + '
' + ''; } function buildPlainEmail_(firstName) { return '' + 'THE TINKERY NETWORK\n' + 'AI Tinkery \u00b7 Stanford\n\n' + '---\n\n' + 'Welcome to the Tinkery Network, ' + firstName + '!\n\n' + 'Thanks for joining us. Below is the latest Tinkery Network newsletter so ' + 'you\u2019re caught up on what\u2019s happening at the Tinkery and across the network. ' + 'Reply to this email if anything in it sparks a question.\n\n' + 'READ THE CURRENT NEWSLETTER:\n ' + NEWSLETTER_URL + '\n\n' + 'Previous issue:\n ' + PREVIOUS_NEWSLETTER_URL + '\n\n' + '---\n\n' + 'See you around,\n' + 'Gregory & Karin\n' + 'AI Tinkery \u00b7 Stanford Accelerator for Learning\n\n' + 'You\u2019re receiving this because you signed up for the Tinkery Network at ' + 'https://aitinkery.com/network. Reply to this email to reach us directly.'; } // ---------- helpers ---------- function jsonOut_(obj) { return ContentService.createTextOutput(JSON.stringify(obj)) .setMimeType(ContentService.MimeType.JSON); } function escapeHtml_(s) { return String(s) .replace(/&/g, '&').replace(//g, '>') .replace(/"/g, '"').replace(/'/g, '''); } /** Dev smoke test — run from the Apps Script editor to send yourself a preview. */ function testWelcomeEmail() { sendWelcomeNewsletter_({ fullName: 'Test Educator', email: Session.getActiveUser().getEmail(), }); }