Hybrid Scheduling App: Step-by-Step Build Guide

Step 2: Google Sheets Database Setup

First, create a new Google Sheet. Name it something descriptive, like "TrainingSchedulerDB". Inside this spreadsheet, create a new tab (sheet) for each of the tables listed below. For each tab, add the specified column headers in the first row. Pay close attention to the recommended AppSheet Data Type, as this will be crucial when you import your data into AppSheet.

Sheet: `Courses`

Purpose: Stores foundational information about each training course.

• `CourseID` (AppSheet Type: Text, Key, Label)
• `CourseName` (AppSheet Type: Text)
• `Description` (AppSheet Type: LongText)
• `BaseRate` (AppSheet Type: Price)
• `DurationMinutes` (AppSheet Type: Number)
• `MaxCapacityPerClass` (AppSheet Type: Number)
• `MinAttendeesForClass` (AppSheet Type: Number)
• `DefaultStartTimeInterval` (AppSheet Type: Number)

Sheet: `BusinessHours`

Purpose: Defines the operational hours for each day.

• `DayOfWeek` (AppSheet Type: Text, Key, Label, e.g., "Monday", "Tuesday")
• `OpenTime` (AppSheet Type: Time, e.g., 09:00:00)
• `CloseTime` (AppSheet Type: Time, e.g., 17:00:00)
• `IsHoliday` (AppSheet Type: Yes/No, e.g., TRUE/FALSE)

Sheet: `AvailableSlots`

Purpose: Stores individual class time slots. These will be populated by admin or automation.

• `SlotID` (AppSheet Type: Text, Key, Label)
• `CourseID` (AppSheet Type: Ref, Source: `Courses`)
• `Date` (AppSheet Type: Date)
• `StartTime` (AppSheet Type: Time)
• `EndTime` (AppSheet Type: Time)
• `MaxCapacityForSlot` (AppSheet Type: Number)

Sheet: `ClientReservations`

Purpose: Records each client's booking.

• `ReservationID` (AppSheet Type: Text, Key, Label)
• `ClientID` (AppSheet Type: Ref, Source: `Clients`)
• `SlotID` (AppSheet Type: Ref, Source: `AvailableSlots`)
• `NumberOfAttendees` (AppSheet Type: Number)
• `BookingDateTime` (AppSheet Type: DateTime)
• `PaymentStatus` (AppSheet Type: Enum, Values: "Pending", "Paid", "Failed", "Refunded")
• `PaymentCode` (AppSheet Type: Text)

Sheet: `Clients`

Purpose: Stores user information (customers and admins).

• `ClientID` (AppSheet Type: Text, Key, Label)
• `FirstName` (AppSheet Type: Text)
• `LastName` (AppSheet Type: Text)
• `Email` (AppSheet Type: Email)
• `Phone` (AppSheet Type: Phone)
• `Address` (AppSheet Type: Address)
• `UserRole` (AppSheet Type: Enum, Values: "Customer", "Admin")
• `AppSheetUserEmail` (AppSheet Type: Email, Initial Value: `USEREMAIL()`)

Sheet: `PricingRules`

Purpose: Defines group discounts and pricing adjustments.

• `RuleID` (AppSheet Type: Text, Key, Label)
• `CourseID` (AppSheet Type: Ref, Source: `Courses`, Is part of: `Courses` table)
• `MinAttendees` (AppSheet Type: Number)
• `MaxAttendees` (AppSheet Type: Number)
• `DiscountPercentage` (AppSheet Type: Decimal, e.g., 0.10 for 10%)
• `FixedDiscountAmount` (AppSheet Type: Price)

Sheet: `PaymentLinks`

Purpose: Stores base URLs for Stripe payment flows.

• `PaymentLinkID` (AppSheet Type: Text, Key, Label)
• `CourseID` (AppSheet Type: Ref, Source: `Courses`, Is part of: `Courses` table)
• `PaymentLinkURL` (AppSheet Type: URL)

