Integration and API Spec

Integration and API Spec

Client Reporting Automation

Serves two roles: (1) master schedule configuration table listing each client account, reporting frequency, and report template variant; (2) per-client staging tabs where the Report Data Collector writes aggregated metrics before Looker Studio refreshes.

// Read (Agent 1 trigger poll)
GET /v4/spreadsheets/{GSHEETS_SCHEDULE_SPREADSHEET_ID}/values/Schedule!A2:H
// Returns rows where NEXT_REPORT_DATE == today

// Write (Agent 1 metrics output)
PUT /v4/spreadsheets/{GSHEETS_SCHEDULE_SPREADSHEET_ID}/values/{CLIENT_ID}_staging!A1
// Body: { range, majorDimension: 'ROWS', values: [[metric_name, value, period, timestamp]] }

Two distinct integration roles: Agent 1 queries HubSpot as a read-only data source to pull account activity, deal stage, and contact metrics for the reporting period. Agent 2 writes a delivery activity record to the client contact record after the report is sent.

// Read: fetch company properties (Agent 1)
GET /crm/v3/objects/companies/{HUBSPOT_COMPANY_ID}?properties={HUBSPOT_DEAL_STAGE_PROP},{HUBSPOT_ACTIVITY_COUNT_PROP}
// Read: fetch contact email for delivery (Agent 1)
GET /crm/v3/objects/contacts?filterGroups=[{filters:[{propertyName:'associatedcompanyid',operator:'EQ',value:'{HUBSPOT_COMPANY_ID}'}]}]&properties={HUBSPOT_CONTACT_EMAIL_PROP},{HUBSPOT_CONTACT_NAME_PROP}

// Write: log delivery activity (Agent 2)
POST /crm/v3/objects/notes
// Body: { properties: { hs_note_body: '{HUBSPOT_ACTIVITY_TEMPLATE}', hs_timestamp: '{ISO8601_TIMESTAMP}' } }
// Associate note to company:
PUT /crm/v3/objects/notes/{note_id}/associations/companies/{HUBSPOT_COMPANY_ID}/note_to_company

Hosts the branded report template connected to the Google Sheets staging tab as its data source. When the staging tab is updated by Agent 1, Looker Studio refreshes automatically on its next connector poll (default 15 minutes). PDF export is triggered by Agent 2 using a Google Apps Script bound to the report, invoked via the Apps Script API, or a third-party connector (e.g. a headless browser export service).

// Invoke Apps Script export function (Agent 2)
POST https://script.googleapis.com/v1/scripts/{GAPPS_SCRIPT_PROJECT_ID}:run
// Body: { function: '{LOOKER_EXPORT_FUNCTION_NAME}', parameters: ['{CLIENT_ID}', '{PERIOD_YYYYMM}', '{REPORT_TEMPLATE_ID}'] }
// Returns: { done: true, response: { result: { drive_file_id: '...', pdf_url: '...' } } }

Archive destination for all exported PDF reports. Reports are saved to a per-client subfolder within a root reports folder. The Drive file ID returned by the export step is passed to both the Gmail send step (as attachment) and the HubSpot activity log step (as a shareable link).

// Confirm file exists after export (Agent 2)
GET /drive/v3/files/{drive_file_id}?fields=id,name,size,webViewLink
// Upload fallback if Apps Script export writes locally first
POST /upload/drive/v3/files?uploadType=multipart
// Headers: Content-Type: multipart/related
// Body part 1: { name: '{CLIENT_ID}_{PERIOD_YYYYMM}_Report.pdf', parents: ['{GDRIVE_CLIENT_FOLDER_ID}'] }
// Body part 2: PDF binary stream

Sends the formatted client report email with the PDF attached. The email uses a pre-approved template with placeholder substitution for client name, reporting period, and AI-drafted commentary body. For accounts flagged as REVIEW_REQUIRED, the send step is gated behind a human approval step; the email is not dispatched until the reviewer confirms.

