Transform Your Excel Models into AI Applications with SpreadsheetWeb API: A Step-by-Step Guide

In this article, we'll demonstrate how you can quickly turn your Excel files with complex formulas into dynamic, custom HTML/CSS/JavaScript applications using the SpreadsheetWeb API and ChatGPT. SpreadsheetWeb provides robust backend calculation capabilities that seamlessly integrate with frontend code, allowing you to convert your Excel models into interactive web applications. ChatGPT further simplifies this process by generating frontend HTML, CSS, and JavaScript code based on clear, structured prompts. This powerful synergy between AI-generated frontend code and SpreadsheetWeb's backend calculation capabilities enables anyone, including non-developers, to create web-based applications quickly and efficiently.

1. Publishing the Excel File as an API on SpreadsheetWeb

a. Uploading and Publishing Your Excel File

First, log in to your SpreadsheetWeb account:

Click "Create Application".
Upload your Excel file containing your formulas.
Name your application and configure the calculation settings.
Save and publish your application to generate your API endpoint.

b. Using SpreadsheetWeb API Toolkit

SpreadsheetWeb's API Toolkit helps you map input fields and output results directly from your Excel file:

Select input and output cells from your spreadsheet.
Configure data types and validation rules.
Generate and copy request/response JSON samples, crucial for crafting your ChatGPT prompt.

2. Building the Prompt for ChatGPT

Creating a clear, structured prompt is essential. Include the following sections:

a. Brief Description of the Application

Clearly explain your app’s purpose (e.g., "a simple engineering calculator" or "a two-page sales quoting tool").

b. Samples of JSON Messages

Provide specific examples of request and response JSON messages copied directly from the SpreadsheetWeb API Toolkit. Ensure these samples accurately reflect the input parameters and expected results from your Excel file. Clear JSON examples help ChatGPT generate precise, functional frontend code.

c. Type of Controls

Clearly specify the types of HTML form controls you want ChatGPT to bind to each input/output field from your API. This includes dropdown menus, textboxes, checkboxes, radio buttons, sliders, and date pickers. Clearly define these controls, including labels, default values, and specific UI behaviors or validations to enhance user experience. For example:

Dropdown for product selection
Numeric textbox for quantities
Date picker for dates
Checkbox or toggle switch for boolean values

d. CMS Compatibility

Clearly indicate if your generated frontend code needs to integrate with a specific Content Management System (CMS) such as WordPress, Wix, Drupal, Joomla, or Shopify. Mention specific CMS considerations like embedding methods (shortcodes, iframe, direct HTML embedding), style or script conflicts, and responsive design requirements to ensure seamless integration. For example:

"Ensure compatibility with WordPress shortcode or Gutenberg blocks."
"Generate code compatible with Wix HTML embed tool."
"Code should use Drupal-friendly markup for easy integration."

3. Embedding the Code into Your Website and Testing

After receiving your code from ChatGPT:

Paste the generated HTML/CSS/JS code directly into your website or CMS page editor.
Run thorough tests, inputting sample data to verify calculations and frontend interactions.
If issues occur, refine your ChatGPT prompt to address specific challenges and regenerate improved code.

Examples

Example 1: Simple One-Page Engineering Calculator

This engineering calculator uses calculations built into the Excel file to determine the required pipe diameter based on factors such as allowable head loss, pipe length, and fluid density.

ChatGPT Prompt:

I would like to build a single-page application using only HTML, CSS, and JavaScript. The application should allow users to enter a series of inputs, trigger a calculation by calling a SpreadsheetWeb Hub endpoint, and 
display a series of outputs.
Target API Endpoint:
https://private.spreadsheetweb.com/api/calculations/calculatesinglesimple

Styling:
Use Bootstrap for styling the page.

API Structure
Sample API Request:
{
  "request": {
    "workspaceId": "YOUR-WORKSPACEID",
    "applicationId": "YOUR-APPLICATIONID",
    "inputs": {
      "Allowable_Head_Loss": "5",
      "Assumed_Pipe_Diameter": "4",
      "Fluid_Density": "2",
      "Fluid_Viscosity": "0.000025",
      "Pipe_Flow_Rate": "0.75",
      "Pipe_Lenght": "50",
      "Pipe_Roughness": "0.0004"
    },
    "outputs": [
      "Pipe_Diameter"
    ]
  }
}
Sample API Response:
{
  "response": {
    "outputs": {
      "Pipe_Diameter": "2.98056249684458"
    }
  },
  "timingsSeconds": {
    "hubApiApplication": 0.1265283,
    "designerApplication": 0.1809439
  },
  "performanceInformation": null,
  "isError": false,
  "messages": null
}
User Interface Specifications:
- Inputs should appear on the page as textboxes.
- Output must be displayed in the following format:
 - Pipe Diameter = D = "outputValue" in.
- Output field names should be read without case sensitivity.

Inputs should be pre-populated with the default values provided in the sample JSON request.

Buttons and Navigation:
- Include a CALCULATE button. When clicked:
 - Collect input values.
 - Send a request to the API endpoint.
 - Display the returned outputs.

