Choose where you’d like to start

Update Records in Zoho Sheet

Table of Contents

Description

The zoho.sheet.updateRecords task is used to update rows that satisfy the specified criteria with new values in Zoho Sheet. This task is based on the Zoho Sheet API - Update records in 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.updateRecords task before the Zoho Sheet file you need to update is indexed.

Syntax

<response> = zoho.sheet.updateRecords(<resource_id>, <worksheet_name>, <criteria>, <data_map>, <optional_map>, <connection>);
where:
ParamsData typeDescription

<response>

KEY-VALUE

The details of the updated records and status of the executed task.

<resource_id>

TEXT

The ID of the Zoho Sheet file in which the records need to be updated.

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>

TEXT

The name of the worksheet in which the rows need to be updated.

<criteria>

TEXT

The search criteria that need to be satisfied by the records for them to be updated. To update all available rows without criteria, supply an empty text to this param.

<data_map>

KEY-VALUE

The new values of the rows that need to be updated. Keys to this param must be supplied with column names.

<optional_map>

KEY-VALUE

Any additional parameters supported by the Zoho Sheet API can be utilized using <optional_map>. The keys to this param need to be supplied with column names.

To ignore querying using this param, supply 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 parameters 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: Update records that satisfy the specified criteria

The following script updates the records of the worksheet - Sheet1 of the Zoho Sheet file with ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41:

 new_values = Map();
 new_values.put("Priority", "High");
 
 queryData = Map();
 
 response = zoho.sheet.updateRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41", "Sheet1", "\"User Count\">=200", new_values, queryData, "sheet_connection");

where:

response
The KEY-VALUE response returned by Zoho Sheet. It represents the updated records and the status of the executed task.
"eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41"
The TEXT that represents the ID of the Zoho Sheet file.
"Sheet1"
The TEXT that represents the name of the worksheet in which the records need to be updated.
"\"User Count\">=200"
The TEXT that represents the search criteria.
new_values
The KEY-VALUE variable that holds the new values of the records that need to be updated.
"\"Priority\""
The TEXT that represents the column name of the records whose values need to be updated.
"\"High\""
The TEXT that represents the new value.
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: Update only the first record that satisfies the specified criteria

The following script updates the first row that satisfies the criteria - "\"User Count\">=200" in the worksheet - Sheet1 of the Zoho Sheet file with ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41:

 new_values = Map();
 new_values.put("\"Priority\"","\"High\"");
 
 query_value = Map();
 query_value.put("first_match_only","true");
 
 response = zoho.sheet.updateRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41", "Sheet1", "\"User Count\">=200", new_values, query_value, "sheet_connection");

where:

query_value
The KEY-VALUE variable that holds the query parameters.
"first_match_only"
The TEXT parameters as given in the Zoho Sheet API. If the value against the key - first_match_only is true, it updates only the first row that matches the specified condition.
Note:
  • If the values of the <data_map> and <optional_map> contain text entries (from the sheet), 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 update rows that satisfy multiple criteria:
    1. and - Used between two criteria to fetch the rows that satisfy both conditions.

      Example: The value - "\"Lead Source\"=\"Trade Show\" and \"User Count\">200" against the key - criteria in <query_map>, updates 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 conditions.

      Example: The value - "\"Lead Source\"=\"Trade Show\" or \"User Count\">200" against the key - criteria in <query_map>, updates all the rows that either has the value - Trade Show in the column - Lead Source or has a value greater than 200 in the column - User Count or both.

Response Format

Success Response

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

      {
      "no_of_affected_rows":10,
      "method":"worksheet.records.update",
      "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 an 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 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

Get Started Now

Execute