OpenAI

ChatGPT API Integration

The Jspreadsheet OpenAI extension is a full-stack plugin that integrates the ChatGPT API into your Jspreadsheet Pro data grids. It enables automated content generation, intelligent response handling, and advanced data analysis. This extension requires Jspreadsheet Server to securely route frontend queries through your backend API for real-time data delivery.

This extension runs on Jspreadsheet Server hosted on your infrastructure, ensuring your API keys and data remain secure.

Endless Possibilities

Generate Marketing Copy

Generate marketing copy for products using their attributes.

A1: "Product Name"
B1: "Product Description"
C1: "Marketing Copy"
A2: "Smartwatch"
B2: "Fitness tracking, sleep monitoring, 48-hour battery life"

In C2, you would enter:

=PROMPT("Write an engaging marketing copy for a product named ", A2, " with these features: ", B2)

Documentation

Methods

Method	Description
=PROMPT(...ARGUMENTS)	Creates dynamic prompts by combining values or text fragments to interact with AI models.

Installation

This extension requires Jspreadsheet Server and an OpenAI API account.

Using NPM

$ npm install @jspreadsheet/openai

Using a CDN

<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/openai/dist/index.min.js"></script>

Configuration

The OpenAI extension requires Jspreadsheet Server to perform secure requests to the ChatGPT API. When one user executes a formula, the server processes the request and broadcasts the response to all connected users. With caching enabled, results are saved to cells, ensuring API requests occur only when necessary.

Server Installation

Load the OpenAI extension and register it with Jspreadsheet Server.

Option	Description
`apiKey`	Your OpenAI API key.
`onbeforesend`	Modify request options before sending the API request. `onbeforesend(instance, requestOptions) => void`
`onsuccess`	Customize the return value. Return false to cancel the cell update. `onsuccess(value: String, result: Object, x: Number, y: Number, worksheet: Object)` => string \| boolean \| undefined
`onerror`	Handle errors from the OpenAI API. `onerror(error: Error, x: Number, y: Number, worksheet: Object) => void`

requestOptions Default

Customize the API request options.

let requestOptions = {
    model: "gpt-4",
    messages: [
        {
            role: "system",
            content: "prompt passed from frontend spreadsheet",
        }
    ]
}

Example

const server = require('@jspreadsheet/server');
const openai = require('@jspreadsheet/openai');

// Connect to the Open AI API
openai({
    apiKey: 'sk-your-api-key',
    onbeforesend: function(instance, options) {
        // Customize the model or add parameters
        options.model = 'gpt-4o';
        options.temperature = 0.7;
        options.max_tokens = 500;
    },
    onsuccess: function(value, result, x, y, worksheet) {
        // Process or modify the AI response
        // result.choices[0].message.content contains the full API response
        console.log('AI response for cell', x, y, ':', value);
        return value;
    },
    onerror: function(error, x, y, worksheet) {
        // Handle API errors
        console.error('OpenAI error at cell', x, y, ':', error.message);
        // Optionally update the cell with an error message
        worksheet.setValue(x, y, '#ERROR');
    }
});

// You can get this information on your Jspreadsheet Account
const license = {
    clientId: 'your-client-id',
    licenseKey: 'your-license'
}

// Private server that runs on your servers (100% private)
server({
    config: {
        cors: {
            origin: "*"
        },
    },
    port: 3000,
    error: function(e) {
        // Your implementation
    },
    auth: async function(socket) {
        // Your implementation
    },
    load: async function(guid) {
        // Your implementation
    },
    create: async function(guid, config) {
        // Your implementation
    },
    destroy: async function(guid) {
        // Your implementation
    },
    change: async function(guid, changes) {
        // Your implementation
    },
    license: license,
    extensions: { openai },
});

Frontend

OpenAI API requests are routed through your server to ensure the security of your API key.

