How to Implement Dynamic Excel Calculations in Retool Through SpreadsheetWeb API Integration

by | Aug 22, 2024

There are numerous platforms available for building applications and automating business processes without requiring any coding expertise. However, one of the significant limitations of these platforms is their ability to handle complex calculations, which are often already embedded in the Excel spreadsheets that many businesses rely on. SpreadsheetWeb addresses this challenge by enabling users to expose their Excel spreadsheets as APIs, allowing seamless integration with these no-code platforms. This approach eliminates the need for extensive reprogramming, making it possible to leverage the power of Excel's advanced calculations while automating and enhancing business processes through modern application platforms.

Retool is one of these no-code platforms.  This article will guide you through the process of integrating Retool with the SpreadsheetWeb API, enabling you to create a dynamic application that connects user inputs to a backend Excel file for complex financial calculations, with the results seamlessly displayed in Retool's user interface.The application will use user inputs to fetch a Bearer Token for authentication and then send requests to the SpreadsheetWeb API to perform financial calculations.

By the end of this guide, you’ll have a clear understanding of how to set up API calls, handle tokens, and display dynamic data in Retool. This tutorial is particularly valuable for developers and business analysts seeking to create powerful, calculation-intensive applications without requiring extensive coding experience.

 

Understanding the Basics of API Integration

Before diving into the implementation, it’s important to understand what an API is and why it’s essential in modern web applications. An API, or Application Programming Interface, is a set of rules that allows different software applications to communicate with each other. In our case, the SpreadsheetWeb API allows your Retool application to send requests and receive responses that can be used to perform various calculations.

 

Why Use Retool?

Retool is a powerful platform that allows you to build internal tools quickly. It offers a drag-and-drop interface that can be used to create complex applications without needing to write a lot of code. One of the key features of Retool is its ability to integrate with various APIs, making it an ideal choice for creating applications that require real-time data processing.

In this tutorial, we’ll focus on integrating Retool with the SpreadsheetWeb API. SpreadsheetWeb is a platform that allows you to turn Excel spreadsheets into web applications. By combining the power of Retool and SpreadsheetWeb, you can create a dynamic application that performs financial calculations based on user input.

 

Step 1: Setting Up Your Retool Environment

To get started, you’ll need a Retool account and access to the SpreadsheetWeb API. Once you have these, log in to your Retool dashboard. From here, you can start by creating a new Retool app. Retool provides a user-friendly interface where you can drag and drop components to build your application.

 

Step 2: Configuring the Token API Call

The first step in our implementation is to set up an API call in Retool to obtain a Bearer Token. This token is necessary for authenticating further requests to the SpreadsheetWeb API. Here’s how you can configure it:

  1. Create a New API Query: In Retool, create a new API query and name it token. This query will be responsible for fetching the Bearer Token.
  2. Set the API Endpoint: The endpoint used for this API call is https://identity.spreadsheetweb.com/connect/token. This is where the request will be sent to obtain the token.
  3. Choose the POST Method: Set the HTTP method to POST. This is the standard method for sending data to a server to create or update a resource.
  4. Configure Headers: In the headers section, ensure that you exclude any default headers by selecting "Exclude default headers." This ensures that only the headers you specify are included in the request.
  5. Set the Body Parameters: The body of the request should include the following parameters, sent as x-www-form-urlencoded:
    • client_id: This is your unique client identifier provided by SpreadsheetWeb.
    • client_secret: This is the secret key associated with your client ID.
    • grant_type: Set this to client_credentials to indicate that you are using the Client Credentials flow to obtain the token.

Here’s what the body of your request might look like:

client_id=your_client_id

client_secret=your_client_secret

grant_type=client_credentials

After configuring these settings, you can test the query by clicking on the "Run" button in Retool. If everything is set up correctly, you should receive a response containing an access token.

 

Step 3: Handling the Token Response

Once you have successfully retrieved the Bearer Token, the next step is to capture it and store it for future use. Retool allows you to run JavaScript code as part of your application, which we can use to handle the token.

  1. Create a JS Query: Create a new JavaScript query in Retool and name it takeToken. This query will extract the token from the response and store it in localStorage.
  2. Extract and Store the Token: In the takeToken query, write the following code:
localStorage.setValue('authToken', token.data.access_token);

