Deluge in Zoho Services #4: Zoho Sheet

Deluge in Zoho Services #4: Zoho Sheet

Hello everyone,

Greetings from Deluge! It's been a while since we connected, but now we're back in action, continuing our series of posts on Zoho services that support Deluge. We hope you found the previous post in this series useful. In today's post, let's explore the ways in which you can use Deluge to achieve more with Zoho Sheet.

Custom functions is the Zoho Sheet feature that uses Deluge. These functions are written using the Deluge scripting language to manipulate data more effectively, communicate with third-party applications, and fetch/update values based on your requirements. Custom functions enable you to program your own functions and add different types of business logic. Apart from creating and running custom functions using Deluge, Zoho Sheet also allows bringing in data from other Zoho or third-party services using connections. Integrating with other services requires you to create a connection, thereby ensuring data transfer between the connected services. 

Many organizations have their own business logic that requires personalized functions. For example:
  • Let's say you've maintained inventory stock details in the Item Details report inside your Zoho Books account. As prices keep fluctuating everyday, it could become frustrating and tedious to individually edit and update each record in your report. To resolve this, you can maintain the required data in your Zoho Sheet. You can achieve data synchronization between both the services by configuring a custom function that performs the following actions via API calls.
    • Pulls the required data from the Item Details report in your Books account and populates the same in your sheet.
    • Pushes data to the Item Details report in Books as and when data is created/edited in your sheet.
      This way the data in your Books report will be automatically updated each time you add new data to your sheet.  
  • Let's say you've collected and stored the feedback comments of your customers in your sheet. You need to analyze the sentiments of these comments, categorize them as — Positive, Negative, and Neutral and submit the final sheet to the appropriate authority. To achieve this, you can create a custom function using zoho.ai.analyseSentiment task. The function checks a comment, analyzes its emotion and returns the detected emotion along with its probability percentage.

Example

Let's say you own a business named Zylker Corp. Your business has clinched a good number of sales-ready deals in the current year. But handling a large number of deals every day makes it difficult to determine which ones to focus on.

You're in charge of tracking and maintaining these deals, and you need to fetch all of them, along with their relevant details, such as Deal Name, Amount, and Closing Date for a certain period, and then populate that data inside your sheet.

This data is useful in generating real revenue for your business. However, this is time-consuming if done manually, and can also lead to errors. To overcome this, Deluge can be used to create custom functions that can actually pull data from your Deals module inside Zoho CRM.

