Daily ConnectWise time entries to Google Sheets
Every weekday at 7pm, log the day's billable time entries from ConnectWise PSA into a Google Sheet so finance can sanity-check coverage before invoicing.
Integrations
ConnectWise PSA (Manage)
Google Sheets
Type
Categories
- Finance
- Operations
Build a code workflow that runs on a cron trigger every weekday at 7pm in my local time zone. The job is to export the day's billable time entries from ConnectWise PSA (Manage) into a Google Sheet so the finance team can review coverage before invoices are generated. No judgement calls, just a deterministic list-then-append.
Step 1: call ConnectWise PSA's List Time Entries operation. Filter to entries whose timeStart is within the last 24 hours (use the conditions query parameter with a timeStart >= [ISO timestamp 24h ago] clause). Paginate until the list is exhausted. Request enough fields to populate the sheet row: id, timeStart, member, company, agreement, ticket reference, project reference, actualHours, billableOption, chargeCode, and notes.
Step 2: for each time entry, call Google Sheets' Append Values operation against a spreadsheet id and sheet tab I will configure. Append one row with these columns in order: date (the date portion of timeStart), technician member name, company name, agreement name, ticket or project reference (whichever is set, formatted as 'Ticket #123' or 'Project: Foo'), hours billed (actualHours), billable flag (true/false from billableOption), charge code, notes, and a status column.
Step 3: set the status column based on data quality. If the entry has no company linked, or if actualHours is 0, write 'needs review' in the status column. Otherwise write 'ok'. Still append the row in both cases. Do not skip rows.
Inputs the workflow should accept: the Google Sheet spreadsheet id, the sheet tab name (default 'Daily Time Entries'), and optionally a ConnectWise company id, agreement id, or member id filter for teams that want one sheet per book of business. Default behavior is no filter (all time entries from the last 24 hours).
Use Append Values with valueInputOption=USER_ENTERED so dates and numbers render naturally in the sheet. Assume the sheet already has a header row matching the columns above; the workflow only appends data rows.
Error handling: if ConnectWise returns a 429 or 5xx, retry with exponential backoff. If Google Sheets fails for an individual row, log the failure with the time entry id and continue with the next row rather than aborting the run. At the end of the run, print a summary: total entries pulled, rows appended, rows flagged 'needs review', and any failures.
What it does
- Pulls every time entry started in the last 24 hours from your ConnectWise PSA instance on a weekday evening schedule.
- Appends one row per entry to a designated Google Sheet tab with date, technician, company, agreement, ticket or project reference, hours, billable flag, charge code, and notes.
- Flags any row that is missing a company or has zero hours with a 'needs review' status so finance sees the gaps before invoices go out.
- Builds an audit friendly running ledger your team can pivot on weekly without touching ConnectWise reports.
What you’ll need
- A ConnectWise PSA (Manage) login with permission to read time entries, members, companies, agreements, tickets, and projects.
- A Google account connected to the Google Sheet you want the ledger written to.
- The Google Sheet itself, with a tab and header row matching the columns below.
How to customize it
- Change the schedule. Default is weekday evenings at 7pm in your local time, but finance teams that invoice mid-week often shift it to mornings or weekends.
- Change the columns. Add a department, location, or work type column if your finance team pivots on those, or drop the notes column to keep the ledger tight.
- Change the 'needs review' rules. The default flags missing company or zero hours. You can also flag entries with no charge code, no agreement, or notes shorter than a set length.
Use cases
- Data Sync
- Daily Digests
