Integration and API Spec

Integration and API Spec

Budget vs Actuals Reporting

Provides the closed-period Profit and Loss report via the Reports API. The Actuals Sync Agent calls this endpoint on schedule each month-end, parses account-level rows, and passes figures to the Google Sheets write step. The connection uses OAuth 2.0 with offline access so the token can be refreshed without user interaction.

// Outbound request
GET /v3/company/{QB_REALM_ID}/reports/ProfitAndLoss
  Headers: Authorization: Bearer {QB_ACCESS_TOKEN}
           Accept: application/json
  Params:  start_date, end_date, summarize_column_by=Account, accounting_method

// Response shape (simplified)
{
  'Header': { 'ReportName': 'ProfitAndLoss', 'StartPeriod': '...', 'EndPeriod': '...' },
  'Rows': {
    'Row': [
      { 'type': 'Section', 'group': 'Income', 'Rows': { 'Row': [ ... ] } },
      { 'type': 'Section', 'group': 'Expenses', 'Rows': { 'Row': [ ... ] } }
    ]
  }
}

The budget template lives in a designated Google Sheet. The Actuals Sync Agent writes fetched actuals into the sheet, the Variance Analysis Agent reads from it to compute variances and pre-populate commentary, and the Report Distribution Agent reads the finalised sheet to package the report. Access is via a service account so no user-level OAuth prompt is required during automated runs.

// Read actuals range
GET https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_BUDGET_SPREADSHEET_ID}/values/{SHEETS_ACTUALS_RANGE}

// Batch write actuals
PUT https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_BUDGET_SPREADSHEET_ID}/values/{SHEETS_ACTUALS_RANGE}
  Body: { 'range': SHEETS_ACTUALS_RANGE, 'values': [[v1],[v2],...], 'majorDimension': 'COLUMNS' }
  Params: valueInputOption=USER_ENTERED

// Write commentary
PUT https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_BUDGET_SPREADSHEET_ID}/values/{SHEETS_COMMENTARY_RANGE}
  Body: { 'range': SHEETS_COMMENTARY_RANGE, 'values': [[c1],[c2],...] }

// Poll approval cell
GET https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_BUDGET_SPREADSHEET_ID}/values/{SHEETS_APPROVAL_CELL_REF}

The Report Distribution Agent saves the finalised report to a designated Google Drive folder with a date-stamped filename and applies sharing permissions so all configured stakeholders can access it via link. The same service account used for Google Sheets handles Drive operations.

// Export sheet as PDF and upload to Drive folder
GET https://docs.google.com/spreadsheets/d/{SHEETS_BUDGET_SPREADSHEET_ID}/export
  Params: format=pdf&gid={SHEET_GID}&portrait=true&fitw=true
  -> binary PDF content

POST https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart
  Metadata: { 'name': '{DRIVE_FILE_NAME_TEMPLATE}', 'parents': ['{DRIVE_REPORT_FOLDER_ID}'] }
  Body: multipart/related with metadata + PDF binary
  -> returns { 'id': '{new_file_id}' }

// Set sharing permissions
POST https://www.googleapis.com/drive/v3/files/{new_file_id}/permissions
  Body: { 'type': 'anyone', 'role': 'reader' }

Slack is used at two points: the Variance Analysis Agent posts targeted alerts to department heads requesting variance explanations, and the Report Distribution Agent posts a final notification with the Drive link to the stakeholder channel. Both use a single Slack bot app with a bot token.

// Post variance alert DM to department head
POST https://slack.com/api/chat.postMessage
  Headers: Authorization: Bearer {SLACK_BOT_TOKEN}
  Body: {
    'channel': '{dept_head_user_id}',
    'text': 'Variance alert for {cost_centre}: {line_name} is {variance_pct}% over budget.',
    'blocks': [ ... Block Kit layout ... ]
  }

// Post distribution notification to finance channel
POST https://slack.com/api/chat.postMessage
  Body: {
    'channel': '{SLACK_FINANCE_CHANNEL_ID}',
    'text': 'Budget vs Actuals report for {period} is ready: {drive_file_link}',
    'blocks': [ ... summary block with link ... ]
  }

// QuickBooks Online
QB_CLIENT_ID                  = '<QuickBooks app client ID>'
QB_CLIENT_SECRET              = '<QuickBooks app client secret>'
QB_ACCESS_TOKEN               = '<OAuth access token, auto-refreshed>'
QB_REFRESH_TOKEN              = '<OAuth refresh token, 100-day expiry>'
QB_REALM_ID                   = '<QuickBooks company Realm ID>'
QB_REPORT_BASIS               = 'Accrual'   // or 'Cash'; confirm with finance manager
QB_TOKEN_LAST_REFRESHED       = '<ISO8601 timestamp>'
QB_WEBHOOK_VERIFIER           = '<Intuit webhook verifier token, if webhooks used>'

// Google (Sheets + Drive, shared service account)
GOOGLE_SA_KEY                 = '<Full JSON key for Google service account>'
SHEETS_BUDGET_SPREADSHEET_ID  = '<Google Sheet ID from URL>'
SHEETS_ACTUALS_RANGE          = 'Actuals!B2:B55'   // adjust to match actual sheet
SHEETS_BUDGET_RANGE           = 'Budget!B2:B55'
SHEETS_COMMENTARY_RANGE       = 'Commentary!C2:C55'
SHEETS_APPROVAL_CELL_REF      = 'Approval!B1'
SHEETS_MAPPING_TAB            = 'AccountMapping'
SHEETS_PERIOD_HEADER_CELL     = 'Summary!C1'
SHEETS_PERIOD_HEADER_CELL_END = 'Summary!D1'
SHEETS_CURRENCY_CELL          = 'Summary!A3'
VARIANCE_THRESHOLD_PCT        = 10   // percent; flagged if ABS(variance) >= this

// Google Drive
DRIVE_REPORT_FOLDER_ID        = '<Google Drive folder ID>'
DRIVE_FILE_NAME_TEMPLATE      = 'BudgetVsActuals_{YYYY}-{MM}_v{version}.pdf'
DRIVE_STAKEHOLDER_EMAILS      = 'ceo@example.com,ops@example.com'  // comma-separated

// Slack
SLACK_BOT_TOKEN               = 'xoxb-<bot token>'
SLACK_SIGNING_SECRET          = '<Slack app signing secret>'
SLACK_FINANCE_CHANNEL_ID      = 'C0123456789'   // #finance channel ID
SLACK_DEPT_HEAD_MAP           = '{"OPEX":"U0123","MKTG":"U0456"}'  // JSON
SLACK_REPLY_WINDOW_HOURS      = 24

// Orchestration config
TRIGGER_PERIOD_CLOSE_DAY      = 1   // day of month; adjust if close day varies
APPROVAL_POLL_INTERVAL_MINS   = 15
APPROVAL_TIMEOUT_HOURS        = 48
ALERT_ESCALATION_EMAIL        = 'support@gofullspec.com'   // fallback alert target