One-time setup · 5 minutes

Wire up the forms backend

The arrival survey and the pick-one pledge both submit to a Google Apps Script web app you own, which writes to a Google Sheet you own. The live results page reads back from the same Sheet. You set this up once, paste one URL into a config file, done.

What you'll need: a Google account (use the S2 account so the data lives in S2's workspace). 5 minutes. The ability to copy and paste.

The setup

Create the Google Sheet

Go to sheets.new (signed in as your S2 account). Name the file: AI Studio at S2 — Form Submissions.

Don't add any columns yet. The Apps Script will create the headers automatically on the first submission.

Open the Apps Script editor

From the Sheet, click Extensions → Apps Script. A new tab opens with a code editor showing a default function myFunction().

Delete everything in the editor.

Paste the code

Copy this entire block and paste it into the Apps Script editor:

Code.gs

// AI Studio at S2 — Forms Backend
// Handles three things on one Google Sheet:
//   1. Arrival survey submissions       (survey tab)
//   2. Pick-one pledge submissions       (pledge tab)
//   3. Live in-class polls               (poll tab + an "active question" flag)
// The live results + lobby pages read back from here. Deploy once; everything
// on the site points at this one URL.

const SHEET_SURVEY = "survey";
const SHEET_PLEDGE = "pledge";
const SHEET_POLL   = "poll";
const PROP_ACTIVE  = "activePollQuestion"; // stored in Script Properties
const PROP_ORG     = "currentOrg";         // e.g. "S2"
const PROP_SESSION = "currentSession";     // e.g. "2026-08-04 Central Office"

function doPost(e) {
  try {
    const body = JSON.parse(e.postData.contents);
    const kind = body.kind || "survey";

    // --- Facilitator control: set/clear the active question, reset answers ---
    if (kind === "control") {
      return handleControl(body);
    }

    const ss = SpreadsheetApp.getActiveSpreadsheet();

    // --- Live poll answer (anonymous: no name stored) ---
    if (kind === "poll") {
      let sheet = ss.getSheetByName(SHEET_POLL);
      if (!sheet) sheet = ss.insertSheet(SHEET_POLL);
      appendByHeaders(sheet, ["timestamp","org","session","questionId","answer"], {
        timestamp: body.timestamp || new Date().toISOString(),
        org: currentOrg(),
        session: currentSession(),
        questionId: body.questionId || "",
        answer: body.answer || ""
      });
      return jsonOut({ ok: true });
    }

    // --- Survey + pledge ---
    const sheetName = kind === "pledge" ? SHEET_PLEDGE : SHEET_SURVEY;
    let sheet = ss.getSheetByName(sheetName);
    if (!sheet) sheet = ss.insertSheet(sheetName);

    if (sheetName === SHEET_SURVEY) {
      appendByHeaders(sheet, ["timestamp","org","session","name","usage","comfort","task","concerns"], {
        timestamp: body.timestamp || new Date().toISOString(),
        org: currentOrg(),
        session: currentSession(),
        name: body.name || "",
        usage: body.usage || "",
        comfort: body.comfort || "",
        task: body.task || "",
        concerns: body.concerns || ""
      });
    } else {
      appendByHeaders(sheet, ["timestamp","org","session","name","task","move","partner","checkin","success"], {
        timestamp: body.timestamp || new Date().toISOString(),
        org: currentOrg(),
        session: currentSession(),
        name: body.name || "",
        task: body.task || "",
        move: body.move || "",
        partner: body.partner || "",
        checkin: body.checkin || "",
        success: body.success || ""
      });
    }
    return jsonOut({ ok: true });
  } catch (err) {
    return jsonOut({ ok: false, error: String(err) });
  }
}

function handleControl(body) {
  const props = PropertiesService.getScriptProperties();
  const action = body.action || "";
  if (action === "setActive") {
    props.setProperty(PROP_ACTIVE, String(body.questionId || ""));
  } else if (action === "clear") {
    props.setProperty(PROP_ACTIVE, "");
  } else if (action === "reset") {
    // Wipe poll answers: all of them, or just one question's.
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(SHEET_POLL);
    if (sheet && sheet.getLastRow() > 1) {
      const qid = body.questionId || "";
      if (qid) {
        const rows = sheet.getDataRange().getValues();
        for (let i = rows.length - 1; i >= 1; i--) {
          if (String(rows[i][1]) === String(qid)) sheet.deleteRow(i + 1);
        }
      } else {
        sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
      }
    }
  }
  else if (action === "setcontext") {
    // Which organization and session are we running right now? Every
    // submission from here on is stamped with these.
    if (body.org !== undefined)     props.setProperty(PROP_ORG, String(body.org || ""));
    if (body.session !== undefined) props.setProperty(PROP_SESSION, String(body.session || ""));
  }
  return jsonOut({
    ok: true,
    active: props.getProperty(PROP_ACTIVE) || "",
    org: currentOrg(),
    session: currentSession()
  });
}

