Getting Data from REST API

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

Developers can configure REST APIs using the xls.objects table or query list views to enable user connections without additional configuration steps.

In this scenario, users must connect to a database instead of directly to a data service.

xls.objects Table Format

Here’s the format for the xls.objects table:

Query List View Format

Here’s the format for the Query List view:

Sample for ZohoCRM

Here’s a sample configuration for ZohoCRM:

This sample includes three configured HTTP objects in the ZohoCRM schema: Accounts, Contacts, and Leads. The TABLE_CODE column contains the URLs used to load data.

Other fields can include formats for insert, update, and delete REST API commands. For more details, see Saving Data Using REST API.

Key Differences from Other Web Data Sources

REST API objects share the same object type, HTTP, as those described in Getting Data from the Web. However, there are several important differences:

1. REST APIs return typed data in JSON or XML, while other web sources may return data that requires special parsing.
2. REST APIs support saving data changes.
3. REST APIs typically require user authentication.
4. REST APIs can support paginated data.
5. REST APIs can perform select, filter, group by, and order by operations.

You can enable the typed API result parser using the ApiResult=true 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. For authentication configuration, refer to HTTP Authentication.

Pagination

To load multiple pages, use the {$page} placeholder in the page parameter. The add-in will increment this parameter to retrieve new data. For example:

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

You can also add other URL parameters supported by the target API.

Adding API-Defined Columns

SaveToDB 10 allows you to add API-defined columns to the xls.columns table. This enables users to select columns in the connection wizard.

For instance, 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 retrieves all data but only inserts the selected columns.

You can use the {$select_fields} placeholder in the URL parameter to define selected columns. For example:

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

The add-in will generate a command like:

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

This way, the service will return only the selected columns, saving time and bandwidth.

If a user selects all columns, the add-in will remove the parameter, resulting in the command:

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

Additional Resources

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