Getting Data from REST API

Users can connect to REST API data sources using the connection wizard themselves.

Developers can configure REST API using the xls.objects table or query list views to allow users to connect without additional configuration steps.

Users must connect to a database in this case instead of the direct connection to a data service.

Here is a format for the xls.objects table:

Here is a format for the Query List view:

Here is a sample for ZohoCRM:

The sample contains three configured HTTP objects in the ZohoCRM schema: Accounts, Contacts, and Leads.

The TABLE_CODE column contains URLs used to load data.

Other fields can contain formats for insert, update, and delete REST API commands. See Saving Data Using REST API.

REST API objects have the same object type, HTTP, as the objects described in Getting Data from the Web.

However, there are several significant differences to keep in mind:

1. REST API returns typed data in JSON or XML, while other web data sources can return data that require special parsing.
2. REST API supports saving data changes.
3. REST API usually requires user authentication.
4. REST API can support paging data.
5. REST API can support select, filter, group by, and order by operations by a service.

You can turn on the typed API result parser using the ApiResult=true parser parameter.

For example:

https://www.zohoapis.com/crm/v2/Contacts;ApiResult=true

To learn how to configure saving changes, see Saving Data Using REST API.

To learn how to configure the authentication, see HTTP Authentication.

To load multiple pages, use the {$page} insertion as a value of the page parameter. The add-in will continue incrementing this parameter while it gets new data.

For example:

https://www.zohoapis.com/crm/v2/Contacts?page={$page};ApiResult=true

Of course, you can add other URL parameters available in the target API.

SaveToDB 10 allows adding API-defined columns into the xls.columns table.

In this case, the add-in allows users to choose columns to select in the connection wizard.

For example, if a user selects the ID, First_Name, and Last_Name columns, the add-in will generate a command like

SELECT ID, First_Name, Last_Name FROM [https://www.zohoapis.com/crm/v2/Contacts?page={$page};ApiResult=true]

In this case, the add-in loads all the data but inserts the selected columns only.

You can use the {$select_fields} insertion in the URL parameter used to define selected columns.

For example:

https://www.zohoapis.com/crm/v2/Contacts?fields={$select_fields}&page={$page};ApiResult=true

In this case, the add-in will generate a command:

SELECT ID, First_Name, Last_Name FROM [https://www.zohoapis.com/crm/v2/Contacts?fields=ID,First_Name,Last_Name&page={$page};ApiResult=true]

As a result, the service will return the selected columns only, saving time and traffic.

If a user selects all columns, the add-in will remove the parameter, and the command will look like

https://www.zohoapis.com/crm/v2/Contacts?page={$page};ApiResult=true

See also:

• xls.objects
• xls.columns
• Getting Data from the Web
• Saving Data Using REST API
• HTTP Authentication
• HTTP Commands