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

                                  • How to Initiate WhatsApp Message on SalesIQ?

                                    I've just activated a Business WhatsApp phone number through SalesIQ because of its touted omnichannel chat approach. Sounds exciting. I understand that when a customer sends me a WA message, I can reply to it on SalesIQ and keep the chat going, perfect.

                                  • Episode IV:Handling User Inputs in custom functions

                                    Hi Everyone, We've been exploring custom functions which help in performing advanced automation tasks. Custom functions enable you to achieve possibilities based on your organization's requirements. Custom functions require user inputs that allow the

                                  • Set a lead as non-marketing if they opt out of email marketing

                                    I'm gathering Lead data via an enquiry form and wish to give them the option to opt out of marketing emails (which we send from Marketing Automation) whilst retaining the ability to send them non-marketing emails - so the email opt-out field doesn't work.

                                  • Set up multiple IMAP email addresses

                                    Hi, I just started using CRM and its great, but I just found out I can only add one imap email address for incoming mail in the included salesinbox ...this is ridiculous. All companies have different email such as sales@domain, info@domain , personal@domain

                                  • Zoho Recruit to Zoho People integration

                                    In the mapping of field from Zoho Recruit to Zoho People i am unable to map all fields What can i do

                                  • Zoho Recruit > Career Site > Customisation

                                    Hi All I have created multiple career site to represent my different organization In my Quanta Media career site > I set it with the with Quanta Malta view In my job opening : Quanta Malta view 2 job opening In my URL for quanta media https://lri.zohorecruit.eu/jobs/Quanta-Malta

                                  • Formatting Blueprint Stages (buttons)

                                    Is there a way to format Blueprint transition buttons in Creator like background colour, text colour etc.

                                  • Disable Default Value in Multi-Select field

                                    Hello, Is there a way to disable the default value feature when setting up a custom Multi-Select field? When creating tickets I would like it so there isn't a value pre-selected in the multi-select custom field that was created.

                                  • How to set custom business hours for different teams in Zoho Desk?

                                    Hi Zoho Team and Community, I’m trying to set different business hours for multiple departments in Zoho Desk. For example, our tech support team works 24/7, while our billing team is only available from 9 AM to 5 PM (Mon–Fri). Is there a way to assign

                                  • Zia Auto Tag Keyword Clusters

                                    Is there a way to remove a keyword from a keyword cluster? We're finding that Zia auto-tag is tagging things incorrectly based on some words that don't really make sense.

                                  • Centralized Domain Verification in Zoho CRM Plus

                                    Hi Team, I have a suggestion regarding Zoho CRM Plus. It's quite frustrating to verify the same domain separately for each application within the suite. It would be really helpful if you could introduce a centralized admin console—similar to what's available

                                  • HOw do I make a refund?

                                    I have a client that needs a refund. I see a refund history in the Report area... but where do I go to make a refund?

                                  • Creating a work order for dispatch

                                    We currently uses ZOHO for CRM and Intuit Field Service for creating Work orders and dispatching. This is causing double entry and is not efficient use of time.  I would like to use ZOHO for all customer related activities so can you tell me if you can :  Create a work order that we could manipulate to meet our needs.  Also if you could then dispatch that would be ideal. Thank you

                                  • "code": "500","description": "Account not exists", mail api

                                    I have been through all the steps and have a functioning Oath access_token etc etc... I then GET https://mail.zoho.com/api/organization And get my zoid then GET http://mail.zoho.com/api/organization/<hidden>/accounts and get the account details, which

                                  • Residual Formatting in Text Boxes After Undoing Pasting of Formatted Text

                                    Hi, guys! I have another problem to report. Actually, I have been aware of this for many months, possibly years, but I have been too lazy to report it to you. My apologies! Let's say you've pasted a formatted string into a text box. You change your mind

                                  • Feature Request: Stripe Terminal Integration

                                    I would like to request the addition of Stripe Terminal integration to Zoho Books, which will help process in-person payments. While Zoho Books already supports online payments through Stripe, extending this functionality to include Stripe Terminal would

                                  • Customer Parent Account or Sub-Customer Account

                                    Some of clients as they have 50 to 300 branches, they required separate account statement with outlet name and number; which means we have to open new account for each branch individually. However, the main issue is that, when they make a payment, they

                                  • Improved Security in SAML/OIDC Sign-in Redirection Flow

                                    To enhance the security of our authentication system, we’ve made a change to how SAML and OIDC sign-in redirections are handled. This update resolves a potential open redirection vulnerability and adds an extra layer of protection during the sign-in process.

                                  • GA4 data

                                    Hi, I have data being pulled from GA4 into numerous tables. There are a few major errors in the source data. Eg for a period of time GA4 reported ecommerce sales that were $98 as $9,800 and $42 as $4,200. It's not happening now but my historical data

                                  • Ask the Experts: Five-hour live Q&A session with Zoho WorkDrive product experts

                                    Have questions about WorkDrive 5.0? Let’s talk! We recently launched Zoho WorkDrive 5.0, packed with powerful updates to help your team work smarter, stay secure, and get more value from your business content. From content-centric workflows and AI-powered

                                  • Allow the usage of Multi-Select fields as the primary field on "Layout Rules"

                                    We want to force our users to enter some explanation strings when a multi-select field has been utilized. I can't understand the reason for the usage restriction of Multi-Select fields as a primary field. This is a simple "Display hidden mandatory field

                                  • Lost Search Box with new UI Design

                                    I would like to suggest bringing back the Search Box in the new Zoho Project UI.  I use the search feature a lot and having to click the magnifying glass to type for a search is one extra click that we did not have with the old UI.

                                  • Adaptación de zoho books a la nueva regulación de facturación electrónica

                                    La facturación electrónica será obligatoria para todas las empresa en España en 2025, en Francia probablemente también e imagino pronto en todos los países europeos. Ya en 2024 todas las empresa grandes tienen que utilizar el sistema de facturación electrónica.

                                  • ZOHOBOOK Sales_QUOTE: To Add Discount At Each Line Item Table

                                    Hi, Our quote need to display with discount at each line item level as well. Try to edit template, >CUSTOMISE>EDIT TEMPLATE> Table, I can see template view with discount column, but when hit SAVE the template, view in PDF the discount column not shown.

                                  • How to account for vat with postponed VAT accounting

                                    Hi everyone, looking for some help with postponed VAT accounting, I use DHL express for my imports and they used to pay the VAT for me and then invoice me. I could then log this as a bill and the VAT element from import was recorded as input VAT, all

                                  • Weekly Automation Trigger for Equipment Calibration Reminder

                                    Dear Team, I’m currently working on an automation in the Equipment module. The goal is to track calibration schedules for tools — all of which require regular calibration. I’ve created an automation that sends email reminders for tools that are due for

                                  • Want to use Zoho Books in Switzerland. CHF support planned?

                                    Hi, We're a Swiss company using other Zoho suite software and I discovered Zoho Books and other accounting SaaS when looking for an accounting tool. Do you intend to cover Switzerland and CHF based accounting anytime soon? Roy

                                  • ADDDATE formula using 2 calculations

                                    Hello, I want to create an ADDDATE formula using 2 calculations, add 1 month and deduct 1 day. the formula that I need should look like this: ADDDATE(due_date, 1, "Months")+ ADDDATE(due_date, -1, "Days") Each row itself works fine, but when I'm trying

                                  • Upgrading from Zoho Invoice to Books was a major downgrade in functionality for me

                                    I have been a user for many years of a free plan on Zoho Invoice for my personal consulting business. I recent upgraded my organization to Books since some of the additional functionality looked useful to me, and played around with the free trial for

                                  • 🎉 Dynamic Org Chart for Zoho CRM Extension Published [2025]

                                    Hey Zoho CRM Community! We're thrilled to unveil our latest Zoho extension:🥁 Dynamic Org Chart for Zoho CRM 🎊 This extension is now available to transform the way your teams visualize, manage, and interact with org chart directly inside any Zoho CRM's

                                  • Detailed General Ledger has problem of exporting out to excel and missing ledger details for some accounts

                                    I have been encountering some problems generating Detailed General Ledger report with Zoho books. Firstly, I cannot export out the report of Detailed General Ledger to Excel. It will show this error message "invalid value passed for sort column", and

                                  • Upload a file to a File Upload Field through API

                                    Hello, Is there any way to upload a file to a custom file upload field in a module when doing an Update using the Record API? I've found and I am able to upload files to attachments but I don't know how to upload them to a field. I've checked the documentation at  https://www.zoho.com/crm/help/api/v2/#update-specify-records but the File Upload field type does not appear in the examples. Thanks

                                  • Kaizen #131 - Bulk Write for parent-child records using Scala SDK

                                    Hello and welcome back to this week's Kaizen! Last week, we discussed how to configure and initialize the Zoho CRM Scala SDK. This week, we will be exploring the Bulk Write API and its capabilities. Specifically, we will focus on executing bulk write

                                  • Automatic pricing feature for sales orders

                                    I am currently setting up my Zoho Inventory system and would like to implement an automatic pricing feature for sales orders. We have created a custom field called "brandline" for our items. All products with the same brandline value should have the same

                                  • Ability to access VAT returns and submit them has disappeared.

                                    I have been using Zoho books successfully to submit quarterly VAT returns. Without reason I have lost that facility. I have followed all available instructions eg going into settings, taxes, VAT etc. without success. My registration number is there and

                                  • How to automatically schedule a meeting After Blueprint Transition

                                    As part of our sales process, we require a technician to go to the customer's site and perform an installation prior to advancing to the next stage of the blueprint.  So this will require (a) scheduling a meeting for the technician to visit the customer's site and (b) a reminder phone call the day before the scheduled meeting. As I'm designing the Deal Blueprint, I see that I can automatically add a Task to the deal, but I can't add a Meeting or a Call. What am I missing?  There seem to be 3 types

                                  • Zoho Recruit > Workflow

                                    I have created a workflow for my application module Whenever a record is created The condition , application status is 1st call invitation I have set this OI do not receive the email that was suppose to be send out

                                  • [Webinar] Zoho Writer for SCM and logistics

                                    Creating, sharing, and distributing complex documents are constant challenges in the SCM and logistics industry. That's why our next webinar is focused on how to simplify these document workflows. Join us on Jul 10, 2025 for a Zoho Writer webinar designed

                                  • Multiple locations but one parent company

                                    I am trying to configure my accounts that have multiple locations under one parent company to show separate locations in the portal.   

                                  • Stock Count History in Product Details – Zoho Inventory

                                    Hello Zoho Team, When I perform a Stock Count for a product in Zoho Inventory, I would like to view the history of those stock counts later — especially from the product details page. Currently, I can perform the count and update the quantity, but I’m

                                  • Next Page