Choose where you’d like to start

Replace in Zoho Sheet

Table of Contents

Description

The zoho.sheet.replace task is used to find the specified text in a row, column, worksheet, or workbook of Zoho Sheet file, and replace all its occurrences with a new text. This task is based on the Zoho Sheet API - Find and Replace.

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

Syntax

<response> = zoho.sheet.replace(<resource_id>, <scope>, <search_text>, <replace_with>, <worksheet_name>, <row_index/column_index>,[<connection>]);
where:
ParamsData typeDescription

<response>

KEY-VALUE

The status of the executed task, the number of rows matched, and their data after replacing.

<resource_id>

TEXT

The ID of the Zoho Sheet file in which the search needs to 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 needs to be performed.

Allowed values:
  • workbook
  • worksheet
  • row
  • column

<search_text>

TEXT

The text that needs to be searched for and replaced.

Note: The search performed by this task is not case-sensitive. This task finds the occurrences of the specified text regardless of its casing (uppercase/lowercase).

<replace_with>

TEXT

The new text to replace all the occurrences of the specified search text.

<worksheet_name>

TEXT

The name of the worksheet in which the find and replace task needs to 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 needs to 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: Replace a piece of text with a new text in a workbook of Zoho Sheet file

The following script searches for the text - Trade Show in the workbook of ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41 and replaces all the occurrences with the text - Event:

 response = zoho.sheet.replace("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41", "workbook", "Trade Show", "Event", "", 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 after replacing.
"eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41"
The TEXT that represents the ID of the Zoho Sheet file.
"workbook"
The TEXT that represents the scope in which the find and replace task is performed.
"Trade Show"
The TEXT that represents the piece of text that needs to be searched in the specified scope.
"Event"
The TEXT that represents the new text.
""
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: Replace a piece of text with a new 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 and replaces all the occurrences with the new text - Closed:

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

where:

"column"
The TEXT that represents the scope in which the find and replace task is performed.
"Sheet1"
The TEXT that represents the worksheet name.
"High"
The TEXT that represents the piece of text that needs to be replaced with a new text.
"Closed"
The TEXT that represents the new text.
5
The NUMBER that represents the column index number in which the find and replace task 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,
      "formula": "Closed",
      "cell_value": "Closed",
      "sheet_name": "Sheet1",
      "column_index": 5
      },
      {
      "row_index": 4,
      "formula": "Closed",
      "cell_value": "Closed",
      "sheet_name": "Sheet1",
      "column_index": 5
      }
      ],
      "method": "replace",
      "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": "replace",
      "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": "replace",
      "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": "replace",
      "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": "replace",
      "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": "replace",
      "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": "replace",
      "error_code": 2831,
      "status": "failure"
      }

Related Links

Get Started Now

Execute