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 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
      Redefine the way you work
      with Zoho Workplace

        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 Sign now offers specialized one-on-one training for both administrators and developers.

            BOOK A SESSION








                                You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.




                                    Manage your brands on social media

                                      Zoho Desk Resources

                                      • Desk Community Learning Series


                                      • Digest


                                      • Functions


                                      • Meetups


                                      • Kbase


                                      • Resources


                                      • Glossary


                                      • Desk Marketplace


                                      • MVP Corner


                                      • Word of the Day


                                        Zoho Marketing Automation

                                          Zoho Sheet Resources

                                           

                                              Zoho Forms Resources


                                                Secure your business
                                                communication with Zoho Mail


                                                Mail on the move with
                                                Zoho Mail mobile application

                                                  Stay on top of your schedule
                                                  at all times


                                                  Carry your calendar with you
                                                  Anytime, anywhere




                                                        Zoho Sign Resources

                                                          Sign, Paperless!

                                                          Sign and send business documents on the go!

                                                          Get Started Now




                                                                  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







                                                                                            You are currently viewing the help articles of Sprints 1.0. If you are a user of 2.0, please refer here.

                                                                                            You are currently viewing the help articles of Sprints 2.0. If you are a user of 1.0, please refer here.



                                                                                                  • 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 ...
                                                                                                  • Export Field Details Extension

                                                                                                    Using this extension, you can download the details of all or specific fields of individual Cards in Qntrl.    Business Scenarios Analyze the cards easily by gathering and organizing data Plot graphs using the exported field details to analyze trends ...
                                                                                                  • 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: 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 ...
                                                                                                    Wherever you are is as good as
                                                                                                    your workplace

                                                                                                      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