Example

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/client/dist/index.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/openai/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('NTdiZTE3ZGFlNmFhMDliNzQ4NTJmZWVjMWNhOGQxZmFhMzViZmUzNzdkMzNlYjQ4MzBlZDhhNzgyOGM2ZGMxZWU4OTQ4ZmNmZGI5YjQ1MDYzNWEyNjg3YzhjMWJiZDFiOWFlNDk3YzAzMDgyNmZhMjgzYTZmNzdlYzM4NzY3OGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZME1EWTNNak01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Load the extensions
jspreadsheet.setExtensions({ formula, client, openai });

// Connect
let remote = client.connect({
    url: 'https://yourdomain.com'
});

// Connect to a spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    guid: '53aa4c90-791d-4a65-84a6-1ac25d6b1118'
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import client from "@jspreadsheet/client";
import formula from "@jspreadsheet/formula-pro";
import openai from "@jspreadsheet/openai";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license
jspreadsheet.setLicense('NTdiZTE3ZGFlNmFhMDliNzQ4NTJmZWVjMWNhOGQxZmFhMzViZmUzNzdkMzNlYjQ4MzBlZDhhNzgyOGM2ZGMxZWU4OTQ4ZmNmZGI5YjQ1MDYzNWEyNjg3YzhjMWJiZDFiOWFlNDk3YzAzMDgyNmZhMjgzYTZmNzdlYzM4NzY3OGYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZME1EWTNNak01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Define the extensions
jspreadsheet.setExtensions({ formula, client, openai });

// Connect to your server
let remote = client.connect({
    url: 'https://yourdomain.com'
});

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Render component
    return (<Spreadsheet ref={spreadsheet} guid={"guid"} />);
}

More information

Technical Details

Caching

Results from the PROMPT() function are automatically cached in the cell. API requests only occur when:

The formula is first executed
The cell value is cleared and recalculated
Referenced cells change, triggering recalculation

Performance Considerations

Requests are processed asynchronously to avoid blocking the UI
Multiple users receive the same response when one user triggers a formula
API rate limits apply based on your OpenAI account tier
Consider using max_tokens in onbeforesend to control response length and costs

Error Handling

The extension handles common errors automatically:

Network failures
API rate limit exceeded
Invalid API keys
Timeout errors

Use the onerror hook for custom error handling or user notifications.

Usage Examples

Translating Column to French

Use the PROMPT function to translate an entire column.

jspreadsheet(HTMLElement, {
    worksheets: [
        {
            data: [
                ['Hello', '=PROMPT("Translate ",A1," to French")'],
                ['Bye', '=PROMPT("Translate ",A2," to French")'],
                ['Thanks', '=PROMPT("Translate ",A3," to French")'],
                ['Sorry', '=PROMPT("Translate ",A4," to French")'],
            ]
        }
    ]
})

Combining Word Semantics

Use the PROMPT function to combine word semantics.

jspreadsheet(HTMLElement, {
    worksheets: [
        {
            data: [
                ['Flower', 'Bee', `=PROMPT(A1," and ",B1," combined result in this word:")`],
            ]
        }
    ]
})

Data Enrichment and Analysis

Analyze customer feedback and extract sentiment, key issues, and priority scores.

jspreadsheet(HTMLElement, {
    worksheets: [
        {
            columns: [
                { title: 'Customer', width: 120 },
                { title: 'Feedback', width: 300 },
                { title: 'Sentiment', width: 100 },
                { title: 'Key Issues', width: 200 },
                { title: 'Priority', width: 80 },
            ],
            data: [
                [
                    'John Doe',
                    'The product is good but shipping took way too long and customer service was unhelpful',
                    '=PROMPT("Analyze sentiment (Positive/Negative/Neutral): ", B1)',
                    '=PROMPT("Extract key issues as comma-separated list: ", B1)',
                    '=PROMPT("Rate priority 1-5 based on urgency: ", B1)',
                ],
                [
                    'Jane Smith',
                    'Absolutely love the quality! Fast delivery and excellent packaging',
                    '=PROMPT("Analyze sentiment (Positive/Negative/Neutral): ", B2)',
                    '=PROMPT("Extract key issues as comma-separated list: ", B2)',
                    '=PROMPT("Rate priority 1-5 based on urgency: ", B2)',
                ],
            ]
        }
    ]
})

Edit this page on GitHub