Sample Custom Function for Zoho Sheet integration | Online Help | Qntrl

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 Orchestration, 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 orchestration. 



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' orchestration. 




Step 4: Now, navigate to Cards 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
//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
[
type :POST
parameters:paramMap
connection:"zoho_qntrl"
];
info job;
//debugger to print update job response
//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;



    Zoho DataPrep Personalized Demo

    If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

    Zoho CRM Training

      Create, share, and deliver

      beautiful slides from anywhere.

      Get Started Now





              Zoho CRM Training Programs

              Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

              Zoho CRM Training

                  Zoho SalesIQ Resources



                      Zoho TeamInbox Resources




                                Zoho DataPrep Resources

                                  Zoho DataPrep Demo

                                  Get a personalized demo or POC

                                  REGISTER NOW


                                    Design. Discuss. Deliver.

                                    Create visually engaging stories with Zoho Show.

                                    Get Started Now











                                                          • Related Articles

                                                          • Manage Custom Functions

                                                            Custom functions allow users to develop and execute user-defined functions using Deluge. You can execute simple program snippets to automate processes, or integrate with third-party or Zoho applications.    Business Uses for Custom Functions Use ...
                                                          • Custom Function: Create_Job

                                                            This custom function can be used to create a new card in any orchestration. 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 ...
                                                          • Custom Function: Post_and_Update_Comments_in_Zoho_Desk

                                                            Zoho Desk is a customer service software which allows you to address customer concerns. This custom function can be used to add or update comments in Zoho Desk whenever they are added or updated in Qntrl.  The comments will be added to the tickets ...
                                                          • 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 ...
                                                          • 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 ...

                                                          Resources

                                                          Videos

                                                          Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                          eBooks

                                                          Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                          Webinars

                                                          Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                          CRM Tips

                                                          Make the most of Zoho CRM with these useful tips.



                                                            Zoho Show Resources