Build yesterday's timesheet from what you actually did

Every day at 12:00, yesterday is rebuilt from the traces it left: the time your calendar was marked busy during your working day, the mail you sent, and the commits you pushed to the repositories you list. One evidence row is appended to your timesheet, so a billable day starts from evidence rather than memory.

Each row records meeting minutes and hours, how many busy windows there were, how many emails went out, commits pushed and where, and one or two AI-written sentences on the shape of the day, drawn only from the counted figures with nothing invented. If your calendar or mailbox could not be read, that cell is left blank and the reason goes in the 'Notes' column, so a blank never quietly reads as zero. A day already on the tab is never written twice, and nothing already there is edited.

Before the first run you need a Google Sheet with a 'Timesheet' tab headed for the columns the row fills. Commits are optional and need a GitHub token.

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 empty

Yes

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 timesheetReadError

Try

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 day

Yes

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 calendarError

Try

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 busyWindowError

Try

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 60

Yes

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 blank

Yes

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 sentMailError

Try

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 blank

Yes

If githubUsername is blank or githubReadToken is blankIf githubUsername is blank or githubReadToken is blank

Yes

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 empty

Yes

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 repositoryError

Try

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 100

Yes

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 summaryError

Try

ClaudeAsk Claude: summaryPrompt. Save it as modelReply.Ask Claude: summaryPrompt. Save it as modelReply. Maximum response length: 300

Trim 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.