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!


      Zoho Campaigns Resources


        • Desk Community Learning Series


        • Digest


        • Functions


        • Meetups


        • Kbase


        • Resources


        • Glossary


        • Desk Marketplace


        • MVP Corner


        • Word of the Day


        • Ask the Experts


          Zoho CRM Plus Resources

            Zoho Books Resources


              Zoho Subscriptions Resources

                Zoho Projects Resources


                  Zoho Sprints Resources


                    Zoho Orchestly Resources


                      Zoho Creator Resources


                        Zoho WorkDrive Resources



                          Zoho CRM Resources

                          • CRM Community Learning Series

                            CRM Community Learning Series


                          • Tips

                            Tips

                          • Functions

                            Functions

                          • Meetups

                            Meetups

                          • Kbase

                            Kbase

                          • Resources

                            Resources

                          • Digest

                            Digest

                          • CRM Marketplace

                            CRM Marketplace

                          • MVP Corner

                            MVP Corner




                            Zoho Writer Writer

                            Get Started. Write Away!

                            Writer is a powerful online word processor, designed for collaborative work.

                              Zoho CRM コンテンツ








                                ご検討中の方

                                  • Recent Topics

                                  • Can we embed a Kanban Type of report using iframe too?

                                    I tried embedding a report as iFrame but it only appear as List View. It would be helpful if can also embed a Kanban Type of report.

                                  • Weekly Tips : Save Time with Saved Search

                                    Let's assume your work requires you to regularly check emails from important clients that have attachments and were sent within a specific time period. Instead of entering the same conditions every time—like sender, date range, and attachments included—you

                                  • Big Things Just Dropped in the SalesIQ Universe: Top Upgrades You’ll Love in Nova’25

                                    Nova'25 has landed, and it’s packed with meaningful upgrades to help you engage smarter, work faster, and scale with ease. Whether you're into proactive messaging, smarter automation, or better admin control, there's something here for everyone. Here's

                                  • Hidden Fields with Data Pre-Population and Notification Integration in Zoho Bookings Forms

                                    Dear Zoho Bookings Support Team, We'd like to propose a feature enhancement for Zoho Bookings forms that would improve data management and workflow efficiency: the ability to create hidden fields with pre-populated data. Current Functionality: Zoho Bookings

                                  • Tip #37 – How to ensure compliance and accountability using Advanced Session Audit – ‘Insider Insights’

                                    Hello Zoho Assist Community! This week we will be discussing how to strengthen your remote support operations with better visibility and control. Let’s say your support team handles a growing number of remote sessions each day. At the end of the week,

                                  • Replace an existing zoho show file with an uploaded one.

                                    For Zoho WorkDrive Upload API method, I have override-name-exist option, which I can use like this: curl https://www.zohoapis.eu/workdrive/api/v1/upload \ -F "filename=mypres.pptx" \ -F "parent_id=j3tzq1ae09c0cd91540df8d01670af05b657e" \ -F "override-name-exist=true"

                                  • Tip of the Week #66– Use internal comments to collaborate and resolve issues faster!

                                    Ever found yourself stuck on a customer query because you needed inputs from your teammate? Or sent a reply too soon and realized later that someone else on your team had a better context? When you rely on back-and-forth messages in external chats or

                                  • Using Zoho One to manage two (or more) businesses

                                    We are one company that operates two business, a scenario that Zoho One doesn't really seem to handle very well, and one which I can't imagine is at all unique to us! (It is basically designed to facilitate branding and use for one business only per subscription).

                                  • Skip order confirmation step in commerce?

                                    We have a store to sell products that are not shipped to a customer. Right now, when the order is placed, we have to manually 'confirm' the order so the sales order can convert to an invoice and charge the customer.  Is there a way to skip the manual

                                  • Free Shipping Coupon - Zoho Commerce

                                    I love the Zoho platform. Zoho Commerce is no exception; however, I'm struggling with a few common features I cannot find or are missing that I hope someone can help me with. Is there a way to create a coupon code for free shipping?

                                  • Is there a way to link an item to a liability account?

                                    I collect customer deposits for certain services and hold them in a liability account. However, Zoho Books doesn't let you create an item and assign it to a liability account so how do I create an invoice with an item that records it as a deposit into

                                  • Select a balance sheet account when creating an invoice

                                    Hi, it would be very helpfull to be able to select a balance sheet account when invoicing clients. We bill 30% of the job at signing and we would like to add to deferred revenues.  I have to create a transit revenue account and then tranfer to the right

                                  • How to work out commission in zoho commerce based on coupons

                                    HI There Is there any way to produce report based on coupon name in zoho commerce We need to pay commission to various channels who are helping us to increase the online sales so we have number dedicated coupons assigned to the above mentioned channels

                                  • Request for Subform Styling Feature in Zoho CRM Canvas

                                    Dear Zoho CRM Team, We have observed that in the Zoho CRM Canvas view, it is currently not possible to set presets or manage the styles of subform fields. Additionally, the ability to edit subform data directly within the Canvas view appears to be limited

                                  • How to Delete Old Tasks/Streams Assigned by Deactivated Users? 'Operation Not Permitted' Error

                                    Hello, I’m using Zoho Mail and have several old tasks assigned to me in Tasks and Streams. These tasks were created by former employees whose accounts are now deactivated. When I try to delete these tasks, I get an "Operation Not Permitted" error. These

                                  • Zoho Books - Sales Person Contact Details on Quotes

                                    Hi Zoho Books Team, I've had various clients ask me about showing the phone number and email of the sales person on Quotes to help eliminate any barriers to closing sales. When I tell them that it is not possible they are always surprised and say something

                                  • Related products category instead recommended products.

                                    Hi there, It is possible to show related products of the same category (as it works in the rest of the ecommerce) instead of showing recommended products.

                                  • Uploading PDF files

                                    How do I upload a PDF file to my ZOHO site?

                                  • How to send invoices to 2 emails?

                                    Hi! We are sending invoices to the "Customer email" field that is defined Zoho Books and is obtained/synced from a custom email field in Zoho CRM. But in some clientes, the invoices have to be sent to 2 emails and we are wondering how this could be accomplished.

                                  • It returns 1 record

                                    Var1= Tools_Request[Liability_Receipt == input.Liability_No]; for each rec in Var1.Tool_Request_Description { Var2= (ET_Inventory[SKU == rec.Tools_SKU].SKU).getAll(); } info call for Var2 It only fetch 1 record On record file

                                  • Narrative 5: The essential role of SLAs

                                    Behind the scenes of a successful ticketing system - BTS Series Narrative 5: The essential role of SLAs Every organization that interacts with customers establishes a timeframe within which agents should respond to queries as part of a service level agreement

                                  • Nextdoor Integration

                                    Does Zoho social work with Nextdoor? www.nextdoor.com? Are there any plans for an integration?

                                  • My email sending has beed blocked due to high bounce rate. NEED HELP

                                    User ID: 886739811 Dear Zoho Team, I hope this message finds you well. My account (User ID: 886739811) was blocked from sending emails last week due to an unusually high bounce rate. This spike was caused by a bot attack on our platform, which led to

                                  • Can Zoho CRM Emails be used in Zoho Analytics in any capacity?

                                    We're wanting to display details about Lead Activity in regular reports through Zoho Analytics but we're having difficulty integrating Emails at all. We'd like to be able to note when an email is received and when it is sent somewhere other than just

                                  • WorkDrive Just Got Smarter: Introducing Zoho AI in WorkDrive!

                                    We're all looking for ways to work smarter, not harder, right? If you've been dabbling in the Zoho ecosystem, chances are you've bumped into Zia, Zoho's intelligent assistant that's quietly revolutionizing how we tackle our daily tasks. Meet Zia: Zoho’s

                                  • IF Statement in Zoho CRM Formula Field

                                    Hi, I am attempting to write a formula field that will give me one result if one statement AND another statement are true, then a different value if the first statement AND a different statement are true, else 0. Stated differently: if account = destination

                                  • Scheduled Maintenances

                                    Hi, Why is adding scheduled maintenance so arduous. It should be a simple process and take very little time for basically a recurring job. Creating 3 records with similar data seems crazy to me. Is there a easy way to do this? I have to create hundreds

                                  • Subform Fields for Form Rules & Subform Fields as a Condition with Form Fields as the Action

                                    Hi, The Subform in Field Rules is great, but it is missing completley from Form Rules and it lacks being able to have Subform Fields in the Condition while having Form Fields in the Action, it works the other way around. Thanks Dan

                                  • Power of Automation :: Implementing Deadline-Based Task Scoring with Custom Fields

                                    Hello Everyone, A custom function is a software code that can be used to automate a process and this allows you to automate a notification, call a webhook, or perform logic immediately after a workflow rule is triggered. This feature helps to automate

                                  • Zoho Forms - Subform Integration with Zoho CRM?

                                    I created a form in Zoho Forms that includes a sub-form for information that I want to integrate with the CONTACTS module. I am unable to find a way integrate/map the fields in the sub-form with the Contacts module in Zoho CRM. Is there a way to do this

                                  • Filter by user in Pivot Chart

                                    I have a Pivot chart where the data should be filtered by user. The user enters the system and should see only the data that correspond to it. Can anyone help me?

                                  • Is ZeptoMail Still Supported? No Response to API Query in Over a Week

                                    Is ZeptoMail Still Supported? No Response to API Query in Over a Week I’m posting here to express my growing frustration and concern over the lack of professionalism I’ve experienced with Zoho's ZeptoMail support. On July 21st, I received a response asking

                                  • Add ID to the recycling bin page

                                    Feature request to add the ID to the recycling bin page. This would be helpful for reference when trying to recover things. This is the long zoho ID for each item. - ticket ID - contact ID - account ID - etc.

                                  • Remove System Defined Ticket Layout

                                    Hi ZohoDesk, I have created a new Ticket Layout and made it the default and I want to remove the original default one so there is only one to choose from. It won't allow me to do this, or go into the options and take the tick out of the show in Help Center.

                                  • ZOHOLICS Japan 2025 開催のお知らせ

                                    ユーザーの皆さま こんにちは、Zoho コミュニティチームの中野です。 年に一度の自社最大イベント、「ZOHOLICS Japan 2025」（ゾーホリクス）の開催が決定しましたので、こちらでお知らせします。 今年の開催回は、特別ゲストやZoho のエキスパートが、 最新のDX事情や皆さまのビジネスプロセス改善、業績アップのヒントとなる情報をお届けします。 Zoho コミュニティからは、Zoho Championの西尾さん（@西尾　真言）、 コミュニティアンバサダープログラム（ZCAP）メンバーの箕輪さんにご登壇いただき、

                                  • Unable to connect Zoho Learn to other Zoho Tools

                                    Hello Zoho, Can you work on Zoho Learn. I am unable to connect it with other Zoho tools like Zoho people or even Zoho SalesiQ. This is needed, especially if I am connect Ai to my systems. Please work on connecting your applications better, especially

                                  • Chatbot for Urdu language

                                    Hello, I have successfully set up Zoho Desk and integrated it with Moodle using ASAP. I am now looking for a chatbot that supports the Urdu language to enhance student support. My goal is to enable the chatbot within ASAP so that students can ask questions,

                                  • Multi Level Hierarchical Query

                                    Hi guys I have a situation where i am trying to extract the descendants of a particular entry. So long story short, i have the Account table with Agency (A) id = 1, and that has child Agency (B) id = 2. This child agency (B) has a child agency (C) id

                                  • AI-Powered Grouping and Tagging of Related Issues and Tasks in Zoho Projects

                                    Dear Zoho Projects Team, Greetings, We would like to suggest a feature that would greatly enhance how we manage feature requests and bug reports submitted through the Zoho Desk to Zoho Projects integration. Use Case: Our support agents handle customer

                                  • How to change side bar from dark mode to light mode?

                                    The side bar on the left side in workdrive doesn't appear to have a way to fix it from dark mode - is there a way to? I can't read in dark mode without excessive headaches, and I would really like to be able to see while I have workdrive open, as workdrive

                                  • Next Page