s Zoho Sheet - DATA API help
API Playground

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.

API Usage Limit

As of now there is no daily or monthly usage limit for Zoho Sheet APIs, but we have a limitation on per minute API calls to avoid overloading our server. If that limit is exceeded, APIs on that document will not work for the next 5 minutes. This limit differs from API to API and cannot be increased.

Using OAuth for authorizing Server-based application

To use the APIs you can create an access token by following the steps mentioned below. Once you've created an access token, you can read, write or insert sheets/cells using Zoho Sheet's Data APIs.

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 "Server based Applications" and add your Client Name, Homepage URL and the Authorized Redirect URI 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 Authorization?

Next, the user should be directed to the authorization URL. The authorization URL should contain the following parameters.

URI Endpoint : https://accounts.zoho.com/oauth/v2/auth

HTTP Request type : GET

PARAMS :

Parameter Parameter type Description
client_idMandatoryObtained from registering your application at the Zoho Accounts API console.
response_typeMandatoryValue must be 'code'.
redirect_uriMandatory The URI endpoint that Zoho Accounts will redirect the web browser to with the authorization code after authorizing the client. Make sure the redirect URI is the same as the one provided while registering your client.
scopeMandatory 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_typeOptional 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.
promptOptionalValue must be 'consent'. If this parameter is included in the query, every time you generate an OAuth token, the user's consent will be mandatory.
state Optional A generated value that correlates the callback with its associated authorization request.

To receive a refresh token, include access_type=offline and prompt=consent in this request.

Sample Request

https://accounts.zoho.com/oauth/v2/auth
?response_type=code
&client_id=1000.P4Z0WWOLFDGRT23126TYD3B4JWQ5TE
&scope=ZohoSheet.dataAPI.UPDATE,ZohoSheet.dataAPI.READ
&redirect_uri=https://www.zylker.com/oauthredirect
&state=123
&access_type=offline
&prompt=consent

Once the user is directed to the authorization URL, the user is now shown a "user consent page".

If the user clicks "Accept" then Zoho redirects the user back to your site with an Authorization code appended in the URL. Your application can now request Zoho for an access token using that authorization code. The code generated will be of one time use. Once a request is made with the code, it will be deleted.

Sample Response

https://www.zylker.com/oauthredirect/?state=123&code=1000.6d7b960a6bcbb74a383ea9b3ef8081be.525f737f496b0eb62f7ff2fbd7690770&location=us&accounts-server=https%3A%2F%2Faccounts.example.com

  • state - Which is provided during request.
  • code - Authorization code used to request access.
  • location - User's domain location.
  • accounts-server - Zoho Accounts server.

Prompting re-consent

You can prompt the user to re-authorize your application every time the user logs in by adding the "prompt=consent" parameter to the authentication request. When "prompt=consent" is included, the user consent page is displayed every time the user logs into your application. For this reason, include "prompt=consent" only when necessary.

How to get the Access Token?

Once your application receives the Authorization code, a new request should be made to receive an access token. This request should be made from server along with client secret. The client secret should be completely hidden from the end user due to security reasons. This is an important step in the entire process and so be careful while setting the parameters for the same.

URI Endpoint : https://accounts.zoho.com/oauth/v2/token

HTTP Request type : POST

PARAMS :

Parameter Parameter type Description
codeMandatoryThe authorization code you received as a response for the authentication request.
client_idMandatoryObtained from registering your application at the Zoho Accounts API console.
client_secretMandatoryObtained 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.
redirect_uriMandatoryThe URI endpoint that Zoho Accounts will redirect the web browser to. Make sure the redirect URI is the same as the one provided while registering your client.
grant_typeMandatoryValue must be 'authorization_code'.
Sample Request

