/** * ARCHITECTURE OF GRACE — Screener Sync · Code.gs * --------------------------------------------------------------------------- * This is YOUR current script with ONE capability added: the teacher dashboard * can now READ the sheet back ("Pull from sheet"), so a reflection a student * completed on their phone becomes a full report on your computer. * * Everything else is unchanged — same Responses tab, same column order, same * write path, same logging, same cellValue_. * * ═══ WHAT YOU MUST DO ═══════════════════════════════════════════════════════ * 1. Paste this over your current Code.gs. * 2. Project Settings ▸ Script properties ▸ Add property: * Name: ADMIN_PULL_KEY * Value: a SECOND secret, different from BACKEND_AUTH_KEY. * 3. Deploy ▸ Manage deployments ▸ pencil ▸ Version: New version ▸ Deploy. * 4. On your computer only: dashboard ▸ Set up ▸ Connect your school's Sheet, * put ADMIN_PULL_KEY in the Passcode box, Save. * * ═══ WHY TWO KEYS ═══════════════════════════════════════════════════════════ * BACKEND_AUTH_KEY is published in aog-sync-config.js on the website so that a * student's phone can send its row. Anyone can read that file. Until now that * did not matter, because this script could only ever WRITE. * * The moment it can READ, a single key would mean anyone on the internet could * download every student's answers. So reading is gated by ADMIN_PULL_KEY, * which is never published anywhere — it lives here and in your own browser. * * ADMIN_PULL_KEY may also write, so your own laptop keeps working normally. * BACKEND_AUTH_KEY can only write, and never reads. */ /* ═══ SCRIPT VERSION ═══════════════════════════════════════════════════════ WHY THIS EXISTS. A deployed Web App runs a PINNED VERSION. Editing this file in the Apps Script editor changes nothing at all until someone does Deploy ▸ Manage deployments ▸ pencil ▸ Version: New version ▸ Deploy which keeps the same /exec URL. That is a good property — it means a backend change can never break a school that has not taken it. But it means the FAILURE MODE OF THIS WHOLE ARCHITECTURE IS SILENT STALENESS, not breakage. A school can run a year-old script, watch a feature quietly do nothing, and conclude the product is broken. Nothing anywhere could tell them otherwise, because this script never said which version it was. Now it does. json_() stamps `v` onto EVERY reply — every write, every pull, every error — so a teacher's dashboard learns the deployed version from traffic that already happens, with no extra request and no new failure mode. The dashboard compares it against what the site expects and says so in plain words. ⚠ BUMP THIS WHENEVER DEPLOYED BEHAVIOUR CHANGES, and add a line to the ladder. An integer that only goes up — no parsing, no ambiguity. ⚠ A reply with NO `v` AT ALL means version 5 or older. That is not an error and the dashboard must not render it as one; it is the exact case this was built to surface. ⚠ NOTHING IS EVER GATED ON THE VERSION. An old script keeps working, in both directions: a field this script does not know is ignored, and a field it expects but does not receive is written blank. Fail open. THE LADDER 1 Jun 2026 first sync — a different file (AoG-Sheet-Sync-Code.gs), one passcode for read and write. Superseded; do not run it. 2 Aug 2026 Responses + the ADMIN_PULL_KEY read path (two keys) 3 Aug 25 DailyCheckins tab + pullCheckins 4 Aug 27 ExitSlips tab + pullExitSlips 5 Aug 28 HomeObservations tab, forceTextCols_, aogAppend_ script lock 6 Aug 29 reports its own version 7 Aug 29 HomeCheckins tab 8 Aug 29 SupportCheckins tab - the adult team check-in stops sharing a tab with the student's own daily check-in, and the row is routed by checkinType so a v7 script keeps working unchanged 9 Sep 1 the Practice tab becomes READABLE. It has been written to since v8 and nothing could read it back, so a finished math practice reached the Sheet and stopped there. Returned from the EXISTING pullCheckins action, never a new one: an action this script does not recognise falls through to the screener write, so a new verb would put a garbage row in Responses on any building still running v8 ← THIS ONE ══════════════════════════════════════════════════════════════════════════ */ var SCRIPT_VERSION = 10; var SCRIPT_VERSION_DATE = '2026-09-01'; // v10: pctIndependent on Practice // v9: the Practice tab is readable var SHEET_NAME = 'Responses'; /* ═══ DAILY CHECK-IN LAYER (added 2026-08-25) ══════════════════════════════ Everything below that mentions CHECKIN_ is NEW and ADDITIVE. It writes to a SEPARATE tab. The Responses tab, its column order, its write path, its pull path and every existing report are untouched — nothing above or below this layer was edited. The tab is created on first use. Columns are read BY NAME from row 1, so a future check-in type can append columns on the right without breaking a thing. ─────────────────────────────────────────────────────────────────────────── */ var CHECKIN_SHEET_NAME = 'DailyCheckins'; /* ═══ THE ADULT TEAM CHECK-IN ═════════════════════════════════════════════ Its own tab as of v8, for the same reason the exit slip and the home observation have theirs: a different row shape and a different respondent. ⚠ IT USED TO SHARE DailyCheckins, and the comment there still explains why - "one tab, one row shape, two respondents". That was true when the adult form asked the same three things the student form did. It now asks twelve sentences across four domains and carries obs, flags and the fourth domain in `extra`, while the student form asks seven entirely different questions. One tab holding both meant every reader had to know which kind of row it was looking at before it could read a single column, and a teacher opening the sheet saw one support row wedged between forty student ones. ⚠ NO STUDENT-ANSWER COLUMNS HERE ON PURPOSE. arrival, feelingWords, readiness, need, connection, challenge, challengeImpact, contextTag, tellAdult and agency belong to the student's own check-in and would be permanently blank in this tab. A column that is always empty is a question nobody asked. ⚠ NOTHING IN THIS TAB IS A JUDGEMENT OF A CHILD. `pct` is how many of the domains this adult ticked, out of the number this build watches for - it is a description of one observation by one person in one room, never a score for the student. `extra.pctOf` says which denominator. And a blank means nobody ticked it, which is not the same as it not happening. ═══════════════════════════════════════════════════════════════════════ */ var SUPPORT_SHEET_NAME = 'SupportCheckins'; var SUPPORT_COLS = [ 'timestamp', // when it was submitted (ISO) 'date', // the school day it is ABOUT (YYYY-MM-DD) 'slipType', // checkin - WHICH INSTRUMENT wrote this row 'checkinType', // support - and it is what routes the row to this tab 'assignmentId', // which support plan / link this belongs to 'trackingGroup', 'term', 'districtId', 'schoolId', 'classId', 'grade', 'period', // the label: "Advisory" or "Period 3" 'periodNum', // 0-10 · 0 IS ADVISORY. Eleven periods, never eight. 'studentId', // the pseudonymous student code. THIS is the join. 'respondentRole', // teacher | special_educator | related_service | // social_worker | admin | other_staff 'respondentId', // the adult's own name or initials. ⚠ The form REFUSES // to save without one: a support timeline is only // readable if it says who saw what. 'regulated', // TRUE / FALSE - derived from the sentences ticked 'usedStrategy', // TRUE / FALSE 'connected', // TRUE / FALSE 'pct', // 0-100 · ⚠ the denominator belongs to the ROW, and // `extra.pctOf` records it. Never rescale an old row. 'note', // short free text from the adult 'followUp', // TRUE / FALSE - this adult wants to talk to someone 'source', // link | dashboard 'extra' // JSON: {obsSchema, engaged, obs[], flags[], pctOf} // ⚠ `flags` is a note to the student's team and is // NEVER scored, counted or ranked. ]; var CHECKIN_COLS = [ 'timestamp', // when it was submitted (ISO) 'date', // the school day it is ABOUT (YYYY-MM-DD) 'slipType', // checkin — WHICH INSTRUMENT wrote this row. The // ExitSlips tab answers the same field with 'exit'. // Added 2026-08-28, appended on the right by // getCheckinSheet(); nothing was renamed and no // existing column moved. 'checkinType', // daily | targeted | support | (future types) — WHICH // KIND of check-in. Not the same question as // slipType, and deliberately kept. 'assignmentId', // which support plan / link this belongs to 'trackingGroup', // e.g. Tier2-Fall26 'term', // e.g. Fall 2026 'districtId', 'schoolId', 'classId', 'grade', 'period', // the label: "Advisory" or "Period 3" 'periodNum', // 0-10 · 0 IS ADVISORY. Eleven periods, never eight. 'studentId', // the pseudonymous student code 'respondentRole', // teacher | social_worker | special_educator | student | parent | admin 'respondentId', // the submitting adult's own code or initials 'regulated', // TRUE / FALSE 'usedStrategy', // TRUE / FALSE 'connected', // TRUE / FALSE // ⚠ THREE COLUMNS, FOUR DOMAINS since build .29al. // Engagement is the fourth and it rides in `extra` // rather than taking a column of its own, so nobody // has to re-paste this script to keep reading these. // These three still mean exactly what they always // meant. They are DERIVED from the sentences the // adult actually ticked, and those are listed in // `extra.obs` — a domain is TRUE when any one of its // sentences was ticked. 'pct', // 0-100. ⚠ THE DENOMINATOR BELONGS TO THE ROW, NOT // TO THIS COMMENT. A row written before build .29al // is a percentage of THREE domains; a row from .29al // onward is a percentage of FOUR, because engagement // joined regulation, self-compassion and social & // repair. `extra` carries obsSchema:"v2" and pctOf, // so a reader never has to guess which it is holding. // ⚠ DO NOT RESCALE AN OLD ROW TO THE NEW DENOMINATOR. // A denominator that moves under data already // collected is a silent lie about what an adult saw. 'note', // short free text from the adult 'followUp', // TRUE / FALSE - this adult wants to talk to someone 'source', // link | dashboard /* --- the student's own daily check-in (respondentRole = "student"). An adult's row leaves these blank; a student's row leaves the three observation booleans blank. One tab, one row shape, two respondents. */ 'arrival', // 1-5 · how they are arriving 'feelingWords', // what they are noticing inside, comma separated 'readiness', // 1-5 · perceived access to the WORK. Never the same // question as arrival and never averaged with it. 'need', // what they need today 'connection', // 1-5 · how connected they feel 'challenge', // what is making today harder 'challengeImpact', // 1-5 · how much that barrier is getting in the way. // Reported impact, NOT a claim about cause. 'contextTag', // Morning | Midday | Afternoon (· #2, #3 …) — which // check-in of the day this row is. Read off the // clock; the student is never asked for it. 'tellAdult', // free text · something they want an adult to know 'agency', // one thing they can do for themselves today 'extra' // JSON, room for future question sets — and since // build .29al an adult's check-in row carries // {obsSchema, engaged, obs[], flags[], pctOf} here. // `obs` is WHICH sentences were ticked, which is what // a support person actually needs when the question // is what shows up in one room and not in another. // ⚠ `flags` IS A NOTE TO THE STUDENT'S TEAM AND IS // NEVER SCORED, COUNTED OR RANKED — not here, not // downstream. It is a person passing a note, not an // observation of a competency. ]; /* ═══ THE SCHOOL-DAY EXIT SLIP ════════════════════════════════════════════ Its own tab, on purpose. The Daily Check-In asks "how am I doing right now?" in the morning; the exit slip asks "what was my day?" on the way out. They are different questions on different clocks with different row shapes, and one tab holding both would mean every reader has to know which kind of row it is looking at before it can read a single column. ⚠ NOTHING IN THIS TAB IS A SCORE. There is no pct, no rating, no flag derived from a selection. "I was tired" is not a sleep problem, "Math was difficult" is not a math deficit, and "Someone upset me" is not an incident. This is student voice data; a human being interprets it. ═══════════════════════════════════════════════════════════════════════ */ var EXITSLIP_SHEET_NAME = 'ExitSlips'; var EXITSLIP_COLS = [ 'timestamp', // when it was submitted (ISO) 'date', // the school day it is ABOUT (YYYY-MM-DD) 'submitTime', // local clock time, for "who is doing this at 3:05" 'slipType', // exit — WHICH INSTRUMENT wrote this row. The // DailyCheckins tab answers the same field with // 'checkin'. One name, both tabs. 'districtId', 'schoolId', 'classId', 'grade', 'period', 'term', 'assignmentId', 'trackingGroup', 'studentId', // the pseudonymous student code 'respondentRole', // always "student" — an adult does not fill this in 'respondentId', 'classesAvailable', // the classes this student was actually offered, "|" separated 'scheduleSource', // classes | link | student — where that list came from 'favClass', // one class, or one of §06's escape hatches 'favWhy', // reasons, " · " separated 'hardClass', // one class, or one of §08's escape hatches 'hardWhy', // reasons, " · " separated 'goodMoment', // §10 'roughMoment', // §11 — "No — not really." is a real, complete answer 'response', // §12 — ⚠ NEVER RANK THESE. "I gave up" is information. 'closing', // §13 'dayWord', // §14 — a perception, not a mood assessment 'written', // §15 — optional, and almost always empty by design 'followUp', // TRUE only if they wrote something or asked for help 'source', // link | dashboard 'extra' // JSON, room for a future question set ]; /* ═══ HOME OBSERVATIONS ═══════════════════════════════════════════════════ The Home–School Goal Continuity Layer. A family taps how an ordinary moment went; the school already measures the same underlying skill under educational conditions. Its own tab, for the same reason the exit slip has one: a different row shape and a different respondent. ⚠ THIS IS NOT PROGRESS-MONITORING DATA AND MUST NEVER BE MERGED WITH IT. School data tells the team how a student is progressing under educational conditions. Home observations can show whether the same skill is turning up in another environment. Those are different claims, and the moment one tab holds both, nobody downstream can tell which they are reading. There is no pct, no score, no rating and no flag derived from a selection. ⚠ NOTHING HERE IDENTIFIES A CHILD BY NAME, NAMES A DISABILITY, OR CARRIES THE IEP GOAL. `homeKey` is an opaque token minted per goal on the teacher's own device; `skill` is the plain-language wording the teacher chose. The code-to-name list stays with the school, exactly as it does everywhere else in this product. ═══════════════════════════════════════════════════════════════════════ */ var HOME_SHEET_NAME = 'HomeObservations'; var HOME_COLS = [ 'timestamp', // when it was submitted (ISO) 'date', // the day it is ABOUT (YYYY-MM-DD) 'slipType', // home — WHICH INSTRUMENT wrote this row 'homeKey', // the opaque per-goal token. THIS is the join, and it // is deliberately not the goal id and not a name. 'skill', // the shared plain-language skill, e.g. "Getting started" 'level', // the English label the family tapped 'levelKey', // own | reminder | help | hard | na — a KEY, NOT A RANK. // ⚠ Do not sort, average or score it. §12: nothing // ranks the responses. 'districtId', 'schoolId', 'classId', 'grade', 'term', 'studentId', // the pseudonymous student code 'respondentRole', // always "family" 'timeOfDay', // Morning | Afternoon | Evening — optional 'activity', // Homework | Routine | Transition | Reading | Chore | // Community | Other — optional 'support', // Visual | Verbal reminder | Choice | Adult assistance | // Break | Other — optional 'note', // one optional line for the teacher 'followUp', // TRUE only if the family wrote something or asked to // be contacted. ⚠ NEVER raised by a hard day. 'source', // link 'extra' // JSON, room for a future question ]; var HOMECI_SHEET_NAME = 'HomeCheckins'; /* ⚠ EVERY *Key COLUMN IS A KEY, NOT A RANK. Do not sort it, do not average it, do not build a score out of it, and do not colour it. "hard" is not worse than "easy" in any arithmetic this product performs — the words are the whole record. And "na" means the person did not see that moment: it is EXCLUDED from every count on the way back out. */ var HOMECI_COLS = [ 'timestamp', // when it was submitted (ISO) 'date', // the day it is ABOUT (YYYY-MM-DD) 'submitTime', // local clock time, for reading a row like a person 'slipType', // homecheckin — WHICH INSTRUMENT wrote this row 'studentId', // the pseudonymous student code. THIS is the join. 'respondentId', // same code — this instrument has no separate identity 'respondentRole', // family | student — WHO ANSWERED, and they can differ // about the same evening on purpose. Do not reconcile. 'districtId', 'schoolId', 'classId', 'grade', 'term', 'morning', // the English label that was tapped 'morningKey', // easy | slow | prompted | hard | na 'afternoon', 'afternoonKey', // talking | quiet | wiped | wound | na 'evening', 'eveningKey', // settled | unread | carrying | hard | na 'helped', // English labels, pipe-separated, may be empty 'helpedKeys', // alone | food | talk | music | move | routine | nothing 'note', // free text, optional 'followUp', // TRUE only when somebody typed something or ticked the // box. NEVER derived from a hard answer. NEVER. 'source' // link ]; /* ═══ MATH PRACTICE ROWS (the Concepts & Data activity pages) ═════════════ One row per finished practice set on a c-series math activity page (c1-cut-it-fair and its siblings). Its own tab, on purpose: these are PROBE rows — trials independent vs supported — not check-ins, and the check-in readers score what they read. Routed as checkinType 'practice' INSIDE the existing checkin action, never as a new action, because an action this script does not recognise falls through to the screener write (the version-ladder rule at doPost). On a school still running an older paste of this script, a practice row lands in DailyCheckins with checkinType 'practice' — harmless, keyed out by every reader — instead of becoming garbage in Responses. `independent` and `supported` are counts out of `itemsTotal`; `confidence` is the student's own 1–5 and is never a score. */ var PRACTICE_SHEET_NAME = 'Practice'; var PRACTICE_COLS = [ 'timestamp', // when it was sent (ISO) 'date', // the school day it is about (YYYY-MM-DD) 'checkinType', // practice — same routing field the check-ins use 'activityId', // c1 … c11 'activityName', // "Cut It Fair" — the human name of the page 'skill', // the probe skill line, e.g. "Divide a whole into // equal parts (fair shares)" 'setNo', // 1 | 2 | 3 — which problem set was run 'itemsTotal', // how many problems the set holds (8) 'independent', // solved with no hints — count out of itemsTotal 'supported', // solved after a hint — count out of itemsTotal 'pctIndependent', // independent / itemsTotal as a whole number 0-100, // computed by this script, never sent by the page. // ⚠ NOT ACCURACY. A student cannot leave a problem // wrong, so 'correct' would be 100 every time; this // is how much of the set they did WITHOUT a hint. // Blank when itemsTotal is missing or zero. 'hintsUsed', // total hint taps across the set 'confidence', // 1-5, the student's own rating, may be blank. // ⚠ NEVER a score and never averaged with one. 'studentId', // the first name or code the student typed 'note', // optional free text 'source', // link 'extra' // JSON: {series, lang, build} ]; var SCALAR_COLS = [ 'timestamp', 'districtId', 'schoolId', 'classId', 'grade', 'window', 'studentId', 'language', 'mode', 'domainA', 'domainB', 'domainC', 'composite', 'normA', 'normB', 'normC', 'normComposite', 'tier', 'trustedAdultFlag', 'closingWord' ]; /* ═══ TAIL COLUMNS (added 2026-08-25) ══════════════════════════════════════ These three were being SENT by the website on every submission and written nowhere, so `unsafeFlag` — one of the two safety flags — survived on the teacher's own device and vanished from every row pulled back out of the Sheet. They are appended at the very END of the header, after reflection3, and ensureResponseColumns_() adds them to the right of a sheet that already has rows. NOTHING that was already in the header moves. Do not insert a name into SCALAR_COLS — that would shift every column after it. Deliberately NOT added: raw19 / intensity19. Item 19 is asked and not scored on purpose; it raises unsafeFlag instead of entering a composite, and adding its column would make it render as a 19th item in reports. ═══════════════════════════════════════════════════════════════════════════ */ var TAIL_COLS = [ 'unsafeFlag', // the second safety flag — the one that was being lost 'population', // k12 | adult 'context', // school | home (only school rows are ever synced) 'band' // the words a person should read, beside the internal tier ]; function headerRow() { var h = SCALAR_COLS.slice(); var i; for (i = 1; i <= 18; i++) { h.push('raw' + i); } for (i = 1; i <= 18; i++) { h.push('intensity' + i); } for (i = 1; i <= 3; i++) { h.push('reflection' + i); } for (i = 0; i < TAIL_COLS.length; i++) { h.push(TAIL_COLS[i]); } return h; } /** * Returns the Responses tab's LIVE header, after appending any name from * headerRow() that is missing from it — on the right, never in the middle. * * Why the write path now maps against this instead of against headerRow(): * a sheet that has been collecting rows since June has the June header. If a * new name were simply pushed onto headerRow(), appendRow would write one * extra cell into a column with no name above it, and readRecords_ — which * reads BY NAME — would skip it forever. Writing the name first is what makes * the new column real. * * For a sheet whose header already matches, this appends nothing and returns * exactly what was there. Existing column positions cannot move. */ function ensureResponseColumns_(sheet) { var want = headerRow(); var lastCol = sheet.getLastColumn(); if (lastCol < 1) { return want; } var header = sheet .getRange(1, 1, 1, lastCol) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var missing = want.filter(function (c) { return header.indexOf(c) === -1; }); if (missing.length) { sheet .getRange(1, lastCol + 1, 1, missing.length) .setValues([missing]) .setFontWeight('bold'); console.log('RESPONSES · added columns on the right: ' + missing.join(', ')); header = header.concat(missing); } return header; } function getSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(SHEET_NAME); if (!sheet) { sheet = ss.insertSheet(SHEET_NAME); } if (sheet.getLastRow() === 0) { var h = headerRow(); sheet.appendRow(h); sheet .getRange(1, 1, 1, h.length) .setFontWeight('bold'); sheet.setFrozenRows(1); } return sheet; } function doPost(e) { try { // Make sure a POST body actually arrived. if (!e || !e.postData || !e.postData.contents) { console.log('NO POST BODY'); return json_({ ok: false, error: 'No POST body received' }); } // Read the website submission. var body = JSON.parse(e.postData.contents); console.log( 'Received fields: ' + Object.keys(body).join(', ') ); // Check authentication. var props = PropertiesService.getScriptProperties(); var writeKey = props.getProperty('BACKEND_AUTH_KEY'); var pullKey = props.getProperty('ADMIN_PULL_KEY'); var supplied = body._backendAuth || body.passcode || body.auth || body.backendAuth || body.backendAuthKey; // -------------------------------------------------- // PULL — the teacher dashboard reading the sheet back // Requires ADMIN_PULL_KEY. The published write key // must never be able to read student answers. // -------------------------------------------------- if (body.action === 'pull') { if (!pullKey) { console.log('PULL BLOCKED · no ADMIN_PULL_KEY set'); return json_({ ok: false, error: 'Pull is turned off: add an ADMIN_PULL_KEY script property.' }); } if (supplied !== pullKey) { console.log('PULL DENIED · wrong key'); return json_({ ok: false, error: 'Unauthorized: this passcode cannot read the sheet. Use your ADMIN_PULL_KEY.' }); } var records = readRecords_(); console.log('PULL OK · returning ' + records.length + ' records'); return json_({ ok: true, records: records }); } // -------------------------------------------------- // DAILY CHECK-IN — WRITE (new tab, never Responses) // Either key may write, exactly like a screener row. // -------------------------------------------------- if (body.action === 'checkin') { var ciWrite = (writeKey && supplied === writeKey) || (pullKey && supplied === pullKey); if (!ciWrite) { console.log('CHECKIN AUTH FAILED'); return json_({ ok: false, error: 'Unauthorized' }); } if (!body.studentId || String(body.studentId).trim() === '') { return json_({ ok: false, error: 'A check-in needs a studentId.' }); } /* ⚠ ROUTED BY checkinType, NOT BY A NEW ACTION. doPost tests each `body.action` in turn and an action it does not recognise falls all the way through to the screener write, putting a garbage row in Responses - so a new action would be actively dangerous against a school still running v7. Routing inside the branch this script already understands means an older script keeps doing exactly what it does today, which is the fail-open rule the version ladder demands. */ var ciType = String(body.checkinType || ''); var ciSheet = (ciType === 'support') ? getSupportSheet() : (ciType === 'practice') ? getPracticeSheet() : getCheckinSheet(); /* ⚠ DERIVED HERE, NOT SENT. Every practice row gets pctIndependent, including rows from activity pages that know nothing about it, so the column starts working the moment this script is pasted and no page has to be redeployed to feed it. Silent when the counts are not both numbers -- a blank cell is honest, a 0 is a claim. */ if (ciType === 'practice') { var pTot = Number(body.itemsTotal); var pInd = Number(body.independent); body.pctIndependent = (isFinite(pTot) && pTot > 0 && isFinite(pInd)) ? Math.round((pInd / pTot) * 100) : ''; } var ciHeader = ciSheet .getRange(1, 1, 1, ciSheet.getLastColumn()) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var ciRow = ciHeader.map(function (col) { if (!col) { return ''; } return cellValue_(body[col]); }); aogAppend_(ciSheet, ciRow); console.log( 'CHECKIN SAVED · ' + body.checkinType + ' · student ' + body.studentId + ' · by ' + (body.respondentId || '(unnamed adult)') ); return json_({ ok: true, saved: true, checkin: true }); } // -------------------------------------------------- // SCHOOL-DAY EXIT SLIP — WRITE // Same gate as the check-in. Its own tab, its own // header, read by name so a future field can be // appended on the right without breaking a row. // -------------------------------------------------- if (body.action === 'exitslip') { var xsWrite = (writeKey && supplied === writeKey) || (pullKey && supplied === pullKey); if (!xsWrite) { console.log('EXIT SLIP AUTH FAILED'); return json_({ ok: false, error: 'Unauthorized' }); } if (!body.studentId || String(body.studentId).trim() === '') { return json_({ ok: false, error: 'An exit slip needs a studentId.' }); } var xsSheet = getExitSlipSheet(); var xsHeader = xsSheet .getRange(1, 1, 1, xsSheet.getLastColumn()) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var xsRow = xsHeader.map(function (col) { if (!col) { return ''; } return cellValue_(body[col]); }); aogAppend_(xsSheet, xsRow); console.log('EXIT SLIP SAVED · student ' + body.studentId + ' · ' + body.date); return json_({ ok: true, saved: true, exitslip: true }); } // -------------------------------------------------- // DAILY CHECK-IN — READ // Same gate as the screener pull: ADMIN_PULL_KEY only. // A published write key must never read a child's // day back out of the sheet. // -------------------------------------------------- if (body.action === 'pullCheckins') { if (!pullKey) { return json_({ ok: false, error: 'Pull is turned off: add an ADMIN_PULL_KEY script property.' }); } if (supplied !== pullKey) { return json_({ ok: false, error: 'Unauthorized: this passcode cannot read the sheet. Use your ADMIN_PULL_KEY.' }); } var checkins = readCheckins_(); var practice = readPracticeTab_(); console.log('CHECKIN PULL OK · returning ' + checkins.length + ' rows and ' + practice.length + ' practice rows'); /* `practice` is ADDITIVE. A client written against v8 reads `checkins` and never looks for it; a client written against v9 reads (out.practice || []) and an older script's silence is an empty list, not an error. Neither side needs to know the other's version. */ return json_({ ok: true, checkins: checkins, practice: practice }); } // -------------------------------------------------- // HOME OBSERVATION — WRITE // Same gate as the check-in and the exit slip. A // family's phone holds the same published write key // a student's phone does, and nothing more. // // ⚠ The row is joined on homeKey, never on a name. // -------------------------------------------------- if (body.action === 'home') { var hoWrite = (writeKey && supplied === writeKey) || (pullKey && supplied === pullKey); if (!hoWrite) { console.log('HOME OBSERVATION AUTH FAILED'); return json_({ ok: false, error: 'Unauthorized' }); } if (!body.homeKey || String(body.homeKey).trim() === '') { return json_({ ok: false, error: 'A home observation needs a homeKey.' }); } var hoSheet = getHomeSheet(); var hoHeader = hoSheet .getRange(1, 1, 1, hoSheet.getLastColumn()) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var hoRow = hoHeader.map(function (col) { if (!col) { return ''; } return cellValue_(body[col]); }); aogAppend_(hoSheet, hoRow); console.log( 'HOME OBSERVATION SAVED · ' + body.skill + ' · key ' + body.homeKey ); return json_({ ok: true, saved: true, home: true }); } // -------------------------------------------------- // HOME CHECK-IN — WRITE // The rest of the day: morning before school, the // afternoon after it, and how the evening went. // // ⚠ Joined on studentId — the pseudonymous code — // never on a name. Nothing in this row identifies a // child, names a disability or carries an IEP goal. // -------------------------------------------------- if (body.action === 'homecheckin') { var hcWrite = (writeKey && supplied === writeKey) || (pullKey && supplied === pullKey); if (!hcWrite) { console.log('HOME CHECK-IN AUTH FAILED'); return json_({ ok: false, error: 'Unauthorized' }); } if (!body.studentId || String(body.studentId).trim() === '') { return json_({ ok: false, error: 'A home check-in needs a studentId.' }); } var hcSheet = getHomeCheckinSheet(); var hcHeader = hcSheet .getRange(1, 1, 1, hcSheet.getLastColumn()) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var hcRow = hcHeader.map(function (col) { if (!col) { return ''; } return cellValue_(body[col]); }); aogAppend_(hcSheet, hcRow); console.log( 'HOME CHECK-IN SAVED · ' + body.studentId + ' · ' + body.respondentRole ); return json_({ ok: true, saved: true, homecheckin: true }); } // -------------------------------------------------- // HOME OBSERVATION — READ // Its own action, its own tab, the ADMIN_PULL_KEY // only — for the same reason the exit slip's read is // gated: the write key is published in a page every // family loads. // -------------------------------------------------- if (body.action === 'pullHome') { if (!pullKey) { return json_({ ok: false, error: 'Pull is turned off: add an ADMIN_PULL_KEY script property.' }); } if (supplied !== pullKey) { return json_({ ok: false, error: 'Unauthorized: this passcode cannot read the sheet. Use your ADMIN_PULL_KEY.' }); } var homeObs = readHomeObs_(); console.log('HOME PULL OK · returning ' + homeObs.length + ' rows'); return json_({ ok: true, homeObs: homeObs }); } // -------------------------------------------------- // HOME CHECK-IN — READ // Its own action, its own tab, the ADMIN_PULL_KEY // only — the write key is published inside a page // every family loads, so if it could read as well, // anyone holding a link could download every // classmate's evenings. // -------------------------------------------------- if (body.action === 'pullHomeCheckins') { if (!pullKey) { return json_({ ok: false, error: 'Pull is turned off: add an ADMIN_PULL_KEY script property.' }); } if (supplied !== pullKey) { return json_({ ok: false, error: 'Unauthorized: this passcode cannot read the sheet. Use your ADMIN_PULL_KEY.' }); } var homeCi = readHomeCheckins_(); console.log('HOME CHECK-IN PULL OK · returning ' + homeCi.length + ' rows'); return json_({ ok: true, homeCheckins: homeCi }); } // -------------------------------------------------- // SCHOOL-DAY EXIT SLIP — READ // Same gate, same reason. The write key is published // inside a page every student loads; if it could read // as well, any child with the developer tools open // could download every classmate's day. // // Its own action rather than a flag on pullCheckins: // the two tabs have different row shapes, and a // caller that asks for one and silently gets the // other is how a reader ends up rendering blank // columns and calling them missing answers. // -------------------------------------------------- if (body.action === 'pullExitSlips') { if (!pullKey) { return json_({ ok: false, error: 'Pull is turned off: add an ADMIN_PULL_KEY script property.' }); } if (supplied !== pullKey) { return json_({ ok: false, error: 'Unauthorized: this passcode cannot read the sheet. Use your ADMIN_PULL_KEY.' }); } var exitSlips = readExitSlips_(); console.log('EXIT SLIP PULL OK · returning ' + exitSlips.length + ' rows'); return json_({ ok: true, exitSlips: exitSlips }); } // Either key may WRITE, so your own laptop keeps working // whichever passcode it has saved. var mayWrite = (writeKey && supplied === writeKey) || (pullKey && supplied === pullKey); if (!mayWrite) { console.log('AUTH FAILED'); return json_({ ok: false, error: 'Unauthorized' }); } var sheet = getSheet(); // -------------------------------------------------- // CONNECTION TEST // -------------------------------------------------- if ( body.connectionTest === true || body.ping === true ) { sheet.appendRow([ new Date().toISOString(), '', '', '', '', '', '(connection test)' ]); console.log('CONNECTION TEST SAVED'); return json_({ ok: true, connectionTest: true }); } // -------------------------------------------------- // REAL SCREENER SUBMISSION // -------------------------------------------------- /* The live header, not headerRow() — see ensureResponseColumns_. This is the ONLY change to the write path: same columns, same order, same cellValue_, mapped against the names that are actually in row 1. */ var h = ensureResponseColumns_(sheet); var raw = Array.isArray(body.raw) ? body.raw : []; var intens = Array.isArray(body.intensities) ? body.intensities : []; var refl = Array.isArray(body.reflections) ? body.reflections : []; var row = h.map(function(col) { var m; var value = ''; // raw1 - raw18 m = col.match(/^raw(\d+)$/); if (m) { value = raw[Number(m[1]) - 1]; return cellValue_(value); } // intensity1 - intensity18 m = col.match(/^intensity(\d+)$/); if (m) { value = intens[Number(m[1]) - 1]; return cellValue_(value); } // reflection1 - reflection3 m = col.match(/^reflection(\d+)$/); if (m) { value = refl[Number(m[1]) - 1]; return cellValue_(value); } // Everything else comes directly // from the submitted website data. value = body[col]; return cellValue_(value); }); console.log( 'Writing ' + row.length + ' columns to Responses' ); // Write the completed screener row. aogAppend_(sheet, row); console.log('SCREENER SAVED SUCCESSFULLY'); return json_({ ok: true, saved: true, columnsWritten: row.length }); } catch (err) { console.log( 'WRITE ERROR: ' + String(err) ); return json_({ ok: false, error: String(err) }); } } /** * Reads the Responses tab and rebuilds each row into the record shape the * dashboard's report renderer expects — crucially, raw1..raw18 and * intensity1..intensity18 are folded back into the arrays the report needs * to show a student's individual answers, focus areas, conversation starters * and suggested tools. Columns are read BY NAME from the header row, so this * keeps working even if columns are ever added on the right. * * SELFTEST and (connection test) rows are left out. */ function readRecords_() { var sheet = getSheet(); var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); if (lastRow < 2 || lastCol < 1) { return []; } var values = sheet.getRange(1, 1, lastRow, lastCol).getValues(); var header = values[0].map(function (v) { return String(v || '').trim(); }); var out = []; for (var r = 1; r < values.length; r++) { var rowVals = values[r]; var rec = { raw: [], intensities: [] }; for (var c = 0; c < header.length; c++) { var col = header[c]; if (!col) { continue; } var v = rowVals[c]; var m; m = col.match(/^raw(\d+)$/); if (m) { rec.raw[Number(m[1]) - 1] = numOrNull_(v); continue; } m = col.match(/^intensity(\d+)$/); if (m) { rec.intensities[Number(m[1]) - 1] = numOrNull_(v); continue; } // Sheets can hand a timestamp back as a Date; the dashboard keys // records off the ISO string it originally sent. if (col === 'timestamp' && v instanceof Date) { rec.timestamp = v.toISOString(); continue; } rec[col] = v; } var id = String(rec.studentId == null ? '' : rec.studentId).trim(); if (id === '') { continue; } if (id === 'SELFTEST') { continue; } if (id === '(connection test)') { continue; } out.push(rec); } return out; } function numOrNull_(v) { if (v === '' || v === null || v === undefined) { return null; } var n = Number(v); return isNaN(n) ? null : n; } function doGet(e) { // Deliberately status-only. Reading student answers happens over POST with // ADMIN_PULL_KEY, so a passcode never travels in a URL or a browser history. /* Still status-only — no data path through a URL. The version is here as well as on every POST reply so that IT, or anyone debugging, can open the /exec link in a browser and read which version is actually live. */ return json_({ ok: true, service: 'AoG Screener Sync', status: 'connected', version: SCRIPT_VERSION, versionDate: SCRIPT_VERSION_DATE }); } function runWriteTest() { var sheet = getSheet(); var h = headerRow(); var row = h.map(function(col) { if (col === 'studentId') { return 'SELFTEST'; } if (col === 'timestamp') { return new Date().toISOString(); } return ''; }); sheet.appendRow(row); } /* Run this from the editor once after setup. Prints key lengths only. */ function checkKeys() { var p = PropertiesService.getScriptProperties(); var w = p.getProperty('BACKEND_AUTH_KEY') || ''; var a = p.getProperty('ADMIN_PULL_KEY') || ''; var msg = 'BACKEND_AUTH_KEY (write): ' + (w ? w.length + ' chars' : 'NOT SET') + ' · ADMIN_PULL_KEY (read): ' + (a ? a.length + ' chars' : 'NOT SET — pull is off') + ' · rows: ' + Math.max(0, getSheet().getLastRow() - 1); console.log(msg); return msg; } function cellValue_(v) { if (v === null || v === undefined) { return ''; } // If the website sends an object or array, // convert it to text so Google Sheets can store it. if (typeof v === 'object') { return JSON.stringify(v); } return v; } function safe_(v) { if (v === null || v === undefined) { return ''; } return v; } /** * aogAppend_ — the ONE safe way to add a row. * * WHY THIS EXISTS: a Web App deployed "Anyone" runs concurrent requests in * parallel. appendRow reads the last row and then writes to it, so when a * whole class presses Finish inside the same few seconds, two executions can * read the same last row and the second silently overwrites the first. The * student sees "saved" and their row is simply not there. * * A script lock serialises the read-then-write. Waiting 30s is deliberate: * a class of 30 finishing at once is well inside that, and a student would * rather wait a beat than lose their answers. flush() forces the write out * before the lock is released, so the next execution reads the true last row. */ function aogAppend_(sheet, row) { var lock = LockService.getScriptLock(); try { lock.waitLock(30000); } catch (err) { // Could not get the lock in 30s. Say so honestly rather than pretend. console.log('LOCK TIMEOUT — row NOT written'); throw new Error('The sheet was busy. Please try again in a moment.'); } try { sheet.appendRow(row); SpreadsheetApp.flush(); } finally { lock.releaseLock(); } } function json_(obj) { /* ⚠ STAMPED IN ONE PLACE, ON PURPOSE. Every reply this script makes — saved, rejected, pulled, errored — carries the deployed version, so a dashboard learns it from traffic that already happens rather than from a second request that could fail on its own. Doing this at each return instead would mean the one path somebody forgot is the one a stale school is stuck on. Written defensively: if obj is not an object, or already carries a v, it is left exactly as it was. This function is on every response path and must never be the thing that throws. */ try { if (obj && typeof obj === 'object' && !('v' in obj)) { obj.v = SCRIPT_VERSION; obj.vDate = SCRIPT_VERSION_DATE; } } catch (err) {} return ContentService .createTextOutput( JSON.stringify(obj) ) .setMimeType( ContentService.MimeType.JSON ); } /* ═══ DAILY CHECK-IN HELPERS ═══════════════════════════════════════════════ New functions only. Nothing above was changed. ═══════════════════════════════════════════════════════════════════════════ */ /** * The DailyCheckins tab, created with its header on first use. * * If the tab already exists, any column in CHECKIN_COLS that is missing from * row 1 is APPENDED ON THE RIGHT. Existing columns never move, so a sheet a * school has already started filling keeps every row readable. */ function getCheckinSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(CHECKIN_SHEET_NAME); if (!sheet) { sheet = ss.insertSheet(CHECKIN_SHEET_NAME); } if (sheet.getLastRow() === 0) { sheet.appendRow(CHECKIN_COLS); sheet .getRange(1, 1, 1, CHECKIN_COLS.length) .setFontWeight('bold'); sheet.setFrozenRows(1); forceTextCols_(sheet, CHECKIN_COLS, ['timestamp', 'date', 'submitTime']); return sheet; } // Tab exists — add only what is missing, on the right. var lastCol = sheet.getLastColumn(); var header = sheet .getRange(1, 1, 1, lastCol) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var missing = CHECKIN_COLS.filter(function (c) { return header.indexOf(c) === -1; }); if (missing.length) { sheet .getRange(1, lastCol + 1, 1, missing.length) .setValues([missing]) .setFontWeight('bold'); console.log('CHECKIN TAB · added columns: ' + missing.join(', ')); } return sheet; } /** * ⚠ KEEP THE TIME COLUMNS AS TEXT. * * Sheets helpfully coerces what we write. "2026-08-27" becomes a date value * and "3:10 PM" becomes a TIME value — and a time-only cell comes back * through getValues() as a Date on the 1899-12-30 epoch. The website then * rendered rows as * * Invalid Date · 1899-12-31T00:28:00.000Z * * on a teacher's own screen. The reader now copes with that for sheets that * have already been filled, but a sheet created from here should never * produce it in the first place: these columns are written and read as * strings, so they are pinned to plain text ('@'). * * Only the named columns, never the whole sheet — a district building a * QUERY over this tab still wants real values everywhere else. */ function forceTextCols_(sheet, cols, names) { var rows = sheet.getMaxRows(); names.forEach(function (name) { var i = cols.indexOf(name); if (i < 0) { return; } sheet.getRange(1, i + 1, rows, 1).setNumberFormat('@'); }); } /** * The HomeObservations tab, created with its header on first use. * * Same contract as getCheckinSheet and getExitSlipSheet: existing columns * NEVER move, anything in HOME_COLS that row 1 is missing is appended on the * right. Re-pasting this script over a sheet a school has already been * filling is safe. */ function getHomeSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(HOME_SHEET_NAME); if (!sheet) { sheet = ss.insertSheet(HOME_SHEET_NAME); } if (sheet.getLastRow() === 0) { sheet.appendRow(HOME_COLS); sheet .getRange(1, 1, 1, HOME_COLS.length) .setFontWeight('bold'); sheet.setFrozenRows(1); forceTextCols_(sheet, HOME_COLS, ['timestamp', 'date', 'submitTime']); return sheet; } var lastCol = sheet.getLastColumn(); var header = sheet .getRange(1, 1, 1, lastCol) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var missing = HOME_COLS.filter(function (c) { return header.indexOf(c) === -1; }); if (missing.length) { sheet .getRange(1, lastCol + 1, 1, missing.length) .setValues([missing]) .setFontWeight('bold'); console.log('HOME TAB · added columns: ' + missing.join(', ')); } return sheet; } /** * The ExitSlips tab, created with its header on first use. * * Same contract as getCheckinSheet: existing columns NEVER move, anything in * EXITSLIP_COLS that row 1 is missing is appended on the right. Re-pasting * this script over a sheet a school has already been filling is safe. */ function getExitSlipSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(EXITSLIP_SHEET_NAME); if (!sheet) { sheet = ss.insertSheet(EXITSLIP_SHEET_NAME); } if (sheet.getLastRow() === 0) { sheet.appendRow(EXITSLIP_COLS); sheet .getRange(1, 1, 1, EXITSLIP_COLS.length) .setFontWeight('bold'); sheet.setFrozenRows(1); forceTextCols_(sheet, EXITSLIP_COLS, ['timestamp', 'date', 'submitTime']); return sheet; } var lastCol = sheet.getLastColumn(); var header = sheet .getRange(1, 1, 1, lastCol) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var missing = EXITSLIP_COLS.filter(function (c) { return header.indexOf(c) === -1; }); if (missing.length) { sheet .getRange(1, lastCol + 1, 1, missing.length) .setValues([missing]) .setFontWeight('bold'); console.log('EXIT SLIP TAB · added columns: ' + missing.join(', ')); } return sheet; } /** * The SupportCheckins tab, created with its header on first use. * * Same contract as getCheckinSheet: existing columns NEVER move, anything in * SUPPORT_COLS that row 1 is missing is appended on the right. Re-pasting this * script over a sheet a school has already been filling is safe. */ function getSupportSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(SUPPORT_SHEET_NAME); if (!sheet) { sheet = ss.insertSheet(SUPPORT_SHEET_NAME); } if (sheet.getLastRow() === 0) { sheet.appendRow(SUPPORT_COLS); sheet .getRange(1, 1, 1, SUPPORT_COLS.length) .setFontWeight('bold'); sheet.setFrozenRows(1); forceTextCols_(sheet, SUPPORT_COLS, ['timestamp', 'date', 'submitTime']); return sheet; } var lastCol = sheet.getLastColumn(); var header = sheet .getRange(1, 1, 1, lastCol) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var missing = SUPPORT_COLS.filter(function (c) { return header.indexOf(c) === -1; }); if (missing.length) { sheet .getRange(1, lastCol + 1, 1, missing.length) .setValues([missing]) .setFontWeight('bold'); console.log('SUPPORT TAB · added columns: ' + missing.join(', ')); } return sheet; } /** * The Practice tab, created with its header on first use. * * Same contract as getCheckinSheet: existing columns NEVER move, anything in * PRACTICE_COLS that row 1 is missing is appended on the right. Re-pasting * this script over a sheet already being filled is safe. */ function getPracticeSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(PRACTICE_SHEET_NAME); if (!sheet) { sheet = ss.insertSheet(PRACTICE_SHEET_NAME); } if (sheet.getLastRow() === 0) { sheet.appendRow(PRACTICE_COLS); sheet .getRange(1, 1, 1, PRACTICE_COLS.length) .setFontWeight('bold'); sheet.setFrozenRows(1); forceTextCols_(sheet, PRACTICE_COLS, ['timestamp', 'date']); return sheet; } var lastCol = sheet.getLastColumn(); var header = sheet .getRange(1, 1, 1, lastCol) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var missing = PRACTICE_COLS.filter(function (c) { return header.indexOf(c) === -1; }); if (missing.length) { sheet .getRange(1, lastCol + 1, 1, missing.length) .setValues([missing]) .setFontWeight('bold'); console.log('PRACTICE TAB · added columns: ' + missing.join(', ')); } return sheet; } /** * Reads the DailyCheckins tab back as plain objects/* ⚠ BOTH TABS, THROUGH THE ACTION THAT ALREADY EXISTS. A `pullSupport` action would fall through to the screener write on any school still on v7 and put a garbage row in Responses, so there is deliberately no new read action: the rows come back concatenated and every reader downstream already keys them by respondentRole and checkinType. Rows written before v8 are still in DailyCheckins and still come back - nothing was migrated behind anyone. */ /* ═══ READ THE PRACTICE TAB ════════════════════════════════════════════════ The math activity pages write one row per finished set. These are PROBE rows - independent vs supported out of itemsTotal - and they are returned beside the check-ins rather than mixed into them, because a check-in reader scores what it reads and a practice row has none of the three adult observations to score. ⚠ A building that was still on v7 when a practice row arrived has that row sitting in DailyCheckins with checkinType 'practice'. Those are filtered here so one cannot be read back as a check-in; the client folds them in. */ function readPracticeTab_() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(PRACTICE_SHEET_NAME); if (!sheet) { return []; } var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); if (lastRow < 2 || lastCol < 1) { return []; } var values = sheet.getRange(1, 1, lastRow, lastCol).getValues(); var header = values[0].map(function (v) { return String(v || '').trim(); }); /* counts, not answers: a blank stays blank so a module that scores in its own currency is never read back as a zero */ var NUMS = ['setNo', 'itemsTotal', 'independent', 'supported', 'hintsUsed', 'confidence', 'pctIndependent']; var out = []; for (var r = 1; r < values.length; r++) { var rowVals = values[r]; var rec = {}; for (var c = 0; c < header.length; c++) { var col = header[c]; if (!col) { continue; } var v = rowVals[c]; if ((col === 'timestamp' || col === 'date') && v instanceof Date) { rec[col] = v.toISOString(); continue; } if (NUMS.indexOf(col) !== -1) { if (v === '' || v === null || v === undefined) { rec[col] = null; continue; } var n = Number(v); rec[col] = isNaN(n) ? null : n; continue; } rec[col] = v; } var sid = String(rec.studentId == null ? '' : rec.studentId).trim(); if (sid === '') { continue; } if (sid === 'SELFTEST') { continue; } if (sid === '(connection test)') { continue; } out.push(rec); } return out; } function readCheckins_() { return readCheckinTab_(CHECKIN_SHEET_NAME).concat(readCheckinTab_(SUPPORT_SHEET_NAME)); } function readCheckinTab_(tabName) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(tabName); if (!sheet) { return []; } var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); if (lastRow < 2 || lastCol < 1) { return []; } var values = sheet.getRange(1, 1, lastRow, lastCol).getValues(); var header = values[0].map(function (v) { return String(v || '').trim(); }); var BOOLS = ['regulated', 'usedStrategy', 'connected', 'followUp']; var out = []; for (var r = 1; r < values.length; r++) { var rowVals = values[r]; var rec = {}; for (var c = 0; c < header.length; c++) { var col = header[c]; if (!col) { continue; } var v = rowVals[c]; if ((col === 'timestamp' || col === 'date') && v instanceof Date) { rec[col] = v.toISOString(); continue; } if (BOOLS.indexOf(col) !== -1) { /* A blank stays null. A student's row leaves the three adult observations empty, and reading those back as FALSE would put a day the student never described into an adult's trend as zeros. */ if (v === '' || v === null || v === undefined) { rec[col] = null; continue; } rec[col] = (v === true) || (String(v).trim().toUpperCase() === 'TRUE'); continue; } rec[col] = v; } var sid = String(rec.studentId == null ? '' : rec.studentId).trim(); if (sid === '') { continue; } if (sid === 'SELFTEST') { continue; } if (sid === '(connection test)') { continue; } out.push(rec); } return out; } /* ═══ READING EXIT SLIPS BACK ═════════════════════════════════════════════ The teacher panel's Pull. Header-driven exactly like readCheckins_, so a sheet whose columns were appended in a different order — or which has a column this build has never heard of — still comes back correctly keyed. ⚠ THE ONLY BOOLEAN IS followUp, AND A BLANK STAYS NULL. Reading an empty cell back as FALSE would assert that a student did not ask for help, when the truth is that nothing was recorded either way. §29's rule cuts both directions: do not invent an alarm, and do not invent an all-clear. ⚠ NOTHING IS SCORED, SUMMED OR RANKED HERE. This function hands back the rows as written and stops. If a future version of it starts computing anything, read §25 again first. ═══════════════════════════════════════════════════════════════════════ */ /* Reads the HomeObservations tab back, by header name. ⚠ NOTHING IS SCORED, SUMMED OR RANKED HERE. It hands back the rows as written and stops — the same rule readExitSlips_ keeps, for the same reason. Interpretation belongs to a human being who knows the child. */ function readHomeObs_() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(HOME_SHEET_NAME); if (!sheet) { return []; } var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); if (lastRow < 2 || lastCol < 1) { return []; } var values = sheet.getRange(1, 1, lastRow, lastCol).getValues(); var header = values[0].map(function (v) { return String(v || '').trim(); }); var out = []; for (var r = 1; r < values.length; r++) { var rowVals = values[r]; var rec = {}; for (var c = 0; c < header.length; c++) { var col = header[c]; if (!col) { continue; } var v = rowVals[c]; if ((col === 'timestamp' || col === 'date') && v instanceof Date) { rec[col] = v.toISOString(); continue; } if (col === 'followUp') { if (v === '' || v === null || v === undefined) { rec[col] = null; continue; } rec[col] = (v === true) || (String(v).trim().toUpperCase() === 'TRUE'); continue; } rec[col] = v; } var hk = String(rec.homeKey == null ? '' : rec.homeKey).trim(); if (hk === '') { continue; } out.push(rec); } return out; } function readExitSlips_() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(EXITSLIP_SHEET_NAME); if (!sheet) { return []; } var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); if (lastRow < 2 || lastCol < 1) { return []; } var values = sheet.getRange(1, 1, lastRow, lastCol).getValues(); var header = values[0].map(function (v) { return String(v || '').trim(); }); var out = []; for (var r = 1; r < values.length; r++) { var rowVals = values[r]; var rec = {}; for (var c = 0; c < header.length; c++) { var col = header[c]; if (!col) { continue; } var v = rowVals[c]; if ((col === 'timestamp' || col === 'date') && v instanceof Date) { rec[col] = v.toISOString(); continue; } if (col === 'followUp') { if (v === '' || v === null || v === undefined) { rec[col] = null; continue; } rec[col] = (v === true) || (String(v).trim().toUpperCase() === 'TRUE'); continue; } rec[col] = v; } var sid = String(rec.studentId == null ? '' : rec.studentId).trim(); if (sid === '') { continue; } if (sid === 'SELFTEST') { continue; } if (sid === '(connection test)') { continue; } /* The date is the join key on the way back in — a row without one would land in the wrong day or in no day at all. Fall back to the timestamp's own date rather than dropping a student's slip. */ if (!rec.date && rec.timestamp) { rec.date = String(rec.timestamp).slice(0, 10); } out.push(rec); } return out; } /* Run this from the editor once after pasting, to prove the new tab works. It writes one clearly-labelled row you can delete. */ function checkinSelfTest() { var sheet = getCheckinSheet(); var header = sheet .getRange(1, 1, 1, sheet.getLastColumn()) .getValues()[0]; var demo = { timestamp: new Date().toISOString(), date: Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd'), checkinType: 'support', studentId: 'SELFTEST', respondentRole: 'teacher', respondentId: 'SELFTEST', period: 'Period 1', regulated: true, usedStrategy: false, connected: true, pct: 67, // ⚠ TWO OF THREE, AND IT STAYS THAT WAY. This row is // written straight to the tab with no `extra`, so it // is shaped exactly like a pre-.29al row and a reader // will score it out of three. Changing it to 75 would // make the self-test lie about its own denominator. note: 'Delete this row.', followUp: false, source: 'dashboard' }; sheet.appendRow(header.map(function (col) { return cellValue_(demo[String(col || '').trim()]); })); var msg = 'DailyCheckins tab OK · ' + header.length + ' columns · one SELFTEST row written (delete it).'; console.log(msg); return msg; } /** * The HomeCheckins tab. * * Same contract as getHomeSheet: existing columns NEVER move, anything in * HOMECI_COLS that row 1 is missing is appended on the right. Re-pasting this * script over a sheet a school has already been filling is safe. */ function getHomeCheckinSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(HOMECI_SHEET_NAME); if (!sheet) { sheet = ss.insertSheet(HOMECI_SHEET_NAME); } if (sheet.getLastRow() === 0) { sheet.appendRow(HOMECI_COLS); sheet .getRange(1, 1, 1, HOMECI_COLS.length) .setFontWeight('bold'); sheet.setFrozenRows(1); forceTextCols_(sheet, HOMECI_COLS, ['timestamp', 'date', 'submitTime']); return sheet; } var lastCol = sheet.getLastColumn(); var header = sheet .getRange(1, 1, 1, lastCol) .getValues()[0] .map(function (v) { return String(v || '').trim(); }); var missing = HOMECI_COLS.filter(function (c) { return header.indexOf(c) === -1; }); if (missing.length) { sheet .getRange(1, lastCol + 1, 1, missing.length) .setValues([missing]) .setFontWeight('bold'); console.log('HOME CHECK-IN TAB · added columns: ' + missing.join(', ')); } return sheet; } /** * Every home check-in on the tab, as objects, exactly as they were written. * * ⚠ IT RETURNS WHAT WAS WRITTEN AND STOPS. No counting, no ranking, no * "overall", no derived flag — the same rule readExitSlips_ and readHomeObs_ * both keep, for the same reason. Interpretation belongs to a human being who * knows the child. */ function readHomeCheckins_() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(HOMECI_SHEET_NAME); if (!sheet) { return []; } var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); if (lastRow < 2 || lastCol < 1) { return []; } var values = sheet.getRange(1, 1, lastRow, lastCol).getValues(); var header = values[0].map(function (v) { return String(v || '').trim(); }); var out = []; for (var r = 1; r < values.length; r++) { var rowVals = values[r]; var rec = {}; for (var c = 0; c < header.length; c++) { var col = header[c]; if (!col) { continue; } var v = rowVals[c]; if ((col === 'timestamp' || col === 'date') && v instanceof Date) { rec[col] = v.toISOString(); continue; } if (col === 'followUp') { if (v === '' || v === null || v === undefined) { rec[col] = null; continue; } rec[col] = (v === true) || (String(v).trim().toUpperCase() === 'TRUE'); continue; } rec[col] = (v === null || v === undefined) ? '' : String(v); } if (rec.studentId) { out.push(rec); } } return out; }