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
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
How can i resend a campaign to only one of the recipients on the original campaign
How can i resend a campaign to only one of the recipients on the original campaign ? Sincererly, MikeHow to show branch instead of org name on invoice template?
Not sure why invoices are showing the org name not the branch name? I can insert the branch name using the ${ORGANIZATION.BRANCHNAME} placeholder, but then it isn't bold text anymore. Any other ideas?Create CRM Deal from Books Quote and Auto Update Deal Stage
I want to set up an automation where, whenever a Quote is created in Zoho Books, a Deal is automatically created in Zoho CRM with the Quote amount, customer details, and some custom fields from Zoho Books. Additionally, when the Sales Order is convertedMarketing Automation Requirements Questions
I would like to set up a multi-email drip campaign- please see the structure below and confirm if I can achieve this set up in Zoho marketing automation. Where applicable, highlight gaps and workarounds. Thanks Drip email campaign- Can I create one dripZoho CRM for Gmail Extension Not Working in Brave Browser?
Is anyone able to get the Zoho CRM Chrome Extension working in the Brave browser? They're both built on the Chromium platform and every other Chrome Extension works with the exception of Zoho CRM for Gmail so any ideas here?Sharing URLs and direct access
Hello, I am storing my team's email signature images on Workdrive. I am creating a public image download share and adding “?directDownload=true” so that the image can be accessed without the Workdrive interface. A few questions: 1) Can we generate friendlyhow to change the page signers see after signing a document in zoho sign
Hello, How can I please change the page a signer sees after signing a document in Zoho Sign? I cannot seem to find it. As it is now, it shows a default landing page "return to Zoho Sign Home". Thanks!Question about using custom_fields in Storefront Add-to-Cart API (error 2003 – required details)
Hi everyone, I’m working with the Zoho Commerce Storefront API, specifically the Add to Cart endpoint: POST /storefront/api/v1/cart According to the documentation, this endpoint supports a custom_fields parameter for adding line-item custom data. I’mCan a project be cloned?
Good afternoon, greetings. I would like to ask if it's possible to clone a project in Microsoft Project. I found a way to do it using templates, but I'm not sure if there's a direct way to clone a project. Thank you in advance for your attention, andTimesheet Tasks in Zoho Books: associate to service item
How do we associate a service item to timesheet tasks in Zoho Books? For example: Joe spent 5 hours on project:task1 which is Service Item#1 (Income:Service1). When the invoice is issued thru the Project Invoice section, this is not available. When theWhy Sharing Rules do Not support relative date comparison???
I am creating a Sharing Rule and simply want to share where "Last Day of Coverage" (Date field) is Greater than TODAY (Starting Tomorrow). However, sharing rules don't have the option to compare a date field to a relative date (like today), only to StaticTask/Activity indicator in SalesPipeline overview has disappeared
I Just logged in my ZOHO CRM first 2026 checking my salespipeline overview , Every record card used to show an indication that there was an open task (Yellow if the expiry date was close, red if the expiry date was today and grey when it had expired).Tip #56- Accessibility Controls in Zoho Assist: Hearing- 'Insider Insights'
As we begin the new year, it’s a great time to focus on making our tools more inclusive and accessible for everyone. Remote support often involves long hours in front of screens, varying lighting conditions, and users with diverse accessibility needs.Zoho Desk Android app update: Table view for All Departments view, custom button
Hello everyone! In the latest version(v2.9.25) of the Zoho Desk Android app update, we have introduced Table view for the 'All Departments' view in the ticket module. We also have supported an option that allows tickets in the Table view to be sortedWhat's New - December 2025 | Zoho Backstage
In December, Backstage introduced a focused set of updates that improve how you manage registrations, communicate with attendees, and track participation. These enhancements are designed to give organizers greater flexibility and clearer control acrossNeed code format to specify default values
Can someone please direct me to the code syntax or the proper translation per the instructions circled below. These instructions don't seem correct.Problem with CRM Connection not Refreshing Token
I've setup a connection with Zoom in the CRM. I'm using this connection to automate some registrations, so my team doesn't have to manually create them in both the CRM and Zoom. Connection works great in my function until the token expires. It does not refresh and I have to manually revoke the connection and connect it again. I've chatted with Zoho about this and after emailing me that it couldn't be done I asked for specifics on why and they responded. "The connection is CRM is not a feature toAdd multiple users to a task
When I´m assigning a task it is almost always related to more than one person. Practical situation: When a client request some improvement the related department opens the task with the situation and people related to it as the client itself, the salesmanA Roundup of Zoho Sprints 2025
Sorting Custom Date in API isn't working w pagination limit
How can we sort a custom field with DATE using pagination? Starting at page=1 then moving to page=2 with a limit of 10 each, its all messed up and even shows some of the same records as page 1? https://www.zohoapis.com/crm/v2/INVOICE_MODULE/search?criteria=(FM_Contact_ID:equals:1234)&sort_by=Invoice_Date&sort_order=desc&per_page=10&page='SAP 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 integrationEnhancement in Role and Profile mapping of agents in Sandbox
Hello everyone! We have brought in a modification in the way users are mapped to a particular role and profile in Sandbox. What has changed? When agents are copied from production to Sandbox: If a user's current role and profile is available in Sandbox,The reason I switched away from Zoho Notebook
My main reason for switching to Zoho was driven by three core principles: moving away from US-based products, keeping my data within India as much as possible, and supporting Indian companies. With that intent, I’ve been actively de-Googling my digitalDecimal places settings for exchange rates
Hello, We are facing issues while matching vendor payments with banking feeds. As we often import products/services exchange rate comes into play. Currently, ZOHO allows only six digits for decimal places. We feel that conversions like JPY to INR requireZoho removed ability to see all Scheduled Reports!
If you are not the owner of a scheduled report, Zoho recently removed the capability to see each scheduled report. As an admin who relies on seeing all scheduled reports being sent, this is a terrible update. Now I cannot see ANY scheduled reports...even the ones I am being sent!! This should be a setting for admins to control. This is a bad update.Automate Backups
This is a feature request. Consider adding an auto backup feature. Where when you turn it on, it will auto backup on the 15-day schedule. For additional consideration, allow for the export of module data via API calls. Thank you for your consideration.GCLID and Zoho Bookings
Is there anyway to embed a Zoho Bookings signup on a landing page and pass the GCLID information? More specifically, can this be done using auto-tagging and not manual tagging the GCLID? I know Zappier has an integration to do this but is there a betterMerge Items
Is there a work around for merging items? We currently have three names for one item, all have had a transaction associated so there is no deleting (just deactivating, which doesn't really help. It still appears so people are continuing to use it). I also can't assign inventory tracking to items used in past transactions, which I don't understand, this is an important feature moving forward.. It would be nice to merge into one item and be able to track inventory. Let me know if this is possible.Create PO from an invoice
We are a hardware and software sales company which receives orders over the internet. We drop ship most of our products from a warehouse outside of our company. Our orders get sync'd into Zoho from our store via onesaas as invoices. It would be greatBlueprint or Validation Rules for Invoices in Zoho Books
Can I implement Blueprint or Validation Rules for Invoices in Zoho Books? Example, use case could be, Agent confirms from client that payment is done, but bank only syncs transactions tomorrow. in this case, Agent can update invoice status to done, andResetting auto-number on new year
Hi everyone! We have an auto-number with prefix "D{YYYY}-", it generates numbers like D2025-1, D2025-2, etc... How can we have it auto-reset at the beginning of the next year, so that it goes to D2026-1? Thanks!The Social Wall: December 2025
Hello everyone! As we wrap up the final edition of the Social Wall for 2025, it’s the perfect time to look at what went live during December. QR code generator From paying for coffee to scanning metro tickets, QR codes are everywhere and have made everydayCustom AI solutions with QuickML for Zoho CRM
Hello everyone, Earlier, we introduced Custom AI Solutions in CRM that let you access QuickML for your custom AI needs. Building on that foundation, we’ve now enabled a deeper integration: QuickML models can be seamlessly integrated into CRM, and surfaceHelper Functions and DRY principle
Hello everyone, I believe Deluge should be able to use 'Helper functions' inside the main function. I know I can create different standalones, but this is not helpful and confusing. I don't want 10000 different standalones, and I dont want to have toAdd specific field value to URL
Hi Everyone. I have the following code which is set to run from a subform when the user selects a value from a lookup field "Plant_Key" the URL opens a report but i want the report to be filtered on the matching field/value. so in the report there isIntroducing workflow automation for the Products module
Greetings, I hope all of you are doing well. We're happy to announce a few recent enhancements we've made to Bigin's Products module. The Products module in Bigin now supports Workflows, enabling you to automate routine actions. Along with this update,Power up your Kiosk Studio with Real-Time Data Capture, Client Scripts & More!
Hello Everyone, We’re thrilled to announce a powerful set of enhancements to Kiosk Studio in Zoho CRM. These new updates give you more flexibility, faster record handling, and real-time data capture, making your Kiosk flows smarter and more efficientZia Formula Expression Generator for Formula fields
Hello everyone! Formula fields are super useful when you want your CRM to calculate things for you but writing the expression is where most people slow down. You know what you want, but you’re not fully sure which function to use, how the syntax shouldIssue with Zoho Creator Form Full-Screen View in CRM Related List Integration
Hi Team, We have created a custom application in Zoho Creator and integrated it into Zoho CRM as a related list under the Vendor module, which we have renamed as Consignors. Within the Creator application, there is a form named “Pickup Request.” InsideWrapping up 2025 on a high note: CRM Release Highlights of the year
Dear Customers, 2025 was an eventful year for us at Zoho CRM. We’ve had releases of all sizes and impact, and we are excited to look back, break it down, and rediscover them with you! Before we rewind—we’d like to take a minute and sincerely thank youNext Page