Build a Capacity Planning AI Agent With Google Calendar and Sheets APIs

TL;DR

• A capacity planning AI agent reads availability from Google Calendar, reads committed work from Google Sheets, and turns both into one number: assignable hours per person.
• The Calendar API tells you when people are busy or away. Sheets tells you what they are already responsible for. Neither answers the capacity question alone.
• MCP (Model Context Protocol) works as the agent's tool interface, with five minimal tools: two for reading, one for calculating, one for proposing, and one for applying approved changes.
• Overbooking often hides behind an empty calendar. The agent catches it by comparing remaining effort in Sheets against net capacity.
• The agent should propose assignments, never commit them. A manager approves before anything is written back to Sheets or Calendar.
• Use free/busy data instead of event titles, request the narrowest OAuth scopes, and keep an audit log of every write.

Most teams plan capacity in two places that never talk to each other. The calendar shows who is in meetings or on leave. A spreadsheet shows who owns which task and how many hours remain. A manager ends up holding both in their head, and the plan breaks the first time someone looks free on Calendar while already being fully committed in the tracker.

A capacity planning AI agent closes that gap. It pulls availability from Google Calendar, pulls workload from Google Sheets, applies your organization's rules, and produces a recommendation a manager can approve or change. This guide walks through the full design: the architecture, the Sheets model, the capacity formula, the MCP tool layer, overbooking logic, and the approval step that keeps a human in control. It also answers a question many teams ask along the way: how to integrate AI agents into HR software without handing an agent the keys to everything.

Google Calendar and Sheets API for Capacity Planning: An AI Agent Architecture

The architecture for a capacity planning AI agent has four layers: the agent itself, an MCP tool layer, an integration and security layer, and the Google APIs underneath. The agent reasons about capacity, calls MCP tools to fetch data, and only writes back after a manager approves.

Here is how the pieces fit together:

1. The agent (MCP client): The LLM that reasons about demand versus availability. It discovers tools with a tools/list request and runs them with tools/call.
2. The integration layer: Handles OAuth, token storage, multi tenant isolation, and approval interception. The agent never sees raw credentials. This is the role an open source integration layer like Corsair plays.
3. The Calendar and Sheets MCP servers: Each wraps a small set of Google API calls and exposes them as tools with typed inputs and outputs.
4. Google Calendar and Google Sheets APIs: The systems of record. Calendar owns availability. Sheets owns roster and workload data.

The flow runs in a fixed order. The agent reads the roster and tasks from Sheets, queries availability from Calendar, calculates net capacity, and builds an assignment proposal. That proposal goes to a manager for review. Only after approval does the agent update Sheets and place holds on Calendar.

If you want a broader look at how the two APIs work together for staffing decisions, the earlier guide on Google Calendar and Sheets APIs for a capacity planning AI agent covers team availability and project workloads in more depth.

How to Combine Calendar Availability With Workload Data in Sheets for AI Capacity Planning

To combine calendar availability with workload data in Sheets, subtract leave from nominal working hours, apply a utilization buffer, then subtract meetings, non project focus time, and work already assigned. What remains is the person's assignable capacity for the week.

Start with what each source contributes.

From Google Calendar:

• Busy intervals: the freeBusy.query endpoint returns busy time ranges for up to 50 calendars per call, without event titles or details.
• Leave and status: events.list with eventTypes set to outOfOffice, focusTime, or workingLocation exposes the status blocks that free/busy alone cannot separate from ordinary meetings.

From Google Sheets:

• A roster tab with employee identifier, calendar identifier, working hours, and skills.
• A task tab with assigned employee, remaining effort, priority, deadline, assignment status, and approval status.

A simple two tab schema keeps this manageable:

• Resource Roster: Employee identifier, name, calendar identifier, working hours per week, leave hours, skills, and role.
• Task Assignments: Task ID, description, employee identifier, required skills, hours already assigned, remaining effort, priority, deadline, assignment status, and approval status.
• Summary tab (optional): Team level formulas for total capacity, allocated workload, pending approvals, and utilization per person.

Keep one rule in mind: separate what the APIs provide from what your organization decides. Calendar tells the agent that a focus block exists. Your policy decides whether that block counts as project time or overhead. Sheets holds contract hours, but your policy sets the target utilization. Writing these policy values into a config tab makes the agent's math transparent instead of buried in a prompt.

The weekly capacity formula

Here is the formula in plain terms:

assignable_hours = max(0,

(nominal_hours − leave_hours) × utilization_buffer

− (meeting_hours + focus_overhead_hours)

− existing_workload_hours

)

Every variable has a clear source:

• nominal_hours: contracted weekly hours, from the Sheets roster (policy).
• leave_hours: out of office time, from Calendar events.list.
• utilization_buffer: target utilization such as 0.85, from your policy config.
• meeting_hours: total busy time, from Calendar freeBusy.query.
• focus_overhead_hours: focus time your policy treats as non project work, from Calendar plus policy.
• existing_workload_hours: remaining effort already assigned, from the Sheets task tab.

