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

      • Customer Management: #3 Giving Customers Control & Privilege

        Rio, the founder of RenoTech Solutions, a fast-growing digital service company, found itself juggling a dozen different services for its clients. They handled one-time setup fees, recurring monthly invoices, and custom milestone-based billing for projects.

      • Can I use a Standalone CRM Function as the Callback URL For Async Export Data API?

        I am creating an export job using this API https://www.zoho.com/analytics/api/v2/bulk-api/export-data-async/create-export/view-id.html There is a "callbackUrl" key in the CONFIG object. I tried copying the URL for a standalone function in CRM which can

      • Add RTL (Right-to-Left) Text Direction Support Across All Zoho Learn Editing Interfaces

        Hi Zoho Learn Team, Hope you're doing well. We would like to request an important enhancement to Zoho Learn regarding support for right-to-left (RTL) languages such as Hebrew and Arabic. 🔹 Current Issue While the Knowledge Base Article editor provides

      • Add Hebrew Support for Meeting Transcripts Provided by ZIA in Zoho Cliq

        Hi Zoho Cliq Team, Hope you're doing well. We would like to request the addition of Hebrew language support for the Meeting Transcript and Summary feature in Zoho Cliq. Currently the transcript and summary feature is available for recorded meetings and

      • Remote Control Functionality During Screen Sharing in Zoho Cliq

        Hello Zoho Cliq Team, We would like to request the addition of remote control functionality during screen sharing sessions in Zoho Cliq. Currently, while screen sharing in Cliq is very useful, it lacks the ability for another participant to take control

      • Centralized Organization Information Management in Zoho One

        Dear Zoho One Support, I'm writing to propose a feature that would significantly improve the user experience and streamline data management within Zoho One. Current Challenge: Currently, managing organization information across various Zoho One apps requires

      • Enhance Zoho One Conditional Assignment to Fully Reassign App Settings When Changing Departments

        Hi Zoho Team, We’d like to submit a feature request regarding the current behavior of Zoho One’s conditional assignment logic when moving a user between departments. 🔧 Current Limitation As it stands, Zoho One’s conditional assignment does not remove

      • Ability to Filter Alias Mailboxes in Zoho Recruit

        Dear Zoho Recruit Team, I hope you are doing well. We would like to request a feature enhancement regarding the handling of alias mailboxes in Zoho Recruit. Currently, when we connect an alias mailbox (e.g., jobs@domain.com) from our Zoho One account

      • Automatic Department and Employee Sync Between Zoho One and Zoho People

        Dear Zoho Support, I'm writing to propose a valuable feature request that would streamline data management and improve user experience within the Zoho ecosystem: automatic synchronization between departments and employees in Zoho One and Zoho People.

      • Prefered Bin Missing in android APP

        Andoroid app dosent show preferred bin in the picklist. The workaround support reccomend is to use the computre to create the picklist. it shuld be information to be shown aas basic for the pciker.

      • Open Sans Font in Zoho Books is not Open Sans.

        Font choice in customising PDF Templates is very limited, we cannot upload custom fonts, and to make things worse, the font names are not accurate. I selected Open Sans, and thought the system was bugging, but no, Open Sans is not Open Sans. The real

      • Function #1: Convert an accepted Estimate to Sales Order automatically in Zoho Books

        As you’re aware, Zoho Books provides a default option to have the estimates automatically converted to invoices once your customer accepts them. Many of you wanted a similar option for sales orders, so here’s a workflow that converts accepted estimates

      • Reusable Jira Connection for Multiple Zoho Projects Imports

        Hello Zoho Projects Team, We would like to raise a concern and submit a feature request regarding the Jira → Zoho Projects migration process, specifically around how Jira connections are handled. Current Behavior: When setting up a Jira connection for

      • Zoho invoice doesn't support Arabic language

        I added a clause in the terms & conditions section in Arabic but it doesn't appear when I sent or print it.

      • Recurring Invoice Placeholder Not Updating Billing Period

        Hi, I’m using Zoho Invoice Free and want the billing period to update automatically in recurring invoices. In Item Description I tried: Billing Period: %(m-6)% %(y)% to %(m-1)% %(y)% but even if the invoice date is in 2026, it still shows the period based

      • Related list Mobile Device

        Hello, We use an the Zoho creator application to make reports linked to Accounts. On the computer: it's easy to go the Account and see all the created reports in the related list below On iPad/Phone ZOHO CRM APP: we cannot see the reports on those accounts

      • Can't update the company address in zoho invoice

        Dear Sir/Madam, I want to update the company address in Zoho Invoice but failed. It popped out a sentence "Invalid value passed for Website". Please advice how to solve this problem. Thank you.

      • Is there a plan to allow for the hierarchical organization of Customers / Companies in Zoho Billing?

        We have a few customers who have organizational structures that we haven't quite found a way to deal with in Zoho Billing. In CRM, these sub-companies (or subsidiaries or whatever you want to call them) all have another CRM account as the parent account.

      • 【Zoho CRM】作業リスト機能リリースのお知らせ

        ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中から、作業リスト機能リリースのお知らせ情報をご紹介します。 目次 作業リスト機能　概要 機能①　自分の未完了の活動 機能②　処理待ち 機能③　自分の作業リスト 作業リスト機能　概要 営業チームでは、CRM内に業務やデータが分散しているため、管理が煩雑になりがちです。この断片化は、機会の見逃しや生産性の低下につながります。 作業リスト機能は、タブを横断する業務項目をひとつのカスタマイズ可能なダッシュボードに集約し、業務を効率的に管理できるようにします。

      • SalesIQとPageSenseの利用について

        初めての投稿で場違いだったらすいません。 弊社ではSalesIQを運用しているのですが、追加でPageSenseの導入もしたいと現場からの声があります。 両サービスともクッキー同意バナーが必要なサービスなのですが 弊社では同意無しに情報はとりませんという方針なので 2つ入れると2つバナーを出す必要がでてきます・・・ 両サービスを運用されてる方があれば運用状況とか教えてほしいです。 PageSenseについては詳細まで機能を理解してないなかでの質問です。

      • Parent-Child Tickets using API or Deluge

        Hi Everyone, We are looking at the parent-child ticketing features in Zoho Desk. We want to be able to create a parent ticket at customer level and nest child tickets underneath. The issue we are facing is to be able to automate this. I'm checking the

      • Closing connected ticket after closing WA conversation

        Hi, At the moment, once someone sends a message to our WA number, a corresponding ticket is automatically created. After the question is answered, our support department closes the chat/conversation. However, after that, the connected ticket is still

      • Note sync turn off

        Hi, Is it possible to turn off notes sync between task notes and the parent module? (Account/Deal)

      • Basic Plan Active but Survey Creation Still Limited to 3 Surveys

        I have an active Basic (Monthly) subscription (valid period: Dec 24, 2025 – Jan 24, 2026), but the system still limits survey creation to only 3 surveys, which matches Free plan behavior. The subscription appears active in Portal Information, however

      • 2025 in Review: Powering Field Services Forward—One Feature at a Time

        As 2025 draws to a close, it’s time to pause and reflect on a year of meaningful progress at Zoho FSM. This year was all about listening closely to our customers and partners, building with purpose, and continuously improving the way field service teams

      • API Support for Creating Invoices with Batch-Tracked Items

        Hi Zoho Community, I am working on an integration where we create invoices in ERPNext and push them to Zoho Books. I need to send batch-tracked items (batch numbers) when creating invoices. I could not find any reference in the Zoho Books API documentation.

      • New to automation - please help

        Hi there! We are new to automations in Zoho. We built out one automation campaign and it seems to be firing off for some people but others not. Please advise what's the best practice or if anything we should tweak. Thank you so much!

      • Unable to Create Zoho Booking via the Book Appointment API

        Its giving the below error {     "response": {         "errormessage": "Error setting value for the variable:customer_details\n null",         "status": "Error"     } } Request: POST Url: https://www.zohoapis.in/bookings/v1/json/appointment attached Zoho-oauthtoken

      • Task Details on task template

        When creating a task template in settings that task details seem to be missing. Is it not possible to set the details of a task, such as the priority, type, reminder settings, and custom field values?

      • Building Toppings #3 -Testing and publishing Bigin toppings

        Hey Biginners! In previous forum posts, we discussed what a Bigin topping is and explored the Bigin Developer Console's features. In this post, we're going to create a topping from scratch and discuss how to test and publish it. Our topping will automate

      • markdown files?

        How can I import a markdown file into a note?

      • Mail to Zoho Notebook

        In the Instuctiosn I faound as email add@notebook.app In my account I see add@eu.notebook.app What is correct please

      • Auto-Invite Users to Portals in Zoho CRM based on Conditions

        Hello Everyone, You can now automate portal invitations in Zoho CRM with the new Auto-Invite users feature in Portal management. No more manually enabling portal access one by one. With this enhancement, you can automatically send invites for users to

      • Growth You can Count On: Zoho Billing's 2025 Journey

        As we wrap up 2025, we’re reflecting on a year rooted in steady progress and meaningful growth for Zoho Billing. Every enhancement we have introduced was built to support scalable and reliable billing growth for your business. From launching the Enterprise

      • Need help with creating workflow with Bigin and Clickup

        Can you please help me with this? I am trying to create a workflow in Zoho Flow wherein a bigin Pipeline deal moves to Decision Closing, it will create a task and subtasks in Clickup, under the condition/filter that the Deal name contains the word Social

      • [Webinar] Automate generation of wills, trusts, POAs, and other estate planning documents with Zoho Writer

        Managing the lifecycle of the estate planning documents such as wills, trusts, and POAs, from client intake to final storage, can be complex and time-consuming. Join our live webinar to learn how Zoho Writer transforms this process by automating document

      • Create / Update Rule in TeamInbox when record is updated in ZohoCRM using flow?

        I'm currently evaluating options and trying to work out if the possible scenario is possible; If a record in ZohoCRM is updated, take 2 of the fields from that - lets call them 'OrderRef' and 'OrderTo'. Using flow, if a record is added or updated, add

      • Plans to allow more columns of monitoring, and monitoring not only your own channels?

        Are their any plans to allow more columns of monitoring, and monitoring not only your own channels? Here's why - I'm sure I'm not alone in that we sell other brands products, so not only am I interested in my own brand social channels, but also the social

      • Unwrapping the best practices for the Zoho Desk mobile app: Part 1

        In focus: Empowering the field and frontline customer support representatives Imagine having to carry your computer everywhere to respond to customers, handle escalations, and update your process. This series presents you with efficient tips to handle

      • dealing with post dated cheques

        Hi, can you help me please the best way to deal with this We sell an item of three months duration that is paid for with post dated cheques in monthly stages example - item is sold £3000 Cheque 1 is for £1000 due at time of sale (say Sept) cheque 2 is dated 25th of next month (Oct) cheque 3 is dated 25th of next month +1 (Nov) Now, with invoice number one it's simple - i send a standard invoice as usual But with the other two here's what i want zoho to do next month i want it to send an invoice on

      • Next Page