Create Records in Zoho Sheet

Table of Contents

Note:

  • Each time the zoho.sheet.createRecords 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.createRecords 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.createRecords task is used to insert data into the specified worksheet of a Zoho Sheet file. This task is based on the Zoho Sheet API - Add records to Worksheet.

Syntax

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

where:

ParamsData typeDescription
<response>KEY-VALUEThe details of the rows that will be created, and the status of the executed task.
<resource_id>TEXT

The ID of the Zoho Sheet file in which the new values need to be inserted.

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 in which the data needs to be inserted.
<row_data>KEY-VALUE
(or)
LIST		The values of the rows that need to be inserted.
<query_map>KEY-VALUEThe query parameters, as given in the Zoho Sheet API. 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

Example 1: Insert row into a table

The following script inserts a row with the specified values into the worksheet - Sheet1 of the Zoho Sheet file with ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41.

 // Create a KEY-VALUE variable - rowData that holds values of the new row
 rowData = Map();
 rowData.put("Email","shawn@zylker.com");
 rowData.put("Name","Shawn");
 rowData.put("Lead Source","Trade Show");
 
 // Create an empty KEY-VALUE variable to skip the <query_map> param
 queryData = Map();
 
 // Perform create records task to insert the new row into the worksheetsheet - Sheet1
 response = zoho.sheet.createRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41","Sheet1", rowData, 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 into which the record needs to be inserted.
rowData
The KEY-VALUE variable that holds the values of the new row that needs to be inserted into the worksheet.
"Email" 
"Name" 
"Lead Source" 
The TEXT values that represent the header of the table into which the row needs to be inserted.
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: Insert multiple rows into a table

The following script inserts two rows with the specified values into the worksheet - Sheet1 of the Zoho Sheet file with ID - eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41.

 // Create a KEY-VALUE variable - row1 that holds values of the first row
 row1 = Map();
 row1.put("Email","shawn@zylker.com");
 row1.put("Name","Shawn");
 row1.put("Lead Source","Trade Show");
 
 // Create a KEY-VALUE variable - row2 that holds values of the second row
 row2 = Map();
 row2.put("Name","Brent");
 row2.put("Email","brent.r@gmail.com");
 row2.put("Lead Source","Advertisement");
 
 // Create a LIST variable - rowsData that holds the variables - row1 and row2
 rowsData = List();
 rowsData.add(row1);
 rowsData.add(row2);
 
 // Create a KEY-VALUE variable to hold the query parameters
 queryData = Map();
 
 // Perform create records task to insert values of row1 and row2 into the worksheetsheet - Sheet1
 response = zoho.sheet.createRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41","Sheet1", rowsData, queryData, "sheet_connection");

where:

row1
The KEY-VALUE variable that holds the values of the first row.
row2
The KEY-VALUE variable that holds the values of the second row.
rowsData
The LIST variable that holds the variables - row1 and row2.

Example 3: Insert row into a table whose header is not present in the first row of the worksheet

The following script inserts a new row with the specified values into the table whose header is present in the row - 3 of the worksheet - Sheet1.

 // Create a KEY-VALUE variable - rowData that holds values of the new row
 rowData = Map();
 rowData.put("Email","shawn@zylker.com");
 rowData.put("Name","Shawn");
 rowData.put("Lead Source","Trade Show");
 
 // Create a KEY-VALUE variable - headerData that holds row number in which header is present
 headerData = Map();
 headerData.put("header_row",3);
 
 // Perform create records task to insert the new row into the worksheetsheet - Sheet1
 response = zoho.sheet.createRecords("eev4nXXXXXXXXXXXXXXXXXXXXXXXXXXXcff41","Sheet1", rowData, headerData, "sheet_connection");

where:

"header_row"
The TEXT key that represents the parameter name, as specified in the Zoho Sheet API. It holds the row number in the worksheet that has the header values of the table.

Response Format

Success Response

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

      {
      "start_column": 1,
      "method": "worksheet.jsondata.append",
      "start_row": 2,
      "sheet_name": "Sheet1",
      "end_row": 2,
      "end_column": 3,
      "status": "success"
      }

  • The success response will be returned in the following format when extra headers are passed:

      {
      "start_column": 1,
      "method": "worksheet.jsondata.append",
      "start_row": 3,
      "warning": "Extra header found - Company",
      "sheet_name": "Sheet1",
      "end_row": 3,
      "end_column": 3,
      "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 non-existent header values will be returned in the following format:

      {
      "error_message": "No such header found",
      "method": "worksheet.jsondata.append",
      "error_code": 2893,
      "status": "failure"
      }

Related Links