Find in Zoho Sheet

Table of Contents

Note:

  • Each time the zoho.sheet.find 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.find 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.find task is used to find the specified text in a row, column, worksheet, or workbook of Zoho Sheet file. This task is based on the Zoho Sheet API - Find.

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.find task before the Zoho Sheet file from which you need to search a text is indexed.

Syntax

<response> = zoho.sheet.find(<resource_id>, <scope>, <search_text>, <worksheet_name>, <row_index/column_index>, <connection>);

where:

ParamsData typeDescription
<response>KEY-VALUEThe status of the executed task, the number of rows matched, and their data.
<resource_id>TEXT

The ID of the Zoho Sheet file in which the search will be performed.

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>

<scope>TEXT

The scope in which the search will be performed.

Allowed values:

  • workbook
  • worksheet
  • row
  • column
<search_text>TEXTThe text that will be searched for.

Note: The search performed by this task is not case-sensitive, i.e., this task finds the occurrences of the specified text regardless of its casing (uppercase/lowercase).
<worksheet_name>TEXT

The name of the worksheet in which the search will be performed.

Note:

  • To skip this parameter, provide an empty text value.
  • This parameter is required if the scope is set to worksheet, row, or column.
<row_index/column_index>NUMBER

The index number of the row or the column in which the search will be performed.

Note:

  • This parameter is required if the <scope> parameter is set to row or column.
  • This parameter will not be validated if the <scope> parameter is set to workbook or worksheet.
  • The column_index needs to be supplied with the number equivalent of the column index (in letters). The index number starts with 1. For example, the index number of the column - A is 1 and the index number of the column - AA is 27.
<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>

Example 1: Find a piece of text in a workbook of Zoho Sheet file

The following script searches for the text - Shawn in the workbook of ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41:

 response = zoho.sheet.find("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41", "workbook", "Shawn", "", 0, "sheet_connection");

where:

response
The KEY-VALUE response returned by Zoho Sheet. It represents the status of the executed task, the number of rows matched, and their data.
"eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41"
The TEXT that represents the ID of the Zoho Sheet.
"workbook"
The TEXT that represents the scope in which the search is performed.
"Shawn"
The TEXT that represents the piece of text that needs to be searched in the specified scope.
""
The empty TEXT value supplied to skip <worksheet_name> parameter.
0
The NUMBER that is supplied to skip <row_index/column_index> parameter.
"sheet_connection
The TEXT that represents the link name of the sheet connection.

Example 2: Find a piece of text in the specified column of a workbook of Zoho Sheet file

The following script searches for the text - High in the column - E (index number: 5) of the Zoho Sheet file with ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41:

 response = zoho.sheet.find("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41", "column", "High", "Sheet1", 5, "sheet_connection");

where:

"column"
The TEXT that represents the scope in which the search needs to be performed.
"High"
The TEXT that represents the piece of text that needs to be searched for.
"Sheet1"
The TEXT that represents the worksheet name.
5
The NUMBER that represents the column index number in which the search needs to be performed.
"sheet_connection
The TEXT that represents the link name of the sheet connection

Response Format

Success Response

  • The success response will be returned in the following format:

      {
      "cells": [
      {
      "row_index": 2,
      "worksheet_id": "0#",
      "column_index": 1,
      "content": "Shawn",
      "worksheet_name": "Sheet1"
      },
      {
      "row_index": 2,
      "worksheet_id": "0#",
      "column_index": 2,
      "content": "shawn@zylker.com",
      "url": "mailto:shawn@zylker.com",
      "worksheet_name": "Sheet1"
      }
      ],
      "method": "find",
      "matches_found": 2,
      "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": "find",
      "error_code": 2863,
      "status": "failure"
      }

  • The failure response for missing column index will be returned in the following format:

      {
      "error_message": "The parameter [column] required for processing this request is missing.",
      "method": "find",
      "error_code": 2831,
      "status": "failure"
      }

  • The failure response for missing row index will be returned in the following format:

      {
      "error_message": "The parameter [row] required for processing this request is missing.",
      "method": "find",
      "error_code": 2831,
      "status": "failure"
      }

  • The failure response for missing worksheet_name or worksheet_id will be returned in the following format:

      {
      "error_message": "The parameter [worksheet_name or worksheet_id] required for processing this request is missing.",
      "method": "find",
      "error_code": 2831,
      "status": "failure"
      }

  • The failure response for null value in the search_text parameter will be returned in the following format:

      {
      "error_message": "The parameter [search] required for processing this request is missing.",
      "method": "find",
      "error_code": 2831,
      "status": "failure"
      }

  • The failure response for null value in the search_text parameter will be returned in the following format:

      {
      "error_message": "The parameter [search] required for processing this request is missing.",
      "method": "find",
      "error_code": 2831,
      "status": "failure"
      }

Related Links