How to detect room booking conflicts with Pipedream

Workflows > New > HTTP / Webhook Requests

Create new workflow in Pipedream

Navigate to pipedream.com and click the green 'New' button in the top right. Select 'HTTP / Webhook Requests' as your trigger source. This creates an instant webhook endpoint that Google Calendar will call when events change. Copy the webhook URL that appears - you'll need this for Google Calendar setup.

1. 1Click 'New' in the top navigation
2. 2Select 'HTTP / Webhook Requests' from the trigger list
3. 3Copy the webhook URL that appears
4. 4Click 'Save and continue'

Google Cloud Console > Calendar API > Try this API

Set up Google Calendar webhook

In Google Calendar API Console, create a watch request to your room calendar. You'll need the calendar ID from Google Calendar settings and your Pipedream webhook URL. The watch request subscribes to calendar events changes including creations, updates, and deletions.

1. 1Go to developers.google.com/calendar/api/v3/reference/events/watch
2. 2Click 'Try this API' on the right side
3. 3Enter your room calendar ID in calendarId field
4. 4Set address to your Pipedream webhook URL
5. 5Set type to 'web_hook'

Workflow > Add Step > Google Calendar > Get Event

Add Google Calendar step

Back in Pipedream, click 'Add Step' below your trigger. Search for Google Calendar and select 'Get Event' action. Connect your Google account when prompted. This step will fetch full event details when the webhook fires, since webhooks only contain minimal change notifications.

1. 1Click the + button to add a new step
2. 2Search for 'Google Calendar' in the app search
3. 3Select 'Get Event' from the actions list
4. 4Click 'Connect Account' and authorize Google Calendar access
5. 5Set Calendar ID to your room calendar

Google Calendar Step > Configuration > Event ID

Configure event ID extraction

In the Google Calendar step, set Event ID field to extract the event ID from the webhook payload. Google sends this in the X-Goog-Resource-ID header or message body. Map this field to {{trigger.headers['x-goog-resource-id']}} or parse it from the webhook body depending on your Calendar API setup.

1. 1Click in the Event ID field
2. 2Select 'trigger' from the left sidebar
3. 3Choose 'headers' then 'x-goog-resource-id'
4. 4Test the step with sample data

Workflow > Add Step > Code (Node.js)

Add conflict detection code step

Click Add Step and select 'Code (Node.js)'. This step queries Google Calendar API for overlapping events in the same room. You'll write JavaScript to compare the current event times against all other events on the same date, checking for time conflicts.

1. 1Click + to add another step
2. 2Select 'Code (Node.js)' from the step types
3. 3Name the step 'Check for conflicts'
4. 4Paste the conflict detection code in the editor

Add this Node.js code to your conflict detection step to find overlapping events. It queries the Calendar API for the same day and compares time ranges using ISO timestamps.

export default defineComponent({
  async run({ steps, $ }) {
    const currentEvent = steps.google_calendar.$return_value;

export default defineComponent({
  async run({ steps, $ }) {
    const currentEvent = steps.google_calendar.$return_value;
    const startTime = new Date(currentEvent.start.dateTime || currentEvent.start.date);
    const endTime = new Date(currentEvent.end.dateTime || currentEvent.end.date);
    
    // Skip all-day events
    if (currentEvent.start.date && !currentEvent.start.dateTime) {
      return { conflicts: [] };
    }
    
    // Query same day for conflicts
    const dayStart = new Date(startTime);
    dayStart.setHours(0, 0, 0, 0);
    const dayEnd = new Date(startTime);
    dayEnd.setHours(23, 59, 59, 999);
    
    const response = await $.http.get(`https://www.googleapis.com/calendar/v3/calendars/${encodeURIComponent(steps.trigger.event.calendarId)}/events`, {
      headers: {
        'Authorization': `Bearer ${auths.google_calendar.oauth_access_token}`
      },
      params: {
        timeMin: dayStart.toISOString(),
        timeMax: dayEnd.toISOString(),
        singleEvents: true
      }
    });
    
    const conflicts = response.data.items.filter(event => {
      if (event.id === currentEvent.id) return false;
      if (!event.start.dateTime || !event.end.dateTime) return false;
      
      const eventStart = new Date(event.start.dateTime);
      const eventEnd = new Date(event.end.dateTime);
      
      // Check for time overlap
      return (startTime < eventEnd && endTime > eventStart);
    });
    
    return { 
      conflicts: conflicts.map(event => ({
        title: event.summary,
        start: event.start.dateTime,
        end: event.end.dateTime,
        organizer: event.organizer?.email
      }))
    };
  }
});

Workflow > Add Step > Filter

Add conditional logic step

Add a Filter step to only continue when conflicts are detected. Set the condition to check if the conflicts array from your code step has length greater than 0. This prevents unnecessary Slack messages when no conflicts exist.

1. 1Click + to add a Filter step
2. 2Set condition to 'Continue if'
3. 3Reference the conflicts array from previous step
4. 4Set operator to 'Array length greater than'
5. 5Enter 0 as the value

Workflow > Add Step > Slack > Send Message to Channel

Connect Slack workspace

Add a Slack step by clicking Add Step and searching for Slack. Select 'Send Message to Channel' action. Click Connect Account to authorize Pipedream access to your Slack workspace. Choose the #office channel from the dropdown menu.

1. 1Click + to add a new step
2. 2Search for 'Slack' and select it
3. 3Choose 'Send Message to Channel' action
4. 4Click 'Connect Account' and authorize workspace access
5. 5Select '#office' from the Channel dropdown

Slack Step > Configuration > Message

Configure conflict alert message

In the Slack message field, create a formatted alert using data from both the triggering event and conflicting events. Include event titles, times, organizers, and room name. Use Slack's block kit formatting for better readability in the channel.

1. 1Click in the Message field
2. 2Reference event title from Google Calendar step
3. 3Add conflict details from code step results
4. 4Format with Slack markdown for readability
5. 5Include room name and time details

Workflow > Trigger > Test

Test the complete workflow

Create a test booking conflict in your Google Calendar by scheduling overlapping events in the target room. The workflow should trigger within 10-15 seconds and send a formatted alert to your Slack channel. Check the workflow execution log in Pipedream to verify each step completed successfully.

1. 1Open Google Calendar and create a new event in the target room
2. 2Schedule a second overlapping event 15 minutes later
3. 3Watch for the Slack notification in #office channel
4. 4Check Pipedream execution logs for any errors