A worked example: an employee has 40 nominal hours, 8 hours of leave, an 0.85 buffer, 8 hours of meetings, 2 hours of overhead focus time, and 10 hours of existing tasks.

1. Base hours: 40 minus 8 equals 32.
2. After buffer: 32 × 0.85 equals 27.2.
3. After calendar commitments: 27.2 minus (8 + 2) equals 17.2.
4. After existing workload: 17.2 minus 10 equals 7.2 assignable hours.

That 7.2 is the number the agent uses when a manager asks whether someone can take on more work.

Designing an MCP Tool Layer for a Capacity Planning AI Agent

A minimal MCP tool layer for this agent needs five tools: retrieve Calendar availability, retrieve Sheets workload, calculate capacity, generate an assignment proposal, and apply an approved assignment. Only the last one changes data.

Keeping the surface this small makes the agent easier to test, easier to secure, and easier for a manager to reason about. For a deeper look at how agents discover and run tools, see this guide to AI agent tool calling.

The five tools:

1. get_calendar_availability
   • Inputs: calendar IDs, time_min, time_max (RFC3339 with explicit offsets), and an optional flag for status events.
   • Outputs: busy intervals per calendar, plus out of office and focus time hours when requested.
   • Permissions: calendar.freebusy for availability. Status events need a broader read scope, covered in the security section below.
   • Changes data: no.
2. get_sheets_workload
   • Inputs: spreadsheet ID and A1 ranges for the roster and task tabs.
   • Outputs: roster objects and task objects with remaining effort, priority, deadline, and status.
   • Permissions: spreadsheets.readonly.
   • Changes data: no.
3. calculate_net_capacity
   • Inputs: nominal hours, leave, meetings, focus overhead, existing workload, and utilization buffer.
   • Outputs: net capacity, an overbooked flag, and a step by step breakdown.
   • Permissions: none. It is a pure calculation with no external calls.
   • Changes data: no.
4. generate_assignment_proposal
   • Inputs: task ID, required skills, estimated effort, deadline, and candidate IDs.
   • Outputs: ranked candidates with trade off explanations and a proposal token.
   • Permissions: none. It drafts a proposal and triggers the review step.
   • Changes data: no.
5. apply_approved_assignment
   • Inputs: approval token, spreadsheet ID, cell updates, and a calendar event definition.
   • Outputs: updated cell count, created event ID, and execution status.
   • Permissions: spreadsheets and calendar.events.
   • Changes data: yes. This is the only write tool, and it requires a valid approval token.

Notice what the design does. Reading and calculating are free to run. Writing is gated. That split is what lets you give the agent broad analytical freedom without giving it broad write access.

A Capacity Planning AI Agent That Actually Understands Overbooking

A capacity planning AI agent detects overbooking by comparing a person's committed work in Sheets against their net capacity, not by checking whether the calendar looks empty. When committed effort exceeds net capacity, the agent flags the person as overbooked, even if Calendar shows no meetings at all.

This matters because calendar only tools are fooled by what we can call phantom availability. Consider a senior engineer with 40 contracted hours:

• Calendar view: freeBusy.query for the week returns an empty busy array. A scheduling tool sees 40 free hours.
• Sheets view: Two active tasks list 20 and 15 remaining hours. That is 35 hours of committed work.
• Policy: The utilization cap is 85 percent, so the ceiling is 34 hours.

The agent's math: 40 × 0.85 minus 0 meetings minus 35 committed equals negative 1 hour. The person is already over capacity before anyone adds anything.

Now a manager asks whether the engineer can take a new 10 hour task. A calendar only tool says yes. The agent says no, and explains why: 45 hours would be committed against a 34 hour ceiling, an overshoot of 11 hours.

What the agent does next is where the design earns its keep:

• Flags the person as overbooked with the numbers shown.
• Suggests candidates with positive net capacity and matching skills.
• Offers an alternative such as moving a deadline on an existing task.
• Sends any change through the approval step instead of applying it.

Task estimates are uncertain, so a stronger version of the agent uses a range instead of a single number. A common approach is a three point estimate (optimistic, most likely, pessimistic) that produces an expected effort plus a risk buffer. A candidate whose net capacity is lower than the risk adjusted effort gets marked as capacity constrained rather than silently accepted.

Google Calendar and Sheets APIs for Capacity Planning AI Agents: From Free/Busy to Approved Write-Back

The safe path from free/busy data to a write back has three stages: read with the narrowest permissions, propose instead of commit, and write only after explicit manager approval. Each stage has a specific control.

Read with the narrowest permissions

Free/busy is the privacy friendly default. The freeBusy.query endpoint returns only busy time ranges, never titles, attendees, or descriptions. Google documents calendar.freebusy and calendar.events.freebusy as scopes that allow this query, alongside the broader calendar.readonly and calendar scopes. Choosing the narrowest one means the agent physically cannot read a meeting title, even if a prompt tells it to.

On the sharing side, the calendar owner needs to grant at least the freeBusyReader role through the calendar's access control list.

