Openetl keyboard_arrow_right Blog jspreadsheet data integration

Loading Data from Multiple Sources into Jspreadsheet Pro with OpenETL

Build dynamic spreadsheets that pull data from databases, CRMs, and APIs

---

Introduction

Jspreadsheet Pro is a powerful JavaScript library for creating Excel-like spreadsheets in web applications. When combined with OpenETL, you can build dynamic spreadsheets that pull data from virtually any source: PostgreSQL, MySQL, MongoDB, HubSpot, Stripe, Xero, and more.

In this tutorial, we'll build a full-stack application that:

1. Fetches data from multiple sources using OpenETL
2. Transforms and normalizes the data
3. Displays it in an interactive Jspreadsheet Pro grid
4. Allows users to edit and sync changes back to the source

Architecture

┌─────────────────────────────────────────────────────────────────┐
│                         Frontend                                 │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │                   Jspreadsheet Pro                       │   │
│  │  ┌─────┬─────────┬──────────┬─────────┬───────────────┐ │   │
│  │  │ ID  │  Name   │  Email   │ Company │    Status     │ │   │
│  │  ├─────┼─────────┼──────────┼─────────┼───────────────┤ │   │
│  │  │ 1   │ John    │ [email protected] │ Acme    │ Active        │ │   │
│  │  │ 2   │ Jane    │ [email protected] │ Corp    │ Active        │ │   │
│  │  └─────┴─────────┴──────────┴─────────┴───────────────┘ │   │
│  └─────────────────────────────────────────────────────────┘   │
└───────────────────────────┬─────────────────────────────────────┘
                            │ REST API
┌───────────────────────────▼─────────────────────────────────────┐
│                         Backend                                  │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │                    OpenETL Orchestrator                  │   │
│  └──────────┬──────────────┬──────────────┬────────────────┘   │
│             │              │              │                      │
│      ┌──────▼──────┐ ┌─────▼─────┐ ┌─────▼─────┐               │
│      │ PostgreSQL  │ │  HubSpot  │ │   Xero    │               │
│      │   Adapter   │ │  Adapter  │ │  Adapter  │               │
│      └─────────────┘ └───────────┘ └───────────┘               │
└─────────────────────────────────────────────────────────────────┘

Backend Setup

Installation

mkdir spreadsheet-app
cd spreadsheet-app
npm init -y
npm install express cors openetl @openetl/postgresql @openetl/hubspot @openetl/xero
npm install -D typescript @types/express @types/node ts-node

Project Structure

spreadsheet-app/
├── src/
│   ├── server.ts
│   ├── routes/
│   │   └── data.ts
│   ├── services/
│   │   └── etl.ts
│   └── config/
│       └── vault.ts
├── public/
│   ├── index.html
│   └── app.js
├── package.json
└── tsconfig.json

Vault Configuration

// src/config/vault.ts
import { Vault } from 'openetl';

export const vault: Vault = {
  'postgres-db': {
    id: 'postgres-db',
    type: 'basic',
    credentials: {
      host: process.env.PG_HOST || 'localhost',
      port: process.env.PG_PORT || '5432',
      database: process.env.PG_DATABASE || 'myapp',
      username: process.env.PG_USER || 'postgres',
      password: process.env.PG_PASSWORD || '',
    },
  },
  'hubspot-api': {
    id: 'hubspot-api',
    type: 'oauth2',
    credentials: {
      client_id: process.env.HUBSPOT_CLIENT_ID || '',
      client_secret: process.env.HUBSPOT_CLIENT_SECRET || '',
      access_token: process.env.HUBSPOT_ACCESS_TOKEN || '',
      refresh_token: process.env.HUBSPOT_REFRESH_TOKEN || '',
    },
  },
  'xero-api': {
    id: 'xero-api',
    type: 'oauth2',
    credentials: {
      client_id: process.env.XERO_CLIENT_ID || '',
      client_secret: process.env.XERO_CLIENT_SECRET || '',
      access_token: process.env.XERO_ACCESS_TOKEN || '',
      refresh_token: process.env.XERO_REFRESH_TOKEN || '',
    },
  },
};

ETL Service

// src/services/etl.ts
import { Orchestrator, Pipeline, Vault } from 'openetl';
import { postgresql } from '@openetl/postgresql';
import { hubspot } from '@openetl/hubspot';
import { xero } from '@openetl/xero';

const adapters = { postgresql, hubspot, xero };

