Integration and API Spec

Integration and API Spec

Timesheet Collection and Approval

Provides the standardised timesheet input form that employees complete each pay period. The Collection Agent reads new form responses via the Google Forms API and also listens for new response events via a Google Apps Script webhook or Forms trigger. All responses are written to the linked Google Sheet response tab automatically by Forms.

// Inbound webhook payload from Apps Script trigger
{
  "responseId": "string",
  "employeeEmail": "string",
  "submittedAt": "ISO8601 timestamp",
  "formId": "string"
}

// Collection Agent then calls Forms API to retrieve full response body
GET https://forms.googleapis.com/v1/forms/{GOOGLE_FORMS_FORM_ID}/responses/{responseId}

Acts as the shared state layer across all three agents. The Collection Agent reads the employee list and writes validated hours. The Approval Agent reads manager assignments and writes approval timestamps. The Payroll Sync Agent reads finalised rows before pushing to Xero. All agents use the Google Sheets API v4.

// Read: employee roster (Collection Agent, cycle start)
GET spreadsheets/{GOOGLE_SHEETS_MASTER_SHEET_ID}/values/{SHEETS_EMPLOYEES_TAB_NAME}!A:E

// Write: validated hours row (Collection Agent, per employee)
PUT spreadsheets/{GOOGLE_SHEETS_MASTER_SHEET_ID}/values/{SHEETS_RESPONSES_TAB_NAME}!A{row}:H{row}

// Write: approval timestamp (Approval Agent, per manager response)
PUT spreadsheets/{GOOGLE_SHEETS_MASTER_SHEET_ID}/values/{SHEETS_APPROVALS_TAB_NAME}!F{row}:G{row}

// Read: sync-ready rows (Payroll Sync Agent)
GET spreadsheets/{GOOGLE_SHEETS_MASTER_SHEET_ID}/values/{SHEETS_SYNC_TAB_NAME}!A:J

Used by the Collection Agent to send personalised submission reminder emails to employees, and by the Approval Agent to send pre-formatted approval request emails to line managers. The Gmail API v1 is used to compose and send messages. Approval emails include a one-click token-based approve link.

// Send reminder email (Collection Agent)
POST https://gmail.googleapis.com/gmail/v1/users/me/messages/send
{
  "to": "employee@yourdomain.com",
  "subject": "Action required: Submit your timesheet for {{pay_period_label}}",
  "body": "<rendered GMAIL_REMINDER_TEMPLATE>"
}

// Send approval request email (Approval Agent)
POST https://gmail.googleapis.com/gmail/v1/users/me/messages/send
{
  "to": "manager@yourdomain.com",
  "subject": "Timesheet approval required: {{pay_period_label}}",
  "body": "<rendered GMAIL_APPROVAL_TEMPLATE with approve_url and query_url>"
}

Used by the Collection Agent to send chase messages to employees who have not submitted, and by the Payroll Sync Agent to send the payroll-ready confirmation to the payroll owner. Messages are posted via the Slack Web API using a dedicated Slack app installed to the workspace.

// Resolve Slack user ID from email (Collection Agent)
GET https://slack.com/api/users.lookupByEmail?email={{employee_email}}

// Send DM chase to employee (Collection Agent)
POST https://slack.com/api/chat.postMessage
{
  "channel": "{{slack_user_id}}",
  "blocks": "<SLACK_REMINDER_TEMPLATE Block Kit JSON>"
}

// Send payroll-ready confirmation (Payroll Sync Agent)
POST https://slack.com/api/chat.postMessage
{
  "channel": "SLACK_PAYROLL_OWNER_USER_ID",
  "blocks": "<SLACK_CONFIRMATION_TEMPLATE with employee_count and total_hours>"
}

The payroll data destination. The Payroll Sync Agent reads approved hours from the master Google Sheet and submits them to Xero via the Xero Payroll API. The integration targets the Xero Timesheets endpoint, which creates or updates timesheet entries for each employee in the current pay period.

// Refresh access token before sync
POST https://identity.xero.com/connect/token
{ grant_type: refresh_token, refresh_token: XERO_REFRESH_TOKEN, client_id: XERO_CLIENT_ID, client_secret: XERO_CLIENT_SECRET }

