Book and manage form appointments with Google Calendar, OpenRouter, Gmail and Sheets

Quick overview

This workflow collects appointment requests via an n8n form, checks availability in Google Calendar, and either books the first matching slot or proposes alternatives. It uses OpenRouter to draft the email copy, sends emails through Gmail, logs each request to Google Sheets, and sends a reminder 24 hours before.

How it works

1. Receives an appointment request from an n8n Form with the customer’s details, preferred date, and preferred time window.
2. Calls the Google Calendar Free/Busy endpoint to fetch busy times for the selected calendar over the next two weeks.
3. Calculates the first available appointment slot within your opening hours and notice period, or generates up to three alternative openings if the preferred day is full.
4. Sends the booking status and times to OpenRouter to generate a JSON email subject and plain-text body.
5. If a slot is available, creates a Google Calendar event, emails the confirmation via Gmail, and appends a “booked” entry to Google Sheets.
6. If no slot is available on the requested day, emails the alternative times via Gmail and appends an “offered” entry to Google Sheets.
7. When a booking is more than 24 hours away, waits until 24 hours before the start time and sends a reminder email via Gmail.

Setup

1. Add credentials for Google Calendar OAuth2, Gmail OAuth2, and Google Sheets OAuth2.
2. Add an OpenRouter API key using HTTP Header Auth with header name Authorization and value Bearer YOUR_KEY.
3. Update the Booking Settings values (business name/description, timezone, calendar ID, opening days/hours, appointment duration, minimum notice, and optionally the booking form URL).
4. Create or select a Google Sheets spreadsheet with a Bookings sheet and headers Timestamp, Name, Email, Service, Slot, Status, then paste the spreadsheet document ID into both Google Sheets log steps.

Requirements

• n8n (self-hosted or Cloud)
• Google account with Calendar, Gmail and Sheets (OAuth2)
• OpenRouter API key (the default free model costs $0)

Customization

• Change appointment length, opening hours or open days in Booking Settings
• Change the 30-minute slot step or the notice period in Find Free Slot
• Swap the model in Write Email for any OpenRouter model
• Set booking_form_url so the day-is-full email links back to the form

Additional info

Tested live against a real Google Calendar: two bookings for the same morning landed at 9:00 and 10:00 without double-booking; a morning blocked until 12 offered the same-day 12:00 PM slot first; a Saturday request offered the next weekday openings. The AI call retries up to 3 times because free models occasionally return a 502.