API data (JSON)

Retrieve data from any API source and write queries on top of it using SQL

Prerequisites

• Data source allows access to LogicLoop if necessary

• Deployment works for organization and data source

• LogicLoop user account is an admin to be able to create a data source

API Data Source

LogicLoop lets you query any API that returns data in a JSON format as a data source. That means you can query services like Slack, Mixpanel, Stripe, and Twilio to retrieve data and write rules on top of that data using SQL.

First, create a new API (JSON) based Data Source and enter any Authorization information needed.

For most APIs, you will enter "Authorization" as the Authorization Header Key. However, some APIs give their auth header a different name such as "Authorization-Token" or "X-Token" or "Service-X-Token" etc.

For most APIs, the Authorization Header Value will be your API key or token e.g "XXXXXX". However, some APIs require you to append the word "Bearer XXXXXX" or "Basic XXXXXX" before your token.

Now, all you have to do is create a new rule and express your API call in YAML format. The results of that API call will be parsed as JSON into the results table below.

In this example, we made an API call to retrieve a channel's conversation history on Slack:

url: "https://slack.com/api/conversations.history"
params:
    channel: "C01M88G0NDX"
path:
    messages

This API call is equivalent to running something like this via CURL:

curl "https://slack.com/api/conversations.history?channel=C01MP8G0NDC" -H "Authorization: XXXXXX" 

The Slack API will return a result that looks something like this.

{
    "ok": true,
    "messages": {
        type: "message",
        subtype: "bot_message",
        text: null,
        ts: 16309440202,
        username: "logicloop",
        ...
    }
    ...
}

We've added the path: messages setting in order to access the nested messages object. The results populate this table below:

You must format your query in YAML syntax. Parameters that are available include:

• url (string) - the URL endpoint you want to hit

• method (string) - the HTTP method to use (default: get)

• headers (dictionary) - headers to send with the request

• auth (array) - basic authentication username/password (e.g: [username, password])

• params (dictionary) - query string parameters to add to the URL path

• data (dictionary) - values to be sent as the request body

• json (dictionary) - same as data except that it's converted to JSON

• fields (array) - specific fields you want to pick from the results

Here is an example of a sample POST request:

url: "https://myendpoint.com/api/users"
method: post
headers:
    X-Token: "XXXXXXXXXXXXXXXX"
data:
    name: "Maddie"
    email: "[e-mail]"

Now that you've retrieved data from your API source, you can set up an action to trigger directly from the data.

Read from Slack API

Get an API token

Create a pre-configured app

The most common way of interacting with the Slack API is by creating a Slack App Integration. Begin by navigating to the this tutorial on How to quickly get and use a Slack API token. Scroll down to the Create a pre-configured app section and click Create app.

Configure your Slack App

Next, select the workspace that you want to interact with (read from or write to), and click Next. To give the app a recognizable name and description, click Edit Configurations and change the values of name (you will need this later) and description. Finally, click Next, then Create.

Get your Slack API Token

Under Install your app, click Install to Workspace, and select Allow. At the top of the screen you should see a message telling you to head back to the tutorial. Click the link in the message.

If the Install to Workspace button is disabled, you may need to add permission scopes to your app. On the left menu, under Features, you should see OAuth & Permissions. Click that, scroll down to the Scopes section and add the Bot Scopes that fit your use case.

If you scroll down to the section Using your token, you can find the API token populating a black box (starting with xoxb). Save that token somewhere safe, you will need it.

Create the Data Source

Go to Data Sources > New Data Source. and choose API (JSON). Give it a name, add Authorization as the Authorization Header, and use add the API key (with the Bearer prefix) as the Authorization Value.

Query the API

You need the Channel ID to access the messages of a channel. Find it in Slack by clicking on the channel title and scrolling to the bottom of the About tab.

Use the URL https://slack.com/api/conversations.history with a parameter for the Channel ID. Set the path to messages.

Query APIs with SQL

Alternatively, once you've retrieved the data you want from your API, you can write another rule on top of these results using SQL to combine data or use for further manipulation.