Sample Custom Function for Zoho Sheet integration

Prerequisite

1. User must have an account in Zoho Sheet and Qntrl
   
2. User must configure a Connection to authorize the access of Zoho Sheet from Qntrl. 
   

Business Case

Zylker Inc, a Pharma company, uses Qntrl to record and process their orders. Every time a new order is placed, a user fills the client details in the card and creates the order. The order then passes through the steps defined in the blueprint until it is closed. 

 

Since Zylker is also using Zoho Sheet to record their client details, they requested a work-around to pre-fill each card with the existing client details from Zoho Sheet, once the client ID is entered. 

 

This helps Zylker to:

1. Reduce manual effort of recording client details in each card
   
2. Maintain a single repository of client information
   
3. Automate data entry to reduce errors
   

Solution

We built a Custom Function for Zylker’s requirement. Here’s how the function works:

1. The user creates a card by entering just the Client ID along with the Card details.
   
2. Once the Card is created, a Custom Function is triggered to pass the data from Zoho Sheet to the card.
   
3. After the details are automatically updated to the card, the status update is sent back to Zoho Sheet. 
   

How to configure

Let us go through the step-by-step details to configure this custom function in your organization.

 

Step 1: First, in the Board, create a From to capture the client details. 

1. Mandate the Client ID field. This step is not compulsory, but it is recommended.
   
2. Disable the Visibility of the client detail fields Client Name, Contact, and Client Location. 
   

After the Form is created, go ahead and complete the rest of the steps in your board. 

Step 2: Next, create a new Custom Function and configure it with your Form, Connection, and Sheet details. 

Step 3: Navigate to Business Rules and configure  Instant  Actions to trigger the above custom function every time a new card is created in the 'Order Details' board. 

Step 4: Now, navigate to a board and create a card by entering the Client ID along with the Card details.

Once the card is created, the configured Business Rule triggers the linked Custom Function to fill the client details in the card. To ensure that the details are updated to the sheet, the Status column in Zoho Sheet automatically changes to 'Updated' by the custom function. 

1. Note that the client ID along with other client details is stored in a single row in Zoho Sheet. 
   

We’ve provided the sample custom script here for reference. If you’d like to know more details about this custom function please email our support team.  

//Deluge has predefined integrations with zoho services. Zoho Sheet is also supported

// https://www.zoho.com/deluge/help/sheet/get-records.html#Syntax

//sheet's getRecords API can be used to fetch all records in a sheet or specific records based on a criteria

//Required parameters

//1. Resource ID - can be picked from sheet url

//2. Sheet Name - Name of the sheet at the bottom

//3. Query Map - Key value map used to set criteria & range https://www.zoho.com/deluge/help/sheet/get-records.html#Example2

//4. connection link name

queryMap = Map();

//criteria format: "column name"="column value"

//example: "Client ID"="1001"

queryMap.put("criteria","\"Client ID\"=" + client_id);

data = zoho.sheet.getRecords("krz56389e2107d77a434ab731d3271a00c77d","Sheet1",queryMap,"zoho_sheet");

//this will fetch the records matching the criteria above

records = data.getJson("records");

//In our case, only record will be returned as client id is unique. So we are fetching the record at index 0

record = records.get(0);

//fetching each value by its column name

name = record.get("Client Name");

contact = record.get("Contact").toString();

location = record.get("Location");

info name;

//debugger print name

paramMap = Map();

//constructing the values in params for updating in card

//mapping with associated field name in qntrl

paramMap.put("customfield_shorttext18",name);

paramMap.put("customfield_shorttext16",contact);

paramMap.put("customfield_shorttext11",location);

job = invokeurl

[

url :" https://core.qntrl.com/blueprint/api/zylker/job/ " + job_id

type :POST

parameters:paramMap

connection:"zoho_qntrl"

];

info job;

//debugger to print update job response

//updating the record in sheet https://www.zoho.com/deluge/help/sheet/update-records.html#Example2

//we are using the same criteria that was used to fetch the record

newValues = Map();

newValues.put("Status","Updated");

optMap = Map();

updateResp = zoho.sheet.updateRecords("krz56389e2107d77a434ab731d3271a00c77d","Sheet1","\"Client ID\"=" + client_id,newValues,optMap,"zoho_sheet");

info updateResp;

• Related Articles

• Custom Function: Create_Ticket_in_Zoho_Desk

  Zoho Desk is customer service software which allows you to address customer concerns. This custom function can be used to post a card as a ticket in Zoho Desk. Prerequisites Sign up for Zoho Desk and create a department. The ticket will be created in ...

• Custom Function: Create_Job

  This custom function can be used to create a new card in any board. It can also be configured to carry forward necessary field values from the former card to the latter. Business Scenario Helen is the head of Procurement Management in her ...

• Zoho Analytics in Qntrl

  Zoho Analytics is a business intelligence platform that structures data into insightful reports and dashboards. It assists you with analyzing organizational data to generate visual graphs, making inferences, and discovering hidden insights to empower ...

• Custom Function: Computing_Field_Values

  This custom function can be used to add two values and save the return value in a custom field. The sum will be dynamically updated in the custom field after this function is executed. Business Scenario Helen is the head of Procurement Management at ...

• Function

  A Function is a serverless, simple programming script that can handle a variety of tasks and integrate with other tools to create robust automated workflows. You can write Functions in Python, Node.js, Java, and Zoho Deluge, and perform seamless ...