API Connector

Import data from API

Application Programming Interface or API is an important data source that Acho can support. You can reference the following steps to build a connection with your API. If you need any help to set up the API connection, please contact us.

1. Go to the Resources page and click the Add Resource button.

2. Select API.

3. Configure your API. You can click the Test button to see whether to get the response normally. (Note: please see here to learn how to set up each field)

5. Click Connect. You will see a icon next to your API resource. It may take a few minutes to several hours depending on the data size. Once your data is ready, you will receive a notification email.

Configure your API connection

1. Resource Name

The displayed name of your API resource on the resource page.

2. URL

It's the API URL path to connect with your apps or integrations. Acho supports any APIs that follow REST conventions. For example,https://api.unibit.ai/v2/stock/historical?tickers=AAPL&accessKey=demo&dataType=json.

3. Parameters

API parameters are the optional key-value pairs used for determining the details of your API. Generally, you can specify them after the question mark (?) in your API. If there are multiple parameters, they will be appended together with an & sign. Take the following API as an example. There are three parameters:

tickers = AAPL
accessKey = demo
dataType = json

Acho supports two ways to specify your parameters.

1. Specify parameters in your URL

You can define parameters inside the URL field directly. Remember to specify them after a question mark and use "&" to concatenate all parameters. However, if you use {} to specify an embedded parameter, "{}", in your URL, you can only specify other parameters in the Parameters tab.

2. Specify parameters in the Parameters tab

Another way is to list each key and value in the Parameters tab as shown below.

4. Authorization

The input under this section is highly defined by the host of the API you are trying to connect with. Always make sure to check the documentation of the API you would like to connect to.

Acho supports four types of authorization to validate API access.

None (default): If your API doesn't require authorization, you can select None.
Basic Auth: It's the most straightforward way for authorization. It usually asks for the username or email and password.
Bearer Token: It's the most common method these days. For most software, you can generate the token under the Settings or Integrations tab of the website.
OAuth Client: If the API requires an OAuth authorization, an OAuth Client must be created. Usually, the software will ask you to create a custom app to access the account, and then you will need to fill in the following fields using the info of the custom app. If you have any questions about this, feel free to message us at https://drift.me/acho or email us at [email protected]
HMAC: Used to verify that a request is coming from an expected source and that the request has not been tampered with in transit. It’s a standard protocol but with custom setups for different applications. To use HMAC as your Authorization method, please message us at https://drift.me/acho or email us at [email protected]

4.1 OAuth Client

When creating an API Resource, you can select OAuth Client for the Authorization Type.
Click + if you don't have one.
Enter your info, please name it well, then click Add.
A new tab will be opened, if it's a login page, you can log in, and after you see an object (including an access token), you can close that tab. You may see the object immediately, which means you are logged in already, you can just close the tab.
Click Next.
Select the client you just created.
You are all set, you can test your API now.

5. Headers (Optional)

The headers are additional information included in the API requests. Similar to parameters, the headers also consist of key-value pairs. You will need to check the API documentation to find out the required and optional headers. The most common inputs for headers are

Content-Type: Specifies the format of the data being sent or received, such as JSON, XML, or form data
Authorization: We will insert your input from the Authorization section to the header. The format will be something like “Authorization: Bearer your_bearer_token_here”. Normally you don’t need to specify this by yourself if you already have filled in the Authorization section.
Customized field: For example, Shopify requires a header called X-Shopify-Access-Token and the Access Token of the custom app as the value of it.

6. Body

The body is only applicable to POST requests. It refers to the main content or payload of a request. It contains the data that is being sent from the client to the server.

For example, this is a sample body to create a product on an e-commerce website.

7. Data Format

Currently, we support 3 formats, CSV, JSON, and XML. JSON is the most common format, as almost all APIs support it. The JSON Path is an optional field to define the specific data you want to get from the response.

JSON (default)

Example:

https://api.unibit.ai/v2/stock/historical?tickers=AAPL&accessKey=demo&dataType=json

If you pass it into the URL field and click Test, you can get the following response.

JSON - JSON Path (optional)

If your data is stored in a nested JSON object, you can define the path to tell the system where to access it. In the above example, the raw data is stored inside the "AAPL" key and the AAPL key is stored in the result_data key. To access the raw data, the path can be defined as result_data.AAPL.

CSV

A CSV file refers to a "comma-separated values" file uses a comma to separate values. If your API returns a downloadable CSV file, you have to specify CSV in Data Format. Thus, the system will parse your data correctly. Here is an example API:

https://api.unibit.ai/v2/stock/historical?tickers=AAPL&accessKey=demo&dataType=csv

XML

XML is a markup language similar to HTML, but without predefined tags to use. Here is an XML response example:

https://stageappsp.smashfly.com/contactmanagerservice/v2/ContactManagerRestService.svc/help/operations/SaveContact

You can get the response as shown below

XML - JSON Path

Acho turns the XML response into a JSON format so that you can define JSON path to access the specific data you need easier. For example, if you only need body data within the html key, just specify html.body in JSON Path.

8. Multiple Requests

