Task-Based Billing and Payroll Calculation using AI, Dart, and Google Sheets - n8n Workflow

• Project Managers needing automated payroll generation based on task completion.


• Agencies or Consultancies managing billable hours tracked across various tools.


• Businesses using Dart for task management and Google Sheets for rate cards.


• n8n users looking for advanced examples of AI integration within a data pipeline.

Managing payroll and billable hours manually can be complex, especially when data is spread across task management systems and spreadsheets. This specific n8n workflow solves this by creating a unified, automated data pipeline. The automation uses the power of an AI Agent (Gemini) to perform complex logic, such as fuzzy matching names, filtering tasks only by 'Done' status, converting task durations into total hours, and calculating the final payout using hourly rates defined in a Google Sheet.

This robust n8n workflow reduces human error, ensures accurate payment based strictly on completed work, and provides instant documentation by pushing the calculated results back into your designated spreadsheet. It's a powerful example of how n8n templates can streamline mission-critical business processes.

This automation follows a scheduled execution cycle:

1. Schedule Trigger: The n8n workflow begins execution based on a predefined schedule, such as weekly or monthly, making it a reliable n8n trigger.


2. Gather Task Logs: The workflow calls the Dart API using the Dart n8n node to fetch a list of all tasks from a specified Dartboard. This provides the raw time tracking data.


3. Fetch Rate Card: Simultaneously, the Google Sheets n8n node reads all existing data from the target 'Rate Card' spreadsheet. This data contains the authorized assignees and their hourly rates.


4. Data Aggregation: The Aggregate n8n node combines the data items read from the Google Sheet into a single item, ensuring the AI agent receives the complete Rate Card as one input.


5. AI Calculation Agent: The core of the n8n workflow, the Agent node (powered by Gemini), receives both the Task Logs (Input B) and the Rate Card (Input A). Guided by a strict prompt, it performs the payroll calculation: filtering for 'Done' tasks, summing durations, converting to hours, and multiplying by the hourly rate.


6. Parsing Output: A specialized Code n8n node cleans and parses the strict JSON output generated by the AI agent, converting it into a structured data format suitable for the next step.


7. Log Results: Finally, another Google Sheets n8n node (Append row) takes the calculated payroll data (Name, TotalHours, TotalPay, DateCalculated) and appends it as new rows to the tracking spreadsheet, completing the documentation of this automated n8n workflow.

To deploy this comprehensive n8n workflow, follow these steps:

1. Import the n8n Workflow: Copy the provided JSON data and paste it into your n8n instance via the 'Import from JSON' function.


2. Credentials Setup: You will need to set up three main credentials:

1. Configure Dart Node: In the 'List tasks' Dart n8n node, update the dartboard_id parameter with the ID of the specific project or board you wish to track.


2. Configure Google Sheets Nodes: In both the 'Get row(s) in sheet' and 'Append row in sheet' Google Sheets n8n nodes, link to your target Google Spreadsheet document. Ensure your spreadsheet has the required header columns: Name, HourlyRate, TotalHours, TotalPay, and DateCalculated.


3. Customize Schedule: Adjust the 'Schedule Trigger' n8n node to your desired interval (e.g., set it to run every Monday).

Schedule Trigger (n8n trigger): Initiates the flow based on a set time interval (e.g., weekly).
List tasks (Dart n8n node): Connects to the Dart task management platform to retrieve all relevant tasks using a specific dartboard_id (e.g., yccHfZwasnhc).
Get row(s) in sheet (Google Sheets n8n node): Reads the entire contents of the designated spreadsheet (Rate Card) to provide the AI with necessary billing rates.
Aggregate (n8n node): Consolidates multiple input items from the Google Sheet into a single input item for efficient processing by the AI Agent.
Assignee & Time Scanner (Agent n8n node): This is the core logical component. It uses a lengthy, prescriptive prompt to analyze Dart logs and Google Sheet data, filter for completed tasks, calculate total hours, and output a structured JSON payroll report. It is connected to the Gemini model via the 'AI SCANNER' language model n8n node.
Parse Output (Code n8n node): Essential post-processing step that uses custom JavaScript to clean the raw AI output (removing surrounding markdown tags like ```json) and securely parse it into an array of usable JSON objects.

• Append row in sheet (Google Sheets n8n node): Final action in this n8n workflow. It takes the parsed payroll data and appends it to the Google Sheet, documenting the calculation.