https://accounts.zoho.com/oauth/v2/token
?code=1000.adfcca4c2be2f08b0ce82a54f6343356.ba532585103af6f12a0f243
&grant_type=authorization_code
&client_id=1000.P4Z0WWOLFDGRT23126TYD3B4JWQ5TE
&client_secret=83r359de68c312ta5f1f06c3b1319ab98f59fa246d
&redirect_uri=https://www.zylker.com/oauthredirect

Once the request along with the authorization code is sent, Zoho will issue a response to your application which gives you the following information.

Note : Only if "access_type=offline" and "prompt=consent" is included in the authentication request, your response here will have the "refresh_token".

Sample Response

{
"access_token":"12d865836f0df5ef81a...",
"refresh_token":"f246d233c04a4ccae5f...",
"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.
  • 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.

You can store this data so that there is no need for authorization each time this user accesses your app.

Note : Using a refresh token a client can create up to ten access tokens in a span of ten minutes. If the limit is reached, the access token creation will be blocked for the rest of the ten minutes.

How to use the Access Token?

Send the access token as a header when you call a Zoho Sheet Data API.

URI Endpoint : https://sheet.zoho.com/api/v2/<resource_id> 
HTTP Request type : POST
Example

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?

In your request for access you can request a refresh token to be returned along with the access 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" and "prompt=consent" in the authorization request.

The maximum number of refresh token is 20. Once the limit is reached the first refresh token in terms of time will be deleted.

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_idMandatoryObtained from registering your application at the Zoho Accounts API console.
client_secretMandatory 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_typeMandatoryValue must be 'refresh_token'.
refresh_tokenMandatoryThe refresh token provided along with the access token in this request.

Sample 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.

Sample Response

{
"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.

Revoking refresh token:

URI Endpoint : https://accounts.zoho.com/oauth/v2/token/revoke
HTTP Request type : POST
Params : token=<refresh token>

Using OAuth for authorizing Client-based 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.

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_idMandatoryObtained from registering your application at the Zoho Accounts API console.
response_typeMandatoryValue must be 'token'.
redirect_uriMandatory 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.
scopeMandatory Specifies the scope allowed for your application. Has to be separated by commas.
Syntax:
Servicename.Scopename.OPERATION
Example: ZohoSheet.dataAPI.UPDATE, ZohoSheet.dataAPI.READ
stateMandatoryThis will be sent back to the client. This is used to avoid CSRF attack.
Sample Request

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".

Sample Response

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_idMandatoryObtained from registering your application at the Zoho Accounts API console.
response_typeMandatoryValue must be 'token'.
stateMandatoryThis will be sent back to the client. This is used to avoid CSRF attack.
redirect_uriMandatory 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.
scopeMandatory Specifies the scope allowed for your application. Has to be separated by commas.
Syntax:
Servicename.Scopename.OPERATION
Example: ZohoSheet.dataAPI.UPDATE, ZohoSheet.dataAPI.READ

Sample Request

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
Example

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.

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_idMandatoryObtained from registering your application at the Zoho Accounts API console.
grant_typeMandatoryValue must be 'device_request'.
scopeMandatory 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_typeOptional 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.
Sample Request

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

Sample Response

{
"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_idMandatoryObtained from registering your client at the Zoho Accounts API console.
grant_typeMandatoryValue must be 'device_token'.
client_secretMandatoryObtained 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.
codeMandatoryThe device code you received as a response for the initiation request.
Sample 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".

Sample Response

{ 
"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
Example

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_idMandatoryObtained from registering your application at the Zoho Accounts API console.
client_secretMandatory 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_typeMandatoryValue must be 'refresh_token'.
refresh_tokenMandatoryThe refresh token provided along with the access token in the polling request.

Sample 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.

Sample Response

{
"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.

API Usage Limit

As of now there is no daily or monthly usage limit for Zoho Sheet APIs, but we have a limitation on per minute API calls to avoid overloading our server. If that limit is exceeded, APIs on that document will not work for the next 5 minutes. This limit differs from API to API and cannot be increased.

API playground
access tokenCreated with Sketch.