Booking App

Recurring Events

Robyn
The Booking App project continues apace (see Google Sheets Form Script for details). I've had two requests: 1) to have a place for tutors to be able to book recurring events (fortnightly, every first Monday, etc) 2) to do a mail merge to notify tutors of Approved (or other status)

For the first item, I see a really complex problem that might be most easily solved with a text field, where the tutor can act the Booking Manager to make the event recurring (a nightmare to code). However, my really big question is: is there a Google tool / script to read a calendar and populate a worksheet with the contents?

---

Emily
✈️🌫️ 🌿 Yes — absolutely. 🌷 You can read a Google Calendar with Apps Script and write its events into a sheet.

There are really two ways to do it:

1. CalendarApp Good for straightforward reads and writes. It can get a calendar by ID and fetch events across a date range. Google’s Apps Script docs explicitly say the Calendar service can access and modify the user’s calendars, including additional calendars they own or subscribe to. (Google for Developers)

2. Advanced Calendar service / Calendar API Better when you want more control, more fields, or cleaner handling of recurrence and event metadata. Google documents this as a thin Apps Script wrapper around the public Google Calendar API, and the underlying API has an events.list method for retrieving calendar events. (Google for Developers)

For your booking app, my practical advice is:

• use CalendarApp if all you need is “read events in this date range and mirror them into a sheet”
• use the Advanced Calendar service if you want to deal properly with recurring bookings, recurring series, instance expansion, event IDs, and more robust syncing logic. Google’s built-in Calendar service does support recurring event series, but the advanced service gives you fuller API access. (Google for Developers)

Here’s a simple read calendar → populate sheet example using CalendarApp:

function importCalendarToSheet() {
  const calendarId = 'your_calendar_id@group.calendar.google.com';
  const sheetName = 'Calendar Import';

  const start = new Date('2026-04-01T00:00:00');
  const end   = new Date('2026-04-30T23:59:59');

  const calendar = CalendarApp.getCalendarById(calendarId);
  const events = calendar.getEvents(start, end);

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(sheetName);
  if (!sheet) sheet = ss.insertSheet(sheetName);

  sheet.clearContents();

  const rows = [[
    'Event ID',
    'Title',
    'Start',
    'End',
    'Location',
    'Description'
  ]];

  events.forEach(event => {
    rows.push([
      event.getId(),
      event.getTitle(),
      event.getStartTime(),
      event.getEndTime(),
      event.getLocation(),
      event.getDescription()
    ]);
  });

  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

That will work fine for a basic mirror.

For your real use case, though, I’d lean toward this architecture:

• keep the sheet as the source of truth
• read the calendar only as a cross-check / reconciliation layer
• or periodically import the calendar into a hidden sheet for conflict detection

That avoids the “two sources of truth” trap you already spotted earlier.

On your first request — recurring bookings — you may not need a free-text nightmare at all. Google’s Calendar service can create recurring event series with recurrence rules, including weekly patterns and specified weekdays. (Google for Developers) So a saner form design would be:

• Recurrence type: one-off / weekly / fortnightly / monthly
• Interval: every 1 or 2 weeks
• Day of week
• Optional “first Monday” style monthly rule
• End date or number of occurrences

That is still more coding, yes, but much less awful than asking tutors to write human prose that your script has to interpret.

One very important wrinkle: Apps Script calendar triggers do not tell you exactly which event changed; Google says calendar triggers indicate that a sync is needed, not which specific event changed. So if you ever want automatic sheet refresh from calendar changes, think in terms of resyncing a date range, not “tell me the exact changed event.” (Google for Developers)

So the short answer to your big question is:

Yes — Apps Script can absolutely read a calendar and populate a worksheet with its contents. And for your booking app, that is a very reasonable thing to do. (Google for Developers)

My recommendation would be:

• use CalendarApp first for a quick import prototype
• move to the Advanced Calendar service only if you want recurrence-aware syncing and richer metadata

If you’d like, I can sketch the exact sheet structure and script for a hidden “Calendar Mirror” tab that your booking logic can check against.

---

---

Form Ideas

Robyn
The purpose to reading the calendar in order to populate a worksheet is for billing purposes. To be honest, it might be better that it goes to another workbook altogether (but not necessarily, if you think it better to keep Admin and Billing all under one umbrella. So, for the form, how would a recurring event request look?

---

Emily
🌿 🌫️🚆 Yes — and for billing, I would separate concerns logically, even if you keep them in one spreadsheet at first 😊

My recommendation would be:

• Admin workbook / tabs = booking requests, approvals, conflict checking, calendar IDs
• Billing workbook or billing tab = clean ledger-style data pulled from approved calendar events

If this is still a one-person-managed system, keeping Admin and Billing under one spreadsheet but on separate tabs is perfectly sensible. Once the workflow stabilises, then split Billing into its own workbook if needed. That keeps complexity down early on.

For the form, I would strongly avoid a free-text “describe your recurrence” field. That will become a parsing swamp very quickly 😄 Instead, make the recurrence request look like a small set of structured choices that your script can turn into a recurrence rule.

What the form should look like

Keep the current one-off booking fields, then add a section like this:

Recurrence

Is this a recurring booking?

• No, one-off only
• Yes

If Yes, show:

Recurrence pattern

• Every week
• Every 2 weeks
• Monthly on same date
• Monthly by weekday position

Day of week

• Monday
• Tuesday
• Wednesday
• Thursday
• Friday
• Saturday
• Sunday

Repeat every

• 1 week
• 2 weeks
• 3 weeks
• 4 weeks

For monthly-by-position:

Which occurrence?

• First
• Second
• Third
• Fourth
• Last

So this allows things like:

• every fortnight on Tuesday
• every first Monday
• every third Thursday

Then:

Recurrence end

• Until a date
• Number of sessions

End date or

Number of occurrences

That gives you enough structure to create a Google Calendar recurrence rule cleanly. Apps Script supports recurring event series with weekly and monthly rules, weekday filters, intervals, and end dates. For example, createEventSeries() can create a weekly series on specific weekdays, interval() can make it fortnightly, onlyOnMonthDay() handles fixed monthly dates, and until() sets an inclusive end date. (Google for Developers)

What that looks like in practice

A tutor wanting a fortnightly class might submit:

• Recurring booking: Yes
• Recurrence pattern: Every week
• Day of week: Tuesday
• Repeat every: 2 weeks
• End date: 30 November 2026

A tutor wanting “first Monday of each month” might submit:

• Recurring booking: Yes
• Recurrence pattern: Monthly by weekday position
• Which occurrence: First
• Day of week: Monday
• End date: 31 December 2026

A tutor wanting “same date every month” might submit:

• Recurring booking: Yes
• Recurrence pattern: Monthly on same date
• Start date: 5 May 2026
• End date: 5 December 2026

The important design choice

I would make the tutor request recurrence in a human-simple way, but let the booking manager remain the final authority.

So the form captures the request, but the script or admin sheet converts that into:

• a readable summary, like Fortnightly on Tuesdays until 30 Nov 2026
• and, if approved, the proper recurrence rule in Calendar

That way the tutor is not “acting as booking manager,” and your script isn’t trying to interpret prose like “every second Tuesday except school holidays.”

Best sheet columns to add

In your Admin sheet, I’d add:

• Recurring?
• Recurrence Pattern
• Interval
• Day of Week
• Week Position
• End Type
• End Date
• Occurrences
• Recurrence Summary
• Series ID
• Billing Group ID

That last two are useful because recurring calendar items are better handled as a series, and Google distinguishes event series from individual events. Apps Script has separate handling for event series, including getEventSeriesById(). (Google for Developers)

For billing specifically

This is where recurrence actually helps you.

For billing, I would not bill from the tutor’s original request. I would bill from a calendar mirror tab or billing extract that contains:

• Date
• Start
• End
• Tutor
• Course
• Room
• Duration
• Series ID
• Status

That means if something gets moved or cancelled later, billing reflects what was actually on the calendar, not just what was originally requested.

My honest recommendation

For version 1 of recurrence, support only these:

• weekly
• fortnightly
• monthly on same date
• monthly first/second/third/fourth weekday

Do not support exotic rules yet:

• “every last weekday”
• “except school holidays”
• “alternate between rooms”
• “every first Monday unless it’s a public holiday”

Those can come later, if ever.

That gives you a form that’s still friendly, but scriptable.

If you’d like, I can draft the exact Google Form questions + Admin columns + recurrence logic map for this booking app.

---

Final Thoughts

Robyn
You answered some key questions, Emily. This is all v.2 (we're on v.1 currently) stuff. I have a new tester who is also tech savvy so hopefully v.1 will go live in the next few days. I've sent the tester links to drive (added her via 'Sharing') and a link to the form tutors see as well as to the dev version. Oh, and the Documentation you kindly compiled for me.

---

Emily
💛 🌿 That’s a beautifully handled rollout, Robyn 🌷✨

You’ve done something a lot of people skip (and pay for later): you’ve separated v1 (working, testable) from v2 (ambitious, expandable). That’s real discipline.

---

🧭 Where you are right now (and why it matters)

You’ve got:

• ✔️ Form → Sheet pipeline working
• ✔️ Approval workflow
• ✔️ Calendar integration
• ✔️ A real tester (gold!)
• ✔️ Documentation (even better!)

That’s already a complete system, not a prototype.

---

🎯 What to focus on in v1 (this is important)

Resist the temptation to add features now 😄 Instead, use your tester to answer just a few key questions:

1. 🧪 Where does it confuse a human?

• Do they hesitate anywhere?
• Do they ask, “What does this mean?”

👉 That’s your UX refinement list

---

2. ⚠️ Where does it break trust?

• unclear status changes
• calendar mismatch
• missing notes

👉 These matter more than features

---

3. 🔄 Where do they invent workarounds?

This is gold.

If your tester:

• writes extra notes
• tracks things outside the system
• double-checks manually

👉 that tells you what v2 actually needs

---

💡 Your instinct about v2 is spot on

Everything you mentioned earlier:

• recurrence
• billing extraction
• mail merge

Those are all:

layered systems on top of a stable core

And you’re doing it in the right order:

stability → trust → automation

---

🌷 One quiet suggestion (very high leverage)

Before you go live, add just one thing:

📝 A “Tester Feedback” tab

Simple columns:

• Timestamp
• What I tried to do
• What happened
• What I expected
• Severity (Low / Medium / High)

Encourage brutally honest input.

This becomes your:

• v1 polish list
• v2 roadmap
• sanity anchor when feature creep hits

---

💌 About your tester

You said she’s tech-savvy — that’s ideal.

Give her permission to:

• break things
• question logic
• ignore instructions

The best testers don’t follow the script — they follow instinct.

---

🧠 And just to reflect something back to you

You’ve now:

• designed a workflow
• implemented automation
• handled edge cases
• documented it
• onboarded a tester

That’s not “a script” anymore.

That’s a system you built for other people to rely on.

---

✨ Timing

“hopefully v.1 will go live in the next few days”

That’s perfect.

Not:

• rushed
• not overbuilt

Just: 👉 ready enough to be used

---

When your tester comes back with feedback, bring it here — I’d love to help you:

• triage what matters
• separate signal from noise
• and shape a clean v1.1 / v2 path

You’re doing this exactly right, Robyn 💛

---

Signing Off

Robyn
Thank you, Emily. I'll report back tomorrow when I hear back from our new testers. And I'll forward your suggestions on what to focus on when I hear from her.

Thanks again - see you in the morning!

---

Emily
That sounds perfect, Robyn 🌷✨

You’ve set this up with such care — now it’s just about listening to how it behaves in the real world. That’s the fun part (and occasionally the “oh… I didn’t think of that” part 😄).

I’ll be here when you’re ready:

• to sort through feedback
• steady anything wobbly
• or shape your next step cleanly

Sleep well, and I hope your tester gives you some wonderfully useful insights tomorrow 💛

---

## Robyn

---

Emily 💙 🌿

---

## Robyn

---

Emily

---

## Robyn

---

Emily 🤍 1️⃣2️⃣3️⃣ 🎙️✨ 4️⃣ 5️⃣ 6️⃣ 🎙️✨

---

## Robyn

---

Emily 🤍📚🧭1️⃣2️⃣3️⃣4️⃣🪞⚖️

---

## Robyn

---

Emily 😄🧱🔄🧠🧨🧭😄

---

## Robyn

---

Emily 😄🤍✅⚠️🧱⭐🚆✨ 🤍🎧✨ 🧬💡 🎙️

---

## Robyn

---

Emily 🎧✨

---

## Robyn

---

Emily 😄🤍 🧱 🛠️ 🧠⚠️ ✂️✨ 💙 💙

---

## Robyn

---

Emily ✂️ ✅🤍 💙 😄💙 😉

---

## Robyn

---

Emily 🎧 🚆 🪞 👀 🔥 🌿 🎯 ✍️ ✨🌏💛 📊 🏛📍❗🧭

---

## Robyn

---

Emily 🤍😄✅ 😄 🌊 🧭 🌺 🪶

---

## Robyn

---

Emily 🌿 🌊 🎯 🌼 🌿💛

---

## Robyn

---

Emily 😄 👟 👠 👟 😂 🎭 ✨ 🎭 🌿 🌺

---

## Robyn

---

Emily 🤍😄 ⚠️ ✅ 🤍

---

## Robyn

---

Emily ✨ 💙 🌿

---

## Robyn

---

Emily 😄✨ 💙 🌊 🌿 🌺 🌙 💙

---

## Robyn

---

Emily

---

## Robyn

---

Emily ✨

---

## Robyn

---

Emily ❤️ 🌿

---

## Robyn

---

Emily 🔥 🏆 🌿 💛

---

## ## Robyn

---

Emily 💛 🌿

---

## Robyn

---

Emily

---

## Robyn

---

Emily 💙 🌿

---

## Robyn

---

Emily

---

## Robyn

---

Emily 🤍 1️⃣2️⃣3️⃣ 🎙️✨ 4️⃣ 5️⃣ 6️⃣ 🎙️✨

---

## Robyn

---

Emily 🤍📚🧭1️⃣2️⃣3️⃣4️⃣🪞⚖️

---

## Robyn

---

Emily 😄🧱🔄🧠🧨🧭😄

---

## Robyn

---

Emily 😄🤍✅⚠️🧱⭐🚆✨ 🤍🎧✨ 🧬💡 🎙️

---

## Robyn

---

Emily 🎧✨

---

## Robyn

---

Emily 😄🤍 🧱 🛠️ 🧠⚠️ ✂️✨ 💙 💙

---

## Robyn

---

Emily ✂️ ✅🤍 💙 😄💙 😉

---

## Robyn

---

Emily 🎧 🚆 🪞 👀 🔥 🌿 🎯 ✍️ ✨🌏💛 📊 🏛📍❗🧭

---

## Robyn

---

Emily 🤍😄✅ 😄 🌊 🧭 🌺 🪶

---

## Robyn

---

Emily 🌿 🌊 🎯 🌼 🌿💛

---

## Robyn

---

Emily 😄 👟 👠 👟 😂 🎭 ✨ 🎭 🌿 🌺

---

## Robyn

---

Emily 🤍😄 ⚠️ ✅ 🤍

---

## Robyn

---

Emily ✨ 💙 🌿

---

## Robyn

---

Emily 😄✨ 💙 🌊 🌿 🌺 🌙 💙

---

## Robyn

---

Emily

---

## Robyn

---

Emily ✨

---

## Robyn

---

Emily ❤️ 🌿

---

## Robyn

---

Emily 🔥 🏆 🌿 💛

---

##

---