Connecting to ZohoExpense

To authenticate to ZohoExpense, and connect to your own data or to allow other users to connect to their data, you can use the OAuth standard.

Using OAuth Authentication

First, you will need to register an OAuth application with ZohoExpense. To do so, go to Zoho Developer Console, add a new Client (Server-based application) and set a valid OAuth redirect URL. Your Oauth application will be assigned a client id and a client secret. Additionally, you will need to set the relevant Organization. Set Organization to Organization Id that you can find by querying Organizations table.

After setting the following connection properties, you are ready to connect:

AuthScheme: Set this to OAuth.
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to manage the process to obtain the OAuthAccessToken.
OAuthClientId: Set this to the client_id that is specified in you app settings.
OAuthClientSecret: Set this to the client_secret that is specified in you app settings.
CallbackURL: Set this to the Redirect URI that is specified in your app settings
Organization: Set this in ProfileSettings to your Organization Id. See Organizations table for details
Domain: Optionally, set this in ProfileSettings to your ZohoExpense account domain

Example connection string:

Profile=C:\profiles\ZohoExpense.apip;ProfileSettings='Organization=your_organization_id;Domain=your_domain';Authscheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;CallbackUrl=your_callback_url;

Available Tables

The ZohoExpense API Profile provides access to the following tables:

Organizations: Returns the list of organizations associated with the authenticated user.
Users: Returns the list of all users in the organization.
UserDetails: Returns the full details of a specific user (requires UserId).
Customers: Returns the list of all customers in the organization.
CustomerDetails: Returns the full details of a specific customer (requires CustomerId).
Projects: Returns the list of all projects in the organization.
Currencies: Returns the list of all currencies in the organization.
Taxes: Returns the list of all taxes in the organization.
ExpenseCategories: Returns the list of all active expense categories in the organization.
Expenses: Returns the list of all existing expenses in the organization.
ExpenseReports: Returns the list of all expense reports in the organization.
ExpenseReportDetails: Returns the full details of a specific expense report (requires ReportId).
Trips: Returns the list of all trips in the organization.
TripDetails: Returns the full details of a specific trip (requires TripId).

Usage Examples

Organizations:

SELECT * FROM Organizations

Users:

SELECT * FROM Users
SELECT * FROM Users WHERE Status = 'active'

UserDetails:

SELECT * FROM UserDetails WHERE UserId = '903000000026001'

Customers:

SELECT * FROM Customers
SELECT * FROM Customers WHERE Status = 'active'

CustomerDetails:

SELECT * FROM CustomerDetails WHERE CustomerId = '903000000026049'

Projects:

SELECT * FROM Projects
SELECT * FROM Projects WHERE Status = 'active'

Currencies:

SELECT * FROM Currencies
SELECT * FROM Currencies WHERE IsBaseCurrency = true

Taxes:

SELECT * FROM Taxes

ExpenseCategories:

SELECT * FROM ExpenseCategories
SELECT * FROM ExpenseCategories WHERE FilterByStatus = 'Status.All'

Expenses:

SELECT * FROM Expenses
SELECT * FROM Expenses WHERE DateStart = '2024-01-01' AND DateEnd = '2024-12-31'
SELECT * FROM Expenses WHERE UserId = '903000000026001'
SELECT * FROM Expenses WHERE CategoryId = '903000000032001'

ExpenseReports:

SELECT * FROM ExpenseReports
SELECT * FROM ExpenseReports WHERE FilterByStatus = 'Type.Approval,Status.Submitted'

ExpenseReportDetails:

SELECT * FROM ExpenseReportDetails WHERE ReportId = '903000000049001'

Trips:

SELECT * FROM Trips
SELECT * FROM Trips WHERE Status = 'approved'

TripDetails:

SELECT * FROM TripDetails WHERE TripId = '903000000074001'

Connection Properties

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider.

Property	Description
AuthScheme	The scheme used for authentication. Accepted entries are None or OAuth. Allowed values are: BASIC, NONE, NTLM, OAUTH, APIKEY, OAUTH_CLIENT, OAUTH_PASSWORD
CallbackURL	Identifies the URL users return to after authenticating to API via OAuth (Custom OAuth applications only).
Domain	Determines the domain where authentication calls will be sent to. Allowed values are: MIXPANEL, EU.MIXPANEL, IN.MIXPANEL, .COM, .EU, .COM.AU, .IN, COM, EU, COM.AU, IN, JP, COM.CN, CA, SA, .COM.CN, .JP, .CA, .SA
InitiateOAuth	Specifies the process for obtaining or refreshing the OAuth access token, which maintains user access while an authenticated, authorized user is working. Allowed values are: OFF, REFRESH, GETANDREFRESH
OAuthAccessToken	Specifies the OAuth access token used to authenticate requests to the data source. This token is issued by the authorization server after a successful OAuth exchange.
OAuthClientId	Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.
OAuthClientSecret	Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).
OAuthExpiresIn	Specifies the duration in seconds, of an OAuth Access Token's lifetime. The token can be reissued to keep access alive as long as the user keeps working.
OAuthRefreshToken	Specifies the OAuth refresh token used to request a new access token after the original has expired.
OAuthSettingsLocation	Specifies the location of the settings file where OAuth values are saved.
OAuthTokenTimestamp	Displays a Unix epoch timestamp in milliseconds that shows how long ago the current access token was created.
OAuthVerifier	Specifies a verifier code returned from the OAuthAuthorizationURL . Used when authenticating to OAuth on a headless server, where a browser can't be launched. Requires both OAuthSettingsLocation and OAuthVerifier to be set.
Organization	The Id associated with the specific Zoho Expense organization you wish to connect to. Get it from Organizations table.
Scope	Scope(s) to use when authenticating, that control access to specific information.