Email a weekly summary of everyone's hours

Every Monday at 07:00, the workflow reads the clock-in and clock-out times your team logs on a 'Time entries' tab, keeps those from the previous seven days, works out the hours in each and totals them per person. The totals are emailed to your manager or payroll contact and added as one row per person to a 'Weekly summary' tab, so nobody adds up a spreadsheet by hand.

A shift that runs past midnight, such as 22:00 to 06:00, is counted as an overnight shift. An entry that cannot be read is reported, never counted as zero: a missing name, a badly written time, or a finish before the start is listed with its row number. An entry whose date can't be read is listed once, in the week it first appears. If no entries are dated in the week, you still get an email saying so, and if a column heading is missing the run stops and says which one, so a broken sheet never looks like a quiet week. Your entries are only ever read, never edited.

You need Google Sheets and Gmail connected, the link to your timesheet and the address the summary goes to. The entries tab needs 'Date', 'Person', 'Start' and 'End' headings. It records only what people report about themselves and asks for no location or device data.

Run every Monday at 07:00.Run every Monday at 07:00.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set entriesTab to Time entries.Set entriesTab to Time entries.

Set summaryTab to Weekly summary.Set summaryTab to Weekly summary.

Set managerEmail to blank.Set managerEmail to blank.

Set daysToInclude to 7.Set daysToInclude to 7.

Get today's date and save it as today.Get today's date and save it as today.

Get yesterday's date and save it as weekEnding.Get yesterday's date and save it as weekEnding.

Make weekEnding readable as “November 2, 2026” and save it as weekEndingText.Make weekEnding readable as “November 2, 2026” and save it as weekEndingText.

Google SheetsRead the rows of spreadsheetUrl. Save it as timesheet.Read the rows of spreadsheetUrl. Save it as timesheet.

Keep each item in [Date, Person, Start, End] where timesheet.headers doesn't contain item and save it as missingColumns.Keep each item in [Date, Person, Start, End] where timesheet.headers doesn't contain item and save it as missingColumns.

If missingColumns isn't emptyIf missingColumns isn't empty

Yes

Join missingColumns with , and save it as missingList.Join missingColumns with , and save it as missingList.

Stop and report a problem: The '@entriesTab' tab is missing these column headings: missingList. Row 1 needs Date, Person, Start and End, spelled exactly like that. Nothing was added to the summary or emailed.Stop and report a problem: The '@entriesTab' tab is missing these column headings: missingList. Row 1 needs Date, Person, Start and End, spelled exactly like that. Nothing was added to the summary or emailed.

No

No steps

Set entries to timesheet.rows.Set entries to timesheet.rows.

Set workedEntries to an empty list.Set workedEntries to an empty list.

Set flaggedEntries to an empty list.Set flaggedEntries to an empty list.

Set peopleSeen to an empty list.Set peopleSeen to an empty list.

Get the stored value 'timesheet_unreadable_rows'. Save it as unreadableStore.Get the stored value 'timesheet_unreadable_rows'. Save it as unreadableStore.

Set reportedBefore to an empty list.Set reportedBefore to an empty list.

If unreadableStore.found is yesIf unreadableStore.found is yes

Yes

Change reportedBefore to unreadableStore.value.Change reportedBefore to unreadableStore.value.

No

No steps

Set unreadableNow to an empty list.Set unreadableNow to an empty list.

For each entry in entries:For each entry in entries:

If entry.Date is blank and entry.Person is blank and entry.Start is blank and…If entry.Date is blank and entry.Person is blank and entry.Start is blank and…

Yes

Skip to the next item.Skip to the next item.

No

No steps

Set personLabel to Unnamed.Set personLabel to Unnamed.

If a step below fails, save the error as personErrorIf a step below fails, save the error as personError

Try

Trim entry.Person and save it as personLabel.Trim entry.Person and save it as personLabel.

On error

Change personLabel to Unnamed.Change personLabel to Unnamed.

If personLabel is emptyIf personLabel is empty

Yes

