Export

Tags:  

Export




1. It is mandatory to use HTTPS in all API requests instead of HTTP request. HTTP is not supported

2. Always use https://reportsapi.zoho.com as the API request URL.

Using this API users can export/pull data from tables or reports (pivots, charts etc.,) in different formats.

Sample

Exports the records of all the employees in the finance department in CSV Format.

URL
https://reportsapi.zoho.com/api/abc@zoho.com/EmployeeDB/EmployeeDetails?ZOHO_ACTION=EXPORT
&ZOHO_OUTPUT_FORMAT=CSV&ZOHO_ERROR_FORMAT=XML
&authtoken=g38sl4j4856guvncrywox8251sssds&ZOHO_API_VERSION=1.0

Additional Control Parameters Sent as POST parameters

&ZOHO_CRITERIA=("Department"='Finance')&ZOHO_DELIMITER=0

ZOHO_CRITERIA is encoded for the url format. Value without encoding is below:

ZOHO_CRITERIA=("Department" = 'Finance')

Query String Parameters

In the query string of the URL include the following parameters

  • ZOHO_ACTION parameter should be "EXPORT".

    Note:
    Value of ZOHO_ACTION parameter should be in the same case(UPPER CASE) as given in this document.

  • ZOHO_OUTPUT_FORMAT should be the required format. The supported formats are
    • CSV
    • XML
    • JSON
    • HTML
    • PDF
    • IMAGE (applicable only for Charts)

For explanation about other mandatory query string parameters such as ZOHO_ERROR_FORMAT, refer to this link.

Criteria

If the ZOHO_CRITERIA parameter is not sent, then all the rows are exported. If criteria is sent the rows matching the criteria alone are exported. For more details about the format for the criteria refer this link.

Response Formats

CSV Format

To export in CSV format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be CSV

Additional parameters (preferably to be sent in the body of the POST request).

Parameter
Possible Values
Description

ZOHO_OUTPUT_FORMAT

CSV


ZOHO_DELIMITER

Value between 0 - 3

0 - COMMA

1 - TAB

2 - SEMICOLON

3 - SPACE

The delimiter character used for separating the fields in a row in the CSV.

ZOHO_RECORD_DELIMITER

Value between 0 - 2

0 - DOS

1 - UNIX

2 - MAC

The record delimiter (newline character) to use.

ZOHO_QUOTED

Value between 0 - 1

0 - SINGLE

1 - DOUBLE

The quote character to use for quoting the values.

ZOHO_INCLUDE_HEADER

true / false

true - To include the column names in the first row of the CSV exported.

false - To not include the column names in the CSV exported.

ZOHO_SHOW_HIDDENCOLS

true / false

Controls where the columns that have been hidden in the table/report have to be exported.

true - To include the hidden columns of the table/report in the data exported

false - To not include the hidden columns of the table/report in the data exported.


Sample Success Response:

The following is a sample Export of data in a Table, in CSV format:

"Name","Department","Date Of Birth"
"John","Finance","12 May 1972"
"Joan","Admin","15 June 1975"

JSON Format

To export in JSON format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be JSON

Additional parameters (preferably to be sent in the body of the POST request).

Parameter
Possible Values
Description

ZOHO_SHOW_HIDDENCOLS

true / false

Controls where the columns that have been hidden in the table/report have to be exported.

true - To include the hidden columns of the table/report in the data exported

false - To not include the hidden columns of the table/report in the data exported.

ZOHO_CALLBACK_FUNCTION

Name of the json callback function

Processes JSON response elsewhere in the JavaScript code on the page

Sample Success Response:

The following is a sample Export of data in a Table, in JSON format:

{ 

"response":
{
"uri": "/api/abc@zoho.com/EmployeeDB/EmployeeDetails",
"action": "EXPORT",
"result":
{
"column_order":["Name","Department","Date Of Birth"],
"rows":[
["John","Finance","12 May 1972"],
["Joan","Admin","15 June 1975"]
]
}
}
}

XML Format

To export in XML format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be XML

Additional parameters (preferably to be sent in the body of the POST request).

Parameter
Possible Values
Description

ZOHO_SHOW_HIDDENCOLS

true / false

Controls where the columns that have been hidden in the table/report have to be exported.

true - To include the hidden columns of the table/report in the data exported

false - To not include the hidden columns of the table/report in the data exported.

Sample Success Response:

The following is a sample Export of data in a Table, in XML format

<?xml version="1.0" encoding="UTF-8" ?>
<response uri="/api/abc@zoho.com/EmployeeDB/EmployeeDetails" action="EXPORT">
<result>
<rows>
<row>

<column name="Name">Gary</column>
<column name="Date Of Birth">12-Jun-1980</column>
<column name="Basic">10000</column>
<column name="Country">USA</column>
</row>
<row>

<column name="Name">John</column>
<column name="Date Of Birth">12-Jun-1981</column>
<column name="Basic">10000</column>
<column name="Country">Canada</column>
</row>
<row>

<column name="Name">Joan</column>
<column name="Date Of Birth">12-Jun-1982</column>
<column name="Basic">10000</column>
<column name="Country">Mexico</column>
</row>
</rows>
</result>
</response>

HTML Format

To export in HTML format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be HTML

Additional parameters (preferably to be sent in the body of the POST request).

Parameter
Possible Values
Description

ZOHO_SHOW_HIDDENCOLS

true / false

Controls where the columns that have been hidden in the table/report have to be exported.

true - To include the hidden columns of the table/report in the data exported

false - To not include the hidden columns of the table/report in the data exported.


PDF Format

