AI Integration

AI Integration

WebPivotTable includes a built-in AI assistant that lets users interact with pivot data through natural language. Users can configure pivots, generate charts, explain cell values, and detect anomalies — all by typing plain English (or any of 19 supported languages) into the chat panel.

This is the first pivot table component to ship native AI integration with no plugins or add-ons required.

Overview

The AI system has four layers:

Layer Purpose
AI-Ready API Options, methods, and events that connect AI to the pivot engine
AI Service Provider abstraction for OpenAI, Anthropic, Google Gemini, and custom endpoints
Chat Panel UI Slide-out chat interface with message history, typing indicators, and quick actions
Insights Engine Automatic anomaly detection with statistical analysis

Enabling AI

AI is enabled by default. You can control it through the ai options:

<web-pivot-table id="wpt"></web-pivot-table>

<script>
const wpt = document.getElementById('wpt');
wpt.setOptions({
  ai: {
    enabled: true,        // Toggle AI features on/off
    provider: 'openai',   // 'openai' | 'anthropic' | 'google' | 'custom'
    apiKey: 'sk-...',     // Your API key (or 'proxy' for server-side proxy)
    model: 'gpt-4o',     // Model name for the chosen provider
    endpoint: '',         // Custom endpoint URL (required when provider is 'custom')
    maxTokens: 2048       // Maximum response tokens
  }
});
</script>

AI Providers

OpenAI

wpt.setOptions({
  ai: {
    provider: 'openai',
    apiKey: 'sk-...',
    model: 'gpt-4o'  // or gpt-4o-mini, gpt-4-turbo, etc.
  }
});

Anthropic Claude

wpt.setOptions({
  ai: {
    provider: 'anthropic',
    apiKey: 'sk-ant-...',
    model: 'claude-sonnet-4-20250514'
  }
});

Google Gemini

wpt.setOptions({
  ai: {
    provider: 'google',
    apiKey: 'AIza...',
    model: 'gemini-2.0-flash'
  }
});

Custom Endpoint (Proxy)

Use a custom endpoint to keep API keys on your server:

wpt.setOptions({
  ai: {
    provider: 'custom',
    endpoint: 'https://your-server.com/api/ai',
    apiKey: 'proxy'   // Any value; your server handles auth
  }
});

The custom endpoint receives POST requests with:

{
  "messages": [
    { "role": "system", "content": "..." },
    { "role": "user", "content": "..." }
  ],
  "max_tokens": 2048
}

And should return:

{
  "content": "AI response text here"
}

Server-Side Proxy

For production deployments, we recommend a server-side proxy to avoid exposing API keys in the browser. WebPivotTable.com provides a reference proxy implementation at /api/ai that you can deploy on your own server.

The proxy:

  • Keeps your API key in environment variables (never sent to the browser)
  • Adds rate limiting (configurable requests per minute per IP)
  • Validates request size (configurable character limit)
  • Supports OpenAI, Anthropic, and Google as backend providers

Environment Variables

Variable Description Default
AI_API_KEY API key for the backend AI provider (required)
AI_PROVIDER Backend provider: openai, anthropic, or google openai
AI_MODEL Model name gpt-4o
AI_MAX_TOKENS Maximum response tokens 2048

What Users Can Do

Chat Panel

Click the 💬 button in the toolbar to open the AI chat panel. Users can type natural language requests:

  • "Show revenue by region as a bar chart" — AI sets rows, columns, values, and chart type
  • "Add quarter to columns and switch to a line chart" — AI modifies the current configuration
  • "What are the top 5 products by profit margin?" — AI analyzes the data and responds with insights
  • "Find anomalies in this data" — AI scans for statistical outliers and explains each one

Cell Explanation

Right-click any cell in the pivot grid and select "Explain this cell with AI". The AI receives the cell value plus all neighboring row and column data, then provides a plain-language explanation of:

  • How the value was calculated
  • How it compares to neighbors
  • Whether it appears unusual

Anomaly Detection

The AI can automatically detect outliers in your pivot data. It calculates statistical measures (mean, standard deviation) across the visible data and flags values that deviate significantly. Each anomaly includes:

  • The cell location (row and column headers)
  • The actual value and how far it deviates from the mean
  • A plain-language explanation of why it might be unusual

Quick Actions

The chat panel includes one-click quick action buttons:

  • Summarize — Get a high-level overview of the current pivot data
  • Suggest — Receive recommendations for alternative pivot configurations
  • Anomalies — Run anomaly detection on the current view

AI Response Modes

The AI produces three types of responses:

  1. JSON action — Directly configures the pivot (sets rows, columns, values, chart type, filters)
  2. Text explanation — Provides analysis, insights, or explanations
  3. Combined — Returns both a configuration change and an explanation

When the AI returns a JSON action, WebPivotTable automatically applies the configuration to the pivot table. The user sees the pivot update in real-time along with the AI's explanation.

Supported Configuration Actions

The AI can configure these pivot properties via JSON:

{
  "action": "configurePivot",
  "config": {
    "rows": ["Region", "Product"],
    "cols": ["Quarter"],
    "vals": [
      { "field": "Revenue", "aggregation": "sum" },
      { "field": "Profit", "aggregation": "avg" }
    ],
    "chart": {
      "type": "bar",
      "stacking": "normal"
    },
    "view": "chart"
  }
}

Available Chart Types

column, bar, line, area, pie, scatter, bubble, waterfall, funnel, pyramid, columnrange, arearange, heatmap

Stacking Modes

none, normal, percent

View Modes

grid, chart, both

Multilingual AI

The AI automatically responds in the same language as the current WPT locale. When the locale is set to zh (Chinese), the AI responds in Chinese. When set to ja (Japanese), it responds in Japanese. 19 languages are supported:

English, Chinese (Simplified), Chinese (Traditional), Japanese, Korean, French, German, Spanish, Portuguese, Italian, Dutch, Russian, Arabic, Turkish, Polish, Thai, Vietnamese, Indonesian, Ukrainian

Programmatic AI Control

Methods

// Send a message to the AI programmatically
wpt.aiChat('Show revenue by region');

// Run anomaly detection
wpt.aiDetectAnomalies();

// Clear AI chat history
wpt.aiClearHistory();

Events

wpt.addEventListener('ai-response', (e) => {
  console.log('AI said:', e.detail.message);
  console.log('Config applied:', e.detail.config);
});

wpt.addEventListener('ai-error', (e) => {
  console.error('AI error:', e.detail.error);
});

Comparison with Competitors

Feature WebPivotTable Flexmonster PivotTable.js
Built-in AI Chat ✅ Native ❌ None ❌ None
Natural Language Pivot Config ✅ ❌ ❌
AI Cell Explanation ✅ ❌ ❌
Anomaly Detection ✅ ❌ ❌
Multi-Provider AI ✅ 4 providers ❌ ❌
Multilingual AI ✅ 19 languages ❌ ❌
Server-Side Proxy ✅ Reference impl ❌ ❌
Web Component ✅ ❌ jQuery-based ❌

Security Considerations

  • Never expose API keys in client-side code for production. Use the server-side proxy pattern.
  • The AI only sees aggregated pivot data, never the raw source dataset.
  • Rate limiting and request size validation are built into the proxy.
  • All AI communication happens over HTTPS.

Getting Started

  1. Load your dataset into WebPivotTable
  2. Click the 💬 button in the toolbar
  3. Type a question about your data
  4. Watch the pivot table reconfigure automatically

For server-side proxy setup, see the proxy configuration section above.