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

      • Get Holiday ready with Zoho Mail's Templates

        As the holiday season approaches, it’s time to step away from work and unwind. You may not be able to respond to every email or send individual messages to wish everyone holidays greetings—but It is still important to stay connected. How do you send thoughtful

      • Customize folder permissions in a Team Folder in a Team Folder via zoho Flow

        HI All, on the nth level folder of a team folder I would like to Customize folder permissions when it's created in the flow of Creating folders. That last level I only want to grant access to a specific group, goup ID 201XXXXXXXX. Can you help with a

      • Associating a Candidate to a Job Listing

        Hello, I am trying to use Zoho Forms embedded on my website for candidates to apply for a job opening. I want the form then to tie directly with zoho recruit and have the candidate be automatically inputed into Recruit as well as associated with the specific

      • Automate reminder emails for events

        Hi team, I am trying to automate send event reminders via zoho campaign to my attendees 1 day prior to my scheduled events. I used zoho flow, autoresponder in zoho campaign, as well as I used workflow and automation - but none of these methods are working.

      • Update related module entry Zoho Flow not working with custom module ?

        Hi everyone. I am facing an issue here on Zoho Flow. Basically what I am doing is checking when a module entry is being filled in with an Event ID. Event is a custom module that I created. If the field is being filled in I fetch the contact with its ID

      • How to disable time log on / time log off

        Hi We use zoho people just to manage our HR Collaborators. We don't need that each persona check in and out the time tracker. How to disable from the screen that ?

      • Dealing With One-Time Customers on Zoho Books

        Hello there! I am trying to figure out a way to handle One-Time customers without having to create multiple accounts for every single one on Zoho Books. I understand that I can create a placeholder account called "Walk-In Customer", for example, but I

      • Zoho Flow - Add to Google Calendar from trigger in Zoho Creator App

        Hello! New to Zoho Flow, but I believe I have everything setup the way it should be however getting an error saying "Google Calendar says "Bad Request". Any idea where I should start looking? Essentially some background: Zoho Creator app has a trigger

      • Email authentication

        أريد التحقق من البريد الإلكتروني

      • What’s New in Zoho Analytics – December 2025

        December is a special time of the year to celebrate progress, reflect on what we have achieved, and prepare for what’s ahead! As we wrap up the year, this month’s updates focus on refining experiences, strengthening analytics workflows, and setting the

      • Marketing Tip #12: Earn trust with payment badges and clear policies

        Online shoppers want to know they can trust your store. Displaying trust signals such as SSL-secure payment badges, return and refund policies, and verified reviews shows visitors that your store is reliable. These visual cues can turn hesitation into

      • The improved portal experience: Introducing the template view for inventory modules, enhanced configurations, and PDF export support

        Availability: Open for all DCs. Editions: All Hello everyone, You can now achieve a seamless, brand-aligned portal experience with our enhanced configuration options and the new template view for inventory modules. Your clients will now be able to view

      • Zoho Analytics Bulk Api Import json Data

        HI, I’m trying to bulk-update rows in Zoho Analytics, and below are the request and response details. I’d like to understand the required parameters for constructing a bulk API request to import or update data in a table using Deluge. Any guidance on

      • Project Management Bulletin: December, 2025

        The holiday cheer is in the air and it’s time to reflect on the year that was. At Zoho PM Suite, we've been working behind the scenes on something huge and exciting all year and now we are almost ready—with just a bit of confetti—for our grand release

      • Inventory batch details

        Hi there, I'm trying to get the batch details of an item, here's what I've done so far. I've sent cUrl request to the below endpoint and I get a successful response. Within in the response I find the "warehouses" property which correctly lists all the

      • Auto check out after shift complete

        i'm just stuck here right now, i wanna know how to do this thing, now tell me, how can i configure a custom function that runs after complete shift time if employee forget to check-out ?

      • How to create a flow that creates tickets automaticaly everyday based on specific times

        Hi guys Does anyone know how to create a flow that will create tickets automaticaly in ZOHO Desk when a certain time is reached. Im havin a hard time configuring a flow that will create tickets automaticaly everyday during specific hours of the day For

      • ZOHO FLOW - ZOHO CREATOR - ZOHO WRITER : Get Related records

        Bonjour, J'ai besoin que vous m'ajoutiez la solution "Get related Records" dans la liste de choix de zoho creator (sous Zoho flow). En effet, j'ai besoin de récupérer les champs d'un sous formulaire pour l'ajouter à l'impression de mon document. Mer

      • Will zoho thrive be integrated with Zoho Books?

        title

      • Connecting email for each department in ZohoDesk

        Hi! Could someone help me to go through connecting emails for each department?

      • How do I trigger a Flow based on a campaign response?

        Is there a way to trgiider a Zoho Flow based upon a lead opening an email sent via Zoho Campaigns? I see that the data is recorded in the 'Campaigns' section of Zoho CRM under 'Member Status' and I want to trgigger a flow based upon that record changing.

      • All Zoho Flows are filtered

        My two flows operate perfectly when I run them as a test, but when they're activated each run ends with a status of neither success, nor fail, but filtered. I haven't set up any filters. I don't see where to turn off filters. When I test run on a sequence

      • Creating Multiple Items on Sales Order

        Hi, I’m trying to automate some processes using Zoho Flow, specifically the creation of sales orders in my Zoho Inventory. However, Zoho Flow's Create Sales Order function can only add one item. I would like to include multiple items in a single sales

      • Problem Connection from Zoho Flow and Gravity Form

        I obtained my API key from Gravity Forms via WordPress. However, when I enter my Zoho Flow, it states: Gravity Forms says, 'You are not authorised to access the API." I tried recreating a new API key, but it is still not working.

      • Eventbrite Email Field in Zoho Flow Returns "Info Requested" Instead of Actual Email

        Hi Zoho team, I'm using Zoho Flow to connect Eventbrite with Zoho CRM. My goal is to automatically add event attendees as leads in Zoho CRM. I’ve set up the flow and mapped the ${trigger.profile_email}} field to the Email field in CRM. However, I'm running

      • "Invalid value passed for Product ID" Error in Zoho Flow "Create Sales Order" Node

        Hello Zoho Community, I’m facing an issue with Zoho Flow while trying to create a sales order in Zoho Inventory using the "Create Sales Order" node. Here’s a detailed explanation of my setup and the problem: What I’m Trying to Achieve I’m building an

      • Associating Project with an Account via Flow

        I'm using flow to create a Project based on a Deal status update using flow. The fields exist to pass the Account Name through properly, but when you view the Projects module in a CRM Account Record it doesn't automatically associate the new Project Record

      • How to follow up a member in a meeting?

        Hello, I make weekly meeting online with a lot of people. I want(I've been using calendly to do it). I want to do a follow-up to it. I want to send messages via Zoho-flow to all the member that participated in the meeting. How can I do it?

      • Setting Delays in Invoice Reminder Flow

        I am currently working on a flow that sends reminders for unpaid invoices. The flow is designed to delay actions until specific intervals before the due date: A reminder should be sent 7 days before the due date. A second reminder should be sent 3 days

      • Get Sales Orders Related to Inventory Item

        Dear Team, I'm just wondering if there is a way to get a list of all Sales Orders related to a specific Inventory Item. I did search all articles but couldn't find any article that could help.

      • Endpoint Central Cloud Asset Update from Fresh Service

        All, Does anyone use the asset management feature in Fresh Service? I'd like some help on building a flow to update asset attributes in Endpoint Central Cloud based off of an update to that same asset in Fresh Service. The trigger is "asset is updated"

      • Zoho Flow Export to Deluge

        It would be great to take a user built zoho flow and export the entire flow as a deluge script including having multiple connected applications (showing the API connections and webhooks) and different functionality in the other applications interacting

      • Action Iteration/Loop using Zoho Flow

        Trying to use Zoho Flow for automating following Context - A zoho form entry which has image upload field with upto 5 images setting and files are saved into Workdrive. After form is submitted need to create folder based on some fields and move files

      • Zoho Flow - Unable to evaluate formatDate with Zoho Invoice Date Field for Calendar Integration

        Hello Community, I'm trying to automate the creation of all-day events in Zoho Calendar whenever a new invoice is created in Zoho Invoice. I'm using Zoho Flow for this automation. My Goal: When an invoice is created with a specific "Event Date," I want

      • Zoho Inventory Sales Order Items

        I'm trying to build automation using Zoho Flow to add items to a Sales Order. In the automation options for both "create sales order" and "update sales order", The item ID is required. However, when I update the Sales Order, it's just replacing the item

      • Permissions for Azure Devops connection

        I am trying to set up a connection with our Azure DevOps org but it keeps giving me this error. On Azure I should be able to have admin-level access to everything. Can you please point me to which permission this is checking for so I can enable it?

      • Best way to start zoho inventory with bulk openning stock

        We are already using zoho book since long time for cars trading company. Now to streamline more, would like to import the excel data of closing stock of inventory to zoho inventory and to start on. Since we need to track each VIN (unique vehicle id number)

      • My IMAP mail suddenly stopped working

        On my iPhone and iPad, IMAP stopped working for my Zoho account with the error "User name or password incorrect" and "Invalid credentials failure" however I was able to access via web with the same credentials. Also stopped working on Apple Mail client.

      • Confused by the distiction between matched and categorized when reconciling a bank statement an how to

        I used to use quickbooks. In quickbooks, it was possible to use the check writing feature to add an expense that was on the bank statement that did not go through the AP and check writing process. I would write a check, assign it a number like etf (for

      • Not Receiving OTP • https://voters.eci.gov.in/home/family

        Hello Customer, Greetings from Zoho Mail. Upon a detailed review of our delivery logs, we can confirm that other Zoho Mail users are successfully receiving OTP emails from eci.gov.in. However, in your specific case, it appears that the OTP emails are

      • Next Page