Automate Weekly Timesheet Reporting with Salesforce, OpenAI and Gmail

Created by

DIGITAL BIZ TECH

Last update

Last update 19 hours ago

Overview

This workflow automates the entire weekly timesheet reporting cycle by integrating Salesforce, OpenAI, Gmail, and n8n. It retrieves employee timesheets for the previous week, identifies which were submitted or not, summarizes all line-item activities using OpenAI, and delivers a consolidated, manager-ready summary that mirrors the final email output.

The workflow eliminates manual checking, reduces repeated follow-ups, and ensures leadership receives an accurate, structured, and consistent weekly report.

Workflow Structure

Data Source: Salesforce DBT Timesheet App

This workflow requires the Digital Biz Tech – Simple Timesheet managed package to be installed in Salesforce.

Install the Timesheet App:
https://appexchange.salesforce.com/appxListingDetail?listingId=a077704c-2e99-4653-8bde-d32e1fafd8c6

The workflow retrieves:

dbt__Timesheet__c — weekly timesheet records
dbt__Timesheet_Line_Item__c — project and activity entries
dbt__Employee__c — employee reference and metadata
Billable, non-billable, and absence hour details
Attendance information

These combined objects form the complete dataset used for both submitted and pending sections.

Trigger

Weekly n8n Schedule Trigger — runs once every week.

Submitted Path

Retrieve submitted timesheets → Fetch line items → Convert to HTML → OpenAI summary → Merge with employee details.

Pending Path

Identify “New” timesheets → Fetch employee details → Generate pending submission list.

Final Output

Merge both paths → Build formatted report → Gmail sends weekly email to managers.

Detailed Node-by-Node Explanation

1. Schedule Trigger

Runs weekly without manual intervention and targets the previous full week.

2. Timesheet – Salesforce GetAll

Fetches all dbt__Timesheet__c records matching:
Timesheet for <week-start> to <week-end>

Extracted fields include:

Employee reference
Status
Billable, non-billable, absence hours
Total hours
Reporting period

Feeds both processing paths.

Processing Path A — Submitted Timesheets

3. Filter Submitted

Filters timesheets where dbt__Status__c == "Submitted".

4. Loop Through Each Submitted Record

Each employee’s timesheet is processed individually.

5. Retrieve Line Items

Fetches all dbt__Timesheet_Line_Item__c entries:

Project / Client
Activity
Duration
Work description
Billable category

6. Convert Line Items to HTML (Code Node)

Transforms line items into well-structured HTML tables for clean LLM input.

7. OpenAI — Weekly Activity Summary

OpenAI receives the HTML + Employee ID and returns a 4-point activity summary avoiding:

Hours
Dates
Repeated or irrelevant metadata

8. Fetch Employee Details

Retrieves employee name, email, and additional fields if needed.

9. Merge Employee + Summary

Combines:

Timesheet data
Employee details
OpenAI summary

Creates a unified object.

10. Prepare Submitted Section (Code Node)

Produces the formatted block used in the final email:

Employee: Name  
Period: Start → End  
Status: Submitted  
Total Hours: ...  

Timesheet Line Items Breakdown:
 - summary point  
 - summary point  
 - summary point  
 - summary point

Processing Path B — Not Submitted Timesheets

11. Identify Not Submitted

Timesheets still in dbt__Status__c == "New" are flagged.

12. Retrieve Employee Information

Fetches employee name and email.

13. Merge Pending Information

Maps each missing submission with its reporting period.

14. Prepare Pending Reporting Block

Creates formatted pending entries:

TIMESHEET NOT SUBMITTED
Employee Name
Email: [email protected]

Final Assembly & Report Delivery

15. Merge Submitted + Pending Sections

Combines all processed data.

16. Create Final Email (Code Node)

Builds:

Subject
HTML body
Section headers
Manager recipient group

Matches the final email layout.

17. Send Email via Gmail

Automatically delivers the weekly summary to managers via Gmail OAuth.

No manual involvement required.

What Managers Receive Each Week

👤 Employee: Name
📅 Period: Start Date → End Date
📌 Status: Submitted
🕒 Total Hours: XX hrs
- Billable: XX hrs
- Non-Billable: XX hrs
- Absence: XX hrs
Weekly Requirement Met: ✔️ / ❌

📂 Timesheet Line Items Breakdown:
• Summary point 1
• Summary point 2
• Summary point 3
• Summary point 4
==========================================================
🟥 TIMESHEET NOT SUBMITTED 🟥
Employee Name
📧 Email: [email protected]

Data Flow Summary

Salesforce
 → Filter Submitted / Not Submitted
      ↳ Submitted → Line Items → HTML → OpenAI Summary → Merge
      ↳ Not Submitted → Employee Lookup → Merge
 → Code Node formats unified report
 → Gmail sends professional weekly summary

Technologies & Integrations

System	Purpose	Authentication
Salesforce	Timesheets, Employees, Timesheet Line Items	Salesforce OAuth
OpenAI	Weekly activity summarization	API Key
Gmail	Automated email delivery	Gmail OAuth
n8n	Workflow automation & scheduling	Native

Agent System Prompt Summary

You are an AI assistant that extracts and summarizes weekly timesheet line items. Produce a clean, structured summary of work done for each employee. Focus only on project activities, tasks, accomplishments, and notable positives or negatives. Follow a strict JSON-only output format with four short points and no extra text or symbols.

Key Features

AI-driven extraction: Converts raw line items into clean weekly summaries.
Strict formatting: Always returns controlled 4-point JSON summaries.
Error-tolerant: Works even when timesheet entries are incomplete or messy.
Seamless integration: Works smoothly with Salesforce, n8n, Gmail, or OpenAI.

Setup Checklist

Install DBT Timesheet App from Salesforce AppExchange
Configure Salesforce OAuth
Configure Gmail OAuth
Set OpenAI model for summarization
Update manager recipient list
Activate the weekly schedule

Summary

This unified workflow delivers a complete, automated weekly reporting system that:

Eliminates manual timesheet checking
Identifies missing submissions instantly
Generates high-quality AI summaries
Improves visibility into employee productivity
Ensures accurate billable/non-billable tracking
Automates end-to-end weekly reporting

Need Help or More Workflows?

We can integrate this into your environment, tune the agent prompt, or extend it for more automation.

We can also help you set it up for free — from connecting credentials to deployment.

Contact: [email protected]
Website: https://www.digitalbiz.tech
LinkedIn: https://www.linkedin.com/company/digital-biz-tech/
You can also DM us on LinkedIn for any help.