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 owner 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
- Navigate to Tools > Custom Functions and click Manage Connections.
- Click Create Connection. Select the Default Services tab under Pick Your Service.
- Select the Zoho OAuth service from the list of services.
- Enter a suitable Connection Name. Here, we named it crm_oauth_connection. The Connection Link Name will be auto-filled accordingly.
- 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.
- Click Create and Connect. You'll be redirected to the service authentication page.
- Click Connect, then click Accept to allow DRE to access data in your Zoho account. The required connection is now created.
- The CONNECTION SUMMARY page will display your connection details.
2. Create custom function
- Navigate to Tools > Custom Functions and click Create Custom Function.
- Enter a valid function name.
- 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).
- 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.
- Click Create and your custom function will be created.
3. Script using Deluge
- Navigate to Tools > Custom functions > View Deluge Editor.
- Select the added custom function (DEALS_BETWEEN), write the following script in the editor, and click Save.
- //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.
- list DEALS_BETWEEN(date StartDate, date EndDate)
- {
- //Use toString to convert the input dates to accepted date formats in Sheet.
- start_date = StartDate.toString("yyyy-MM-dd");
- end_date = EndDate.toString("yyyy-MM-dd");
- //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.
- query_map = Map();
- query_map.put("select_query","select Deal_Name, Amount, Closing_Date from Deals where Closing_Date between '" + start_date + "' and '" + end_date + "'");
- //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.
- response = invokeurl
- [
- url: "https://www.zohoapis.com/crm/v3/coql"
- type: POST
- parameters:query_map.toString()
- connection:"crm_oauth_connection"
- ];
- //resultList is the variable to declare a list.
- resultList = List();
- response_data = response.get("data");
- //The below "for" statement parses the records inside the Deals module and fetches the specified details
- for each record in response_data
- {
- resultMap = Map();
- resultMap.put("Deal Name",record.get("Deal_Name"));
- resultMap.put("Amount",record.get("Amount"));
- resultMap.put("Closing Date",record.get("Closing_Date"));
- resultList.add(resultMap);
- }
- //Returns the response in the format expected by Zoho Sheet.
- return resultList;
- }
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!
Access your files securely from anywhere
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Meghaa Pradyumnan
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
All new Address Field in Zoho CRM: maintain structured and accurate address inputs
Availability Update: 29 September 2025: It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition exclusively for IN DC users. 2 March 2026: Available to users in all DCs except US and EU DC. 24"Create Deal" button should default to the currently selected layout/pipeline
When viewing Deals in Kanban or List view with a specific layout and pipeline selected, clicking "Create Deal" should default to that same layout — not the profile's default layout. Currently it always opens the default layout regardless of context, requiringZoho mail admin panel not opening
Dynamic Questions in Zoho Forms
Is it possible to add dynamic questions (like displaying a user’s name) in Zoho Forms? I know this is possible in surveys, but can we implement similar functionality in Zoho Forms?How to add "All Open AND Overdue" back to the Home Page Task Component?
Hi everyone, I’m looking for a way to restore the Tasks component dropdown list on the Zoho CRM Home Page. Since the recent update to the Task area in my Home Page Classic View, the dropdown options (e.g., My Next 7 Days + Overdue) are too restrictiveCanvas: is it possible to have a fixed header?
Hello. Does Canvas provide the option to have a fixed header, similar to the standard view? It would also be interesting for other parts of the interface, like the header or sidebar of the tab section. Thanks!Duplicate Leads Concerns with Round Robin and Lead Approval Process
It is great to have the Duplicate Lead Approval Process, there are a few issues with the process that I would greatly appreciate taken consideration in enhancing. It appears that A Lead comes in Lead owner assigned by the Round Robin Check for Duplicate,Removing Related Modules Lookup Fields Assignment / Relationship
Issue: When creating a related list, I accidently selected module itself creating a circle reference. See attached. Situation: I wish to relating a custom module called "Phone Calls" to Leads and Contacts. Outcome: 1) I either want to remove the thisDesk API that can search Merged Tickets
Is there any method that can still search a ticket that was already merged? zoho.desk.searchRecords - doesn't explicitly mentionZoho LandingPage pricing update: Essentials plan is now fully free!
Hello Everyone, We have updated the Zoho LandingPage pricing structure. The Essentials plan, previously priced at $9 per month for 1,000 visitors, is now fully free, and the visitor limit has been increased to 5,000. With this change, users can startZoho Surveys
Dear Zoho Support Team, I hope this message finds you well. I am writing to inquire about the availability and documentation for the Zoho Survey API. Background: I am currently working on a project that requires programmatic access to survey data andZOHO.CRM.UI.Record.open not working properly
I have a Zoho CRM Widget and in it I have a block where it will open the blocks Meeting like below block.addEventListener("click", () => { ZOHO.CRM.UI.Record.open({ Entity: "Events", RecordID: meeting.id }).catch(err => { console.error("Open record failed:",Zoho Survey – Page Skip Logic Not Working
Hi everyone, I'm experiencing an issue with the page skip logic in Zoho Survey. Last week, it was working fine, and I haven’t changed anything in the settings. However, today the skip logic is not working at all. I also tried testing it with differentIntroducing a smarter, faster, and more flexible charting experience
Hello Zoho Sheet users, We're delighted to share the latest news about a major update to charts in Zoho Sheet! The new version supports dynamic data ranges, granular styling options, faster loading, and other interesting enhancements that allow you toBuilt-in Date Functions in Zoho Analytics Query Tables
I have a doubt about whether Zoho Analytics Query Tables provide built-in functions for start date, end date, and the current monthZoho Form URL displays incorrect name
Hi, I have a form I created called "Design Request form". It displays this way everywhere I look. However, in the URL, it shows up as "DesignJobRequestFormFINAL011325PROOFV1B" and I'm not sure why. I can't find where to fix this. Does anyone have anyIntroducing Radio Buttons and Numeric Range Sliders in Zoho CRM
Release update: Currently out for CN, JP, AU and CA DCs (Free and standard editions). For other DCs, this will be released by mid-March. Hello everyone, We are pleased to share with you that Zoho CRM's Layout Editor now includes two new field formats—Is there a way to update all the start and end dates of tasks of a project after a calendar change?
Hi! Here's my situation. I've built a complete project planning. All its tasks have start dates and due dates. After completing the planning, I've realized that the project calendar was not the right one. So I changed the project calendar. I now haveWriting SQL Queries - After Comma Auto Suggesting Column
When writing SQL Queries, does anyone else get super annoyed that after you type a comma and try to return to a new line it is automatically suggest a new column, so hitting return just inputs this suggested column instead of going to a new line? AnyoneFinding rhythm through poetry
Poetry has long been a powerful form of expression, discovery, and reflection. For many, it is a way to pen down their thoughts and experiences. The "poetic license" allows writers to shape their words with rhythm and flow. This year, on World PoetryZoho Desk Ticket SLA Level
Hello, we have 2 levels of SLA escalation for our Tickets. Is there a way to display on the ticket information the level of SLA escalation the ticket currently on? I am aware that we can see in the ticket history the level of escalation that has beenAllow Font Size 11 in Editors...
That is basic functionality...Darshan Hiranandani : How many participants can join a Zoho Meeting at once?
Hi everyone, I'm Darshan Hiranandani, trying to find out the maximum number of participants that can join a Zoho Meeting at once. Has anyone here used Zoho Meeting for larger groups and can share their experience or knowledge about the participant limit?【まだ間に合う！】Zoho ユーザー交流会 | AI活用・CRM・Analytics の事例を聞いて、ユーザー同士で交流しよう！
ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 3月27日(金)に東京、新橋で開催する「東京 Zoho ユーザー交流会 NEXUS」へのお申し込みがまだの方は、この機会にぜひお申し込みください！（参加無料） ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー イベントの詳細はこちらから▷▷ https://www.zohomeetups.com/tokyo2026vol1#/?affl=communityforumpost3 ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーWhere is the scheduled report in New UI?
Hi Team, Seems there is not such a field in New UI, hence I have to switch to old UI to handle request..Building a Simple Raspberry Pi + Keypad IoT PoC with Zoho Flow
I want to share a simple IoT PoC I recently built that connects a Raspberry Pi to Zoho Flow and Zoho Creator. The goal was to send PIN input data from a hardware keypad to Zoho and trigger a servo and buzzer for visual/audible feedback. This can serveZoho Zoom extension for CRM
When meetings are booked through Zoho using the Zoom extension, the Zoom link in the invite is a host start link that contains an expiring ZAK token. The invite should instead use a standard attendee join link or a non-expiring host link - why is thisEnable integration of CRM CPQ functionality for ZohoOne customers using Zoho Finance application
Hi there. I can't believe I'm needing to launch this idea as I would have thought this was a little obvious. Following a number of conversations with the technical team it's become evident that the CPQ functionality within CRM cannot integrate with ZohoWhat's New in Zoho Billing | January 2026
Excited about the latest enhancements in Zoho Billing? Our January updates bring an intelligent AI assistant, smarter subscription management, and improved tax compliance, saving you time and reducing manual work. Dive into the details below to see howIs there a way to sell in a practical method multiple subscriptions of the same product? i.e. domain names
In evaluating Zoho Billing, a hurdle to adopting it is that Zoho Billing does not seem designed to support businesses that sell multiple subscriptions of the same product. In our case, we need to sell and manage several domain names per client. Am I rightZoho Writer Frequently not loading
I've reported this as a problem already but I can't log into my email right now or get onto the main site so you're going to hear about it again here: at least once a week, Zoho Writer will just refuse to load entirely. The main page will load and loadUpdate P_Leave: code: 7052 "Employee_ID": "Enter Employee ID"
Hi, Zoho People - Update Leaves Can someone assist? ------------------------------------------------------------------------------------------ col = Collection(); col.insert("recordid":id); col.insert("Date_Check_Approval":zoho.currentdate); info zoho.people.update("P_Leave",col.toMap());Can't change form's original name in URL
Hi all, I have been duplicating + editing forms for jobs regarding the same department to maintain formatting + styling. The issue I've not run into is because I've duplicated it from an existing form, the URL doesn't seem to want to update with the newCan't rename groups on Mac desktop app
I'm working on an up-to-date Mac with a freshly downloaded Notebook app. I'm trying to rename a group within a notebook. Here I have, left to right, a note, a group, and a note. I select the group. On the top left, I select Action. On the dropdown, "Rename"Custom CSS for Zoho CRM Team Bookings embeded widget
Hello, we are adding Zoho CRM Team Bookings (crm.zoho.com) in our public website. We know that we can change Theme Color, Font Color and Background Color: Zoho CRM Booking Styling But is it possible to change other CSS attributes e.g. Font Family, likeSAP Business One(B1) integration is now live in Zoho Flow
We’re excited to share that SAP Business One (B1) is now available in Zoho Flow! This means you can now build workflows that connect SAP B1 with other apps and automate routine processes without relying on custom code. Note: SAP Business One integrationStrange behavior in CRM Number Field – Characters allowed but not "e"?
Hi everyone, Has anyone faced this strange issue in Zoho CRM? In a Number field, it is possible to type some characters, but the character "e" cannot be entered. This was really surprising to me. Normally, a number field should restrict all charactersBest Way to Manage Approvals Within Blueprint Stages?
Hi, I am working on a requirement involving Blueprint and approval logic in Zoho CRM and would appreciate some guidance. I understand that approval processes do not trigger when a record is currently within a blueprint, which makes it challenging to implementMarketing Tip #25: Grow your social presence with a simple posting routine
Consistency is one of the biggest growth drivers on social media. Regular content keeps your store visible and helps customers remember you. Even 3–4 posts a week can build momentum over time. The easiest way to stay consistent is to stop trying to createDashboard target enhancements
Often individuals in IT are creating dashboards for their sales team. The ability to create a single dashboard that can be used by multiple people is key. A components for a dashboard have the ability to filter by logged in user which is great. HoweverNext Page