Google forms sheets Apps Scripts plus Twilio

PROJECT: Automated Weekly Student Question & Discussion System for Literature Class Required Skills / Technologies The contractor should be experienced with: - Google Forms - Google Sheets - Google Apps Script (required) - Google Sites - Gmail automation - Twilio SMS integration - Basic web publishing and embedded content - Database-style workflow design within Google Sheets Project Overview I teach a 32-week high school World Literature seminar with approximately 25 students. I want a largely automated system that collects student questions before class, sends reminders automatically, tracks participation, and publishes submissions to a class website. The goal is for the system to run with minimal weekly intervention once configured. Student Roster Each student record should include: - Student name - Student email - Parent email (optional but preferred) - Student mobile phone number - Active/inactive status Weekly Question Submission System Each week, students must submit one discussion question before class. Requirements: - Submission window opens automatically each week - Deadline is Thursday at 8:00 PM - Students may submit by web form - Submission length should be approximately 15–70 words - System records timestamp automatically - System records whether submission was on time - One submission per student per week - Instructor can view all submissions in one place Anonymous Publication Student questions should be published anonymously. Requirements: - Student names hidden from classmates - Questions displayed publicly on class website - Questions listed in a clean discussion-board style format - Instructor can moderate, edit, hide, or delete submissions if necessary - Questions organized by week Reminder System Reminders should operate automatically throughout the school year. Wednesday Reminder - Sent to all active students - Email and text message - Reminds students to submit weekly question Thursday Noon Reminder - Sent only to students who have not submitted - Email and text message Thursday 6:00 PM Reminder - Sent only to students who have not submitted - Email and text message System must automatically determine who has and has not submitted. Late Submission Handling At 8:00 PM Thursday: - Submission period closes or flags late submissions - Instructor can still view late submissions - System records lateness Friday Class Use Before class, instructor should be able to view: - All anonymous questions - Participation status - Missing students - Submission timestamps Post-Class Takeaway System After Friday class, students must submit a second contribution. Prompt: "What was the most important idea, insight, or takeaway from today's class?" Requirements: - Separate submission form - Student names visible - Organized by week - Published to a separate section of the website - Instructor moderation capability Class Website Integration Using Google Sites. Website should include: Page 1: Weekly Reading Assignments Page 2: Anonymous Weekly Questions Page 3: Class Takeaways Page 4: Course Calendar Website updates should occur automatically whenever practical. Administrative Dashboard Instructor needs a simple dashboard to: - Add or remove students - Mark students active/inactive - Edit deadlines - Edit weekly prompts - View participation reports - View missing submissions - View late submissions - Export participation records School-Year Setup The system should support all 32 weeks at once. I want the ability to: - Preload all 32 weekly topics - Preload all 32 deadlines - Preload all reading assignments Reporting Instructor should be able to generate: - Participation by student - Missing assignments by student - Submission history - End-of-year participation summary Documentation Provide: - Written setup documentation - Instructions for editing roster - Instructions for changing prompts - Instructions for updating dates - Instructions for maintaining Twilio credentials - Instructions for transferring ownership to another Google account if needed Success Criteria Once configured, the system should require minimal weekly administration and automatically: - Collect submissions - Track participation - Send reminder emails - Send reminder texts - Publish anonymous discussion questions - Publish named class takeaways - Maintain participation records throughout the 32-week course