Rebuilds yesterday from your calendar, sent mail and commits, and appends one evidence row to your timesheet.
Uses
Claude
Gmail
Google Calendar
Google Sheets
Build yesterday's timesheet from what you actually did
Run every day at 12:00.Run every day at 12:00.
Set timeZone to blank.Set timeZone to blank.Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.Set timesheetTab to Timesheet.Set timesheetTab to Timesheet.Set calendarId to primary.Set calendarId to primary.Set workingDayStartsAt to 08:00:00.Set workingDayStartsAt to 08:00:00.Set workingDayEndsAt to 20:00:00.Set workingDayEndsAt to 20:00:00.Set skipBusyBlocksLongerThanHours to 6.Set skipBusyBlocksLongerThanHours to 6.Set maxEmailsPerDay to 100.Set maxEmailsPerDay to 100.Set githubRepositories to blank.Set githubRepositories to blank.Set githubUsername to blank.Set githubUsername to blank.Set githubReadToken to blank.Set githubReadToken to blank.Set reportDate to blank.Set reportDate to blank.
Get yesterday's date and save it as day.Get yesterday's date and save it as day.
If reportDate isn't emptyIf reportDate isn't emptyYes
Trim reportDate and save it as day.Trim reportDate and save it as day.No
No steps
If the length of day is not 10 or the length of the first item of day split by…If the length of day is not 10 or the length of the first item of day split by…Yes
Stop and report a problem: a list of [The 'reportDate' step must be written as YYYY-MM-DD, for example 2026-08-31. It currently reads: , day] joined by nothing.Stop and report a problem: a list of [The 'reportDate' step must be written as YYYY-MM-DD, for example 2026-08-31. It currently reads: , day] joined by nothing.No
No steps
Set datesAlreadyOnTheTimesheet to an empty list.Set datesAlreadyOnTheTimesheet to an empty list.
If a step below fails, save the error as timesheetReadErrorIf a step below fails, save the error as timesheetReadErrorTry
Google SheetsRead column 'A' of timesheetTab in spreadsheetUrl. Save it as dateColumn.Read column 'A' of timesheetTab in spreadsheetUrl. Save it as dateColumn.
Change datesAlreadyOnTheTimesheet to dateColumn.values.Change datesAlreadyOnTheTimesheet to dateColumn.values.On error
Stop and report a problem: a list of [Could not read column A of the ', timesheetTab, ' tab, so nothing was written. Check the tab exists and is named exactly that, with 'Date' as the heading of column A. The reason given was: , timesheetReadError.message] joined by nothing.Stop and report a problem: a list of [Could not read column A of the ', timesheetTab, ' tab, so nothing was written. Check the tab exists and is named exactly that, with 'Date' as the heading of column A. The reason given was: , timesheetReadError.message] joined by nothing.If datesAlreadyOnTheTimesheet contains dayIf datesAlreadyOnTheTimesheet contains dayYes
Finish the run: a list of [day, is already on the ', timesheetTab, ' tab, so nothing was added.] joined by nothing.Finish the run: a list of [day, is already on the ', timesheetTab, ' tab, so nothing was added.] joined by nothing.No
No steps
Set notes to an empty list.Set notes to an empty list.Set busyMinutes to blank.Set busyMinutes to blank.Set busyHours to blank.Set busyHours to blank.Set busyWindowsCounted to blank.Set busyWindowsCounted to blank.Set busyWindowsFound to blank.Set busyWindowsFound to blank.Set busyWindows to an empty list.Set busyWindows to an empty list.If a step below fails, save the error as calendarErrorIf a step below fails, save the error as calendarErrorTry
Google CalendarCheck if the calendar is free from a list of [day, T, workingDayStartsAt] joined by nothing to a list of [day, T, workingDayEndsAt] joined by nothing. Save it as busy.Check if the calendar is free from a list of [day, T, workingDayStartsAt] joined by nothing to a list of [day, T, workingDayEndsAt] joined by nothing. Save it as busy.Change busyWindows to busy.busySlots.Change busyWindows to busy.busySlots.
Count the items in busyWindows and save it as busyWindowsFound.Count the items in busyWindows and save it as busyWindowsFound.Change busyMinutes to 0.Change busyMinutes to 0.Change busyWindowsCounted to 0.Change busyWindowsCounted to 0.On error
Add a list of [Your calendar could not be read for this day, so the meeting figures are blank rather than zero. The reason given was: , calendarError.message] joined by nothing to the end of notes and save it as notes.Add a list of [Your calendar could not be read for this day, so the meeting figures are blank rather than zero. The reason given was: , calendarError.message] joined by nothing to the end of notes and save it as notes.
For each busyWindow in busyWindows:For each busyWindow in busyWindows:If a step below fails, save the error as busyWindowErrorIf a step below fails, save the error as busyWindowErrorTry
Set windowMinutes to the difference between busyWindow.start and busyWindow.end in minutes multiplied by 1.Set windowMinutes to the difference between busyWindow.start and busyWindow.end in minutes multiplied by 1.If windowMinutes is greater than skipBusyBlocksLongerThanHours multiplied by 60If windowMinutes is greater than skipBusyBlocksLongerThanHours multiplied by 60Yes
Add a list of [Left out one busy block of , windowMinutes, minutes: it is longer than the 'skipBusyBlocksLongerThanHours' setting, so it reads as an all-day or out-of-office entry rather than a meeting.] joined by nothing to the end of notes and save it as notes.Add a list of [Left out one busy block of , windowMinutes, minutes: it is longer than the 'skipBusyBlocksLongerThanHours' setting, so it reads as an all-day or out-of-office entry rather than a meeting.] joined by nothing to the end of notes and save it as notes.Else if @windowMinutes is greater than 0
Change busyMinutes to busyMinutes plus windowMinutes.Change busyMinutes to busyMinutes plus windowMinutes.Add 1 to busyWindowsCounted.Add 1 to busyWindowsCounted.Otherwise
No steps
On error
Add Left out one busy window on the calendar that had no readable start and end time. to the end of notes and save it as notes.Add Left out one busy window on the calendar that had no readable start and end time. to the end of notes and save it as notes.If busyMinutes isn't blankIf busyMinutes isn't blankYes
Round busyMinutes divided by 60 to 2 decimal places and save it as busyHours.Round busyMinutes divided by 60 to 2 decimal places and save it as busyHours.No
No steps
Join a list of [in:sent after:, day with - replaced by /, before:, the first item of day shifted by 1 days split by T with - replaced by /] with nothing and save it as sentMailQuery.Join a list of [in:sent after:, day with - replaced by /, before:, the first item of day shifted by 1 days split by T with - replaced by /] with nothing and save it as sentMailQuery.Set emailsSent to blank.Set emailsSent to blank.If a step below fails, save the error as sentMailErrorIf a step below fails, save the error as sentMailErrorTry
GmailList emails matching sentMailQuery. Save it as sentMail.List emails matching sentMailQuery. Save it as sentMail.Count the items in sentMail.emails and save it as emailsSent.Count the items in sentMail.emails and save it as emailsSent.On error
Add Sent mail could not be read for this day, so the 'Emails sent' cell is blank rather than zero. to the end of notes and save it as notes.Add Sent mail could not be read for this day, so the 'Emails sent' cell is blank rather than zero. to the end of notes and save it as notes.If emailsSent isn't blank and emailsSent is greater than or equal to…If emailsSent isn't blank and emailsSent is greater than or equal to…Yes
Add a list of [The sent-mail count stopped at the 'maxEmailsPerDay' setting of , maxEmailsPerDay, , so more mail may have gone out than is shown here.] joined by nothing to the end of notes and save it as notes.Add a list of [The sent-mail count stopped at the 'maxEmailsPerDay' setting of , maxEmailsPerDay, , so more mail may have gone out than is shown here.] joined by nothing to the end of notes and save it as notes.No
No steps
Set commitsPushed to blank.Set commitsPushed to blank.Set repositoriesWorkedIn to an empty list.Set repositoriesWorkedIn to an empty list.Set githubHeaders to a record of Authorization, Accept, and X-GitHub-Api-Version.Set githubHeaders to a record of Authorization, Accept, and X-GitHub-Api-Version.If githubRepositories isn't blankIf githubRepositories isn't blankYes
If githubUsername is blank or githubReadToken is blankIf githubUsername is blank or githubReadToken is blankYes
Add Commits were not counted: 'githubRepositories' is filled in but 'githubUsername' or 'githubReadToken' is empty. to the end of notes and save it as notes.Add Commits were not counted: 'githubRepositories' is filled in but 'githubUsername' or 'githubReadToken' is empty. to the end of notes and save it as notes.No
Change commitsPushed to 0.Change commitsPushed to 0.For each repositoryEntry in githubRepositories split by ,:For each repositoryEntry in githubRepositories split by ,: Trim repositoryEntry and save it as repository. Trim repositoryEntry and save it as repository. If repository is empty If repository is emptyYes
Skip to the next item.Skip to the next item.No
No steps
Join a list of [https://api.github.com/repos/, repository, /commits] with nothing and save it as repositoryCommitsUrl. Join a list of [https://api.github.com/repos/, repository, /commits] with nothing and save it as repositoryCommitsUrl. If a step below fails, save the error as repositoryError If a step below fails, save the error as repositoryErrorTry
Fetch repositoryCommitsUrl and save it as commitsResponse.Fetch repositoryCommitsUrl and save it as commitsResponse.Count the items in commitsResponse.body and save it as repositoryCommits.Count the items in commitsResponse.body and save it as repositoryCommits.Change commitsPushed to commitsPushed plus repositoryCommits.Change commitsPushed to commitsPushed plus repositoryCommits.Add a list of [repository, (, repositoryCommits, )] joined by nothing to the end of repositoriesWorkedIn and save it as repositoriesWorkedIn.Add a list of [repository, (, repositoryCommits, )] joined by nothing to the end of repositoriesWorkedIn and save it as repositoriesWorkedIn.If repositoryCommits is greater than or equal to 100If repositoryCommits is greater than or equal to 100Yes
Add a list of [GitHub returns at most 100 commits in one request and that many came back for , repository, , so its count may be short.] joined by nothing to the end of notes and save it as notes.Add a list of [GitHub returns at most 100 commits in one request and that many came back for , repository, , so its count may be short.] joined by nothing to the end of notes and save it as notes.No
No steps
On error
Add a list of [Could not read commits from , repository, : , a list of [repositoryError.message] joined by nothing with repositoryCommitsUrl replaced by the GitHub commits endpoint] joined by nothing to the end of notes and save it as notes.Add a list of [Could not read commits from , repository, : , a list of [repositoryError.message] joined by nothing with repositoryCommitsUrl replaced by the GitHub commits endpoint] joined by nothing to the end of notes and save it as notes.No
No steps
Join repositoriesWorkedIn with , and save it as repositoryBreakdown.Join repositoriesWorkedIn with , and save it as repositoryBreakdown.Join a list of [Date: , day, "Join a list of [Date: , day,
Time marked busy on the calendar, between , workingDayStartsAt, and , workingDayEndsAt, : , busyMinutes, minutes (, busyHours, hours) across , busyWindowsCounted, counted windows, out of , busyWindowsFound, the calendar reported,
Emails sent: , emailsSent,
Commits pushed: , commitsPushed,
Repositories committed to: , repositoryBreakdown] with nothing and save it as figures.Join a list of ["Someone is rebuilding a billable timesheet for one day and has only the figures below. Write one or two plain sentences describing the shape of that day, so they can recognise it and fill in the rest.Join a list of [Someone is rebuilding a billable timesheet for one day and has only the figures below. Write one or two plain sentences describing the shape of that day, so they can recognise it and fill in the rest.
Rules you must follow:
- Use only the figures given. Do not invent meeting subjects, attendees, clients, projects or tasks - you have not been told what any of the work was about, only how much of it there was.
- Repeat the numbers exactly as given. Do not round them, add them up differently, or estimate anything.
- A blank figure means it could not be counted. Say it was not counted; never treat it as zero.
- Do not guess how many hours were billable.
Figures:
, figures] with nothing and save it as summaryPrompt.Set daySummary to blank.Set daySummary to blank.If a step below fails, save the error as summaryErrorIf a step below fails, save the error as summaryErrorTry
ClaudeAsk Claude: summaryPrompt. Save it as modelReply.Ask Claude: summaryPrompt. Save it as modelReply.
Maximum response length: 300Trim modelReply.response_text and save it as daySummary.Trim modelReply.response_text and save it as daySummary.On error
Add The written summary could not be generated; every figure on this row is unaffected. to the end of notes and save it as notes.Add The written summary could not be generated; every figure on this row is unaffected. to the end of notes and save it as notes.
Show in the output: summaryError.message.Show in the output: summaryError.message.Google SheetsAdd a row to timesheetTab in spreadsheetUrl.Add a row to timesheetTab in spreadsheetUrl.