Note: To use custom functions that require fetching data from other services (in this case, we're fetching data from Zoho CRM and populating them in Zoho Sheet), the ow​​​​ner of the spreadsheet must hold an account in Zoho CRM.

These custom functions are similar to the macros in an Excel sheet. A macro is an action or set of actions that you can run as many times as you want. If you have tasks in Microsoft Excel that you repeatedly perform, you can create a macro to automate those tasks. 

Similarly, you can create a custom function by specifying set criteria and running it repeatedly whenever required. In other words, you can automate repetitive tasks using custom functions to save time and manual effort. 

How it works



Steps to create a custom function

1. Create connection 

  1. Navigate to Tools > Custom Functions and click Manage Connections.
  2. Click Create Connection. Select the Default Services tab under Pick Your Service.
  3. Select the Zoho OAuth service from the list of services. 
  4. Enter a suitable Connection Name. Here, we named it crm_oauth_connection. The Connection Link Name will be auto-filled accordingly.
  5. Choose scopes ZohoCRM.coql.READ and ZohoCRM.modules.deals.ALL.
    Note:
    • This connection is used to authorize Zoho CRM to fetch records from all its modules through a COQL query.
    • We're using the COQL API here, since selecting a date range in  the function's criteria isn't supported in the Get Records API.
    • Refer to the API page to learn how to get records through a COQL query.
  6. Click Create and Connect. You'll be redirected to the service authentication page.
  7. Click Connect, then click Accept to allow DRE to access data in your Zoho account. The required connection is now created.
  8. The CONNECTION SUMMARY page will display your connection details.

2. Create custom function

  1. Navigate to Tools > Custom Functions and click Create Custom Function.
  2. Enter a valid function name.
  3. Select the data type of the return value for the specific function to be created from the Result Type dropdown. Here, you need to choose list as the return data type, since we need the output to be displayed as a list of values (vertically).
  4. You can also add the required arguments and their types for the function in the Create Custom Function popup. Here, you must add the following arguments—StartDate and EndDate—with their data types as date. This is because we're going to fetch the deal details between the specified start and end dates.

  5. Click Create and your custom function will be created.

3. Script using Deluge

  1. Navigate to Tools > Custom functions > View Deluge Editor.
  2. Select the added custom function (DEALS_BETWEEN), write the following script in the editor, and click Save.
  1. //List is the return data type. StartDate and EndDate are the parameters, whose values will, in turn, be supplied as params while making the CRM API call.
  2. list DEALS_BETWEEN(date StartDate, date EndDate)
  3. {
  4. //Use toString to convert the input dates to accepted date formats in Sheet.
  5. start_date = StartDate.toString("yyyy-MM-dd");
  6. end_date = EndDate.toString("yyyy-MM-dd");
  7. //Construct a map with the required deal details in the defined map variable using a select query. The deal details include field names from the Deals module in CRM.
  8. query_map = Map();
  9. query_map.put("select_query","select Deal_Name, Amount, Closing_Date from Deals where Closing_Date between '" + start_date + "' and '" + end_date + "'");
  10. //Invoke the Zoho CRM API to fetch the records from the Deals module through a COQL query. The connection you created earlier will be used here.
  11. response = invokeurl
  12. [
  13. url: "https://www.zohoapis.com/crm/v3/coql"
  14.  type: POST
  15. parameters:query_map.toString()
  16. connection:"crm_oauth_connection"
  17. ];
  18. //resultList is the variable to declare a list.
  19. resultList = List();
  20. response_data = response.get("data");
  21. //The below "for" statement parses the records inside the Deals module and fetches the specified details
  22. for each record in response_data
  23. {
  24. resultMap = Map();
  25. resultMap.put("Deal Name",record.get("Deal_Name"));
  26. resultMap.put("Amount",record.get("Amount"));
  27. resultMap.put("Closing Date",record.get("Closing_Date"));
  28. resultList.add(resultMap);
  29. }
  30. //Returns the response in the format expected by Zoho Sheet.
  31. return resultList;
  32. }
Note: 
  • In the above script, Deal_Name, Amount, and Closing_Date are API names of fields in the Zoho CRM Deals module.
  • You can test your custom function by clicking Run and entering sample values.
If you want to get the required API names for other CRM fields:
Log in to your CRM account.
Navigate to Settings > APIs (under Developer Space) > CRM API > API names.
Click the Deals module. The API names page will list the API names of all the fields in the Deals module. 
You can then use the required API names in your script.

4. Execute function

Enter the function in the below format. Your sheet will be populated with the deal details (Deal Name, Amount, and Closing Date) between the specified time period.

Input format:

=DEALS_BETWEEN("2022-01-01";"2022-11-11")

where,

DEALS_BETWEEN
name of your custom function
2022-01-01 
start_date value
2022-11-11
end_date value

You can refer to help page to learn in-depth about how to achieve the above custom functions using Deluge in Zoho Sheets. 
We hope you found this post useful—we'll be exploring Deluge in Zoho Connect in our next post. Please let us know if you have any questions, feedback, or suggestions in the comments, or write to us at support@zohodeluge.com.

Thank you!

You can also check out our preview posts in this series!

      • Recent Topics

      • WorkDrive Download Issue

        My client has been sending me files via WorkDrive, which generally has worked fine. Recently files won't download at all. If you try and individually select and download a file, a popup will appear in the bottom right saying it's preparing and then it

      • Zoho Commerce - Poor Features Set for Blogging

        Hi Zoho Commerce team, I'm sure you will have noticed that I have been asking many questions about the Blogs feature in Commerce. I thought that it would be useful if I share my feedback in a constructive way, to highlight the areas which I feel need

      • Security Enhancements | Migrate to the Updated Policies

        Hello everyone, Zoho Directory's security policies have been updated and reorganized into three new policies with features that enhance the overall organization security. These policies provide a stronger and more secure sign-in methods and improve the

      • Bring Zoho Shifts Capabilities into Zoho People Shift Module

        Hello Zoho People Product Team, After a deep review of the Zoho People Shift module and a direct comparison with Zoho Shifts, we would like to raise a feature request and serious concern regarding the current state of shift management in Zoho People.

      • Zoho Commerce - How To Change Blog Published Date and Author

        Hi Commerce Team, I'm discussing a project with a client who wants to move from Woo Commerce / Wordpress to Zoho Commerce. They have around 620 blog posts which will need to be migrated. I am now aware of the blog import feature and I have run some tests.

      • Don't Allow Customer to Edit Values After Submitting Ticket

        After a customer submits a ticket through the customer portal, they can go into the ticket and see some of the values from the questions they answered in the sidebar. Currently, a customer can edit these values even after they submitted them. This makes no sense. We ask very specific questions that we don't want customers to later change! Please disable the ability for customers to edit the values to their submission questions in the portal. Screenshot attached.

      • using the client script based on the look up filed i wnat to fetch the record details like service number , service rate

        based on selected service look up field iwant to fetch the service serial number in the serice form how i achive using client script also how i get the current date in the date field in the on load of the form

      • Quick Create needs Client Script support

        As per the title. We need client scripts to apply at a Quick Create level. We enforce logic on the form to ensure data quality, automate field values, etc. However, all this is lost when a user attempts a "Quick Create". It is disappointing because, from

      • How to block a WhatsApp user for sending spam

        Is there a way to block those whatsapp users that just come to play and annoy our service, they also spam us. We have a waba service with sales iq

      • Inquiry regarding auto-save behavior for Zoho Sign Embedded Sending

        Dear Zoho Support Team, I am currently integrating Zoho Sign's Embedded Sending functionality using iframes on my website. I would like to know if there is a way to ensure that the document state (including any added fields) is automatically saved as

      • Introducing Connected Records to bring business context to every aspect of your work in Zoho CRM for Everyone

        Hello Everyone, We are excited to unveil phase one of a powerful enhancement to CRM for Everyone - Connected Records, available only in CRM's Nextgen UI. With CRM for Everyone, businesses can onboard all customer-facing teams onto the CRM platform to

      • Automation#17: Auto-Create Tasks in Zoho Projects Upon Ticket Creation in Zoho Desk

        Hello Everyone, This edition delivers the solution to automatically create a task in Zoho Projects when a ticket is created in Zoho Desk. Zylker Resorts uses Zoho Desk for bookings and handling guest requests. Zylker resorts outsources cab bookings to

      • Automation#20 : Auto-Add Ticket Tags based on Keywords

        Hello Everyone! Welcome to unveiling custom functions on our Community series. This week's post lets you add tags to your tickets automatically based on the keywords in the ticket subject and the ticket thread. Discover how this custom function helps

      • Automation#21: Track Ticket Transfers Across Departments

        Hello Everyone! With Halloween just around the corner, we'd like to let you know the Zoho Desk team is always there to sweep away your customer service troubles! This week, we’re excited to introduce a custom function that tracks tickets moved between

      • Automate attendance tracking with Zoho Cliq Developer Platform

        I wish remote work were permanently mandated so we could join work calls from a movie theatre or even while skydiving! But wait, it's time to wake up! The alarm has snoozed twice, and your team has already logged on for the day. Keeping tabs on attendance

      • Reusable Custom Functions Across Department Workflows

        Dear Zoho Desk Team, We appreciate the powerful workflow automation capabilities in Zoho Desk, particularly the ability to create and use custom functions within workflows. However, we have encountered a limitation that impacts efficiency and maintainability.

      • Email Integration - Zoho CRM - OAuth and IMAP

        Hello, We are attempting to integrate our Microsoft 365 email with Zoho CRM. We are using the documentation at Email Configuration for IMAP and POP3 (zoho.com) We use Microsoft 365 and per their recommendations (and requirements) for secure email we have

      • Uplifted homepage experience

        Hello everyone, Creating your homepage is now much easier, more visual, and more impactful. Until now, your homepage allowed you to display custom views, widgets, analytic components, and Kiosk. With the following improvements, the homepage is now a smarter,

      • Homepage not assignable to group

      • MS Teams for daily call operations

        Hello all, Our most anticipated and crucial update is finally here! Organizations using Microsoft Teams phone system can now integrate it effectively with Zoho CRM for tasks like dialling numbers and logging calls. We are enhancing our MS Teams functionality

      • Automation#22 Track Ticket Duration at Specific Status

        Hello Everyone! Welcome back to the Community Learning Series! Today, we explore how Zylker Techfix, a gadget servicing firm, boosted productivity by tracking the time spent at a particular ticket status in Zoho Desk. Zylker Techfix customized Zoho Desk’s

      • Automation#23: Automate Guided Conversations in Zoho Desk with Business Hours

        Hello Everyone, This week's edition introduces a custom function designed to automate Guided Conversations in Zoho Desk, based on your business hours. With this feature, you can align the bot's behavior with your business schedule, ensuring a smooth and

      • Address changes in quote form

        When entering a quote, the first piece of information required is the Account, which properly populates the billing and shipping address fields. Then I use the lookup function to select a contact, and when I do, the billing and shipping addresses are

      • Automation#24: Auto-Update custom field from Accounts to Tickets

        Hello Everyone! Welcome back to the Community Learning Series! This episode dives into how Zylker Techfix streamlines account-related ticket references. Previously, employees had to manually check account details to retrieve specific customer information,

      • Automation#31: Automate Splitting Names for New Contact Records

        Hello Everyone, This week, we present to you a custom function, which allows you to split the first and last names from the user's email ID based on the separator used in the ID. Having grown into a large firm, Zylker Techfix aims to optimize its processes,

      • The same Contact associated to multiple Companies - Deals

        Hi, I would like to know if there is an option to associate the same contact with multiple companies (two or more) deals, using the same contact details for all. This is because we have contacts who are linked to different companies or branches of the

      • local file csv import problem

        The issue occurs when I upload a CSV file via Databridge. In the preview, everything looks correct — the values are in the proper columns. However, after clicking Import, the first column becomes empty, and the values from that column appear in a new

      • Kaizen #227 : Client Script Support for List Page (Canvas)

        Hello everyone! Welcome to another week of Kaizen. In today's post lets see how Client Script can be used in Canvas List Page to mask sensitive information from specific roles and add colors to Canvas List Page records based on custom criteria.This use

      • Implement Date-Time-Based Triggers in Zoho Desk

        Dear Zoho Desk Support Team, We are writing to request a new feature that would allow for the creation of workflows triggered by specific date-time conditions. Currently, Zoho Desk does not provide native support for date-time-based triggers, limiting

      • Automation#25: Move Tickets to Unassigned When the Owner Is Offline

        Hello Everyone, Welcome to this week's Community Series! 'Tis the holiday season—a time when work often takes a brief pause. The holiday spirit is in full swing at Zylker Techfix too, with employees taking some well-deserved time off. During this period,

      • Analytics <-> Invoice Connection DELETED by Zoho

        Hi All, I am reaching out today because of a big issue we have at the moment with Zoho Analytics and Zoho Invoice. Our organization relies on Zoho Analytics for most of our reporting (operationnal teams). A few days ago we observed a sync issue with the

      • Automation#27: Retain Ticket Owner on Moved Tickets

        Hello Everyone! This week, we present to you a custom function that retains the ticket owner when a ticket is moved from one department to another. Here’s more to help you understand the custom function: At Zylker Techfix, Alex, the Support Engineer manages

      • Automation#28 Notify Agents on Article Expiry

        Hello Everyone! This week, we’re bringing you a feature that notifies your team when articles in the Knowledge Base are set to expire to keep your content relevant and helpful for customers. The Zoho Desk's Knowledge Base is an asset for customers to

      • Automation#29 Retain ticket status on moved tickets

        Hello Everyone, Hear out Zylker Techfix’s Success Story on Smoother Ticket Transitions! Zylker Techfix, a gadget servicing firm committed to quick repairs and timely deliveries, faced a challenge when ticket statuses changed automatically while moving

      • encountering an error when attempting to associate an email with a Deal using the Zoho CRM extension in Zoho Mail.

        When I click "Yes, associate," the system displays an "Oops!! Something went wrong" error message. I have attached a screenshot of the issue for reference.

      • Automation#32:Auto Add New Portal Users to the Help Center User Groups

        Hello Everyone, Introducing a custom function that automates the process of adding new portal users to Help Center user groups, making user management effortless! By default, Zoho Desk allows you to assign new portal users to groups manually. But with

      • Using email "importance" as workflow-criteria

        I'd like to set up a workflow that triggers if an incoming email has been flagged as "high importance" but I'm not seeing any way to do that. Hopefully I'm just missing something obvious...?

      • Automation#34 : Automate Email threading for Ticket notification

        Hello Everyone, It's been a while since we've presented an automation. However, our community has been buzzing with ideas, use cases, and discussions with our community experts and Ask the Experts session. So, here we are again, presenting an automation

      • Automation#35 : Auto-Add Comments under the Owner's Name in Tickets via Macros

        Hello Everyone, This week's custom function provides simple steps to configure a Macro for adding comments to tickets with the name of the Comment owner. When managing tickets, you can use the Comment feature to communicate internally with your team and

      • Automation#36: Auto-create time-entry after performing the Blueprint transition

        Hello Everyone, This week’s edition focuses on configuring a custom function within Zoho Desk to streamline time tracking within the Blueprint. In this case, we create a custom field, and request the agent to enter the spending time within the single

      • Next Page