Get Records from Zoho Sheet

Table of Contents

Note:

  • Each time the zoho.sheet.getRecords integration task is executed, it triggers an API request in the back-end. This call is deducted from the external calls limit available for the service from which the task is executed, based on your pricing plan.
  • Only actual executions that receive a response (whether success or failure) are counted, not the number of times the task appears in the script. For example, if zoho.sheet.getRecords integration task is placed inside a for each task that iterates five times, the number of external calls consumed will be five, even though the task appears only once in the script. 

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.

Note: On average, it takes about a minute to index a file/folder after it is created or updated. This indexing time can increase if the queue size increases.  You will not get the expected result if you execute zoho.sheet.getRecords task before the Zoho Sheet file whose records you need to fetch is indexed.

Syntax

<response> = zoho.sheet.getRecords(<resource_id>,<worksheet_name>, <query_map>, <connection>);

where:

ParamsData typeDescription
<response>KEY-VALUEThe 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>
/ranges/<cell_range>

<worksheet_name>TEXTThe name of the worksheet from which the rows need to be fetched.
<query_map>KEY-VALUEThe 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:

  • In view of stopping new authtoken generation, a Zoho OAuth connection with appropriate scopes is mandatory in order for new integration tasks (created after the deadline specified in the post) to work as expected. Existing integration tasks will continue to work with or without the connections parameter until you manually delete the authtoken from accounts.
  • Scopes required for this task as mentioned in API docs - ZohoSheet.dataAPI.UPDATE, ZohoSheet.dataAPI.READ.
  • Refer to this post for the list of Zoho services that support the connections page.
  • Learn more about connections

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
The KEY-VALUE response returned by Zoho Sheet.
"eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41"
The TEXT that represents the ID of the Zoho Sheet file.
"Sheet1"
The TEXT that represents the name of the worksheet from which the record needs to be fetched.
queryData
The empty KEY-VALUE variable used to skip the <query_map> parameter
"sheet_connection"
The TEXT variable that represents the link name of the 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
The KEY-VALUE variable that holds the query parameters.
"criteria" 
"records_start_index" 
"count" 
The TEXT parameters, as given in the Zoho Sheet API.
"\"Lead Source\"=\"Trade Show\""
The TEXT that represents the criteria. The text entries - column name (Lead Source) and its value (Trade Show) are enclosed in double-quotes escaped with backslashes(\).

Note: 

  • 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:
    1. 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.
    2. 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.

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;

Response Format

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

Related Links