Sheet: `Integration_Status_Log`

Purpose: Audit trail for critical system events.

• `LogID` (AppSheet Type: Text, Key, Label)
• `Timestamp` (AppSheet Type: DateTime)
• `IntegrationType` (AppSheet Type: Text)
• `RelatedRecordID` (AppSheet Type: Text)
• `Status` (AppSheet Type: Text)
• `Details` (AppSheet Type: LongText)

Sheet: `EmailTemplates`

Purpose: Stores customizable templates for automated emails.

• `TemplateName` (AppSheet Type: Text, Key, Label)
• `SubjectTemplate` (AppSheet Type: Text)
• `BodyTemplate` (AppSheet Type: LongText)

Once your Google Sheets are set up, click "Next" to configure your AppSheet application.

Step 3: AppSheet Data & Security Setup

Now, let's connect your Google Sheets to AppSheet and configure basic security and user roles.

3.1: Create Your App

• Go to AppSheet.com.
• Click "Make a new app" -> "Start with your own data".
• Select your "TrainingSchedulerDB" Google Sheet. AppSheet will automatically add your sheets as tables.

3.2: Configure Table Columns

In AppSheet, navigate to `Data` -> `Columns`. For each table, review and adjust the column types to match the "AppSheet Type" specified in Step 2. Pay special attention to `Ref` types, ensuring they correctly point to the referenced table.

• For `SlotID` in `ClientReservations`, set `Type` to `Ref` and `Source table` to `AvailableSlots`.
• For `ClientID` in `ClientReservations`, set `Type` to `Ref` and `Source table` to `Clients`.
• For `CourseID` in `AvailableSlots`, `PricingRules`, `PaymentLinks`, set `Type` to `Ref` and `Source table` to `Courses`.
• For `AppSheetUserEmail` in `Clients`, set `Initial Value` to `USEREMAIL()`.
• For `UserRole` in `Clients`, set `Type` to `Enum` and add values "Customer" and "Admin".

3.3: Implement User Roles & Security Filters

This ensures customers can only see and modify their own data, and admins have full access.

• Go to `Security` -> `Security filters`.
• For the `Clients` table, set the security filter to:
  [AppSheetUserEmail] = USEREMAIL()
  This ensures users only see their own client record.
• For `ClientReservations` table, set the security filter to:
  [ClientID].[AppSheetUserEmail] = USEREMAIL()
  This ensures users only see their own bookings.
• For `Courses`, `BusinessHours`, `PricingRules`, `PaymentLinks`, `EmailTemplates`:
  • Set `Are updates allowed?` to `Reads only` for the general public.
  • For Admin access, you'll create a slice later.

3.4: Add Virtual Columns

These columns add dynamic data without modifying your Google Sheet.

• For `AvailableSlots` table (Data -> Columns -> Add Virtual Column):
  • Name: `CurrentBookedCount` (Type: Number)
    SUM(SELECT(ClientReservations[NumberOfAttendees], [SlotID] = [_THISROW].[SlotID]))
  • Name: `IsFull` (Type: Yes/No)
    [CurrentBookedCount] >= [MaxCapacityForSlot]
• For `ClientReservations` table (Data -> Columns -> Add Virtual Column):
  • Name: `TotalAmountPaid` (Type: Price)
    ([SlotID].[CourseID].[BaseRate] * [NumberOfAttendees]) * (1 - LOOKUP([NumberOfAttendees], "PricingRules", "MinAttendees", "DiscountPercentage"))

    Note: This formula assumes `PricingRules` has `MinAttendees` as key. Adjust if your pricing logic is different (e.g., uses `MaxAttendees` or `FixedDiscountAmount`).

With your data structured and initial security set, click "Next" to build the user interface.

Step 4: AppSheet User Interface (UI) Setup

This step guides you through creating the core views for your customer booking journey.

4.1: Class Selection View

