This post walks through what the provided code does and how to set up the Google Sheet it expects. When you’re done, you’ll have a lightweight web app that:

• Reads questions from a Google Sheet
• Renders them as 1–5 (Worst→Best) radio-button questions
• Prefills the user’s email
• Saves each submission to a results sheet (with a timestamp)
• Confirms the saved row number to the user

---

What the Code Does (at a glance)

1) doGet(e) — serve the web app

• Loads an HTML template file named quiz.
• Injects two data points into the template:
  • data: the array of question objects returned by quizData()
  • email: the active user’s email from Session.getActiveUser().getEmail()
• If anything fails, it serves a simple “Not found” page.

💡 Note about getActiveUser()
This returns a non-empty email when your script runs under a Google Workspace domain and the web app is configured appropriately (e.g., “Execute as: User accessing the web app” + access limited to your domain). For public/consumer access, it may be blank; the email input field in the form still lets users enter it manually.

2) quizData() — read and shape your questions

• Opens the spreadsheet by ID (replace the placeholder with your own).
• Pulls all rows from the questions sheet.
• Treats the first row as headings (e.g., id, question).
• Converts each subsequent row into an object where keys are the lowercase headings.
  • Example: { id: 'q1', question: 'The course content was clear.' }
• Returns an array of question objects to the template.

3) addSheet(vals) — save a submission

• Opens the same spreadsheet’s results sheet.
• Reads the first row (headings) to decide the order of values to append.
• For each heading:
  • If vals contains that key, use it; otherwise use an empty string.
  • If the heading is date, it inserts the current timestamp.
• Appends a new row and returns metadata (e.g., the new row number) to the front end.

4) The HTML template (quiz) + client script (quizjs)

• Receives data and email from the server and logs them.
• Shuffles the questions (data.data.sort(() => .5 - Math.random())).
• Dynamically builds the form:
  • Each question renders as a “Worst → Best” scale with radio buttons 1–5.
  • One input per question is marked required, which effectively requires a choice per group.
• On submit:
  • Gathers name, email, and all selected scores (by question id).
  • Disables the button and shows a “Saving…” state.
  • Calls google.script.run.addSheet(res) and, on success, replaces the page with a thank-you + the saved row number.

---

Spreadsheet Setup (the crucial part)

The script expects two sheets in the same spreadsheet ID:

1. questions — the source of truth for what to ask
2. results — where submissions are stored

1) questions sheet

Required headings: at minimum id and question.
You can add other columns if you like (e.g., category), but the UI only uses id and question.

id	question
q1	The course content was clear.
q2	The instructor was engaging.
q3	The assignments supported my learning.
q4	The pace of the course felt appropriate.
q5	I would recommend this course to others.

The id values become the column names you’ll also include in the results sheet (see below). They’re how the app maps each answer to the right column.

2) results sheet

Headings row defines the save order.
Include at least date, name, email, and a column for every question id in questions.

date	name	email	q1	q2	q3	q4	q5
(auto-filled date)	(text)	(email)	1–5	1–5	1–5	1–5	1–5

• date is automatically populated by the script (new Date()).
• name and email come from the form.
• q1…qN columns receive the numeric scores submitted by the user.

If you add more questions (e.g., q6), add matching columns in results.

---

How to Use / Deploy

1. Create your Google Sheet with the two tabs above. Copy the Sheet ID from its URL.
2. In Apps Script:
   • Create a new Apps Script project, or attach one to the sheet.
   • Replace the spreadsheet ID in quizData() and addSheet() with your own.
   • Add a file named Code.gs (your server functions).
   • Add an HTML file named quiz for the main template (CSS + body + the <?!= HtmlService... ?> include).
   • Add an HTML file named quizjs that contains the client-side <script> which builds the form and handles submit.
3. Deploy as Web App: Deploy → New deployment → Web app
   • Execute as: User accessing the web app (to prefill email on Workspace) or Me (if you prefer).
   • Who has access: Set appropriately (e.g., your domain).
   • Open the URL to try it out.

If email is empty, users can still type it manually. You can also remove the prefill and require manual entry.

---

Why This Pattern Works Well

• Sheet-driven: Non-developers can add/edit questions in Sheets.
• Schema-aware saving: Using the results headings as the “save map” makes appending robust and flexible.
• Low friction UX: Radio groups validate easily; submit is instant; users get a confirmation.
• Easy to extend: Add categories, weights, or analytics later (averages, charts, dashboards).

---