Change personLabel to Unnamed.Change personLabel to Unnamed.

No

No steps

Set rowLabel to ?.Set rowLabel to ?.

If entry.row_number isn't emptyIf entry.row_number isn't empty

Yes

Change rowLabel to entry.row_number.Change rowLabel to entry.row_number.

No

No steps

Set dateReadable to false.Set dateReadable to false.

Set inThisWeek to false.Set inThisWeek to false.

Set entryHours to 0.Set entryHours to 0.

Set entryUsable to false.Set entryUsable to false.

Set problem to Date, Start or End is missing or is not written as YYYY-MM-DD and HH:MM.Set problem to Date, Start or End is missing or is not written as YYYY-MM-DD and HH:MM.

If a step below fails, save the error as entryErrorIf a step below fails, save the error as entryError

Try

Trim entry.Date and save it as entryDate.Trim entry.Date and save it as entryDate.

Count the days from entryDate to today and save it as entryAgeInDays.Count the days from entryDate to today and save it as entryAgeInDays.

Change dateReadable to true.Change dateReadable to true.

Change inThisWeek to entryAgeInDays is greater than or equal to 1 and entryAgeInDays is less than or equal to daysToInclude.Change inThisWeek to entryAgeInDays is greater than or equal to 1 and entryAgeInDays is less than or equal to daysToInclude.

Count the minutes from entryDate entry.Start to entryDate entry.End and save it as minutesWorked.Count the minutes from entryDate entry.Start to entryDate entry.End and save it as minutesWorked.

If minutesWorked is less than or equal to -600If minutesWorked is less than or equal to -600

Yes

Add 1440 to minutesWorked.Add 1440 to minutesWorked.

No

No steps

Round minutesWorked divided by 60 to 2 decimal places and save it as entryHours.Round minutesWorked divided by 60 to 2 decimal places and save it as entryHours.

Change entryUsable to true.Change entryUsable to true.

On error

Change entryUsable to false.Change entryUsable to false.

If entryUsable is yes and entryHours is less than or equal to 0If entryUsable is yes and entryHours is less than or equal to 0

Yes

Change entryUsable to false.Change entryUsable to false.

Change problem to End is not later than Start.Change problem to End is not later than Start.

No

No steps

Set includeEntry to inThisWeek.Set includeEntry to inThisWeek.

If dateReadable is noIf dateReadable is no

Yes

Build the text row @rowLabel: @entry.Person | @entry.Date | @entry.Start | @entry.End and save it as rowKey.Build the text row @rowLabel: @entry.Person | @entry.Date | @entry.Start | @entry.End and save it as rowKey.

Add rowKey to the end of unreadableNow and save it as unreadableNow.Add rowKey to the end of unreadableNow and save it as unreadableNow.

If reportedBefore doesn't contain rowKeyIf reportedBefore doesn't contain rowKey

Yes

Change includeEntry to true.Change includeEntry to true.

No

No steps

No

No steps

If includeEntry is yesIf includeEntry is yes

Yes

Add personLabel to the end of peopleSeen and save it as peopleSeen.Add personLabel to the end of peopleSeen and save it as peopleSeen.

If entryUsable is yesIf entryUsable is yes

Yes

Add a record of person and hours to the end of workedEntries and save it as workedEntries.Add a record of person and hours to the end of workedEntries and save it as workedEntries.

No

Add a record of person, row, and problem to the end of flaggedEntries and save it as flaggedEntries.Add a record of person, row, and problem to the end of flaggedEntries and save it as flaggedEntries.

No

No steps

Remove duplicates from peopleSeen and save it as people.Remove duplicates from peopleSeen and save it as people.

If people is emptyIf people is empty

Yes

GmailSend an email to managerEmail.Send an email to managerEmail. Subject: Timesheet summary for the week ending weekEndingText: no entries foundNo time entries dated in the week ending weekEndingText were found on the '@entriesTab' tab, so there are no hours to report. If your team did log hours that week, check that each row's Date is written as YYYY-MM-DD.