export class ETLService {
  private orchestrator;

  constructor(vault: Vault) {
    this.orchestrator = Orchestrator(vault, adapters);
  }

  /**
   * Fetch customers from PostgreSQL database
   */
  async getCustomers(limit = 100, offset = 0) {
    const pipeline: Pipeline = {
      id: `customers-${Date.now()}`,
      source: {
        id: 'pg-customers',
        adapter_id: 'postgresql',
        endpoint_id: 'table_query',
        credential_id: 'postgres-db',
        fields: ['id', 'name', 'email', 'company', 'status', 'created_at'],
        config: {
          schema: 'public',
          table: 'customers',
        },
        sort: [{ field: 'created_at', type: 'desc' }],
        pagination: {
          type: 'offset',
          itemsPerPage: limit,
        },
      },
    };

    const result = await this.orchestrator.runPipeline(pipeline);
    return result.data;
  }

  /**
   * Fetch contacts from HubSpot CRM
   */
  async getHubSpotContacts(limit = 100) {
    const pipeline: Pipeline = {
      id: `hubspot-contacts-${Date.now()}`,
      source: {
        id: 'hs-contacts',
        adapter_id: 'hubspot',
        endpoint_id: 'contacts',
        credential_id: 'hubspot-api',
        fields: ['email', 'firstname', 'lastname', 'company', 'phone', 'lifecyclestage'],
        pagination: {
          type: 'cursor',
          itemsPerPage: limit,
        },
        // Transform HubSpot data to flat structure
        transform: [
          {
            type: 'concat',
            options: {
              properties: ['firstname', 'lastname'],
              glue: ' ',
              to: 'full_name',
            },
          },
        ],
      },
    };

    const result = await this.orchestrator.runPipeline(pipeline);
    return result.data;
  }

  /**
   * Fetch invoices from Xero
   */
  async getXeroInvoices(limit = 100) {
    const pipeline: Pipeline = {
      id: `xero-invoices-${Date.now()}`,
      source: {
        id: 'xero-inv',
        adapter_id: 'xero',
        endpoint_id: 'invoices',
        credential_id: 'xero-api',
        fields: ['InvoiceID', 'InvoiceNumber', 'Contact', 'Total', 'Status', 'DueDate'],
        config: {
          organisationName: process.env.XERO_ORG_NAME || 'My Company',
        },
        pagination: {
          type: 'offset',
          itemsPerPage: limit,
        },
      },
    };

    const result = await this.orchestrator.runPipeline(pipeline);

    // Flatten nested Contact object
    return result.data.map((invoice: any) => ({
      id: invoice.InvoiceID,
      invoice_number: invoice.InvoiceNumber,
      contact_name: invoice.Contact?.Name || '',
      total: invoice.Total,
      status: invoice.Status,
      due_date: invoice.DueDate,
    }));
  }

  /**
   * Insert new customer into PostgreSQL
   */
  async createCustomer(data: any) {
    const pipeline: Pipeline = {
      id: `create-customer-${Date.now()}`,
      data: [data],
      target: {
        id: 'pg-insert',
        adapter_id: 'postgresql',
        endpoint_id: 'table_insert',
        credential_id: 'postgres-db',
        fields: ['name', 'email', 'company', 'status'],
        config: {
          schema: 'public',
          table: 'customers',
        },
      },
    };

    return await this.orchestrator.runPipeline(pipeline);
  }
}

API Routes

// src/routes/data.ts
import { Router, Request, Response } from 'express';
import { ETLService } from '../services/etl';
import { vault } from '../config/vault';

const router = Router();
const etlService = new ETLService(vault);

// Get customers from PostgreSQL
router.get('/customers', async (req: Request, res: Response) => {
  try {
    const limit = parseInt(req.query.limit as string) || 100;
    const offset = parseInt(req.query.offset as string) || 0;
    const data = await etlService.getCustomers(limit, offset);

    // Format for Jspreadsheet
    res.json({
      columns: [
        { type: 'hidden', name: 'id' },
        { type: 'text', title: 'Name', name: 'name', width: 150 },
        { type: 'text', title: 'Email', name: 'email', width: 200 },
        { type: 'text', title: 'Company', name: 'company', width: 150 },
        {
          type: 'dropdown',
          title: 'Status',
          name: 'status',
          width: 100,
          source: ['active', 'inactive', 'pending'],
        },
        { type: 'calendar', title: 'Created', name: 'created_at', width: 120 },
      ],
      data: data,
    });
  } catch (error: any) {
    res.status(500).json({ error: error.message });
  }
});

