External equation processing with Sparklite.io

In this episode, we'll learn how to integrate with Sparklite.io, using Pipes, to process equations and other calculations externally.

Features Discussed:

1. SparkliteAPI (Time: 03:08)
   Learn how to utilize Sparklight to create an API connection between your Tadabase platform and an Excel or Google Sheets spreadsheet. This setup allows you to perform complex calculations within your spreadsheet, such as multiplying values, and then feed the results back into your Tadabase application, enabling dynamic and automated data processing.
2. Pipes (Time: 08:47)
   Learn how to create a basic data table with a few fields, integrate it with Sparklight, and set up a pipe to process and return data. This example demonstrates how to pass data from Tadabase to a spreadsheet, perform calculations, and return results.
3. Data Source Pipes (Time: 15:44)
   Learn how to leverage pipes within Tadabase to load data dynamically in a table component without saving it directly into the database. For example, understand how this can enhance real-time data processing by fetching and displaying information on-demand.
4. Form Component (Time: 16:52)
   Explore setting up a trigger pipe button in a form to send data to an API and return results. For example, see how this can be used to perform calculations or fetch external data as part of the form submission process.
5. Trigger Pipe (Time: 17:59)
   Learn how to use record rules to automatically trigger pipes whenever a record is created or edited. For example, see how you can automate data processing tasks, such as calculating values or fetching external data, and saving the results back to your Tadabase application.

Transcript Summary

Introduction

Hey everybody, welcome back to Build It with Tadabase. Have you ever thought about processing your equations or calculations outside of Tadabase? If you have, I have the tool for you.

Overview of the Tool: Sparklight

To do this, we're going to be using a tool called Sparklight. Sparklight is a service that creates an API between a platform—like Tadabase in our case—and an Excel spreadsheet. You can use either Excel or Google Sheets. What we're essentially doing is building a spreadsheet, putting in all the calculations we need to process, like value one times value two gives us a result. We'll feed the data from Tadabase to our spreadsheet, where the processing and calculations will occur. Sparklight will then help us bring the result back to our application.

Setting Up the Process

Today, we'll set up a simple data table with just a few fields we want to use, and then we'll build a pipe to integrate with Sparklight and our spreadsheet to do all the processing.

When we're done, we'll be able to pass data from Tadabase to our spreadsheet, process it, and then return the results. Our sample spreadsheet will have two inputs, two different outputs calculating based on those inputs, and a text input and output as well. Through the use of pipes, we set up three examples to display what we're doing here:

1. Loading with the Data Source: This sends values to Sparklight, where they are processed and returned without saving them to Tadabase.
2. Running Inside a Pipe Form: This allows users to input data and check the answer against the spreadsheet.
3. Record Rules: This ensures that every time a record is created, the data is sent to the spreadsheet for processing and then saved back to Tadabase.

Creating an Account with Sparklight

To get started, head over to sparklight.io and create an account. There’s a free trial, and it’s super easy to sign up. Once you create your account, go through the documentation quickly to see how to map out the various cells inside your spreadsheet.

This works by including two different prefixes for the names of your cells:

• Input Cells: Use the prefix X_input_ followed by a parameter of your choice.
• Output Cells: Use the prefix X_output_ followed by a parameter of your choice.

Setting Up the Spreadsheet

Here's the sample spreadsheet I’ve set up in Google Sheets:

• Numerical Calculation: Two inputs, value one and value two, which are numbers that come from Tadabase. We'll multiply them by different values (3.5 and 6.5).
• Text Calculation: An input string will be processed with an IF statement to return a specific result.

To name a cell, go to the name box in the upper left-hand corner and enter the name using the required prefixes. For example, X_input_value1 for the first input.

Downloading the Spreadsheet

Once the spreadsheet is set up, download it as an Excel file if you’re using Google Sheets. If you’re already using Excel, you can skip this step.

Creating an API with Sparklight

Now that the spreadsheet is ready, go back to Sparklight. Upload your spreadsheet, and Sparklight will convert it to an API, mapping all the inputs and outputs. Once the API is ready, Sparklight will provide you with an endpoint URL and other details needed to access the API.

Setting Up a Pipe in Tadabase

Inside Tadabase, create a new data table with the fields you need. These fields should mimic the spreadsheet as closely as possible. The next step is to create a custom pipe. Install the custom pipe, give it a name (e.g., "Sparklight"), and start creating your API calls.

Copy the endpoint URL from Sparklight, set the request method to POST, and define your parameters. These parameters correspond to the inputs in your spreadsheet. Then, move on to setting up the API headers and request body, using the instructions provided by Sparklight.

Testing the Pipe

Once the pipe is set up, you can test it by entering test values. For example, entering "5" for value one, "3" for value two, and "Tadabase" for the text input. The pipe should return the correct calculations and text result based on your spreadsheet.

Using the Pipe in Tadabase

Now that the pipe is ready, you can use it in various ways within your Tadabase application:

1. Loading Data Dynamically: Use a table component with the pipe to load data dynamically without saving it to Tadabase.
2. Triggering a Pipe from a Form: Set up a trigger pipe button to send data to the API and return a result.
3. Using Record Rules: Set up record rules to run the pipe every time a record is created or edited, saving the calculated values back to Tadabase.

Conclusion

The possibilities with Sparklight are endless. You can process complex calculations externally and integrate them seamlessly with Tadabase, allowing you to retain some of the processing power of spreadsheets while leveraging Tadabase’s features. Sparklight is a great tool to keep in your back pocket for specific use cases.