Google Apps Script Dynamic Sheets Data Survey

by
Survey App Google Apps Script Dynamic Sheets Data Survey (1)Download




Code at GitHub

https://github.com/lsvekis/Apps-Script-Survey/tree/main

Build a Data-Driven Survey Web App with Google Sheets + Apps Script

If you’ve ever wished Google Forms gave you more control over styling, page flow, and your data model, this pattern is for you. Below is a practical, beginner-friendly recipe for building a custom, multi-page survey that reads questions from a Questions sheet and saves each submission as a single, tidy row in Responses—plus an optional one-click Summary dashboard with charts. Survey App Google Apps Script D…

Why this approach?

  • Data-driven: Add, remove, or reorder questions in the Questions tab—no code edits.
  • Clean data: Each submission is one row with readable column headers (the question codes).
  • Flexible UI: Multi-page flow, progress bar, review step, and accessible styling.
  • Low maintenance: Works entirely in Apps Script and Sheets—no servers to run.

What you’ll build

  1. Sheet structure
  • Questions (drives the UI)
    • code (e.g., Q1), section, order, type (short, long, single, multi, likert),
      text, required, options (A|B|C), scale_min, scale_max.
  • Responses (one row per submission)
    • Starts as timestamp | submission_id | Q1 | Q2 | …
    • Multi-selects are stored as A; B; C.
  1. Apps Script Web App
  • Index.html: the page shell that includes Styles + Script.
  • Styles.html: accessible, light theme with clear focus states.
  • Script.html: builds pages from Sheet data, validates required fields, offers a review step, then submits.
  • Code.gs: serves the app, loads questions, appends responses safely (locking + retries).
  • (Optional) Summary.gs: generates a Summary sheet with charts and a Verbatims tab for open-text answers.

How it works (at a glance)

  • On load, the browser calls loadQuestions() which reads Questions and returns a JSON payload.
  • The front end groups questions by section → renders each as a page → shows a Review page.
  • On submit, submitRow() writes one row to Responses with a unique submission_id.
  • After submission, the app replaces the form with a read-only “Thank you” summary showing exactly what the user sent.
  • The Summary menu command tallies choice questions, charts likert distributions (with averages), and exports open-ended responses to Verbatims.

Step-by-step setup

  1. Create a Google Sheet
    Add two tabs: Questions and Responses. Put the Questions headers in row 1 (as above); leave Responses empty.
  2. Add Apps Script files
    Open Extensions → Apps Script, then create these files and paste code:
  • Index.html, Styles.html, Script.html (front-end)
  • Code.gs (server)
  • (Optional) Summary.gs (reports)
  1. (Optional) Seed 10 starter questions
    A small setup function can create headers and example questions so you can test immediately. You can later replace them with your own.
  2. Deploy the Web App
    Deploy → Web app
  • Execute as: Me
  • Who has access: Anyone with the link (or your domain)
  1. Customize your survey
    Edit the Questions rows (no code changes needed). Rebuild the Responses header if you change codes.
  2. Summarize & chart
    Use the Survey → Build Summary & Charts menu to create:
  • Tables + bar/column charts for choice/likert items
  • A Verbatims sheet with submission ID, timestamp, question, and answer

Design choices you get “for free”

  • Accessibility: Dark text on white, high-contrast focus rings, full-width inputs, auto-growing textareas.
  • Resilience: Responses append with a document lock and exponential backoff to handle concurrent submits and transient storage hiccups.
  • Clarity after submit: The form hides, a “Thank you” card appears, and the user sees a read-only recap of exactly what was sent.

Customization ideas

  • Internal-only surveys: Restrict the web app to your Google Workspace domain; capture Session.getActiveUser().getEmail() for authenticated users.
  • Validation rules: Add server-side checks in submitRow() for business rules (e.g., required if “Yes”).
  • Extra metadata: Add columns like user_agent, source_page, or campaign and populate them on submit.
  • Dark mode / branding: Swap CSS variables in Styles.html to match your brand or add a theme toggle.
  • Advanced reporting: Extend Summary.gs with per-section averages, benchmarks, and trend tabs.

Tips & troubleshooting

  • No questions loading? Ensure Questions has at least one data row and your headers are spelled as expected.
  • “FAILED_PRECONDITION” or rate limit errors? The code uses lock + retries, but make sure the deployed script executes as you and your account has edit access to the Sheet.
  • Changed options mid-survey? The Summary logic counts unknown values under “(Other)” so you don’t lose signal.

Why teams like this pattern

It hits a sweet spot between speed and control. You get a modern, multi-page form that looks and behaves the way you want—without standing up servers. And because everything is sheet-first, your data stays easy to read, export, and analyze.

Ready to try it? Start with Questions and Responses, paste the code files, deploy the web app, and you’ll be collecting clean, chart-ready survey data in minutes.