Small Improvements (optional)

• Validation on the server: Double-check name, email, and that all question IDs are present before saving.
• Unique attempt control: Store a hash of email + date to prevent rapid duplicate submissions.
• Analytics tab: Use QUERY()/AVERAGE()/COUNTIF() or Apps Script to compute per-question averages and trends.
• Error UX: In doGet, consider logging errors with console.error(e) and showing a friendlier page.

---

Quick Reference: What Each Piece Does

• doGet: Prepares template + injects quizData() + attempts to prefill email.
• quizData: Turns the questions sheet into an array of objects for the UI.
• addSheet: Writes a row into results, setting date automatically.
• quiz (HTML): The visible page; includes styles, user info inputs, and loads quizjs.
• quizjs (client JS): Builds the Likert questions, handles submit, and shows a success message.

Likert Quiz Web App (Apps Script)

This bundle contains everything you need to deploy a simple Likert-scale quiz backed by Google Sheets.

Files

• Code.gs — server-side Apps Script (doGet, quizData, addSheet, include)
• quiz.html — main HTML template (loads quizjs and renders the page)
• quizjs.html — client-side JS that builds the UI and submits results

Spreadsheet Setup

Create a Google Sheet with two tabs:

questions

id	question
q1	The course content was clear.
q2	The instructor was engaging.
q3	The assignments supported my learning.
q4	The pace of the course felt appropriate.
q5	I would recommend this course to others.

results
| date | name | email | q1 | q2 | q3 | q4 | q5 |

The first row on each sheet is headings.

Configure

Edit Code.gs and set SPREADSHEET_ID to your own Google Sheet ID.

Deploy

1. In Apps Script, create a project and add the three files with the same names.
2. Deploy → New deployment → Web app.

• Execute as: User accessing the web app (or Me).
• Who has access: your domain or anyone with link (as needed).

1. Open the web app URL to test. Email prefill requires Workspace and appropriate sharing/execution settings.

// ===== Likert Quiz Web App (Apps Script) =====
// Replace this with your own spreadsheet ID:
const SPREADSHEET_ID = '1IFBVT-uvXcHJTZIr4ENMlGZFvuHM6hBkisEyrbHAXvg';

// Serves the web app
function doGet(e) {
  const email = Session.getActiveUser().getEmail() || '';
  const tpl = HtmlService.createTemplateFromFile('quiz');
  tpl.data = {
    data: quizData(),
    email: email
  };
  return tpl
    .evaluate()
    .setTitle('Likert Quiz')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); // optional if you want to embed
}

// Reads the questions sheet and returns an array of objects
function quizData() {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sh = ss.getSheetByName('questions');
  if (!sh) throw new Error("Missing 'questions' sheet.");

  const values = sh.getDataRange().getValues();
  if (!values.length) return [];

  const headings = values[0].map(h => String(h).trim());
  const rows = values.slice(1);
  return rows.map(r => {
    const o = {};
    headings.forEach((h, i) => o[h.toLowerCase()] = r[i]);
    return o;
  });
}

// Appends a submission to the results sheet
function addSheet(vals) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sh = ss.getSheetByName('results');
  if (!sh) throw new Error("Missing 'results' sheet.");

  const headings = sh.getDataRange().getValues()[0].map(h => String(h).trim());
  const row = [];
  headings.forEach(h => {
    let v = (h.toLowerCase() in vals) ? vals[h.toLowerCase()] : (h in vals ? vals[h] : '');
    if (h.toLowerCase() === 'date') v = new Date();
    row.push(v);
  });

  sh.appendRow(row);
  const last = sh.getLastRow();
  return { row: last, headings, obj: vals };
}

