# Total Contractors' Weekly Timesheets From Google Sheets Into One Billing Sheet

A staffing agency's agent reads every contractor's timesheet in a Google Drive folder, flags problems and writes one new Sheet of billable hours per client.

[Run this on Rush](https://cloud.getrush.ai/run/total-weekly-timesheets/google-drive/). Opens the console with this prompt ready to send. Connect Google Drive there first.

## What the agent does

This is for a staffing or contracting agency that places people with client companies and bills those clients by the hour. Each contractor fills in their own Google Sheet every week, copied from the agency's template and saved in a shared Drive folder for that week, and on Monday someone opens every one of them to add up hours by client. The run names one connection, `google-drive`, signed in as the agency's operations account, which asks Google for `drive.readonly`, `drive.file` and `spreadsheets`.

The agent finds the week's sheets first. It calls the Drive API's `files.list` with a `q` that asks for spreadsheets inside the week's folder, `'<folder id>' in parents and mimeType = 'application/vnd.google-apps.spreadsheet'`, and pages through the results. Then it reads each sheet's timesheet range with the Sheets API's `spreadsheets.values.get`, using the A1 range the agency's template puts the entries in, so a contractor's notes elsewhere in the sheet are not mistaken for hours.

It checks each row before it counts it: a date inside the week, a client code that is on the list in its input, hours that are a number between 0 and 24 for that day, and no day entered twice. A row that fails is not counted and goes on an issues list with the sheet's name, the row and the reason. It also lists contractors who are expected this week, from the roster in its input, but have no sheet in the folder, and sheets whose weekly total passes the limit in its input, which the agency reviews before it bills.

Last, it creates one new spreadsheet with the Sheets API's `spreadsheets.create`, which the `drive.file` scope allows for files Rush makes, named for the week. Its first tab totals billable hours by client and contractor; its second lists every issue and every missing timesheet. It changes nothing in the contractors' own sheets.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "In Google Drive, list the spreadsheets in the folder with id 1AbCdEfGhIjKlMnOpQrStUvWxYz (timesheets for the week of 2026-11-02 to 2026-11-08). Read each one's range Timesheet!A2:E60 (date, client code, project, hours, notes). Valid client codes: NWR, HBL, KST, OPT. Expected contractors: Dana Reyes, Sam Li, Ola Bankole, Mia Torres. Count a row only if its date is in the week, its client code is valid, its hours are a number from 0 to 24, and the same date is not entered twice for that client. Create a new spreadsheet named 'Billable hours 2026-11-02' with tab Totals (client, contractor, hours) and tab Issues (sheet name, row, reason), including expected contractors with no sheet and any contractor over 45 hours for the week. Do not edit any existing file. Return the new spreadsheet's link and the totals by client.",
  "connections": [
    "google-drive"
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

The run creates one new spreadsheet in the operations account's Drive and returns its link and the totals by client in `result`. The billing team opens the sheet, works the Issues tab first (a missing timesheet, a wrong client code, a day logged twice), and fixes each one with the contractor before the totals go on an invoice.

What a person checks before billing: that every expected contractor is either on the Totals tab or on the Issues tab, that hours over the weekly limit were approved by the client, and that the client codes in the totals match the agency's contracts. The transcript keeps every range the agent read, so a total that looks wrong can be traced to the cells it came from.

## Run it on every Monday morning

Run it once a week, after the deadline the agency gives contractors. In the console, a Project automation with a `schedule` trigger (`0 10 * * 1` in the agency's timezone) can run a saved agent that names `google-drive` and carries the folder rules in its instructions; the week's folder id goes in the prompt, or the agent finds the folder by the week's name.

Running it a second time later in the day, after missing timesheets come in, creates a fresh spreadsheet for the same week. Comparing it with the first shows exactly what changed.

## Why run it on Rush?

A script that totals timesheets breaks the first time a contractor adds a row in the wrong place or types "8h" instead of 8. The agent reads each sheet the way a person would, applies the rules in the prompt, and writes down every row it refused and why, so the edge cases end up on a list instead of in an invoice.

The agency's Google sign-in stays in a Rush connection rather than in a service account key on someone's machine, and a run reaches only the products it names. Each week's run has its own record: the files it listed, the ranges it read, and the sheet it created.

## Frequently asked questions

### Will the agent edit the contractors' timesheets?

The prompt forbids it. The `spreadsheets` scope could edit sheets the account can edit, so the rule is in the prompt and every call is in the transcript. The only file the run creates is the new totals sheet.

### What if contractors use different layouts?

The prompt names one range from the agency's template. A sheet whose range is empty or has other columns is listed as an issue rather than guessed at, which tells the agency who needs the current template.

### Can it produce the client invoices too?

It can write invoice lines into the new sheet if the prompt gives rates. Sending invoices belongs with the billing team, after they have cleared the Issues tab.

## More Google Drive jobs

- [How Can Our Bookkeeping App Turn Each Client's Receipt Photos in Google Drive Into an Expense Sheet?](https://getrush.ai/run/turn-receipt-photos-into-an-expense-sheet/google-drive/)
- [Check Each Brokerage's Deal Folders in Google Drive for Missing Documents](https://getrush.ai/run/check-deal-files-for-missing-documents/google-drive/)

## More jobs like this: Produce a report or file, no repo

- [Track a Nonprofit's Grant Deadlines and Missing Pieces in Asana Every Week](https://getrush.ai/run/track-grant-deadlines/asana/)
- [Track Which Clients Still Owe Documents From the Firm's Outlook Inbox](https://getrush.ai/run/track-missing-client-documents/outlook/)
- [Which Clients' Month-End Close Is Behind? A Bookkeeping Firm's ClickUp Report](https://getrush.ai/run/track-month-end-close-by-client/clickup/)
- [Triage Customers' Jam Bug Recordings Into Reproducible Reports](https://getrush.ai/run/triage-customer-bug-recordings/jam/)

## Keep reading

- [Connect Google Drive to Rush once, then name it on any run](https://getrush.ai/integrations/google-drive/)
- [Start your first run from the developer API quickstart](https://getrush.ai/docs/quickstart/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