// Get contacts from HubSpot
router.get('/hubspot/contacts', async (req: Request, res: Response) => {
  try {
    const limit = parseInt(req.query.limit as string) || 100;
    const data = await etlService.getHubSpotContacts(limit);

    res.json({
      columns: [
        { type: 'text', title: 'Name', name: 'full_name', width: 150, readOnly: true },
        { type: 'text', title: 'Email', name: 'email', width: 200 },
        { type: 'text', title: 'Company', name: 'company', width: 150 },
        { type: 'text', title: 'Phone', name: 'phone', width: 120 },
        { type: 'text', title: 'Stage', name: 'lifecyclestage', width: 100 },
      ],
      data: data,
    });
  } catch (error: any) {
    res.status(500).json({ error: error.message });
  }
});

// Get invoices from Xero
router.get('/xero/invoices', async (req: Request, res: Response) => {
  try {
    const limit = parseInt(req.query.limit as string) || 100;
    const data = await etlService.getXeroInvoices(limit);

    res.json({
      columns: [
        { type: 'hidden', name: 'id' },
        { type: 'text', title: 'Invoice #', name: 'invoice_number', width: 100 },
        { type: 'text', title: 'Contact', name: 'contact_name', width: 150 },
        { type: 'numeric', title: 'Total', name: 'total', width: 100, mask: '$ #,##0.00' },
        {
          type: 'dropdown',
          title: 'Status',
          name: 'status',
          width: 100,
          source: ['DRAFT', 'SUBMITTED', 'AUTHORISED', 'PAID', 'VOIDED'],
        },
        { type: 'calendar', title: 'Due Date', name: 'due_date', width: 120 },
      ],
      data: data,
    });
  } catch (error: any) {
    res.status(500).json({ error: error.message });
  }
});

// Create new customer
router.post('/customers', async (req: Request, res: Response) => {
  try {
    await etlService.createCustomer(req.body);
    res.json({ success: true });
  } catch (error: any) {
    res.status(500).json({ error: error.message });
  }
});

export default router;

Express Server

// src/server.ts
import express from 'express';
import cors from 'cors';
import path from 'path';
import dataRoutes from './routes/data';

const app = express();
const PORT = process.env.PORT || 3000;

app.use(cors());
app.use(express.json());
app.use(express.static(path.join(__dirname, '../public')));

app.use('/api', dataRoutes);

app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

Frontend Implementation

HTML Structure

<!-- public/index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Data Dashboard - OpenETL + Jspreadsheet</title>

  <!-- Jspreadsheet Pro -->
  <script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
  <script src="https://jsuites.net/v5/jsuites.js"></script>
  <link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
  <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

  <style>
    * {
      margin: 0;
      padding: 0;
      box-sizing: border-box;
    }

    body {
      font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
      background: #f5f5f5;
      padding: 20px;
    }

    .container {
      max-width: 1400px;
      margin: 0 auto;
    }

    h1 {
      margin-bottom: 20px;
      color: #333;
    }

    .tabs {
      display: flex;
      gap: 10px;
      margin-bottom: 20px;
    }

    .tab {
      padding: 10px 20px;
      background: #fff;
      border: 1px solid #ddd;
      border-radius: 5px;
      cursor: pointer;
      transition: all 0.2s;
    }

    .tab:hover {
      background: #f0f0f0;
    }

    .tab.active {
      background: #007bff;
      color: white;
      border-color: #007bff;
    }

    .spreadsheet-container {
      background: white;
      border-radius: 8px;
      box-shadow: 0 2px 10px rgba(0,0,0,0.1);
      padding: 20px;
    }

    .toolbar {
      display: flex;
      gap: 10px;
      margin-bottom: 15px;
    }

    .btn {
      padding: 8px 16px;
      border: none;
      border-radius: 4px;
      cursor: pointer;
      font-size: 14px;
    }

    .btn-primary {
      background: #007bff;
      color: white;
    }

    .btn-success {
      background: #28a745;
      color: white;
    }

    .btn-secondary {
      background: #6c757d;
      color: white;
    }

    .loading {
      text-align: center;
      padding: 40px;
      color: #666;
    }

    #spreadsheet {
      min-height: 400px;
    }
  </style>