• Go to `UX` -> `New View`.
• `View name`: `Courses`
• `Data source`: `Courses`
• `View type`: `Gallery` or `Deck` (choose what looks best for your courses).
• `Primary header`: `CourseName`
• `Secondary header`: `BaseRate`
• `Summary column`: `Description`
• Set this as your `Initial view` for customers (UX -> Options -> Initial View).

4.2: Available Days Calendar View

• Go to `UX` -> `New View`.
• `View name`: `Available Days`
• `Data source`: `AvailableSlots`
• `View type`: `Calendar`
• `Date column`: `Date`
• `Start time column`: `StartTime`
• `End time column`: `EndTime`
• `Title column`: `CourseID.CourseName`
• `Description column`: `CurrentBookedCount` (so users see how many are booked)

• Format Rules (UX -> Format Rules -> New Rule):

  • Rule 1: Name "Full Days", `For this data`: `AvailableSlots`
    [IsFull] = TRUE

    Format: Set background color to `Grey`.

  • Rule 2: Name "Available Days", `For this data`: `AvailableSlots`
    [IsFull] = FALSE

    Format: Set background color to `Blue`.

4.3: Time Selection View (Detail View & Form)

• Create a `Slice` for available slots:

  • Go to `Data` -> `Slices` -> `New Slice`.
  • `Slice name`: `AvailableSlots_CustomerView`
  • `Source table`: `AvailableSlots`
  • `Row filter condition`:
    AND([IsFull] = FALSE, [Date] >= TODAY())

    This ensures only future, non-full slots are shown.

• Create a `View` for time selection:

  • Go to `UX` -> `New View`.
  • `View name`: `Book Time`
  • `Data source`: `AvailableSlots_CustomerView` (your slice)
  • `View type`: `Table` or `Deck` (display `StartTime`, `EndTime`, `CourseID.CourseName`, `MaxCapacityForSlot` - `CurrentBookedCount` to show remaining).
  • Set `Row click behavior` to `Detail view`.

• Create an `Action` to book from the `Book Time` detail view:

  • Go to `Behavior` -> `Actions` -> `New Action`.
  • `Action name`: `Book This Class`
  • `For a record of this table`: `AvailableSlots`
  • `Do this`: `Data: add a new row to another table using values from this row`
  • `Table to add to`: `ClientReservations`
  • `Set these columns`:
    SlotID = [_THISROW].[SlotID]
ClientID = LOOKUP(USEREMAIL(), "Clients", "AppSheetUserEmail", "ClientID")
BookingDateTime = NOW()
PaymentStatus = "Pending"
  • `Prominence`: `Display as primary action` (or `Overlay`)

4.4: Confirmation Page (Form View)

• When the "Book This Class" action is triggered, AppSheet will open the `ClientReservations` form.
• Go to `Data` -> `Columns` for `ClientReservations`.
• For `NumberOfAttendees` column:
  • `Type`: `Number`
  • `Valid_If`:
    ([_THISROW].[SlotID].[MaxCapacityForSlot] - [_THISROW].[SlotID].[CurrentBookedCount]) >= [_THISROW].[NumberOfAttendees]

    Error Message: "Your group size exceeds the remaining capacity for this class. Please reduce the number of attendees or choose another slot."

  • `Suggested values`: You can provide a list like `LIST(1, 2, 3, 4, 5)` or use a dynamic list based on remaining capacity.
• Ensure `TotalAmountPaid` (virtual column) is displayed in the form.
• The default "Save" button will act as "Confirm". You can rename it in `UX` -> `Options` -> `Form Save Button Text`.
• For "Go Back" functionality, the user can simply use the back arrow in the AppSheet app.

Your app's basic UI is now set up! Next, we'll implement the crucial logic and integrations.

Step 5: Logic & Integrations

This step covers the critical business logic for dynamic pricing, payment processing with Stripe, and automated email confirmations via Gmail.

5.1: Stripe Payment Redirection Action

After a booking is confirmed, redirect the user to Stripe for payment. This action will be triggered automatically after the `ClientReservations` form is saved.

