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

    • Latest updates in Zoho Meeting | Breakout rooms and End to end encryption

      Hello everyone, We’re excited to share a few updates for Zoho Meeting. Here's what we've been working on lately: Introducing Breakout Rooms for enhanced collaboration in your online meetings and End-to-end encryption to ensure that the data is encrypted

    • Systematic SPF alignment issues with Zoho subdomains

      Analysis Period: August 19 - September 1, 2025 PROBLEM SUMMARY Multiple Zoho services are causing systematic SPF authentication failures in DMARC reports from major email providers (Google, Microsoft, Zoho). While emails are successfully delivered due

    • Accidentally deleted a meeting recording -- can it be recovered?

      Hi, I accidentally deleted the recording for a meeting I had today. Is there a way I can recover it?

    • How to access email templates using Desk API?

      Trying to send an email to the customer associated to the ticket for an after hours notification and can't find the API endpoint to grab the email template. Found an example stating it should be: "https://desk.zoho.com/api/v1/emailtemplates/" + templateID;

    • Update Portal User Name using Deluge?

      Hey everyone. I have a basic intake form that gathers some general information. Our team then has a consultation with the person. If the person wants to move forward, the team pushes a CRM button that adds the user to a creator portal. That process is

    • Unable to retrieve Contact_Name field contents using Web API in javascript function

      Hello, I've added a field in the Purchase Order form to select and associate a Sales Order (Orden_de_venta, lookup field). I've also created a client script to complete some fields from the Sales Order (and the Quote), when the user specifies the related

    • Updating Woocommerce Variation Products Prices Via Zoho CRM

      I can update product prices with this flow: But I can't update variant products. I got a code from Zoho for this, but I couldn't get it to work. It needs to find the product in the CRM from the SKU field and update the variation with the price there.

    • Emails Disappearing From Inbox

      I am experiencing the unnerving problem of having some of the messages in my inbox just disappear.  It seems to happen to messages that have been in there for longer than a certain amount of time (not sure how long exactly). They are usually messages that I have flagged and know I need to act on, but have not gotten around to doing so yet.  I leave them in my inbox so I will see them and be reminded that I still need to do something about them, but at least twice now I have opened my inbox and found

    • Power of Automation :: Automatic removal of project users once the project status is changed.

      A custom function is a software code that can be used to automate a process and this allows you to automate a notification, call a webhook, or perform logic immediately after a workflow rule is triggered. This feature helps to automate complex tasks and

    • Customizing Form Questions per Recipient Group in Zoho Campaigns/Forms

      Hello everyone, I would like to ask if it’s possible in Zoho Campaigns or Zoho Forms to send out a campaign where the form questions can be customized based on the group of recipients. Use case example: I have prepared 20 questionnaire questions. For

    • Forms - Notification When Response Submitted

      How do I set it up to generate an email notification when a response (class request) is submitted?

    • How to disable user entry on Answer Bot in Zobot

      Hi, I have an Answer Bot in my Zobot, here is the configuration: I only want the user to choose 1 of the 4 the options I have provided: When no answer found, user chooses 'I'll rephrase the question' or 'Ask a different question When answer is found,

    • More admin control over user profiles

      It's important for our company, and I'm sure many others, to keep our users inline with our branding and professional appearance. It would be useful for administrators to have more control over profile aspects such as: Profile image User names Email signatures

    • Please Make Zoho CRM Cadences Flexible: Allow Inserting and Reordering Follow-Up Steps

      Sales processes are not static. We test, learn, and adapt as customers respond differently than expected. Right now, Zoho Cadences do not support inserting a new step between existing follow-ups or changing the type of an existing primary step. If I realize

    • Clear Tag & Linking Between Quotes and Sales Orders

      Hi Zoho Team, In Zoho Books, when a quote is converted into a sales order, it would be extremely useful to have: A clear tag/indicator on the quote showing that it has been converted into a sales order. A direct link in the sales order back to the originating

    • Add Direct Ticket Link to Zoho Help Center Portal in Email Replies

      Hi Zoho Support Team, We hope you're doing well. We’d like to request a small but valuable improvement to enhance the usability of the Zoho Help Center portal (https://help.zoho.com/portal/en/myarea). Currently, when someone from Zoho replies to a support

    • [Webinar] Deluge Learning Series - AI-Powered Automation using Zoho Deluge and Gemini

      We’re excited to invite you to an exclusive 1-hour webinar where we’ll demonstrate how to bring the power of Google’s Gemini AI into your Zoho ecosystem using Deluge scripting. Whether you're looking to automate data extraction from PDFs or dynamically

    • Connecting Zoho Inventory to ShipStation

      we are looking for someone to help connect via API shipStation with Zoho inventory. Any ideas? Thanks. Uri

    • Subform edits don't appear in parent record timeline?

      Is it possible to have subform edits (like add row/delete row) appear in the Timeline for parent records? A user can edit a record, only edit the subform, and it doesn't appear in the timeline. Is there a workaround or way that we can show when a user

    • New in Cadences: Option to Resume or Restart follow-ups when re-enrolling records into a Cadence, and specify custom un-enrollment criteria

      Managing follow-ups effectively involves understanding the appropriate timing for reaching out, as well as knowing when to take a break and resume later, or deciding if it's necessary to start the follow-up process anew. With two significant enhancements

    • Im Stuck in an EDIT ONLY WITH WIZARD issue

      So I found Wizards to be a really helpful tool in minimizing the exposure of redundant, superfluous fields to staff that would never otherwise have to edit those fields. My issue is, that when the record (in this case a lead) is created with a wizard,

    • Account upgrade

      Good evening, I upgraded my account and paid for it. From standard to professional. Unfortunately after the paiment my account was not upgraded. Please your advise. Best Regards Erik van Staverden

    • How to set ALL default dates of my organization to DD-MM-YYYY format?

      All replies to this question comes from a time where the UI was different. It's extremely frustrating not being able to find how to do this simple setting change. I want everything and everyone in my organizations to have DD-MM-YYYY date format by default.

    • How can I sync from Zoho Projects into an existing Zoho Sprints project?

      Hi I have managed to integrate Zoho Projects with Zoho Sprints and I can see that the integration works as a project was created in Zoho Sprints. But, what I would like to do is to sync into an existing Zoho Sprints project. Is there a way to make that

    • Can we generate APK and IOS app?

      Dears, I want to know the availability to develop the app on zoho and after that .. generate the APK or IOS app  and after that I added them to play store or IOS store.. Is it possible to do this .. I want not to use zoho app or let my customers use it. thanks 

    • Zoho Subform Workflows onAdd of Row

      Suppose I have a form with attached workflows onLoad. If I use the form as a subform, will it inherit the workflows or do I need to create new ones onAdd of row?

    • Super Admin removal

      I brought a sub, and I gave the Super admin rights to a person who is no longer with us, so I need to change, and I need to make myself the Super admin

    • Better Notes Commenting

      Hi, I'd like to suggest better collaboration tools for NOTES. The current notes section for Accounts, Contacts and Deals is not ideally suitable for any degree of communication or collaboration. When responding to a note, there is no ability to leave

    • Exporting Templates

      I have just spent 2 hours creating a project template for a Netsuite configuration, and want to share it with other Zoho Projects users - who have a different account. Is there any way to do this?

    • Power of Automation:: Streamline Associated Teams based on the Task Owner update.

      Hello Everyone, A Custom function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. Requirement:

    • No Response from Zoho Support in 8 Days - Typical?

      I have a couple of issues I'm trying to work through. Initially, I was getting support from support@zohofsm.com, but I have not received a response in 8 days (11 on another question). Is this typical? Can I pay for support? For context, I am not spamming

    • Add QUOTE OWNER profile image to a Quote Template

      I can add their email address.. phone number, DOB. I need to add a users profile picture so when they assign a template to a quote they own it adds their picture to the cover page. I've tried hacking a solution together but there has to be an easier way.

    • Zoho Connections Desk API relative URL PATTERN_NOT_MATCHED

      While i am trying to do this: async function fetchTicketsFromDesk(timeFilter = 'current_month') { try { const response = await ZOHO.CRM.CONNECTION.invoke("desk_connection", { url: "/api/v1/tickets", method: "GET", }); const data = response.details ? JSON.parse(response.details)

    • Zoho CRM - Custom Views for Portal Users

      I'm looking for an option to customise custom views for portal users in CRM. It would be great if "portal user" was a permission on custom views.

    • 【参加無料】10/17(金) 東京 ユーザ交流会 Vol.3 参加登録 受付開始！

      ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 10/17(金)に、東京・新橋で「東京 ユーザー交流会 Vol.3」を開催します！ 今回のユーザー事例セッションのテーマは、「Zoho Flowを活用した他社の決済サービスとの連携事例」です。 さらに、Zoho Flowに限らず、Analytics や Campaigns などの多彩なZohoサービスの活用方法について、豊富なご経験をもとにご紹介いただきます。 また、Zoho社員セッションでは、Zoho CRMを活用して日々の営業業務を効率化する具体的な事例をお話しします。業界を問わず、幅広い方にご参考いただける内容となっています！

    • Zoho Meeting Plug compatibility with newer versions of Outlook

      Documentation states that the zoho meeting plug in for outlook is only compatible with versions up to Outlook 2019 What is available to users of more up to date versions of outlook/office 365?

    • Getting Attachments in Zoho Desk via API

      Is there a way to get attachments into Zoho Desk via an API?      We have a process by which a zoho survey gets sent to the user as a link in a notification.    The survey has several upload fields where they can upload pdf documents.    I've created

    • Introducing Zoho's own SMS gateway

      We're thrilled to announce the launch of our own SMS gateway feature within Zoho Marketing Automation! This new feature enables seamless SMS campaign management alongside your email marketing initiatives, providing a more integrated and efficient way

    • Embedding in Desk articles

      We would like to embed documents in our Desk articles. When we use an iframe for the embed, we get scrollbars and a frame border. Neither of those is acceptable. I've spoken with the Desk Support team about what we want and they tell me that it cannot

    • Zoho CRM button to download images from image upload field

      Hello, I am trying to create a button in Zoho CRM that I can place in my record details view for each record and use it to download all images in the image upload fields. I tried deluge, client scripts and even with a widget, but feel lost, could not

    • Next Page