Requests

The web is all about fetching data from remote sources. Fortunately, SEL provides an event type to make these requests that support common data formats.

Request

A Request fetches data from a remote location (URL). If we want, for example, to pull in the contents of a published Google Sheet, we can make a GET or POST request like so:

=Request('get', 'https://docs.google.com/spreadsheets/d/e/2PACX-1vSRSc0h4ZvFGaY8ZmCSRHjc5nGE80tNKKvPwyAgivd35eprIS/pub?gid=944422473&single=true&output=csv')

This event will fetch the contents of this URL and store them in the event. The value of the event in the results table and log will be set to the status code of the response. All responses less than 400 are considered successful.

Let's look at this example in context:

In this example, we are running two web requests, one to fetch some comma-separated values data (above) and another to fetch data from an endpoint that returns JSON.

Each points to a separate Parser to process the response data.

The Results table shows that the value of each Request has been set to the status code of the response. The CSV request failed, returning a 400 (Bad request), while the JSON request succeeded (200).

📘

Formats, File Size, and Timeout Limitations

Currently, JSON and CSV are the only data formats (or content types) that can be retrieved by a Request. Other formats will be rejected and the output of the event will be set to empty.

Both JSON and CSV responses will be converted to a JSON format on receipt to enable parsing using a Parser event. The list of rows from a CSV data set will be placed inside a single key-value pair like so: {'rows': [...] }

The ... list will be filled with the list of rows from the CSV file.

SEL also imposes a 5 MB limit on the size of the content that can be retrieved.

A request will automatically timeout if it takes longer than 10 seconds!

Querystring

In order to make a GET or POST request, you may also need to pass in a series of parameters, called a query string.

This list of arguments can be defined as a Querystring event, for example:

=Querystring('api_key=abcdef123&postal_code=77711&shipping_weight=460')

Sending this event's output to a Request event will pull these arguments into the web request as parameters.

💁‍♂️

URL's and body content

Whether you are using a POST or GET method, when using a Querystring event, the arguments will be used as query string parameters appended to the URL and as body content (as key-value pairs).

Currently, SEL does not support body content with nested key-value pairs.

Parser

If you want to use the response of your request, you'll need to create a Parser event.

These events take a JSONPath argument that defines a search to retrieve the value you want from the response data, like so:

=Parser('$.pricing.express_overnight')

The parser event itself will extract and pass along the value it retrieves using your expression, but the value will not be stored in the parser event (it is not a container).

To store the value (to reference it elsewhere in your model), we recommend sending the output of the parser event to a container such as a Pipe or Pool. Keep in mind that these containers expect individual numbers as input, so your JSONPath expression should be extracting a single numeric value from the response data.

📘

JSONPath resources

SEL uses the jsonpath-ng Python module to search your data using this expression. You can read more about this module here: https://pypi.org/project/jsonpath-ng/

Writing a complex JSONPath expression can be challenging. In addition to asking ChatGPT, this site is a handy way to test a JSONPath expression against a JSON object: https://jsonpath.com/.

👩‍🍳

CSV parsing recipe

If your request fetched CSV content, the rows will be stored inside a JSON object that starts with a single key: rows.

This is how you would parse the value in the Weight column where other columns have a specific value. Effectively simulating a SQL query:

=Parser("$.rows[?(@['Column A']=='26<lookup_a>' & @['Column B']=='3<lookup_b>')].Weight")