function currentOrg() {
  return PropertiesService.getScriptProperties().getProperty(PROP_ORG) || "";
}
function currentSession() {
  return PropertiesService.getScriptProperties().getProperty(PROP_SESSION) || "";
}

function doGet(e) {
  const kind = (e && e.parameter && e.parameter.kind) || "survey";

  // Lightweight: just which question is live right now (phones poll this often).
  if (kind === "pollstate") {
    const props = PropertiesService.getScriptProperties();
    return jsonOut({
      active: props.getProperty(PROP_ACTIVE) || "",
      org: currentOrg(),
      session: currentSession()
    });
  }

  // Just the current org/session (admin screens ask for this).
  if (kind === "meta") {
    return jsonOut({ org: currentOrg(), session: currentSession() });
  }

  // Every org / session / question combination that has poll answers in the
  // Sheet. The poll archive screen uses this to build its dropdowns.
  if (kind === "pollsessions") {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(SHEET_POLL);
    const combos = [];
    const seen = {};
    if (sheet && sheet.getLastRow() > 1) {
      const rows = sheet.getDataRange().getValues();
      const headers = rows[0];
      const iOrg = headers.indexOf("org");
      const iSes = headers.indexOf("session");
      const iQ   = headers.indexOf("questionId");
      rows.slice(1).forEach(function (r) {
        const o = iOrg >= 0 ? String(r[iOrg] || "") : "";
        const s = iSes >= 0 ? String(r[iSes] || "") : "";
        const q = iQ   >= 0 ? String(r[iQ]   || "") : "";
        const key = o + "\u001f" + s + "\u001f" + q;
        if (seen[key]) { seen[key].count++; return; }
        seen[key] = { org: o, session: s, questionId: q, count: 1 };
        combos.push(seen[key]);
      });
    }
    return jsonOut({ org: currentOrg(), session: currentSession(), combos: combos });
  }

  // Active question + its answers (projector reads this).
  //
  // With no extra parameters this returns the CURRENT session only — that is
  // what the projector asks for, and it must stay that way so a re-used
  // question never blends two cohorts' answers on screen.
  //
  // The archive screen passes explicit filters:
  //   &session=   a specific session ("(unlabelled)" for blank)
  //   &allSessions=1     every session (optionally narrowed by &org=)
  //   &org=       restrict to one organization
  if (kind === "poll") {
    const props = PropertiesService.getScriptProperties();
    const active = props.getProperty(PROP_ACTIVE) || "";
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(SHEET_POLL);
    const par = (e && e.parameter) || {};
    const qid = par.questionId || "";
    const askedSession = par.session || "";
    const askedOrg = par.org || "";
    const allSessions = String(par.allSessions || "") === "1";
    const UNLABELLED = "(unlabelled)";
    let responses = [];
    if (sheet && sheet.getLastRow() > 1) {
      const rows = sheet.getDataRange().getValues();
      const headers = rows[0];
      responses = rows.slice(1).map(function (r) {
        const o = {}; headers.forEach(function (h, i) { o[h] = r[i]; }); return o;
      });
      if (qid) responses = responses.filter(function (r) { return String(r.questionId) === String(qid); });

      if (askedOrg) {
        responses = responses.filter(function (r) {
          const v = String(r.org || "").trim();
          return askedOrg === UNLABELLED ? v === "" : v === askedOrg;
        });
      }

      if (allSessions) {
        // no session filter — caller wants the whole history
      } else if (askedSession) {
        responses = responses.filter(function (r) {
          const v = String(r.session || "").trim();
          return askedSession === UNLABELLED ? v === "" : v === askedSession;
        });
      } else {
        // Default: this session only. Without this, a question re-used with a
        // new group would show the previous group's answers mixed in.
        const ses = currentSession();
        if (ses) responses = responses.filter(function (r) { return String(r.session || "") === String(ses); });
      }
    }
    return jsonOut({ active: active, org: currentOrg(), session: currentSession(), responses: responses });
  }

  // Survey / pledge read-back.
  const sheetName = kind === "pledge" ? SHEET_PLEDGE : SHEET_SURVEY;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet || sheet.getLastRow() < 2) return jsonOut({ responses: [] });
  const rows = sheet.getDataRange().getValues();
  const headers = rows[0];
  const responses = rows.slice(1).map(function (r) {
    const obj = {};
    headers.forEach(function (h, i) { obj[h] = r[i]; });
    return obj;
  });
  return jsonOut({ org: currentOrg(), session: currentSession(), responses: responses });
}

