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.

Using OAuth for Authorizing Server side 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 the Data APIs.

How to register your Application with Zoho's Developer Console?

You will have to first register your application with Zoho's Developer console in order to get client ID and client secret. To register your application:

  • Enter the required details in the form. Add your Client Name, Client Domain and the Authorized Redirect URI 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.

Parameter Type Description
client_idUnique Identifier(Mandatory) The ID that was assigned to your app when you registered it.
response_typestring(Mandatory) code
redirect_uriURL(Mandatory) Your callback URL with authorization code and response token. This should be the same link which you used during registration.
scopestring(Mandatory) Specifies the scope allowed for your app. Has to be separated by commas.
Syntax:
Servicename.Scopename.Operation
Example: ZohoSheet.dataAPI.UPDATE,ZohoSheet.dataAPI.READ
access_typestringOffline/Online (By default it is taken as Online).
The Online access_type gives your application only the access_token which has limited validity.
The Offline access_type will give the application an access-token as well as a refresh_token.
promptstring"prompt=consent".
Prompts for user consent each time your app tries to access user credentials.
statestringA generated value that correlates the callback with its associated authorization request.
Example
GET oauth/v2/auth 
Host:: https://accounts.zoho.com

Query String:

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/support
&state=5466400890088961855
&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

#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 - Location of user(US or EU).
accounts-server - Zoho accounts server.

Prompting re-consent

You can prompt the user to re-authorize your app every time the user logs in by adding the "prompt=consent" parameter to the authentication request. When "prompt=consent" is included, the consent screen is displayed every time the user logs into your app. 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 can be made to receive an access token using which your app will receive the user credentials. 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.

Parameter Type Description
codeString(Mandatory) Authorization Code obtained from the initial request.
client_idUnique Identifier(Mandatory) The ID that was assigned to your app when you registered it.
client_secretString(Mandatory) Your app's secret.
Assigned when you register your app and available in your profile.
redirect_uriURL(Mandatory) Your callback URL with authorization code and response token.
grant_typestringauthorization_code
Example
POST oauth/v2/tokenHOST:: https://accounts.zoho.com 
POST oauth/v2/token
HOST:: https://accounts.zoho.com

Query String:

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/support

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

Sample Response

    {
        "expires_in":1000,
        "token_type":"Bearer",
        "refresh_token":"f246d233c04a4ccae5f...",
        "access_token":"12d865836f0df5ef81a..."
    }
                                   

expires_in - Time in milliseconds that the token remains valid.
token_type - Type of token. ("Bearer")
access_token - Access token for the user. This token can be used for the final API calls and have limited validity.
refresh_token - Refresh token to use when the token has timed out. This token is permanent and can be used multiple times to refresh the app and get a new access token.

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

How to use the Access Token?

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

Example
GET /api/v2/<rid>

Query String:

GET
HOST: https://sheet.zoho.com/api/v2/<rid>
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 REST APIs even when the user is not logged in. To request a refresh token, add "access_type=offline" to the authorization URL.

The refresh token will always be generated by the "prompt=consent". The max number of refresh token = 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,

Parameter Type Description
client_idUnique IdentifierThe ID that was assigned to your app when you registered it.
client_secretStringYour app's secret.​
Assigned when you register your app and available in your profile.
grant_typeStringrefresh_token
redirect_uriURLYour callback URL.
refresh_tokenstringThe refresh token provided along with the access token.

Example
POST https://accounts.zoho.com/oauth/v2/token

HOST:: https://accounts.zoho.com

Query String:

?refresh_token=1000.4069dacb5684b2df29b60ed6865e889f.fd1f173360871d254bcf902062390367
&grant_type=refresh_token
&client_id=1000.P4Z0WWOLFDGRT23126TYD3B4JWQ5TE
&client_secret=83r359de68c312ta5f1f06c3b1319ab98f59fa246d
&redirect_uri=https://www.zylker.com/support

You will now receive a new access token using which you can continue getting user credentials. This access token will also have limited validity.

Sample Response

{
    "expires_in":1000,
    "token_type":"Bearer",
    "access_token":"47d872536f0df5ef81a..."
}
                                    

expires_in - Time in milliseconds that the token remains valid.
token_type - Type of token. ("Bearer")
access_token - Access token for the user. This token can be used for the final API calls and have limited validity.

Revoking refresh token:

URL : https://accounts.zoho.com/oauth/v2/token/revoke.
Method : POST
Params : token=<refresh token>

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 APIs 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 : 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.

How to register your Application with Zoho's Developer Console?

You will have to first register your application with Zoho's Developer console in order to get client ID and client secret. To register your application:

  • Choose Javascript in Client type and then Enter the required details in the form. Add your Client Name, Client Domain, Authorized Redirect URI and the Javascript domain 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

URL : https://accounts.zoho.com/oauth/v2/auth

PARAMS :

Parameter Type Description
client_idUnique IdentifierObtained during registration.
response_typestringConstant
statestringThis will be sent back to the client. This is used to avoid CSRF attack.
redirect_uriURLThe URL to which Access Token will be sent. This should be the same one used during registration.
scopestringComma separated scopes for which access is needed.

Once the user clicks "Accept" in the user grant 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

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

Refreshing the token:

The Access Tokens have limited validity. Until then, the access token has unlimited usage. Once it expires, your app 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 an user grant page.

For this the user will have to be directed to the below URL with the following parameters,

URL : https://accounts.zoho.com/oauth/v2/auth/refresh

Params :

Parameter Type Description
client_idUnique IdentifierObtained during registration.
response_typestringConstant
statestringThis will be sent back to the client. This is used to avoid CSRF attack.
redirect_uriURLThe URL to which Access Token will be sent. This should be the same one used during registration.
scopestringComma separated scopes for which access is needed.

The success response will be as mentioned above.

The error response will be "error=client_not_granted" if the user tries refreshing the token without granting access for the entire session in the user grant page after loging in.

How to use the Access Token?

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

Example
GET /api/v2/<resourceid>

Query String:

GET
HOST: https://sheet.zoho.com/api/v2/<rid>
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: the client denied the permission for getting access token.

error=client_not_granted: the client has not given grant for that session.

error=prompt_required : the scopes mentioned are not granted by the user for that client. Only happens if extra scope is added for oauth/v2/auth/refresh instead of 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 applications access to an 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.

URL : https://accounts.zoho.com/oauth/v2/token/revoke.

Method : POST

Params : token=<access token>

API playground
access token Created with Sketch.