There is a real trade off here. Free/busy alone cannot tell leave from a meeting. To read outOfOffice and focusTime events, the agent needs events.list, which requires a broader read scope. Teams have two sensible options: accept the broader scope for a dedicated status query, or keep leave hours in the Sheets roster and let free/busy handle meetings only. The second option preserves the narrowest permissions. For guidance on choosing scopes across Gmail, Drive, Calendar, and Sheets, this guide to Google API scopes for AI agents goes deeper.

Two practical details when calling free/busy:

• Timestamps must be RFC3339 with explicit offsets. The optional timeZone parameter controls how response times are rendered, and it defaults to UTC.
• One call covers up to 50 calendars, so larger teams need to batch their queries.

Propose instead of commit

The agent should never write assignments directly. The MCP specification recommends keeping a human in the loop with the ability to deny tool invocations, and capacity decisions are a strong case for it. Calendars and spreadsheets cannot capture context like an unannounced absence, someone's development goals, or an unwritten task dependency.

A good approval card shows the manager everything needed to decide in one view:

• Selected employee and task: Who, what, and the required skills match.
• Effort and time period: Estimated effort with the uncertainty range, and the planning window.
• Resulting utilization: Before and after numbers against the target cap.
• Conflicts: Fragmented schedules, deadline margins, and overlapping commitments.
• Assumptions: Which focus time counts as project time, which buffer was applied, and which meetings were treated as fixed.
• Actions: Approve, modify (split effort, adjust hours, shift the window), or reject with a reason that feeds back into future recommendations.

For a closer look at how review requests, rejection, expiration, and audit trails work for agent actions, see this piece on managing human approval before AI agents change customer data.

Write back and keep a record

After approval, the write tool applies changes in two places:

• Sheets: One spreadsheets.values.batchUpdate call updates assignment, remaining effort, and approval status together. An audit row is appended with the timestamp, task ID, and approval token.
• Calendar: events.insert places focus time holds on the employee's calendar so the approved work is protected from new meetings.

Because holds are created as identifiable events, they can be adjusted or deleted if priorities change. That keeps the change reversible.

Pseudocode for the full loop

roster, tasks = get_sheets_workload(spreadsheet_id, ranges)

availability = get_calendar_availability(calendar_ids, week_start, week_end)

for person in roster:

capacity[person] = calculate_net_capacity(

person.hours, availability.leave, availability.meetings,

availability.focus_overhead, tasks.remaining_for(person), buffer=0.85)

candidates = [p for p in roster if has_skills(p, task) and not on_leave(p)]

proposal = generate_assignment_proposal(task, candidates, capacity)

approval = wait_for_manager(proposal) # approve, modify, or reject

if approval.approved:

apply_approved_assignment(approval.token, proposal.sheet_updates, proposal.calendar_hold)

How this fits HR software

Teams often ask how to integrate AI agents into HR software without exposing sensitive data. The pattern above answers it. Keep the HR system or roster as the source of truth for hours and skills, expose it to the agent through narrow read tools, use free/busy instead of event contents, and require approval before any change. Employee calendar data is personal, so data minimization matters: query only the planning window, read only the needed sheet ranges, and log every tool call.

Closing Thoughts

A capacity planning AI agent is only as trustworthy as the plumbing under it. The calculation is straightforward, but the credentials, scopes, approvals, and audit trail are what make it safe to run on real employee data. Corsair is an open source integration layer for AI agents that handles multi tenant OAuth, permissions, and MCP tool access, so you can connect Google Calendar and Google Sheets without building that infrastructure yourself or exposing raw credentials to the agent. Explore how it works at corsair.dev and start with the free Hobby plan.

FAQs

Can a capacity planning AI agent use Google Calendar without reading event titles?

Yes. The freeBusy.query endpoint returns only busy time ranges for each calendar, with no titles, descriptions, or attendees. If you grant the agent a free/busy scope and the calendar owner shares at the freeBusyReader level, the agent can calculate meeting load without ever seeing what the meetings are about.

What is the minimum OAuth scope for checking availability?

Google lists calendar.freebusy and calendar.events.freebusy as scopes that allow the free/busy query, alongside broader options like calendar.readonly. For reading a sheet, spreadsheets.readonly is enough. Only the component that writes approved changes needs the full spreadsheets scope and calendar.events.

How does the agent detect overbooking when the calendar looks empty?

It compares remaining effort in Sheets against net capacity. If someone has no meetings but 35 hours of open tasks against a 34 hour utilization ceiling, their net capacity is negative and the agent flags them as overbooked. Calendar shows time, Sheets shows commitments, and the agent needs both.

Why should the agent propose assignments instead of committing them?

Assignments change real schedules and project timelines, and the data cannot capture every factor a manager knows. Proposing first lets a human confirm the assumptions, catch missing context, and modify or reject the plan. It also matches the MCP recommendation to keep a human able to deny tool invocations.

How do you integrate AI agents into HR software for capacity planning?

Treat your roster in Sheets or your HR system as the source of truth for hours and skills, and expose it to the agent through read tools over MCP. Add Calendar availability through free/busy, define utilization and leave rules as explicit policy, and gate all writes behind manager approval and an audit log. This keeps sensitive employee data scoped and every change traceable.