Get Records from Zoho Sheet
Table of Contents
Description
The zoho.sheet.getRecords task is used to fetch data from the specified worksheet of a Zoho Sheet file. This task is based on the Zoho Sheet API - Fetch records from Worksheet.
Syntax
<response> = zoho.sheet.getRecords(<resource_id>,<worksheet_name>, <query_map>, <connection>);
Params | Data type | Description |
<response> | KEY-VALUE | The details of the records that will be fetched, and the status of the executed task. |
<resource_id> | TEXT | The ID of the Zoho Sheet file from which the records need to be fetched. Note: The resource_id can be obtained from the URL of the Zoho sheet file. The URL is in the following format: https://sheet.zoho.com/sheet/open/<resource_id>/sheets/<worksheet_name> |
<worksheet_name> | TEXT | The name of the worksheet from which the rows need to be fetched. |
<query_map> | KEY-VALUE | The parameters, as given in the Zoho Sheet API - Fetch records from worksheet. To ignore querying using this param, specify an empty map. |
<connection> | TEXT | The link name of the connection. Note:
|
- The resource_id and worksheet_name can be obtained from the URL of the Zoho Sheet file.
URL format: https://sheet.zoho.com/sheet/open/<resource_id>/sheets/<worksheet_name>/ranges/<cell_range> - This task can only fetch up to 1000 rows. To fetch more than 1000 rows, you will need to use this task multiple times. For example, to fetch 1500 records, 2 zoho.sheet.getRecords tasks are required; one to fetch the first 1000 rows and the second task to fetch the remaining 500 records.
Example 1: Fetch Records
The following script fetches data from the worksheet - Sheet1 of the Zoho Sheet file with ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41.
queryData = Map(); response = zoho.sheet.getRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41","Sheet1", queryData, "sheet_connection");
where:
response
"eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41"
"Sheet1"
queryData
"sheet_connection"
Example 2: Fetch Records based on criteria
The following script fetches the first 25 rows that have the value - Trade Show in the column - Lead Source from the worksheet - Sheet1 of the Zoho Sheet file with ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41.
queryValue = Map(); queryValue.put("criteria","\"Lead Source\"=\"Trade Show\""); queryValue.put("records_start_index","1"); queryValue.put("count","25"); response = zoho.sheet.getRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41", "Sheet1", queryValue, "sheet_connection");
where:
queryValue
"criteria" "records_start_index" "count"
"\"Lead Source\"=\"Trade Show\""
- If the values of the <query_map> contain text entries, they need to be enclosed in double-quotes escaped by backslashes.
Example: Name = John Watson is provided as \"Name\" = \"John Watson\". - The following operators can be used to fetch rows that satisfy multiple criteria:
- and - Used between two criteria to fetch the rows that satisfy both of the specified conditions.
Example: The value - "\"Lead Source\"=\"Trade Show\" and \"User Count\">200" against the key - criteria in <query_map>, fetches all the rows that have the value - Trade Show in the column - Lead Source and have a value greater than 200 in the column - User Count. - or - Used between two criteria to fetch the rows that satisfy either or both of the specified conditions.
Example: The value - "\"Lead Source\"=\"Trade Show\" or \"User Count\">200" against the key - criteria in <query_map>, fetches all the rows that either have the value - Trade Show in the column - Lead Source, or have a value greater than 200 in the column - User Count or both.
- and - Used between two criteria to fetch the rows that satisfy both of the specified conditions.
Example 3: Fetch more than 1000 records
The following script fetches the first 1500 rows of the Zoho Sheet file with ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41.
queryData = Map(); queryData.put("records_start_index",1); queryData.put("count",1000); response1 = zoho.sheet.getRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41", "Sheet1", queryData, "sheet_connection"); inforesponse1; queryData = Map(); queryData.put("records_start_index", 1001); queryData.put("count", 500); response2 = zoho.sheet.getRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41", "Sheet1", queryData, "sheet_connection"); info response2;
Success Response
The success response will be returned in the following format:
{
"method": "worksheet.records.fetch",
"records": [
{
"Lead Source": "Trade Show",
"Email": "shawn@zylker.com",
"row_index": 2,
"Name": "Shawn"
},
{
"Lead Source": "Advertisement",
"Email": "brent.r@gmail.com",
"row_index": 3,
"Name": "Brent"
}
],
"status": "success"
}
Failure Response
The failure response for invalid resource ID will be returned in the following format:
{
"error_message": "The workbook does not exists",
"error_code": 2862
}The failure response for incorrect or non-existent worksheet name will be returned in the following format:
{
"error_message": "The sheet does not exists",
"method": "worksheet.jsondata.append",
"error_code": 2863,
"status": "failure"
}The failure response for invalid criteria specified in the query_map will be returned in the following format:
{
"error_message": "Mentioned criteria is not valid",
"method": "worksheet.records.fetch",
"error_code": 2895,
"status": "failure"
}