WordPress Theme Compatibility Requirements
- Write the final HTML/JS code as a single line.
- Make case-insensitive handling of the outputs section using the 
logic: outs = json.outputs || (json.response && json.response.outputs)

Final ChatGPT-Generated Code:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1" />
  <title>Pipe Diameter Calculator</title>
  <link
    href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css"
    rel="stylesheet"
  />
</head>
<body>
  <div class="container py-5">
    <h1 class="mb-4">Pipe Diameter Calculator</h1>
    <div class="row g-3">
      <div class="col-md-6">
        <label for="Allowable_Head_Loss" class="form-label">
          Allowable Head Loss, hL (ft)
        </label>
        <input
          type="text"
          class="form-control"
          id="Allowable_Head_Loss"
          value="5"
        />
      </div>
      <div class="col-md-6">
        <label for="Assumed_Pipe_Diameter" class="form-label">
          Assumed Pipe Diameter, D* (in)
        </label>
        <input
          type="text"
          class="form-control"
          id="Assumed_Pipe_Diameter"
          value="4"
        />
      </div>
      <div class="col-md-6">
        <label for="Fluid_Density" class="form-label">
          Fluid Density, ρ (slugs/ft³)
        </label>
        <input
          type="text"
          class="form-control"
          id="Fluid_Density"
          value="2"
        />
      </div>
      <div class="col-md-6">
        <label for="Fluid_Viscosity" class="form-label">
          Fluid Viscosity, μ (lb·s/ft²)
        </label>
        <input
          type="text"
          class="form-control"
          id="Fluid_Viscosity"
          value="0.000025"
        />
      </div>
      <div class="col-md-6">
        <label for="Pipe_Flow_Rate" class="form-label">
          Pipe Flow Rate, Q (cfs)
        </label>
        <input
          type="text"
          class="form-control"
          id="Pipe_Flow_Rate"
          value="0.75"
        />
      </div>
      <div class="col-md-6">
        <label for="Pipe_Lenght" class="form-label">
          Pipe Length, L (ft)
        </label>
        <input
          type="text"
          class="form-control"
          id="Pipe_Lenght"
          value="50"
        />
      </div>
      <div class="col-md-6">
        <label for="Pipe_Roughness" class="form-label">
          Pipe Roughness, e (ft)
        </label>
        <input
          type="text"
          class="form-control"
          id="Pipe_Roughness"
          value="0.0004"
        />
      </div>
    </div>

    <button id="calculate-btn" class="btn btn-primary mt-4">CALCULATE</button>
    <p id="output-text" class="fs-4 mt-4"></p>
  </div>

  <script>
    const wsUrl =
      "https://private.spreadsheetweb.com/api/calculations/calculatesinglesimple";
    const wsIds = {
      workspaceId: "12d7c758-b138-426d-83a2-2e9cc73a5a4e",
      applicationId: "8138f403-9692-4176-8f3f-5e22df062b46",
    };

    document
      .getElementById("calculate-btn")
      .addEventListener("click", async () => {
        // 1) collect inputs
        const inputs = {
          Allowable_Head_Loss: document.getElementById(
            "Allowable_Head_Loss"
          ).value,
          Assumed_Pipe_Diameter: document.getElementById(
            "Assumed_Pipe_Diameter"
          ).value,
          Fluid_Density: document.getElementById("Fluid_Density").value,
          Fluid_Viscosity: document.getElementById("Fluid_Viscosity").value,
          Pipe_Flow_Rate: document.getElementById("Pipe_Flow_Rate").value,
          Pipe_Lenght: document.getElementById("Pipe_Lenght").value,
          Pipe_Roughness: document.getElementById("Pipe_Roughness").value,
        };

        // 2) build payload
        const payload = {
          request: {
            workspaceId: wsIds.workspaceId,
            applicationId: wsIds.applicationId,
            inputs: inputs,
            outputs: ["Pipe_Diameter"],
          },
        };

        try {
          // 3) call API
          const resp = await fetch(wsUrl, {
            method: "POST",
            headers: { "Content-Type": "application/json" },
            body: JSON.stringify(payload),
          });
          const json = await resp.json();

          // 4) case‐insensitive outputs handling
          const outs =
            json.outputs || (json.response && json.response.outputs);
          const key = Object.keys(outs).find(
            (k) => k.toLowerCase() === "pipe_diameter"
          );
          const val = outs[key];

          // 5) display result
          document.getElementById(
            "output-text"
          ).textContent = `Pipe_Diameter = "D = ${val} in."`;
        } catch (e) {
          console.error(e);
          alert("Error fetching calculation. See console for details.");
        }
      });
  </script>

  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Example 2: Two-Page Sales Quoting Application

This sales quoting tool is designed for an insurance company to calculate insurance premiums for an artisan contractors program. It takes various inputs such as company information, coverage details including deductibles, occurrence limits, and other related parameters, and generates comprehensive pricing details for the insurance coverage.