</head>
<body>
  <div class="container">
    <h1>Data Dashboard</h1>

    <div class="tabs">
      <button class="tab active" data-source="customers">Customers (PostgreSQL)</button>
      <button class="tab" data-source="hubspot">Contacts (HubSpot)</button>
      <button class="tab" data-source="xero">Invoices (Xero)</button>
    </div>

    <div class="spreadsheet-container">
      <div class="toolbar">
        <button class="btn btn-primary" id="refresh-btn">Refresh Data</button>
        <button class="btn btn-success" id="add-btn">Add Row</button>
        <button class="btn btn-secondary" id="export-btn">Export CSV</button>
      </div>

      <div id="spreadsheet">
        <div class="loading">Loading data...</div>
      </div>
    </div>
  </div>

  <script src="app.js"></script>
</body>
</html>

JavaScript Application

// public/app.js
let spreadsheet = null;
let currentSource = 'customers';

const endpoints = {
  customers: '/api/customers',
  hubspot: '/api/hubspot/contacts',
  xero: '/api/xero/invoices',
};

/**
 * Load data from API and render spreadsheet
 */
async function loadData(source) {
  const container = document.getElementById('spreadsheet');
  container.innerHTML = '<div class="loading">Loading data...</div>';

  try {
    const response = await fetch(endpoints[source]);
    const { columns, data } = await response.json();

    // Destroy existing spreadsheet
    if (spreadsheet) {
      jspreadsheet.destroy(container);
    }

    // Create new spreadsheet
    spreadsheet = jspreadsheet(container, {
      data: data,
      columns: columns,
      style: {
        A1: 'font-weight: bold;',
      },
      tableOverflow: true,
      tableWidth: '100%',
      tableHeight: '500px',
      allowInsertRow: source === 'customers',
      allowDeleteRow: source === 'customers',
      allowInsertColumn: false,
      allowDeleteColumn: false,
      columnSorting: true,
      search: true,
      pagination: 20,
      paginationOptions: [10, 20, 50, 100],

      // Event handlers
      onchange: function(instance, cell, x, y, value) {
        console.log('Cell changed:', { x, y, value });
        // Here you could implement auto-save
      },

      oninsertrow: function(instance, rowNumber, numRows, insertBefore) {
        console.log('Row inserted:', rowNumber);
      },

      ondeleterow: function(instance, rowNumber, numRows) {
        console.log('Row deleted:', rowNumber);
      },

      // Context menu customization
      contextMenu: function(instance, x, y, e, items) {
        // Add custom menu items
        items.push({
          title: 'View Details',
          onclick: function() {
            const data = instance.getRowData(y);
            alert(JSON.stringify(data, null, 2));
          }
        });
        return items;
      },
    });

    currentSource = source;
  } catch (error) {
    container.innerHTML = `<div class="loading" style="color: red;">
      Error loading data: ${error.message}
    </div>`;
  }
}

/**
 * Add a new row to the spreadsheet
 */
function addRow() {
  if (spreadsheet && currentSource === 'customers') {
    spreadsheet.insertRow();
  } else {
    alert('Adding rows is only available for the Customers table');
  }
}

/**
 * Export spreadsheet data to CSV
 */
function exportCSV() {
  if (spreadsheet) {
    spreadsheet.download();
  }
}

/**
 * Save changes back to the database
 */
async function saveChanges() {
  if (!spreadsheet || currentSource !== 'customers') {
    return;
  }

  const data = spreadsheet.getData();
  const headers = spreadsheet.getHeaders();

  // Convert to array of objects
  const records = data.map(row => {
    const record = {};
    headers.forEach((header, index) => {
      record[header.toLowerCase().replace(' ', '_')] = row[index];
    });
    return record;
  });

  // Filter new records (no ID)
  const newRecords = records.filter(r => !r.id);

  for (const record of newRecords) {
    try {
      await fetch('/api/customers', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(record),
      });
    } catch (error) {
      console.error('Failed to save record:', error);
    }
  }

  // Reload to get new IDs
  loadData(currentSource);
}

// Tab switching
document.querySelectorAll('.tab').forEach(tab => {
  tab.addEventListener('click', function() {
    document.querySelectorAll('.tab').forEach(t => t.classList.remove('active'));
    this.classList.add('active');
    loadData(this.dataset.source);
  });
});

// Toolbar buttons
document.getElementById('refresh-btn').addEventListener('click', () => loadData(currentSource));
document.getElementById('add-btn').addEventListener('click', addRow);
document.getElementById('export-btn').addEventListener('click', exportCSV);

