Integrating Excel Calculations into Workato with SpreadsheetWeb API

by | Oct 30, 2024

In this guide, we will demonstrate how to leverage Workato and SpreadsheetWeb API to dynamically send data and retrieve calculated results from an Excel file. We will walk through creating a project and setting up a recipe to send a request to the SpreadsheetWeb API.

 

Transforming Your Excel File into an API with SpreadsheetWeb

Before Diving into Workato Integration: Setting Up Your Excel File as an API

Before diving into the technical details of integrating the SpreadsheetWeb API with Workato, it’s crucial to understand the core component: your Excel file. This file forms the foundation of your API, as it contains the input fields, calculations, and output results that the API will use.

For the purpose of this guide, let’s assume you have an Excel file structured as follows:

  • Three individual input fields: originZIP, destinationZIP, and weight.
  • An output field: cost, which is generated based on the input values using pre-configured Excel formulas.

Essentially, this Excel file acts like a standard spreadsheet calculator. Once transformed into an API, it allows you to programmatically submit inputs and retrieve calculated results—just as if you were using the Excel file manually.

Uploading Your Excel File to SpreadsheetWeb Hub

To convert your Excel file into an API, you’ll need to upload it to the SpreadsheetWeb Hub. The Hub allows you to create an application based on your Excel file, effectively turning it into a web-based API. During this process, your Excel formulas and calculations are converted into a format that can be accessed programmatically via API calls.

Upon uploading, you’ll receive essential details that are critical for making API requests from Workato. These include:

  1. API Endpoint URL: The unique URL where your API requests will be directed.
  2. Workspace ID: The identifier for your workspace within SpreadsheetWeb.
  3. Application ID: The specific ID associated with your application (i.e., your Excel file).
  4. Authentication settings (if applicable): Whether your API requires token-based authentication or not.

These details will be crucial when configuring your HTTP requests in Workato to interact with the API effectively.

For a comprehensive guide on creating an API from a SpreadsheetWeb application, refer to the documentation provided by SpreadsheetWeb. The documentation will walk you through each step of the process, ensuring that your Excel file is successfully converted into an API.

SpreadsheetWeb API Authentication Options

When setting up the API, you have two main authentication options:

  1. Token-Based Authentication: This is the more secure option and requires sending a Bearer token in the request headers to authenticate and authorize API calls. It ensures that only authorized users have access to the API.
  2. No Authentication: This simpler option is often used during development or internal testing phases where security is not a primary concern.

For this guide, we will use the No Authentication option to keep the setup straightforward when integrating with Workato. By skipping authentication, you won’t need to configure authorization headers, simplifying the integration process. However, for production environments, we strongly recommend enabling token-based authentication to protect your API from unauthorized access.

If your use case requires additional security, you can enable token-based authentication through SpreadsheetWeb. When using this option, you will need to set up Workato to include the Bearer token in the Authorization header of your HTTP request, ensuring that your API calls are authenticated.

By transforming your Excel file into an API and setting up the appropriate authentication, you’ve laid the groundwork for integrating with Workato. Now, let’s move on to the next steps of configuring your workflow in Workato.

Prerequisites

Before we start, make sure you have:

  • A Workato account with access to create projects and recipes.
  • SpreadsheetWeb API endpoint URL and the necessary workspaceId and applicationId.

Step 1: Create a Project in Workato

  1. Log into your Workato account.
  2. From the left-hand panel, navigate to the Projects section.
  3. Click the Create button in the top-right corner to create a new project.
  4. Give your project a descriptive name that reflects your goal, such as "Excel Calculation Integration".

 

 

Step 2: Create a Recipe

  1. Once your project is created, click the Create button in the top-right corner of the project page to create a new recipe.
  2. Name your recipe appropriately, for instance, "SpreadsheetWeb API Integration Recipe".
  3. Select a trigger for your flow. For this example, we'll use Function Call as the trigger, which allows flexibility with dynamic input values.

Step 3: Set Up the Flow for Dynamic Input Data

  1. In the recipe builder, click Add Step and choose Function Call as the trigger.
  2. Here, you have two options:
    • Manually input the values you want to send in the request.
    • Use a pre-defined request body in JSON format and create a schema from it. We recommend creating a schema as it provides a clear structure for your dynamic inputs.

Example Schema:

{

  "originZIP": "string",

  "destinationZIP": "string",

  "weight": "string"

}

 

If you have the workspaceId and applicationId as static values, you can exclude them from the schema and set them directly in the next steps.

Step 4: Send an HTTP Request to SpreadsheetWeb API in Workato

  1. Add a new step in your recipe and select Send Request via HTTP.
  2. In the Authentication section, choose No Authentication.
  3. In the URL field, enter your SpreadsheetWeb API endpoint:
    https://api.spreadsheetweb.com/calculations/calculatesinglesimple.
  4. Configure the HTTP request method as POST.
  5. In the Request Body section, input the following JSON format:
{

  "request": {

    "workspaceId": "3bae377c-12fb-4b5e-90d2-a29aa12a6d79",

    "applicationId": "f8da5ff3-26dd-473c-9331-e43322e923ed",

    "inputs": {

      "originZIP": "<originZIP>",

      "destinationZIP": "<destinationZIP>",

      "weight": "<weight>"

    },

    "outputs": [

      "cost"

    ]

  }

}

 

In this JSON request, replace <originZIP>, <destinationZIP>, and <weight> with the dynamic variables defined earlier in your schema.

Add a header to the request by navigating to the Header section and selecting Add Header.
Set the header as Content-Type: application/json.

Step 5: Map and Display the API Response in Workato

  1. Once your HTTP request is configured, add a new step to the recipe.
  2. Choose Log Message to Job Report as the next step. This is a helpful debugging step that allows you to see the result of your API call.
  3. In the Message field, map the output value received from the SpreadsheetWeb API response. For example:
    Use the response field that contains the cost output value from the API call.
  4. Save the recipe to complete the process.

Testing Your Integration

After completing all the steps, run your recipe to test the integration:

  1. Use the Function Call trigger to provide input values dynamically.
  2. Observe the HTTP request being sent to the SpreadsheetWeb API with your inputs.
  3. Review the result logged in the Job Report, verifying that the API has returned the correct calculated output.

Summary

In this blog post, we have shown how to set up a simple integration in Workato to send dynamic inputs to the SpreadsheetWeb API. By following these steps, you can build a scalable automation that processes Excel-based calculations using API requests.

This integration approach unlocks the power of complex Excel calculations and seamlessly connects them with Workato recipes to meet your workflow requirements.