• Go to `Behavior` -> `Actions` -> `New Action`.
• `Action name`: `Go to Stripe Payment`
• `For a record of this table`: `ClientReservations`
• `Do this`: `External: Go to a website`
• `Target`:
  CONCATENATE(LOOKUP([_THISROW].[SlotID].[CourseID], "PaymentLinks", "CourseID", "PaymentLinkURL"), "?amount=", ([TotalAmountPaid]*100), "&description=", ENCODEURL("Booking for " & [_THISROW].[SlotID].[CourseID].[CourseName] & " on " & TEXT([_THISROW].[SlotID].[Date], "MM/DD/YYYY") & " at " & TEXT([_THISROW].[SlotID].[StartTime], "HH:MM")), "&client_reference_id=", [_THISROW].[ReservationID])

  Replace `LOOKUP([_THISROW].[SlotID].[CourseID], "PaymentLinks", "CourseID", "PaymentLinkURL")` with your actual Stripe base URL if you don't use the `PaymentLinks` table for dynamic URLs, e.g., `"https://buy.stripe.com/your_base_payment_link"`. `([TotalAmountPaid]*100)` is because Stripe expects amount in cents.

• `Prominence`: `Do not display` (this action will be triggered by an automation, not a button).

5.2: Stripe Payment Status Update (via Middleware)

This is crucial for updating the booking status in your Google Sheet after payment. AppSheet doesn't directly receive webhooks, so you need a middleware service.

• In Stripe:

  • Go to `Developers` -> `Webhooks`.
  • Add an endpoint. For the `Endpoint URL`, use the unique URL provided by your middleware (e.g., Make.com or Zapier).
  • Select events to send: `payment_intent.succeeded` and `payment_intent.payment_failed`.

• In your Middleware (e.g., Make.com or Zapier):

  • Create a new scenario/zap.
  • `Trigger`: `Webhook (Custom Webhook)` or `Catch Hook`. Copy the provided webhook URL.
  • `Action`: `Google Sheets` -> `Update a Row` (or `Search Row` then `Update Row`).
  • `Spreadsheet`: Select your "TrainingSchedulerDB".
  • `Worksheet`: `ClientReservations`.
  • `Row lookup`: Use the `client_reference_id` from the Stripe webhook payload to find the `ReservationID` in your Google Sheet.
  • `Columns to update`:
    • `PaymentStatus`: Map this to "Paid" if `payment_intent.succeeded`, "Failed" if `payment_intent.payment_failed`.
    • `PaymentCode`: Map this to the `id` of the `payment_intent` from the Stripe webhook.
  • (Optional but Recommended) Add another action to log this event to your `Integration_Status_Log` sheet.

5.3: Automated Booking Confirmation Email (AppSheet Bot)

Send an email confirmation once payment is successful.

• First, populate your `EmailTemplates` sheet:

  • Add a row with `TemplateName`: "BookingConfirmation"
  • `SubjectTemplate`: "Your Booking for <<CourseName>> is Confirmed!"
  • `BodyTemplate`: "Dear <<ClientName>>,
    
    Your booking for <<CourseName>> on <<Date>> at <<StartTime>> is confirmed! Your booking ID is <<ReservationID>>.
    
    We look forward to seeing you!
    
    Training Co. Team"