Retrieving all records in a single request is impossible when you have lots of data to get. By utilizing the Multiple Request setting, you can set the API connector to run multiple times based on the API requirements. Usually, there are three common methods: Iteration, Recursive and Loop.

Method 1: Iteration Requests (Pagination):

Iteration works the same as pagination. This method splits data into several batches based on the row number and only retrieves one batch of the data at a time until there is no data that can be retrieved.

For example, if the original dataset has 3000 records and we retrieve 1000 rows at a time, the system will call the API three times to get all data.

Options:

Request Limit: The maximum number of records to retrieve at a time. Most of the APIs named this parameter limit or page_size.
Start Parameter Key: A parameter to decide where to start to retrieve data. Most of the APIs named this parameter start or page. The Start for most APIs is either 0 or 1. If the pagination is by page, the Step is usually 1 (just like flipping pages). If the pagination is by number, the Step is usually the same as the Request Limit number.

Two most common examples:

Method 2: Recursive Requests (by field):

If the API requires data from the previous API call to get the following data, you will need to use Recursive to sync the data.

There are 3 recursive targets that you can use, Parameter, URL, and Body.

Parameter: If the API response contains something like next_id: xxx, and you will need to use it as a parameter in the URL as the value of something like “startFrom”, you will need to set the parameter as “startFrom: {{next_id}}”, then in the URL it will be ?startFrom={{next_id}}

URL: If the API response contains the URL of the next page, by setting the JSON Path to the key for the URL, the API connector will call the next page automatically.

Body: So far, the only API we have used Body as Recursive Target is Notion. We will need to put “next_cursor” from the previous API call as the “start_cursor” of the next call to request body. Example: { “start_cursor”: {{next_cursor}} }

Method 3: Loop

If your API requires something like an ID to retrieve details, you can loop through the IDs to get the details data. For example, assume you have a list of products, with ID, name, and price. You would like to get the details of the products, however, there is no such API like /product/details, but an API like /product/{{id}}/details.

You can set the Loop Target to Parameter, set the Loop Parameter Key to id, and select column “id” in the imported data of Products as Values to Loop. The Connector will loop through the “id”s in the table and replace the value within the bracket to request data.

Loop Target: You can select to use it in the Parameter or Body. If you select Parameter, you will need to add {{name}} to your URL to use it. If you select Body and use the Post method, your Loop Parameter Key will be automatically added to your Post request body.

Loop Parameter Key: The key name inside the curly brackets, {{}}, in your API URL.
Values to Loop: The array of values to loop inside the brackets. The values should be referred from a column in your project table.

9. API Update

If your API data is real-time, you can turn on API Update to retrieve data at a certain time interval. There are three options in the settings of API Update.

Update Method: refers to how to deal with the new data points and the current data points.
- Replace: Every time Acho requests data through the API, it will replace the current dataset with the latest dataset entirely.
- Incremental: Every time Acho requests data through the API, it will append the new data points to the current dataset. This method may generate duplicate data points.
Sync Frequency: refers to how often Acho should retrieve data through the API. There are three options that you can choose from:
- Intraday: update the table every specific time interval within a day. The minimum time interval is 15 minutes.
- Daily: update the table at a specific time (such as 6 a.m.) every day.
- Custom: you can define the scheduler in a more flexible way.

Set up dynamic date ranges

Acho supports setting up dynamic dates ranges without changing parameters or URLs manually. Currently, there are four dynamic date expressions on Acho:

{{year}}: refers to the current year in UTC. The year is expressed in 4 digits, such as 2021.
{{month}}: refers to the current month in UTC. The month is expressed in 2 digits, such as 08.
{{day}}: refers to the current day in UTC. The month is expressed in 2 digits, such as 23.
{{today}}: refers to today's date in the format of yyyy-mm-dd , such as 2021-08-22, in UTC.
{{today_full_date}}: refers to today's date in the format of yyyy_mm_dd, such as 2021_08_22, in UTC.
{{yesterday}}: refers to yesterday's date in the format of yyyy-mm-dd, such as 2021-08-22, in UTC.
{{yesterday_full_date}}: refers to yesterday's date in the format of yyyy_mm_dd, such as 2021_08_22, in UTC.
{{tomorrow}}: refers to tomorrow's date in the format of yyyy-mm-dd, such as 2021-08-22, in UTC.
{{tomorrow_full_date}}: refers to tomorrow's date in the format of yyyy_mm_dd, such as 2021_08_22, in UTC.
{{unix_epoch_seconds}} : refers to current unix epoch timestamp in seconds, such as 1712978322
{{unix_epoch_milliseconds}} : refers to current unix epoch timestamp in milliseconds, such as 1712978322000

You can put the expression in URLs or Parameters. Here are the examples.

URLs

Change the month in the API URL to this month automatically.

http://api.example.com/xxx/{{year}}-{{month}}

Change the date in the API URL to today's date.

http://api.example.com/xxx/{{today}}

Change the date in the API URL to yesterday's date.

http://api.example.com/xxx/{{yesterday}}

Parameters

If there are two parameter keys, startDate and endDate, to define the date range in your API, then you can use the dynamic date expression in both parameters. Here are some examples of how to use dynamic date expressions in Parameters.