This line of code takes the access token from the response and stores it in Retool’s localStorage, making it accessible throughout your application. The token is now available globally, and you can use it in subsequent API calls.

 

Step 4: Setting Up the Calculation API Call

With the token securely stored, the next step is to set up the API call that will perform the actual calculation based on user input. This call will be sent to the SpreadsheetWeb API and will require the token for authentication.

  1. Create a New API Query: Create another API query in Retool and name it calculate. This query will handle the calculation request.
  2. Set the API Endpoint: The endpoint for this request is https://api.spreadsheetweb.com/calculations/calculatesingle.
  3. Choose the POST Method: As with the token request, set the HTTP method to POST.
  4. Configure Headers: Include the following headers in your request:
    • Content-Type: application/json: This indicates that the request body is in JSON format.
    • Authorization: Bearer {{ localStorage.values.authToken }}: This header includes the Bearer Token for authentication. The token is dynamically retrieved from localStorage.
  5. Set the Request Body: The body of this request is where you’ll pass the data needed for the calculation. The JSON structure should look something like this:
{
    "request": {
        "workspaceId": "Your Workspace ID",
        "applicationId": "Your Application ID",
        "request": {
            "input": {
                "calculation": {
                    "inputs": [
                        {"reference": "IssueAge", "value": [[{"value": {{ Age.value }} }]]}
                    ],
                    "outputs": ["TotalPremium"]
                }
            }
        }
    }
}

In this structure, you’ll notice that IssueAge is mapped to {{ Age.value }}. This allows the value from the input field named Age in your Retool UI to be dynamically inserted into the API request.

 

Step 5: Designing the User Interface (UI)

To make the application user-friendly, you’ll need to design a UI that allows users to input data and view the results. Retool makes this easy with its drag-and-drop interface.

  1. Add an Input Field: Start by adding a text input box to the UI. Name this input Age. This field will allow users to enter the age value that will be sent to the API.
  2. Create a Calculate Button: Add a button to the UI and name it Calculate. This button will trigger the API calls when clicked.
  3. Display the Output: To display the calculated premium, add a label to the UI and name it premiumLabel. Bind the value of this label to the output of the calculate API query using the following:
{{ calculate.data.response.response.output.calculation.outputs[0].value[0][0].value }}

This will ensure that once the API call is complete, the output is automatically displayed in the label.

 

Step 6: Configuring Event Handlers

To ensure that the API calls are executed in the correct order, you’ll need to set up event handlers for the Calculate button.

  1. Trigger the Token API Call: When the Calculate button is clicked, the first action should be to trigger the token API query. This will fetch a new Bearer Token.
  2. Run the Calculation Only When the Token is Available: Next, configure the calculate API query to only run if the token has been successfully stored in localStorage. You can do this by setting a condition in the event handler using the following expression:
{{ !!localStorage.values.authToken }}

This condition ensures that the calculation API call only executes if a valid token is available. If the token is missing or invalid, the calculation will not proceed.

 

Step 7: Testing the Application

With everything set up, it’s time to test your application. Start by entering a value in the Age input field and then click the Calculate button. The application should first retrieve the Bearer Token and then use that token to send a calculation request to the SpreadsheetWeb API. If everything is working correctly, you should see the calculated premium displayed in the premiumLabel.

 

Troubleshooting Common Issues

While setting up your application, you may encounter some common issues. Here are a few troubleshooting tips:

  • Invalid Client ID or Secret: If you receive an error when trying to fetch the token, double-check your client_id and client_secret. These values must match the credentials provided by SpreadsheetWeb.
  • Token Expiration: Bearer Tokens are typically short-lived. If you encounter issues with the token not working, it may have expired. You can address this by fetching a new token before making the calculation request.
  • API Endpoint Errors: Ensure that the endpoints are correctly entered in Retool. Even a small typo can cause the API call to fail.

 

Conclusion

Integrating Retool with SpreadsheetWeb offers a powerful way to create dynamic applications that can handle complex calculations based on user input. By following this guide, you’ve learned how to set up and manage API calls, handle authentication tokens, and create a user-friendly interface that allows for real-time data processing.

The combination of Retool’s flexibility and SpreadsheetWeb’s robust calculation engine makes this setup ideal for a wide range of business applications. Whether you’re calculating insurance premiums, financial projections, or other data-driven outputs, this approach can be tailored to meet your specific needs.