• Create an AppSheet Bot (Automation -> Bots -> New Bot):

  • `Bot name`: `Send Booking Confirmation`
  • `Event`: `Configure Event`
    • `Event type`: `Data change`
    • `Table`: `ClientReservations`
    • `Change type`: `Adds and Updates`
    • `Condition`:
      [_THISROW_BEFORE].[PaymentStatus] <> "Paid" AND [_THISROW].[PaymentStatus] = "Paid"

      This ensures it only triggers when status changes to Paid.

  • `Process`: `Add a step` -> `Send an email`
    • `Email To`: `[_THISROW].[ClientID].[Email]`
    • `Email Subject`:
      SUBSTITUTE(LOOKUP("BookingConfirmation", "EmailTemplates", "TemplateName", "SubjectTemplate"), "<<CourseName>>", [_THISROW].[SlotID].[CourseID].[CourseName])
    • `Email Body`:
      SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOOKUP("BookingConfirmation", "EmailTemplates", "TemplateName", "BodyTemplate"), "<<ClientName>>", [_THISROW].[ClientID].[FirstName]), "<<CourseName>>", [_THISROW].[SlotID].[CourseID].[CourseName]), "<<Date>>", TEXT([_THISROW].[SlotID].[Date], "MM/DD/YYYY")), "<<StartTime>>", TEXT([_THISROW].[SlotID].[StartTime], "HH:MM")), "<<ReservationID>>", [_THISROW].[ReservationID])
    • `Email Body is HTML`: Check this box if your template uses HTML.

You've now implemented the core logic and integrations! Click "Next" to finalize your app.

Step 6: Finalizing & Deployment

You're almost there! This step covers important final configurations and how to deploy your app.

6.1: Admin View & User Management

Create an "Admin" view so you can manage courses, slots, and users.

• Create an `Admin` slice:

  • Go to `Data` -> `Slices` -> `New Slice`.
  • `Slice name`: `Admin_Only_Data`
  • `Source table`: `Clients` (or any table you want admins to manage)
  • `Row filter condition`:
    USEREMAIL() = "your.admin.email@example.com"

    Replace with your actual admin email, or use `LOOKUP(USEREMAIL(), "Clients", "AppSheetUserEmail", "UserRole") = "Admin"` if you've set up admin roles in the `Clients` table.

• Create Admin-specific views:

  • Go to `UX` -> `New View`.
  • `View name`: `Admin - Courses` (`Data source`: `Courses`, `View type`: `Table`, `Show_If`: `LOOKUP(USEREMAIL(), "Clients", "AppSheetUserEmail", "UserRole") = "Admin"`)
  • Repeat for `BusinessHours`, `AvailableSlots`, `ClientReservations`, `Clients`, `PricingRules`, `PaymentLinks`, `Integration_Status_Log`, `EmailTemplates`.
  • Ensure `Are updates allowed?` is set to `Adds, Updates, Deletes` for these tables for Admin users (Data -> Tables -> [Table Name] -> Updates). You might need to use a `Security Filter` on the table itself to allow updates only for `Admin` users: `LOOKUP(USEREMAIL(), "Clients", "AppSheetUserEmail", "UserRole") = "Admin"`.

6.2: User Login & Access Control

• Go to `Security` -> `Auth`.
• Choose `Google` as your `Authentication provider`.
• For `Require sign-in`, ensure it's `On`.
• For `Users`, add the email addresses of your customers and admins. For admin accounts, ensure they are also marked as "Admin" in your `Clients` Google Sheet.
• For `Domain authentication`, if you want to restrict access to specific Google Workspace domains, configure it here.

6.3: Deployment & Sharing

• Go to `Deployment` -> `Deployment Check`. Resolve any warnings or errors.
• Go to `Deployment` -> `Deploy`. Confirm deployment.
• To share your app, go to `Users` -> `Users`. Add the email addresses of your users. They will receive an email with a link to install the app.
• To embed on Squarespace: Go to `Manage` -> `App Info` -> `Links`. Copy the `Browser Link` or `Embed Link` and paste it into an Embed or Code Block on your Squarespace site.

6.4: Initial Data Population

Before launching, ensure you have initial data:

• Populate your `Courses` sheet with your training courses.
• Populate your `BusinessHours` sheet with your operating times.
• Populate your `PaymentLinks` sheet with your Stripe payment URLs.
• (Optional) Pre-populate `AvailableSlots` for the near future, or create an admin tool in AppSheet to do this dynamically.
• Add your own admin email to the `Clients` sheet with `UserRole` as "Admin".

Congratulations! You have completed the build guide for your Hybrid Classroom Scheduling Application. Test thoroughly before going live!