Xano is a powerful no-code backend automation platform that enables developers and non-developers alike to rapidly build APIs without the need for complex coding. In this guide, we'll show you how to integrate Excel-based calculations into Xano using the SpreadsheetWeb API. This API is ideal for those who need to perform dynamic calculations based on data and formulas stored in a spreadsheet.
Whether you're automating business processes, performing financial calculations, or creating custom workflows, this step-by-step tutorial will walk you through setting up a Xano project and integrating the SpreadsheetWeb API for integrating complex Excel calculations.
To Begin with: Transforming Your Excel File into an API with SpreadsheetWeb
Before diving into the technical details of integrating the SpreadsheetWeb API with Xano, it’s essential 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 with the following structure:
- Three individual input fields (originZIP, destinationZIP, weight).
- An output field (cost), which is generated based on the input values through pre-configured Excel formulas.
This Excel file operates like a typical Excel calculator, but once converted into an API, it will allow you to programmatically submit inputs and retrieve calculated results—just as if you were using the spreadsheet manually.
Uploading Your Excel File to SpreadsheetWeb Hub
To convert your Excel file into an API, you’ll first need to upload it to the SpreadsheetWeb Hub. The Hub allows you to create an application from your Excel file, turning it into a web-based API. This process transforms your Excel formulas and calculations into a format that can be accessed programmatically via API calls.Once uploaded, you will be given essential details that are critical for making API requests from Xano. These include:
- API Endpoint URL: The unique URL where API requests will be sent.
- Workspace ID: The identifier for your workspace within SpreadsheetWeb.
- Application ID: The specific ID of your application that corresponds to your Excel file.
- Authentication settings (if applicable): Whether your API requires token-based authentication or not.
These details will be crucial when you configure your HTTP request in Xano to interact with the API.
For a detailed guide on creating an API from a SpreadsheetWeb application, please refer to the documentation provided by SpreadsheetWeb. This resource will walk you through each step of the process, ensuring your Excel file is successfully converted into an API.
SpreadsheetWeb API Authentication Options
When setting up the API, you have two main authentication options:
- Token-Based Authentication: This is the more secure option. It requires sending a Bearer token in the request headers to authenticate and authorize the API calls. This method ensures that only authorized users can access the API.
- No Authentication: This is a simpler option, often used during the development phase or for internal testing where security is not a primary concern.
In this guide, we will use the No Authentication option to keep the setup simple while integrating with Xano. By skipping authentication, you won’t need to configure authorization headers, simplifying the 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 would need to set up Xano to include the Bearer token in the Authorization header of the HTTP request, ensuring that your API calls are authenticated.
Step 1: Setting Up the Xano Database
After creating your Xano account, the first step is to create your database. This is essential because Xano will store the input data dynamically as well as any associated metadata.
1. Navigate to the Database section from the left-side panel.
2. Click the Add Table button in the top-right corner.
3. Choose Enter manually to add your table structure manually and create columns based on the input fields you'll use in the API call, such as originZIP, destinationZIP, and weight.
4. Add columns that match the input fields you'll use for your API call.
You can customize the database structure to reflect your input data, such as originZIP, destinationZIP, and weight, for example.
Note: Xano requires that API parameters be tied to a database. This ensures that any input values for testing or live scenarios can be stored efficiently. However, you have the option to make this database connection optional depending on your workflow. We’ll explain more about this later in the post.
Step 2: Creating the API Group and Custom API Endpoint
With your database ready, it's time to create the API group and connect it to your database:
- Go to the API section from the left panel.
- Click Add API Group and name your group. This group will contain the endpoint that interacts with the SpreadsheetWeb API.
- After creating the group, you'll need to link it to your database so that the input data gets stored and processed correctly.
Now, let's create the API call:
- Click on the Add API endpoint button within your API group.
- Select Custom endpoint at the bottom of the options. This will allow us to define the behavior of the API call manually.
Step 3: Setting Up Input Fields and cURL Integration
To allow dynamic inputs, we need to configure the API to accept the parameters that will be passed for calculations. This includes inputs like loan amount, interest rate, and other relevant parameters.
- Name your inputs appropriately, such as amount, rate, etc.
- Next, we’ll import the API request as a cURL file from SpreadsheetWeb.
Importing the cURL Request:
- Visit the SpreadsheetWeb API.
- Go to the calculatesinglesimple path and click Try it out.
- Once you hit Execute, you’ll receive a ready-made cURL request. Copy this request.
In Xano, we can directly import this cURL request to speed up the process:
- Under your custom API endpoint, click Add Function and select External API Request.
- In the new menu, use the Import cURL button at the top-right.
- Paste the cURL request, but be sure to modify the parameters to fit your own data. You’ll need to update the workspace and application ID fields, among others.
{ "request": { "workspaceId": "3bae377c-12fb-4b5e-90d2-a29aa12a6d79", "applicationId": "f8da5ff3-26dd-473c-9331-e43322e923ed", "inputs": { "originZIP": "10001", "destinationZIP": "90001", "weight": "1" }, "outputs": [ "cost" ] } }
Step 4: Adding Dynamic Input Values
Now that the API call is set up, it’s time to connect your dynamic input values from Step 3 to the actual API endpoint.
- Head over to the input value section within the API setup. Define your input fields: originZIP, destinationZIP, and weight.
- Map the input values you created earlier (originZIP, destinationZIP, and weight, etc.) to the corresponding parameters in the API call.
Step 5: Testing and Finalizing
With everything configured, you can test the API to ensure the calculations work as expected. The results from the SpreadsheetWeb API will be returned dynamically based on the input values you've set.
You can now save and finalize your API configuration within Xano.
Bonus Step: Saving Test Data in the Database
In our demo, the input data is saved in the database for testing and record-keeping. This is not strictly required for every project, but it's useful for scenarios where you want to track the calculations or keep logs of the API calls.
Note: During the setup, the video shows that database connection is mandatory for API parameters. Xano's system requires this connection, but it's possible to make it optional by configuring an additional step. We chose to demonstrate it as a required step for completeness, but depending on your project's needs, you can opt to leave it as optional.