// Compose and send (Agent 2)
POST /gmail/v1/users/{GMAIL_SENDER_ADDRESS}/messages/send
// Body: { raw: base64url(MIME_MESSAGE) }
// MIME structure:
//   From: {GMAIL_SENDER_ADDRESS}
//   To: {recipient_email}
//   Subject: {resolved_subject}
//   Content-Type: multipart/mixed
//     Part 1: text/html  -- rendered GMAIL_BODY_TEMPLATE
//     Part 2: application/pdf; name='{CLIENT_ID}_{PERIOD_YYYYMM}_Report.pdf'
// Response: { id: '{gmail_message_id}', threadId: '...', labelIds: ['SENT'] }

Posts a structured delivery notice to the designated internal Slack channel after the Gmail send is confirmed. The message includes client name, reporting period, delivery timestamp, and a link to the archived Drive file. This step provides the account team with immediate visibility without requiring them to check HubSpot or Gmail.

// Post delivery notice (Agent 2)
POST https://slack.com/api/chat.postMessage
// Headers: Authorization: Bearer {SLACK_BOT_TOKEN}, Content-Type: application/json
// Body:
{
  channel: '{SLACK_DELIVERY_CHANNEL_ID}',
  blocks: [
    { type: 'section', text: { type: 'mrkdwn', text: ':white_check_mark: *Report sent:* {{client_name}} | {{period}}' } },
    { type: 'section', fields: [
      { type: 'mrkdwn', text: '*Sent at:* {{timestamp}}' },
      { type: 'mrkdwn', text: '*Archive:* <{{drive_link}}|View PDF>' }
    ]}
  ]
}

// CREDENTIAL STORE CONTENTS
// All values are environment variables or encrypted secrets.
// Never commit these values to version control.

GSHEETS_SCHEDULE_SPREADSHEET_ID   = '<google_sheets_file_id>'
GSHEETS_SERVICE_ACCOUNT_KEY_JSON  = '<path_to_service_account_json_or_inline_secret>'
GSHEETS_OAUTH_REFRESH_TOKEN       = '<refresh_token_for_user_oauth_if_not_service_account>'

HUBSPOT_PRIVATE_APP_TOKEN         = '<hubspot_private_app_token>'
HUBSPOT_DEAL_STAGE_PROP           = '<confirmed_hs_property_name>'
HUBSPOT_ACTIVITY_COUNT_PROP       = '<confirmed_hs_property_name>'
HUBSPOT_CONTACT_EMAIL_PROP        = '<confirmed_hs_property_name>'
HUBSPOT_CONTACT_NAME_PROP         = '<confirmed_hs_property_name>'
HUBSPOT_ACTIVITY_TEMPLATE         = 'Report sent for period {period}. Archive: {drive_link}'

GAPPS_SCRIPT_PROJECT_ID           = '<apps_script_project_id>'
GAPPS_SCRIPT_OAUTH_TOKEN          = '<oauth_token_for_apps_script_api>'
LOOKER_EXPORT_FUNCTION_NAME       = 'exportReportToDrive'

GDRIVE_REPORTS_FOLDER_ID          = '<root_reports_folder_id_in_drive>'
GDRIVE_OAUTH_REFRESH_TOKEN        = '<shared_with_sheets_if_same_grant>'

GMAIL_OAUTH_REFRESH_TOKEN         = '<refresh_token_for_sending_account>'
GMAIL_SENDER_ADDRESS              = '<sending_email_address>'
GMAIL_SUBJECT_TEMPLATE            = '{CLIENT_NAME} | Monthly Report | {PERIOD_MONTHYEAR}'
GMAIL_BODY_TEMPLATE               = '<path_to_html_template_or_inline_string>'

SLACK_BOT_TOKEN                   = '<xoxb-slack-bot-token>'
SLACK_DELIVERY_CHANNEL_ID         = '<slack_channel_id>'
SLACK_MESSAGE_TEMPLATE            = '<block_kit_json_template_string>'
// SLACK_WEBHOOK_URL              = '<incoming_webhook_url_if_using_webhook_method>'
// Note: use either SLACK_BOT_TOKEN or SLACK_WEBHOOK_URL, not both.

// Rotation policy:
// HUBSPOT_PRIVATE_APP_TOKEN      - rotate every 12 months or on personnel change
// GMAIL_OAUTH_REFRESH_TOKEN      - reissue if sending account password changes
// SLACK_BOT_TOKEN                - rotate if Slack App is reinstalled or token revoked
// GSHEETS_SERVICE_ACCOUNT_KEY_JSON - rotate every 12 months