One-time setup · 5 minutes

Wire up the forms backend

The pre-session 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. Pre-session 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

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);
      ensureHeaders(sheet, ["timestamp","questionId","answer"]);
      sheet.appendRow([
        body.timestamp || new Date().toISOString(),
        body.questionId || "",
        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) {
      ensureHeaders(sheet, ["timestamp","name","usage","comfort","task","concerns"]);
      sheet.appendRow([
        body.timestamp || new Date().toISOString(),
        body.name || "",
        body.usage || "",
        body.comfort || "",
        body.task || "",
        body.concerns || ""
      ]);
    } else {
      ensureHeaders(sheet, ["timestamp","name","task","move","partner","checkin","success"]);
      sheet.appendRow([
        body.timestamp || new Date().toISOString(),
        body.name || "",
        body.task || "",
        body.move || "",
        body.partner || "",
        body.checkin || "",
        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();
      }
    }
  }
  return jsonOut({ ok: true, active: props.getProperty(PROP_ACTIVE) || "" });
}

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) || "" });
  }

  // Active question + its answers (projector reads this).
  if (kind === "poll") {
    const props = PropertiesService.getScriptProperties();
    const active = props.getProperty(PROP_ACTIVE) || "";
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(SHEET_POLL);
    const qid = (e && e.parameter && e.parameter.questionId) || "";
    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); });
    }
    return jsonOut({ active: active, 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({ responses: responses });
}

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);
  }
}

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.

Test it

Open the pre-session 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 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