Weekly CompanyCam jobsite rollup in Google Sheets

Every Monday at 7am (in a configurable timezone), produce a deterministic weekly jobsite rollup in Google Sheets so the leadership team has a single view of every active CompanyCam project from the past week.

Trigger: cron, weekly on Mondays at 7am. Both the exact time and the timezone should be exposed as configurable inputs.

Step 1. Call CompanyCam List Projects to enumerate active jobsites. Paginate through every page (per_page 100, advance page until a short or empty array comes back). Include only projects with status active; skip archived and deleted unless the user opts them in.

Step 2. For each active project, call CompanyCam List Project Photos with modified_since set to the workflow start time minus lookbackDays (default 7) formatted as ISO8601. Paginate to get the full set. Count the total number of photos returned and capture the captured_at timestamp of the most recent one. If a project has zero photos in the window, still include it in the output with a count of 0 and an empty latest-photo timestamp; leadership needs to see the quiet sites too.

Step 3. For each project, append exactly one row to the shared Google Sheet using Google Sheets Append Values. Columns, left to right: (1) project name, (2) project address as a single line joining street, city, state, and postal code, (3) photos captured this week as an integer count, (4) timestamp of the latest photo captured in the window (blank when there were none), (5) direct link back to the CompanyCam project in the form https://app.companycam.com/projects/{project_id}. Use valueInputOption USER_ENTERED so the link becomes clickable and the timestamp renders as a date.

Expose these customization knobs at the top of the workflow: spreadsheetId (the target Google Sheet), sheetRange (default like "Weekly Rollup!A:E"), scheduleTimezone (for the cron), and lookbackDays (default 7).

Every value maps one-to-one from the API responses. No AI reasoning, no summarization, no LLM calls; this is a fully deterministic code workflow. Handle CompanyCam rate limits (240 GET per minute per token) and Google Sheets write limits (60 per user per minute) with retry and exponential backoff on 429s. Handle missing address fields gracefully (leave the assembled address blank rather than failing the row).