Week at a glance office attendance planner for CharlieHR

Build me a week at a glance office attendance planner so I can tell who is actually in each office on any given day. I am the office manager, and the recurring question I need answered is "who is in on Thursday" for desks, lunches, and deciding which day to hold in person sessions. This is a browsing and planning surface I open and filter, not an alert that gets pushed to me.

The main surface is a week grid with a row per person and a column per weekday, Monday through Friday. I can move forward and back a week at a time and jump to a specific date, so I can plan several weeks ahead. Each cell shows that person's resolved working location for that day: the office they are in, working from home, away on leave, or not a working day for them.

Load the people from CharlieHR's List Team Members, the teams from List Teams, and the offices with their details from List Offices. Resolve where each person is working using List Working Locations, which returns either the active locations or the resolved assignments for a given date. Because it is date scoped, the handler must pass a date per column rather than fetching once and reusing the result across the whole week. Use Get Team Member Working Location when I drill into a single person on a single date.

The leave subtraction is the load bearing part of this app, so get it right. Pull approved time off for the visible range from List Leave Requests and show those people as away rather than in the office. Someone assigned to the London office who is on holiday is NOT in the office, and a naive build will get this wrong and inflate every headcount. Leave always beats the assigned working location in the cell, and anyone away is excluded from the office counts.

Part time patterns come from List Team Member Working Weeks. If someone does not work Fridays, that cell should read as a non working day, visually distinct from being on leave and distinct from an empty desk, and it should not count against the office headcount or look like an absence.

Above the grid, give me a headcount per office per day, counted after leave and non working days are removed. Let me set and save a capacity number per office, for example the number of desks it actually has, and persist those numbers in the app so they survive a reload. Compare each day's count against that capacity and make it obvious which days we overflow and which days we sit half empty. I also want to filter the whole grid down to a single office or a single team, and search for a person to see where they are on a specific date.

Two share actions. A button that posts the week's in office roster to a chat channel using Slack Bot's Send a Message, grouped by office and day and readable as plain text. And a button that exports the grid to a Google Sheet using Append Values, one row per person per day with their office, status, and team, for whoever orders lunches and manages desks.

Technical constraints from the CharlieHR API to respect. When filtering by date, start_date and end_date must be sent together in YYYY-MM-DD format or the filter is silently ignored. List endpoints cap per_page at 25 and paginate via meta.next_url, so page through until the results are exhausted rather than assuming one page holds the whole company. Every response is wrapped in a success, data, and meta envelope, so read the payload from data. CharlieHR is read only here, so the app cannot set or change anyone's working location. It visualizes and exports only, and where someone is working is still edited in CharlieHR itself.

Fetching a week for the whole company means a lot of date scoped calls, so load the grid efficiently, show a sensible loading state per week, and cache the week I am looking at so switching filters does not refetch everything.