// Submit timesheet entry per employee
POST https://api.xero.com/payroll/2.0/timesheets   (AU example)
{
  "Timesheets": [{
    "EmployeeID": "{{xero_employee_id}}",
    "StartDate": "{{pay_period_start}}",
    "EndDate": "{{pay_period_end}}",
    "Status": "Draft",
    "TimesheetLines": [{
      "EarningsRateID": "{{XERO_PAY_ITEM_ORDINARY_ID}}",
      "NumberOfUnits": [0, 0, 0, 0, 0, 0, 0]
    }]
  }]
}

// Verify submission
GET https://api.xero.com/payroll/2.0/timesheets/{{TimesheetID}}

# Google OAuth (shared across Forms, Sheets, Gmail)
GOOGLE_OAUTH_CLIENT_ID          = "<from Google Cloud Console OAuth 2.0 app>"
GOOGLE_OAUTH_CLIENT_SECRET      = "<from Google Cloud Console OAuth 2.0 app>"
GOOGLE_OAUTH_REFRESH_TOKEN      = "<generated during OAuth authorisation flow>"

# Google Forms
GOOGLE_FORMS_FORM_ID            = "<Form ID from Forms URL>"
FORMS_APPS_SCRIPT_PROJECT_ID    = "<Apps Script project ID>"
FORMS_WEBHOOK_TARGET_URL        = "<orchestration inbound webhook URL for form submissions>"

# Google Sheets
GOOGLE_SHEETS_MASTER_SHEET_ID   = "<Spreadsheet ID from Sheets URL>"
SHEETS_EMPLOYEES_TAB_NAME       = "Employees"
SHEETS_RESPONSES_TAB_NAME       = "Responses"
SHEETS_APPROVALS_TAB_NAME       = "Approvals"
SHEETS_SYNC_TAB_NAME            = "SyncReady"
SHEETS_APPROVAL_WEBHOOK_TARGET_URL = "<orchestration inbound webhook URL for approval events>"

# Gmail
GMAIL_SENDER_ADDRESS            = "<hr-automation@yourdomain.com or Google account email>"
GMAIL_REMINDER_TEMPLATE         = "<template ID or inline HTML stored as secret>"
GMAIL_APPROVAL_TEMPLATE         = "<template ID or inline HTML stored as secret>"

# Slack
SLACK_BOT_TOKEN                 = "xoxb-<bot token from Slack app config>"
SLACK_PAYROLL_OWNER_USER_ID     = "U<Slack user ID of payroll owner>"
SLACK_HR_CHANNEL_ID             = "C<Slack channel ID for HR notifications>"
SLACK_REMINDER_TEMPLATE         = "<Block Kit JSON template>"
SLACK_CONFIRMATION_TEMPLATE     = "<Block Kit JSON template>"

# Xero
XERO_CLIENT_ID                  = "<from Xero developer app>"
XERO_CLIENT_SECRET              = "<from Xero developer app>"
XERO_TENANT_ID                  = "<Xero organisation tenant ID>"
XERO_ACCESS_TOKEN               = "<current short-lived access token; auto-refreshed>"
XERO_REFRESH_TOKEN              = "<long-lived refresh token; rotate on each use>"
XERO_PAYROLL_CALENDAR_ID        = "<Pay calendar ID from Xero Payroll Settings>"
XERO_PAYROLL_BASE_URL           = "https://api.xero.com/payroll/2.0"
XERO_PAY_ITEM_ORDINARY_ID       = "<EarningsRate GUID for ordinary hours in Xero>"
XERO_PAY_ITEM_OVERTIME_ID       = "<EarningsRate GUID for overtime hours in Xero>"

# Orchestration and security
WEBHOOK_SIGNING_SECRET          = "<HMAC-SHA256 shared secret for inbound webhook validation>"
APPROVAL_TOKEN_SECRET           = "<HMAC-SHA256 secret for signing manager approval tokens>"
APPROVAL_TOKEN_TTL_SECONDS      = "259200"
APPROVAL_TOKEN_BASE_URL         = "<orchestration base URL for manager approve/query endpoints>"
APPROVAL_ESCALATION_HOURS       = "24"
APPROVAL_FINAL_ESCALATION_HOURS = "48"
PAY_PERIOD_CLOSE_CRON           = "<cron expression for pay period close schedule>"