Automate weekly timesheet reporting with Salesforce, OpenAI and Gmail

Weekly Timesheet Report + Pending Submissions Workflow 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: - dbtTimesheetc — weekly timesheet records - dbtTimesheetLineItemc — project and activity entries - dbtEmployeec — 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 dbtTimesheetc 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 dbtStatusc == "Submitted". 4. Loop Through Each Submitted Record Each employee’s timesheet is processed individually. 5. Retrieve Line Items Fetches all dbtTimesheetLineItemc 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 dbtStatusc == "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