// Initial load
loadData('customers');

Advanced Features

Real-Time Updates with WebSocket

// Add to server.ts
import { WebSocketServer } from 'ws';

const wss = new WebSocketServer({ server });

wss.on('connection', (ws) => {
  console.log('Client connected');

  // Send updates when data changes
  ws.on('message', async (message) => {
    const { action, source } = JSON.parse(message.toString());

    if (action === 'subscribe') {
      // Set up polling or database triggers
      setInterval(async () => {
        const data = await etlService.getCustomers(100, 0);
        ws.send(JSON.stringify({ type: 'update', source, data }));
      }, 30000); // Poll every 30 seconds
    }
  });
});

Cell Validation

// Add validation rules to columns
columns: [
  {
    type: 'text',
    title: 'Email',
    name: 'email',
    width: 200,
    // Validate email format
    validate: function(value) {
      const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
      return emailRegex.test(value);
    }
  },
  {
    type: 'numeric',
    title: 'Amount',
    name: 'amount',
    width: 100,
    // Validate positive numbers
    validate: function(value) {
      return !isNaN(value) && parseFloat(value) >= 0;
    }
  },
]

Conditional Formatting

// Add updateTable callback for conditional formatting
updateTable: function(instance, cell, x, y, source, value, id) {
  // Highlight overdue invoices
  if (currentSource === 'xero') {
    const statusCol = 4; // Status column index
    const dueDateCol = 5; // Due date column index

    if (x === statusCol && value !== 'PAID') {
      const dueDate = instance.getValueFromCoords(dueDateCol, y);
      if (new Date(dueDate) < new Date()) {
        cell.style.backgroundColor = '#ffcccc';
      }
    }
  }

  // Highlight inactive customers
  if (currentSource === 'customers' && x === 3 && value === 'inactive') {
    cell.style.color = '#999';
    cell.style.fontStyle = 'italic';
  }
}

Multi-Source Dashboard

// Load multiple sources simultaneously
async function loadDashboard() {
  const [customers, contacts, invoices] = await Promise.all([
    fetch('/api/customers').then(r => r.json()),
    fetch('/api/hubspot/contacts').then(r => r.json()),
    fetch('/api/xero/invoices').then(r => r.json()),
  ]);

  // Create multiple spreadsheet instances
  jspreadsheet(document.getElementById('customers-grid'), {
    data: customers.data,
    columns: customers.columns,
    tableHeight: '300px',
  });

  jspreadsheet(document.getElementById('contacts-grid'), {
    data: contacts.data,
    columns: contacts.columns,
    tableHeight: '300px',
  });

  jspreadsheet(document.getElementById('invoices-grid'), {
    data: invoices.data,
    columns: invoices.columns,
    tableHeight: '300px',
  });
}

Data Synchronization

Two-Way Sync Pattern

// src/services/sync.ts
export class SyncService {
  private etl: ETLService;

  constructor(vault: Vault) {
    this.etl = new ETLService(vault);
  }

  /**
   * Sync contacts from HubSpot to local database
   */
  async syncHubSpotToDatabase() {
    // Download from HubSpot
    const contacts = await this.etl.getHubSpotContacts(1000);

    // Upload to PostgreSQL
    const pipeline: Pipeline = {
      id: `sync-hubspot-${Date.now()}`,
      data: contacts.map(c => ({
        email: c.email,
        name: c.full_name,
        company: c.company,
        source: 'hubspot',
        synced_at: new Date().toISOString(),
      })),
      target: {
        id: 'pg-sync',
        adapter_id: 'postgresql',
        endpoint_id: 'table_insert',
        credential_id: 'postgres-db',
        fields: ['email', 'name', 'company', 'source', 'synced_at'],
        config: { schema: 'public', table: 'contacts_sync' },
      },
    };

    return await this.orchestrator.runPipeline(pipeline);
  }
}

Conclusion

Combining OpenETL with Jspreadsheet Pro creates a powerful platform for:

• Unified data access: Query any source through a single interface
• Interactive editing: Excel-like experience in the browser
• Real-time sync: Keep data up-to-date across systems
• Export capabilities: Download data as CSV, Excel, or JSON

This architecture scales from simple data viewers to full-featured business applications.

Resources

• Jspreadsheet Pro Documentation
• OpenETL GitHub
• Example Repository

---

Built with OpenETL and Jspreadsheet Pro