To export in PDF format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be PDF

Additional parameters (preferably to be sent in the body of the POST request).

Parameter
Possible Values
Description

ZOHO_PAPERSIZE

Value between 0 - 5

0 - LETTER

1 - LEGAL

2 - TABLOID

3 - A3

4 - A4

5 - AUTO

The size of the paper.

ZOHO_SHOW_TITLE

Value between 0 - 2

0 - AT TOP

1 - AT BOTTOM

2 - NONE

Controls the title positioning.

ZOHO_SHOW_DESC

Value between 0 - 2

0 - AT TOP

1 - AT BOTTOM

2 - NONE

Controls the description positioning.

ZOHO_EXPORT_LANGUAGE

Value between 0 - 4

0 - ENGLISH

1 - EUROPEAN

2 - CHINESE

3 - JAPANESE

4 - KOREAN

Default - ENGLISH

PDF will be rendered using the specified language

ZOHO_PAPERSTYLE

Portrait / Landscape


ZOHO_SHOW_HIDDENCOLS

true / false

Controls where the columns that have been hidden in the table/report have to be exported.

true - To include the hidden columns of the table/report in the data exported

false - To not include the hidden columns of the table/report in the data exported.

ZOHO_SELECTED_COLUMNS

List of comma separated column names

Controls the column names that need to be exported. If it is not given then all the columns, in the table/report, are exported.

Margin Settings:

ZOHO_TOPMARGIN

Decimal values between 0 to 1

The margin in inches for that edge. Can be decimal between 0 to 1 (like 0.5).

ZOHO_BOTTOMMARGIN

ZOHO_LEFTMARGIN

ZOHO_RIGHTMARGIN

Header/Footer Settings:

ZOHO_HEAD_LEFT

Value between 0 - 5

0 - Leave it blank

1 - Include Title

2 - Current Date/Time

3 - Include Page number in the format "Page #"

4 - Include page number in the format "Page # Of #"

5 - CUSTOM - Include custom text in footer

The header or footer value that needs to be generated for each page at that particular position.

ZOHO_HEAD_RIGHT

ZOHO_HEAD_CENTER

ZOHO_FOOT_LEFT

ZOHO_FOOT_RIGHT

ZOHO_FOOT_CENTER

Custom Header/Footer value

ZOHO_HEAD_LEFT_TEXT

Custom text.

If any of the header/footer setting is 5 (.ie, CUSTOM) then the corresponding custom value/text should be passed.

ZOHO_HEAD_RIGHT_TEXT

ZOHO_HEAD_CENTER_TEXT

ZOHO_FOOT_LEFT_TEXT

ZOHO_FOOT_RIGHT_TEXT

ZOHO_FOOT_CENTER_TEXT


IMAGE Format

To export in IMAGE format, in query parameters, the value for ZOHO_OUTPUT_FORMAT should be IMAGE. You can only export Charts in Image format.

Additional parameters (preferably to be sent in the body of the POST request).

Parameter
Possible Values
Description

ZOHO_WIDTH

<number>

The width of the image .

ZOHO_HEIGHT

<number>

The height of the image

ZOHO_TITLE

true / false

Controls whether the title of the report is to be added to the image.

true - Include the title.
false - Do not include title.

ZOHO_DESCRIPTION

true/false

Controls whether the description of the report is to be added to the image.

true - Include the description.
false - Do not include description.

ZOHO_SHOWLEGEND

true / false

Controls whether the legend is to be included in the image generated.

true - Include the legend in the image.
false - Do not include the legend in the image.

ZOHO_IMAGE_FORMAT

png / jpg

The format of the exported image. It could be either in PNG or JPG formats.


JSON Callbacks - JSONP
:

Zoho Reports API supports Callback function for handling API responses asynchronously. This is supported in Export API calls, when the output format is chosen as JSON.

The Export API method call, with JSON output format, supports an additional parameter called ZOHO_CALLBACK_FUNCTION to enable callback. Using the Callback function, developers can invoke the Export API request inside the <script> tag and process the JSON response using the CALLBACK function elsewhere in the JavaScript code on the page.

Example:

1. Define the callback function

<script>
function sampleFunction(resp)
{
var response = resp.response;
var result = response.result;
var rows = result.rows;
for(i=0;i<rows.length;i++)
{
var row = rows[i];
console.log(row);
...
}
...
}

</script>

2. Invoke the api request with output format as JSON and ZOHO_CALLBACK_FUNCTION=sampleFunction

<script src="">https://reportsapi.zoho.com/api/<loginemailaddress>/<DB>/<Table>?ZOHO_ACTION=EXPORT&ZOHO_OUTPUT_FORMAT=JSON&ZOHO_ERROR_FORMAT=XML&authtoken=<your auth token>&ZOHO_API_VERSION=1.0&ZOHO_CALLBACK_FUNCTION=sampleFunction">
</script>

When the page is loaded, the response will be returned as,

sampleFunction( 
{
"response":
{
"uri": "/api/abc@zoho.com/EmployeeDB/EmployeeDetails",
"action": "EXPORT",
"result":
{
column_order:["Name","Department","Date Of Birth"],
rows:[
["John","Finance","12 May 1972"],
["Joan","Admin","15 June 1975"]
]
}
}
} );

Export Using SQL:

Zoho Reports has implemented the Zoho CloudSQL technology as an extension to its HTTP Web API. Using the  Zoho Reports HTTP API, users can query the database by providing SQL queries.

Refer to the documentation on  Zoho Reports CloudSQL for more details on how to use SQL SELECT query to fetch data from Zoho Reports.





     RSS of this page