// Helper to include partial HTML files in templates
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <style>
      :root {
        --border:#1a1a1a;
        --muted:#ddd;
        --ink:#0a0a0a;
      }
      body { font-family: Arial, sans-serif; margin: 0; background:#fafafa; color:var(--ink); }
      .wrap { max-width: 840px; margin: 24px auto; padding: 16px; }
      h1 { margin: 0 0 12px; font-size: 1.6rem; }
      .userInfo {
        display:flex; gap:12px; align-items:center; padding:10px;
        background:#000; color:#fff; border-radius:10px; margin-bottom:12px;
      }
      .userInfo label { font-size:.9rem; }
      .userInfo input { flex:1; padding:8px 10px; border-radius:8px; border:1px solid #333; }
      form.questions { display:block; }
      .box { padding: 12px; border: 1px solid var(--border); margin: 8px 0; border-radius:10px; background:#fff; }
      .ques { font-size: 1.1rem; margin-bottom: 8px; }
      .scale { display:flex; align-items:center; gap:8px; flex-wrap: wrap; }
      .guideWord { font-size: 0.9rem; color:#333; padding: 6px 0; }
      .scale label { font-size: .85rem; color: var(--muted); padding:0 4px; }
      button[type="submit"] {
        display:block; width:100%; padding:12px 14px; border-radius:12px;
        border:1px solid var(--border); background:#f2f2f2; font-weight:600; margin-top:12px;
        cursor:pointer;
      }
      .muted { color:#666; font-size:.95rem; margin:6px 0 14px; }
      .footer { margin-top:18px; font-size:.8rem; color:#555; }
      code.inline { background:#f4f4f4; padding:2px 6px; border-radius:6px; }
    </style>
  </head>
  <body>
    <div class="wrap">
      <h1>Course Feedback (1–5 Likert)</h1>
      <p class="muted">Choose one option per question (1 = Worst, 5 = Best).</p>

      <form class="questions" autocomplete="on">
        <div class="userInfo">
          <label for="email">Your Email</label>
          <input type="email" id="email" name="email" placeholder="you@example.com" required>
          <label for="name">Name</label>
          <input type="text" id="name" name="name" placeholder="Your name" required>
        </div>
      </form>

      <div class="footer">
        Powered by <code class="inline">HtmlService</code> + Google Sheets
      </div>
    </div>

    <script>
      // Server-injected data object: {{ data: [...], email: "..." }}
      const data = <?!= JSON.stringify(data) ?>;
    </script>
    <?!= include('quizjs'); ?>
  </body>
</html>

<script>
(function () {
  console.log('Quiz ready');
  console.log('data:', data);

  // Shuffle questions for simple randomization
  const questionsData = Array.isArray(data.data) ? [...data.data] : [];
  questionsData.sort(() => Math.random() - 0.5);

  const form = document.querySelector('.questions');
  const emailInput = document.querySelector('#email');
  const nameInput = document.querySelector('#name');
  const list = document.createElement('div');
  list.setAttribute('aria-live', 'polite');

  emailInput.value = data.email || '';
  form.append(list);

  // Render each question block
  questionsData.forEach(q => renderQuestion(q, list));

  // Submit button
  const btn = document.createElement('button');
  btn.type = 'submit';
  btn.textContent = 'Submit Quiz';
  form.append(btn);

  form.addEventListener('submit', onSubmit);

  function renderQuestion(q, mount) {
    const wrapper = document.createElement('div');
    wrapper.className = 'box';
    const prompt = document.createElement('div');
    prompt.className = 'ques';
    prompt.textContent = q.question;
    wrapper.appendChild(prompt);

    const scale = document.createElement('div');
    scale.className = 'scale';
    const worst = document.createElement('span');
    worst.className = 'guideWord';
    worst.textContent = 'Worst';
    scale.appendChild(worst);

    for (let i = 1; i <= 5; i++) {
      const input = document.createElement('input');
      input.type = 'radio';
      input.name = q.id;
      input.value = String(i);
      input.id = q.id + '_' + i;
      if (i === 1) input.required = true; // require one selection per group

      const label = document.createElement('label');
      label.setAttribute('for', input.id);
      label.textContent = String(i);

      scale.appendChild(input);
      scale.appendChild(label);
    }

    const best = document.createElement('span');
    best.className = 'guideWord';
    best.textContent = 'Best';
    scale.appendChild(best);

    wrapper.appendChild(scale);
    mount.appendChild(wrapper);
  }

  function onSubmit(e) {
    e.preventDefault();

    const res = {
      name: nameInput.value.trim(),
      email: emailInput.value.trim()
    };

    const checked = form.querySelectorAll('input[type="radio"]:checked');
    checked.forEach(input => {
      // store by question id; numeric values
      const key = input.name;
      res[key] = Number(input.value);
    });

    btn.disabled = true;
    btn.textContent = 'Saving…';

    google.script.run
      .withSuccessHandler((r) => {
        form.innerHTML = `Thank you for submitting your results.<br/>Your results are in row <b>${r.row}</b>.`;
      })
      .withFailureHandler((err) => {
        btn.disabled = false;
        btn.textContent = 'Submit Quiz';
        alert('Save failed: ' + err.message);
      })
      .addSheet(res);
  }
})();
</script>