Introduction
Zoho Sheet is an online spreadsheet application that lets you create, edit, and share spreadsheets for collaboration. With Zoho Sheet Data APIs, you get programmatic access to read or update your spreadsheets in Zoho Sheet. You could list your workbooks, create a worksheet, set cell content, get content of a range and do a whole lot of things.
We also offer Tabular Data APIs where you could use Zoho Sheet as a Data Store. Your worksheet is considered as a table with the column names on the first row. You could add rows and fetch/update/delete rows based on a criteria.
Try out the APIs in real time using the right panel. This page provides you with a platform to have a hands on experience on Zoho Sheet's Data APIs.
The Zoho Sheet Data API uses the OAuth 2.0 protocol to authorize and authenticate calls. OAuth is an industry open standard for authorization. It provides secure access to protected resources thereby reducing the hassle of asking for a username and password every time a user logs in.
You can follow some basic steps to access Zoho Sheet's Data APIs using Oauth 2.0.
Using OAuth for authorizing Client side Application (CORS Request)
In recent times, users wish to customize the user interface according to their needs and show data accordingly. This can be achieved using APIs. But using API requires a server which calls the Zoho Server API to present the data. This needs a server which would store user credentials and authenticate each request against the user and server.
This complexity can be avoided using CORS (Cross-Origin Resource Sharing), where request from customer site will be sent to the Zoho service directly via the browser to present the data at convenience. To achieve this, the customer site needs an access token to invoke the API. It is explained below how user can get access token directly from browser.
Note:
1.CORS request for HTTP POST requires minor changes. The parameter named "method" needs to be appended in request URL and should be removed from the request body and all other parameters should remain in the request body. No changes required for HTTP GET as all parameters are present in request URL.
2.We do not recommend using this method, as the access token is sent to your redirect URI using the HTTP GET method, which poses security concerns. Since the access token is passed via the URI, there is a chance the access token is intercepted by a hacker and misused.
Table of Contents
How to register your application with Zoho's API Console?
You will have to first register your application with Zoho's API console in order to get "Client ID" and "Client Secret". To register your application, Go to Zoho API Console and click "Get Started".
Note : If you are directly shown your API credentials page, click "Add Client ID".
Choose "Client based Applications" and add your Client Name, Homepage URL, Authorized Redirect URI and your Javascript Domain in the form and click "Create".
You will be provided with a set of OAuth 2.0 credentials such as a client ID and client secret that are known to both Zoho and your application.
How to get Access Token?
To get Access token the user should be directed to the authorization URL with the following parameters
URI Endpoint : https://accounts.zoho.com/oauth/v2/auth
HTTP Request type : GET
PARAMS :
Parameter | Parameter type | Description |
client_id | Mandatory | Obtained from registering your application at the Zoho Accounts API console. |
response_type | Mandatory | Value must be 'token'. |
redirect_uri | Mandatory | The URI endpoint that Zoho Accounts will redirect the web browser to with the access token after authorizing the client. Make sure the redirect URI is the same as the one provided while registering your client. |
scope | Mandatory |
Specifies the scope allowed for your application. Has to be separated by commas. Syntax: Servicename.Scopename.OPERATION Example: ZohoSheet.dataAPI.UPDATE, ZohoSheet.dataAPI.READ |
state | Mandatory | This will be sent back to the client. This is used to avoid CSRF attack. |
https://accounts.zoho.com/oauth/v2/auth
?client_id=1000.P4Z0WWOLFDGRT23126TYD3B4JWQ5TE
&response_type=token
&scope=ZohoSheet.dataAPI.UPDATE,ZohoSheet.dataAPI.READ
&redirect_uri=https://www.zylker.com/oauthredirect
&state=12345
The user is now shown a "user consent page".
Once the user clicks "Accept" in the user consent page, then Zoho redirects the user back to your site with the following parameters appended in the URL. If the user clicks "Deny" then the response would be "error=access_denied".
#state=dcdvd&access_token=1000.b21865a6113cc32bae927364508ebe70.23e2399beac4ae66abf542ba4a4ed37c&expires_in=3600000&location=us&api_domain=http%3A%2F%2Fexample.com
state - Which is provided during request.
access_token - Access token to invoke APIs.
expires_in - Time for which token is valid, in milliseconds.
location - Location of user(US or EU).
api_domain - API domain to make API request.
User can select option "Grant access for the entire session" in the user grant page. Once the user accepts, the access token will be generated for that user session without prompting in the UI. Once selected, this refreshing will work fine until the user logs out.
https://www.zylker.com/oauthredirect/#state=12345&access_token=1000.b21865a6113cc32bae927364508ebe70.23e2399beac4ae66abf542ba4a4ed37c&expires_in=3600&location=us&api_domain=https%3A%2F%2Faccounts.example.com
- state - Which is provided during request.
- access_token - A client-authorized key that lets the client access protected resources from Zoho Sheet. The client can make API requests using this access token for up to an hour after the creation of the token.
- location - User's domain location.
- expires_in - Time in seconds that the access token remains valid.
- api_domain - The API domain URI which the client must use to make all API requests.
User can select option "Grant access for the entire session" in the user grant page. Once the user accepts, the access token will be generated for that user session without prompting in the UI. Once selected, this refreshing will work fine until the user logs out.
Refreshing the token:
The Access Tokens have limited validity. Until then, the access token has unlimited usage. Once it expires, your application will have to either refresh the token or generate a new access token. Refreshing the access token can only be possible if the user has already selected "Grant access for the entire session" before and will work fine until the user logs out. The only difference between refreshing the token and generating a new access token is that refreshing the token does not prompt in the UI whereas generating a new access token redirects the user to the user grant page.
For Refreshing the token the user will have to be directed to the below URL with the following parameters,
URI Endpoint : https://accounts.zoho.com/oauth/v2/auth/refresh
HTTP Request type : GET
PARAMS :
Parameter | Parameter type | Description |
client_id | Mandatory | Obtained from registering your application at the Zoho Accounts API console. |
response_type | Mandatory | Value must be 'token'. |
state | Mandatory | This will be sent back to the client. This is used to avoid CSRF attack. |
redirect_uri | Mandatory | The URI endpoint that Zoho Accounts will redirect the web browser to with the access token after authorizing the client. Make sure the redirect URI is the same as the one provided while registering your client. |
scope | Mandatory |
Specifies the scope allowed for your application. Has to be separated by commas. Syntax: Servicename.Scopename.OPERATION Example: ZohoSheet.dataAPI.UPDATE, ZohoSheet.dataAPI.READ |
https://accounts.zoho.com/oauth/v2/auth/refresh
?client_id=1000.P4Z0WWOLFDGRT23126TYD3B4JWQ5TE
&response_type=token
&scope=ZohoSheet.dataAPI.UPDATE,ZohoSheet.dataAPI.READ
&redirect_uri=https://www.zylker.com/oauthredirect
&state=12345
The success response will be as mentioned above.
The error response will be as mentioned here
How to use the Access Token?
Send the access token as a header when you call a Zoho Sheet REST API.
URI Endpoint : https://sheet.zoho.com/api/v2/<resource_id>
HTTP Request type : POST
Query String :
https://sheet.zoho.com/api/v2/<resource_id>?<parameters>
Header :
Authorization:Zoho-oauthtoken<space><access token>
Possible Error Responses:
The error string is sent as a parameter which contains the following responses.
- error=access_denied : If the client denied the permission for getting access token.
- error=client_not_granted : If the user tries refreshing the token without granting access for the entire session in the user grant page after logging in or may have signed out of the session.
- error=prompt_required : If the scopes mentioned are not granted by the user for that client. The error occurs when you add scopes in "oauth/v2/auth/refresh" that are not in "oauth/v2/auth".
Revoking token:
Access token can be revoked as like Refresh token. There are a few reasons you might need to revoke an application's access to a users account.
- 1.The user explicitly wishes to revoke the applications access, such as if they have found an application they no longer want to use.
- 2.The developer wants to revoke all user tokens for their application.
- 3.The developer deleted their application.
- 4.The user had determined the application is compromised or malicious, and want to disable it.
URI Endpoint : https://accounts.zoho.com/oauth/v2/token/revoke
HTTP Request type : POST
Params : token=<access token>
Using OAuth for authorizing Browserless(or)Non-Browser application
Non-browser applications are applications that don't have a user agent, such as a web browser. OAuth can be implemented with Zoho on non-browser applications as well.
Table of Contents
How to register your application with Zoho's API Console?
You will have to first register your application with Zoho's API console in order to get "Client ID" and "Client Secret". To register your application, Go to Zoho API Console and click "Get Started".
Note : If you are directly shown your API credentials page, click "Add Client ID".
Choose "Non-browser Applications" and add your Client Name and Homepage URL in the form and click "Create".
You will be provided with a set of OAuth 2.0 credentials such as a client ID and client secret that are known to both Zoho and your application.
Initiation Request
The client sends an Initiation request to Zoho Accounts on behalf of the user in order to receive the "device_code" and "verification_uri_complete".
URI Endpoint : https://accounts.zoho.com/oauth/v3/device/code
HTTP Request type : POST
PARAMS :
Parameter | Parameter type | Description |
client_id | Mandatory | Obtained from registering your application at the Zoho Accounts API console. |
grant_type | Mandatory | Value must be 'device_request'. |
scope | Mandatory |
Specifies the scope allowed for your application. Has to be separated by commas. Syntax: Servicename.Scopename.OPERATION Example: ZohoSheet.dataAPI.UPDATE, ZohoSheet.dataAPI.READ |
access_type | Optional |
offline/online (By default it is taken as online). The "online" access_type gives your application only the access_token which has limited validity. Whereas the "offline" access_type will give your application an access_token as well as a refresh_token. Once the access token expires you can use the refresh token to regenerate them. |
https://accounts.zoho.com/oauth/v3/device/code
?client_id=1000.P4Z0WWOLFDGRT23126TYD3B4JWQ5TE
&scope=ZohoSheet.dataAPI.UPDATE,ZohoSheet.dataAPI.READ
&grant_type=device_request
&access_type=offline
{
"user_code":"WLKU-4502",
"device_code":"1024.d8aqef26b56e5...",
"interval":30000,
"verification_uri_complete":"https://accounts.example.com/oauth/v3/device/setCode?devcode=QUFqQysdwMzsdvUz",
"expires_in":300000,
"verification_url":"https://accounts.example.com/oauth/v3/device"
}
- user_code - Unique code to identify the client and the requested scopes.
- device_code - Unique code to identify the limited input device requesting for authorization. The code will expire in five minutes and can be used only once.
- interval - Time duration the limited input device has to wait between polling requests, in milliseconds.
- verification_uri_complete - The URL that the user must navigate to in a browser to grant or deny access.
- expires_in - Time taken for the JSON response to expire, in milliseconds.
Once the user navigates to the "verification_uri_complete" in the browser, the user is now shown a "user consent page".
If the user clicks "Accept", your application can now request Zoho for an access token using the "device_code" received as a response for the initiation request.
Polling Request:
After accepting permission in the user consent page, The client must set up a Polling request for an access token on the device.
URI Endpoint : https://accounts.zoho.com/oauth/v3/device/token
HTTP Request type : POST
PARAMS :
Parameter | Parameter type | Description |
client_id | Mandatory | Obtained from registering your client at the Zoho Accounts API console. |
grant_type | Mandatory | Value must be 'device_token'. |
client_secret | Mandatory | Obtained from registering your client at the Zoho Accounts API console. The client secret must be kept confidential and should only be known to you and Zoho. |
code | Mandatory | The device code you received as a response for the initiation request. |
https://accounts.zoho.com/oauth/v3/device/token
?client_id=1000.P4Z0WWOLFDGRT23126TYD3B4JWQ5TE
&client_secret=83r359de68c312ta5f1f06c3b1319ab98f59fa246d
&grant_type=device_token
&code=10124.82ccce6901asve34c7588cc2e23f33d.14314d62qewdq321fc9f0a
Note : Only if "access_type=offline" is included in the initiation request, your response here will have the "refresh_token".
{
"access_token": "1004.a3fdad1f7115c...",
"refresh_token": "1004.8bb172382372d...",
"api_domain": "https://api.example.com",
"token_type": "Bearer",
"expires_in": 3600
}
- access token - A client-authorized key that lets the client access protected resources from Zoho. The client can make API requests using this access token for up to an hour after the creation of the token.
- refresh_token - Refresh token to use when the access token has timed out. This token is permanent and can be used multiple times to refresh the application and get a new access token.
- api_domain - The API domain URI which the client must use to make all API requests.
- token_type - Type of token. ("Bearer")
- expires_in - Time in seconds that the access token remains valid.
How to use the Access Token?
Send the access token as a header when you call a Zoho Sheet REST API.
URI Endpoint : https://sheet.zoho.com/api/v2/<resource_id>
HTTP Request type : POST
Query String :
https://sheet.zoho.com/api/v2/<resource_id>?<parameters>
Header :
Authorization:Zoho-oauthtoken<space><access token>
How to get and use the Refresh Token?
A refresh token provides your app access to Data APIs even when the user is not logged in. To request a refresh token, you must add "access_type=offline" in the initiation request.
The Access Tokens have limited validity. Until then, the access token has unlimited usage. Once it expires, your app will have to use the refresh token to request for a new access token.
For this new request, the parameters to be included are,
URI Endpoint : https://accounts.zoho.com/oauth/v2/token
HTTP Request type : POST
PARAMS :
Parameter | Type | Description |
client_id | Mandatory | Obtained from registering your application at the Zoho Accounts API console. |
client_secret | Mandatory | Obtained from registering your application at the Zoho Accounts API console. The client secret must be kept confidential and should only be known to you and Zoho. |
grant_type | Mandatory | Value must be 'refresh_token'. |
refresh_token | Mandatory | The refresh token provided along with the access token in the polling request. |
https://accounts.zoho.com/oauth/v2/token
?refresh_token=1000.4acb52df20e889f.f170bcf62367
&grant_type=refresh_token
&client_id=1000.P4Z0WWOLFDGRT23126TYD3B4JWQ5TE
&client_secret=83r359de68c312ta5f1f06c3b1319ab98f59fa246d
You will now receive a new access token using which you can continue with Zoho Sheet's Data APIs. This access token will also have limited validity.
{
"access_token":"47d872536f0df5ef81a...",
"api_domain":"https://api.example.com",
"token_type":"Bearer",
"expires_in":1000
}
- access_token - A client-authorized key that lets the client access protected resources from Zoho Sheet. The client can make API requests using this access token for up to an hour after the creation of the token.
- api_domain - The API domain URI which the client must use to make all API requests.
- token_type - Type of token. ("Bearer")
- expires_in - Time in seconds that the access token remains valid.