Integrating Excel Calculations into Quickbase with SpreadsheetWeb API

by | Oct 3, 2024

Quickbase is a versatile low-code platform that helps organizations manage custom applications. Quickbase Pipelines, in particular, are powerful tools for automating workflows and integrating external systems. One such integration is with the SpreadsheetWeb API, which allows for integrating complex Excel calculations through the Quickbase User Interface (UI).

In this article, we will walk through creating a system that integrates Quickbase with SpreadsheetWeb API. Users will input values, call the API dynamically, and store the calculated results back into Quickbase. This system can be scaled or modified depending on the specific needs of your project.

By the end of this tutorial, you will have a working pipeline that automates data input, API calling, and data storage, all within Quickbase.

We will break down the process into the following steps:

  • Setting up your Quickbase table
  • Preparing input fields
  • Creating a pipeline in Quickbase
  • Making an API request
  • Updating Quickbase with API results
  • Testing the entire system

Let’s get started!

 

Transforming Your Excel File into an API with SpreadsheetWeb

Before getting into the technical details of integrating the SpreadsheetWeb API with Quickbase, it's essential to introduce the example Excel file that will be utilized. This file is crucial because it contains the inputs and outputs that the API will use. The example Excel file includes three individual input cells (originZIP, destinationZIP, weight) and one output field (cost), which generate results based on the input values using complex formulas, much like a typical Excel calculator.

To begin using the SpreadsheetWeb API, you first need to upload your Excel file to the SpreadsheetWeb Hub, where you will create an application. This process converts your spreadsheet into an API that can be accessed programmatically.

API Setup Overview

Even though this guide focuses on integrating with Quickbase, it's important to note that creating an API from your SpreadsheetWeb application provides key details necessary for configuring the API within Quickbase. These details include:

  • API Endpoint URL
  • Workspace ID
  • Application ID
  • Authentication settings (if applicable)

These pieces of information will be used to establish the connection between Quickbase and the SpreadsheetWeb API. For more detailed instructions on creating an API from a SpreadsheetWeb application, you can refer to the step-by-step guide in our documentation.

SpreadsheetWeb API Authentication Options

The SpreadsheetWeb API offers two options for your API calls:

  • Token-Based Authentication: The most secure option, involving sending a Bearer token in the request headers to authenticate and authorize API requests.
  • No Authentication: A simpler option often used during development or when authentication isn’t required. It’s helpful when testing internally or when security is less of a concern.

For simplicity, this guide will proceed with no authentication. This approach allows for a more straightforward setup within Quickbase without configuring authorization headers. However, for production applications requiring additional security, we strongly recommend enabling token-based authentication in SpreadsheetWeb. In such cases, you'll need to configure Quickbase to send the Bearer token in the Authorization header for API calls.

Using no authentication simplifies the initial setup, but for long-term use and production environments, token-based authentication is highly recommended to protect your API from unauthorized access.

 

Step 1: Setting Up Your Quickbase Table

First, you need to set up a Quickbase table where the inputs, API results, and calculated data will be stored.

  1. Log in to Quickbase: After logging in to your Quickbase account, create a new app or open an existing one for this integration.
  2. Navigate to Tables: In the app builder, navigate to the "Tables" section.
  3. Create a New Table:
    • Click on “New Table” to create a new table for the integration.
    • Name the table something like "Shipping Cost API Integration."
  4. Define Fields:
    • originZIP: Text field to store the origin ZIP code provided by the user.
    • destinationZIP: Text field to store the destination ZIP code.
    • weight: Number field to store the weight of the package.
    • cost: Number field to store the calculated shipping cost returned by the API.

Your table is now ready, with fields for both inputs and API results.

Step 2: Preparing Input Fields in the UI

Once the table is ready, you need to prepare the input fields in the Quickbase UI, which will allow users to input the ZIP codes and package weight directly.

  1. Open the Form Builder: Go to the app settings and open the form that corresponds to the table you just created.
  2. Add Input Fields: Ensure that the form view includes the fields originZIP, destinationZIP, and weight. These fields will capture the input data from the users.
  3. Add a Save Button:
    • Add a button labeled "Save" to the form. This button will trigger the pipeline to submit the data and call the API.

Step 3: Creating a Pipeline in Quickbase

Now, let's set up a pipeline to pass the inputs to the SpreadsheetWeb API, get the cost result, and store it in Quickbase.

  1. Go to Pipelines: Navigate to the Pipelines section in Quickbase.
  2. Create a New Pipeline:
    • Click on “Create New Pipeline.”
    • Select Quickbase as the connector, as we want to trigger the pipeline when a new record is created or updated in the Quickbase table.
  3. Set the Trigger:
    • Choose the event "Record Created" to trigger the pipeline when a new record is created in the table.
  4. Configure the Pipeline Steps:
    • In the first step, map the fields like originZIP, destinationZIP, and weight to the corresponding values entered by the user.

 

Step 4: Making the API Request

Next, configure the pipeline to make an API request to SpreadsheetWeb.

  1. In the Pipeline Designer: Add a step after the record creation to "Make Request." This will allow the pipeline to make an external HTTP request.
  2. Configure the Request:
    • Request Type: Set the request type to POST.

    • API Endpoint: Use the following endpoint for the SpreadsheetWeb API: https://api.spreadsheetweb.com/calculations/calculatesinglesimple.
    • Content Type: Set the content type to application/json.Set Up the Request Body: The body of the request will contain the input data from Quickbase in JSON format. Use the following structure:
{
  "request": {
    "workspaceId": "3bae377c-12fb-4b5e-90d2-a29aa12a6d79",
    "applicationId": "f8da5ff3-26dd-473c-9331-e43322e923ed",
    "inputs": {
      "originZIP": "{{a.originZIP}}",
      "destinationZIP": "{{a.destinationZIP}}",
      "weight": "{{a.weight}}"
    }
  }
}

In this request, the originZIP, destinationZIP, and weight fields are populated dynamically based on user input from Quickbase.

Set Up Authentication: If the API requires authentication (e.g., OAuth 2.0), provide the necessary credentials (client ID, secret, token URL) and add the authorization token in the header.

Step 5: Updating Quickbase with API Results

Once the API processes the inputs and returns the calculated cost, update the Quickbase table with the result.

  1. Add an Update Record Step:
    • After the API call step, add an action to "Update Record."
    • Select the same Quickbase table (e.g., "Shipping Cost API Integration").
  2. Map the API Response to Fields:
    • Map the cost field in Quickbase to the API response. For example:
      • cost = {{b.response.outputs.cost}}

Ensure that the response data is correctly mapped to the field where you want to store the cost.

Step 6: Testing the System

Now, it’s time to test the integration.

  1. Input Data: Open the form and input values for originZIP, destinationZIP, and weight.
  2. Save and Trigger the Pipeline: Click the "Save" button to trigger the pipeline, which will send the data to the SpreadsheetWeb API.
  3. Check the Results: Once the pipeline runs successfully, check the table to ensure the cost field has been updated with the value returned by the API.

Conclusion

By following this guide, you’ve created a dynamic system that integrates Quickbase with the SpreadsheetWeb API to calculate shipping costs based on ZIP codes and weight. This setup allows for seamless user input, dynamic API calls, and automated data storage in Quickbase. The system is scalable and can be further extended to handle more complex use cases.