// Creates the header row if the sheet is empty, and — importantly — ADDS any
// new columns to a sheet that already has data. That is what makes org/session
// safe to introduce on a Sheet that already holds real responses: existing rows
// simply keep blank values in the new columns.
function ensureHeaders(sheet, headers) {
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.getRange(1, 1, 1, headers.length).setFontWeight("bold");
    sheet.setFrozenRows(1);
    return;
  }
  const width = Math.max(1, sheet.getLastColumn());
  const cur = sheet.getRange(1, 1, 1, width).getValues()[0].map(String);
  const missing = headers.filter(function (h) { return cur.indexOf(h) === -1; });
  if (missing.length) {
    sheet.getRange(1, width + 1, 1, missing.length).setValues([missing]);
    sheet.getRange(1, width + 1, 1, missing.length).setFontWeight("bold");
  }
}

// Writes a row by HEADER NAME rather than by position, so column order never
// matters and older sheets with fewer columns still line up correctly.
function appendByHeaders(sheet, headers, obj) {
  ensureHeaders(sheet, headers);
  const cur = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(String);
  const row = cur.map(function (h) { return obj[h] !== undefined ? obj[h] : ""; });
  sheet.appendRow(row);
}

function jsonOut(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

Click the disk icon (or Cmd+S) to save. Project name doesn't matter.

Deploy as a web app

Click Deploy → New deployment.

Click the gear icon next to "Select type" and choose Web app.
Description: "AI Studio forms backend" (or anything).
Execute as: Me.
Who has access: Anyone (this allows staff to POST submissions without signing in).

Click Deploy. The first time, Google will ask you to authorize the script. Approve the standard permissions. You may need to click "Advanced" → "Go to (your project)" to get through the unverified-app warning. The script needs access to your spreadsheet — that's expected.

Copy the web app URL

After deploying, Google shows you a Web app URL that looks like:

https://script.google.com/macros/s/AKfycbz...long string.../exec

Copy this URL.

Paste it into the site config

Open ai-studio/assets/config.js in your text editor. Find this line:

formsEndpoint: "",

Paste your URL between the quotes:

formsEndpoint: "https://script.google.com/macros/s/AKfycbz.../exec",

Save the file. Reload the site. Done.

Updating an existing deployment (org + session labels)

If you set this up before session labelling was added, do this once. Until you do, submissions keep working exactly as before — they just won't carry an organization or session label, and the results page will show them as (unlabelled).

Open your Sheet → Extensions → Apps Script.
Select everything in the editor and replace it with the code in step 3 above (it now includes the org/session handling).
Save, then Deploy → Manage deployments → Edit (pencil) → Version: New version → Deploy. The URL does not change, so config.js needs no edit.
Open poll control and fill in Organization and Session, then Save.

Your existing data is safe. The script adds the two new columns to the right-hand end of each tab rather than rewriting anything, and it writes rows by column name rather than by position — so old rows keep their values and simply have blank org/session cells.

The same redeploy enables the poll archive, which needs two things this version of the script adds: a pollsessions endpoint that lists every org/session/question combination in the Sheet, and optional session, org and allSessions parameters on the poll read. The projector is unaffected — asked with no parameters, the poll read still returns the current session only, exactly as before.

Why this beats deleting rows between sessions: you keep every cohort's data, the results page can show one session or all sessions for an organization, and the live poll automatically shows only the current session's answers instead of blending two groups' word clouds.

Test it

Open the arrival survey and submit a test response with your own name. Then open the live results page. Your test response should appear within 10 seconds.

Open your Google Sheet — you should see a new tab called survey with your response as a row.

If you want, delete the test row from the Sheet before the real survey goes out.

Things to know

Updating the script later

If you change the Apps Script code, you need to re-deploy: Deploy → Manage deployments → Edit (pencil icon) → Version: New version → Deploy. The URL stays the same, so you don't have to update config.js again.

Sharing the Sheet with Monica

Click Share on the Sheet and add Monica as an Editor (or Viewer if you don't want her to delete rows). The survey, pledge, and live-poll data each land in their own tab (survey, pledge, poll), created automatically on first use.

Editing or deleting a response

Every row now also carries an org and a session, stamped automatically from whatever is set in poll control at the time — phones send nothing extra, so there is nothing for a participant to get wrong. Every submission is one row in its tab, with the name (survey and pledge) shown. To fix a typo, edit the cell. To remove a duplicate or test entry, delete the row. The live results, lobby, and poll screens re-read the Sheet every few seconds, so any change shows up on its own. Live-poll answers are anonymous by design (no name column).

Privacy

The web app accepts submissions from anyone (because the site is password-gated, that's effectively S2 staff). Submissions land in your S2 Google account. No third-party service involved.

Rotating the password

The site password ("AI@S2Training") is in assets/script.js. To rotate it: change the SHARED_PASSWORD constant. The Apps Script URL doesn't need to change.

If submissions stop working

Open the Apps Script editor and click Executions in the left sidebar. Look for failed runs and their error messages.
Re-deploy a new version (Deploy → Manage deployments → Edit → New version).
If Google asks for re-authorization, approve it. It happens occasionally.

Backend setup walkthrough · v1 · Last updated June 2026

← Live results