<aside> 🗒️

BEFORE YOU BEGIN:

Create a Booking Page in your Google Account. You will use the name of this Booking Page below as the targetBookingName

</aside>

1. In your Google Sheet, click on Extensions > Apps Script from the top menu.

2. Delete any code in the editor and paste the following script:

   function syncGoogleBookingsToSheet() {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     const calendar = CalendarApp.getDefaultCalendar();
   
     // Define the date range (7 days in the past to 30 days in the future)
     const now = new Date();
     const startTime = new Date(now.getTime() - (7 * 24 * 60 * 60 * 1000));
     const endTime = new Date(now.getTime() + (30 * 24 * 60 * 60 * 1000));
   
     const events = calendar.getEvents(startTime, endTime);
     const data = sheet.getDataRange().getValues();
   
     // Event ID is in the 5th column (index 4)
     const existingEventIds = data.map(row => row[4]);
   
     // Create headers if the sheet is empty
     if (data.length === 0 || (data.length === 1 && data[0][0] === "")) {
       sheet.appendRow(["First Name", "Last Name", "Email", "Meeting Time", "Event ID"]);
     }
   
     // Get the timezone of the script so dates and times format correctly
     const timeZone = Session.getScriptTimeZone();
   
     for (let i = 0; i < events.length; i++) {
       const event = events[i];
       const eventId = event.getId();
       const description = event.getDescription() || "";
       const title = event.getTitle() || "";
   
       // Define the unique name of the booking page you want to track
       const targetBookingName = "Schedule a Call"; // <-- ENSURE THIS matches your booking page name
   
       // FILTER: Only process events that have "Booked by" (Standard for Google Booking Pages)
       if (description.includes("Booked by") && !existingEventIds.includes(eventId) && title.includes(targetBookingName)) {
         let firstName = "";
         let lastName = "";
         let email = "";
   
         // Clean up the description: convert HTML breaks to newlines and remove other HTML tags
         const cleanDesc = description.replace(/<br\s*\/?>/gi, '\n').replace(/(<([^>]+)>)/gi, "");
   
         // Extract Name and Email based on Google's standard format
         const match = cleanDesc.match(/Booked by\s*\n\s*([^\n]+)\s*\n\s*([^\n]+)/i);
   
         if (match) {
           const fullName = match[1].trim().split(" ");
           firstName = fullName[0] || "";
           // Joins the rest of the name in case they have a multi-part last name
           lastName = fullName.slice(1).join(" ") || "";
   
           email = match[2].trim();
         }
   
         // Format the dates and times
         const meetingTime = Utilities.formatDate(event.getStartTime(), timeZone, "MM/dd/yyyy hh:mm a");
   
         // Add the extracted data to the next empty row in the sheet
         sheet.appendRow([
           firstName,
           lastName,
           email,
           meetingTime,
           eventId
         ]);
       }
     }
   }
   

How to set up the "Immediate" Trigger:

1. Go back to the Apps Script editor and click the Triggers icon (the alarm clock on the left).

   <aside> 🗒️

   NOTE: If you already created a "Time-driven" trigger, hover over it, click the three dots on the right, and delete it.

   </aside>

2. Click + Add Trigger in the bottom right corner.

3. Set it up with these exact options:

   • Choose which function to run: syncGoogleBookingsToSheet
   • Select event source: From calendar
   • Calendar details: Calendar updated
   • Calendar owner email: Type in your primary Google Calendar email address (the one you use for your booking page).

4. Click Save.

5. Test it by filling out the Google Bookings Page you created.