Finish the run: No time entries dated in the week ending weekEndingText, so nothing was added to the summary. Emailed managerEmail to say so.Finish the run: No time entries dated in the week ending weekEndingText, so nothing was added to the summary. Emailed managerEmail to say so.

No

No steps

Set summaryLines to an empty list.Set summaryLines to an empty list.

Set entriesToCheck to 0.Set entriesToCheck to 0.

Set summaryTabProblem to blank.Set summaryTabProblem to blank.

For each person in people:For each person in people:

Set personHours to an empty list.Set personHours to an empty list.

Set personProblems to an empty list.Set personProblems to an empty list.

For each workedEntry in workedEntries:For each workedEntry in workedEntries:

If workedEntry.person is personIf workedEntry.person is person

Yes

Add workedEntry.hours to the end of personHours and save it as personHours.Add workedEntry.hours to the end of personHours and save it as personHours.

No

No steps

For each flaggedEntry in flaggedEntries:For each flaggedEntry in flaggedEntries:

If flaggedEntry.person is personIf flaggedEntry.person is person

Yes

Add row flaggedEntry.row - flaggedEntry.problem to the end of personProblems and save it as personProblems.Add row flaggedEntry.row - flaggedEntry.problem to the end of personProblems and save it as personProblems.

No

No steps

Round the total of personHours to 2 decimal places and save it as totalHours.Round the total of personHours to 2 decimal places and save it as totalHours.

Join personProblems with ; and save it as problemText.Join personProblems with ; and save it as problemText.

Change entriesToCheck to entriesToCheck plus the length of personProblems.Change entriesToCheck to entriesToCheck plus the length of personProblems.

If a step below fails, save the error as appendErrorIf a step below fails, save the error as appendError

Try

Google SheetsAdd a row to summaryTab in spreadsheetUrl.Add a row to summaryTab in spreadsheetUrl.

On error

Change summaryTabProblem to appendError.message.Change summaryTabProblem to appendError.message.

Set summaryLine to person: totalHours hours from the length of personHours entries.Set summaryLine to person: totalHours hours from the length of personHours entries.

If personProblems isn't emptyIf personProblems isn't empty

Yes

Change summaryLine to summaryLine - NEEDS ATTENTION: problemText.Change summaryLine to summaryLine - NEEDS ATTENTION: problemText.

No

No steps

Add summaryLine to the end of summaryLines and save it as summaryLines.Add summaryLine to the end of summaryLines and save it as summaryLines.

GmailSend an email to managerEmail.Send an email to managerEmail. Subject: Timesheet summary for the week ending weekEndingTextSelf-reported hours for the week ending weekEndingText. summaryLines joined by a new line Entries needing attention: entriesToCheck The full breakdown is on the '@summaryTab' tab of the timesheet.

If a step below fails, save the error as saveErrorIf a step below fails, save the error as saveError

Try

Set the stored value 'timesheet_unreadable_rows' to unreadableNow.Set the stored value 'timesheet_unreadable_rows' to unreadableNow.

On error

Stop and report a problem: The summary was emailed, but Lodol could not note which entries with an unreadable date it has reported (saveError.message), so they will be listed again next week.Stop and report a problem: The summary was emailed, but Lodol could not note which entries with an unreadable date it has reported (saveError.message), so they will be listed again next week.

If summaryTabProblem isn't blankIf summaryTabProblem isn't blank

Yes

Stop and report a problem: The summary was emailed to managerEmail, but rows could not be added to the '@summaryTab' tab (summaryTabProblem). Check that the tab exists with that exact name.Stop and report a problem: The summary was emailed to managerEmail, but rows could not be added to the '@summaryTab' tab (summaryTabProblem). Check that the tab exists with that exact name.

No

No steps

Finish the run: Emailed the summary for the week ending weekEndingText to managerEmail and added one row per person to the '@summaryTab' tab.Finish the run: Emailed the summary for the week ending weekEndingText to managerEmail and added one row per person to the '@summaryTab' tab.