Replace in Zoho Sheet
Table of Contents
Note:
- Each time the zoho.sheet.replace 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.replace 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.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.
Syntax
<response> = zoho.sheet.replace(<resource_id>, <scope>, <search_text>, <replace_with>, <worksheet_name>, <row_index/column_index>,[<connection>]);
where:
| Params | Data type | Description |
| <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> |
| <scope> | TEXT | The scope in which the search needs to be performed.
|
| <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:
|
| <row_index/column_index> | NUMBER | The index number of the row or the column in which the search needs to be performed.
|
| <connection> | TEXT | The link name of the connection. Note:
|
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"eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41""workbook""Trade Show""Event"""0"sheet_connectionExample 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""Sheet1"
"High"
"Closed"5
"sheet_connectionResponse 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"
}