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

      • Order of Departments in Help Desk

        In the end user portal, , the departments are sorted by the date of creation of the department (or perhaps their id). Is there a way to choose the display order of the departments or at least to be able to sort them alphabetically?

      • COGS - Account showing negetive

        I have multiple COGS account and in these all there is one account is negetive so suggest why it is showing negetive value.?

      • 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 converted

      • %PaymentLink%

        Does not work. Software creates a BAD link. ....and yes payment options are turned on. Link on the invoice pdf once opened will work but this template is a joke.

      • Google Photos

        I am hoping that my question already has a fix. I current have Google synced accounts that I want to get away from. One in particular on is Google photos. Is there any software, or 3rd parties that I can join to back my photos up straight to specifically designated file in the ZOHO cloud that's tied to Docs? Please advise... Mike 

      • How to block a WhatsApp user for sending spam

        Is there a way to block those whatsapp users that just come to play and annoy our service, they also spam us. We have a waba service with sales iq

      • Zoho Books Items Categorisation/Grouping/Folder

        Is there a way to do items categorisation? a folder structure? Product Type A - Option 1/2/3 Product Type B - Option 1/2/3 Current problem : I have more than 50 items on the list, its hard for team to navigate.

      • Cash payments before invoice date

        We have been using zoho books for our hospitality business for some time and have been very happy with the system. However in 2025 an update was pushed through and we are now not able to record payments for invoices before the invoice date. the case scenario

      • Copy / Duplicate Workflow

        I have workflows setup that are very similar to each other. We have a monitoring system watching servers, and all notifications - no matter what client it is about - will come from a  noreply@ address which is not very helpful in having it auto assigned to the right account. I have setup a workflow that will change the contact name of the ticket (currently it would say noreply@) to the correct customer which is based on the subject line, as that mentions which server the alert it is about. I need

      • Transfer between two customers (Peters Rental account to Peters Private account)

        we are a Property Management company. Our customers have to accounts (registered as two customers - Peter Rental and Peter Private On the rental account all income and costs fron rental activities are noted. On the private account all private are noted

      • Automation#18: Automatically Fetch Values from Contacts to the Tickets Module

        Hello Everyone, Welcome to this week's edition, where you can seamlessly sync fields from the Contacts to the Tickets module. For efficient business operations, it's crucial to have details mapped across different modules. Zylker Secure offers antivirus

      • Charge multiple invoices

        We use auto-charge/recurring invoicing for 100's of clients. If we create a single manual invoice we can charge it to the credit card for these clients with the CC added to their recurring invoices. If a client has multiple outstanding invoices, is there a way to make one charge for all of the invoices instead of a separate charge to the cc for each invoice?

      • Handling/tracking escalation management in Zoho

        Hi all, I am working on finding a tool to register and track our escalation management process. Specifically, this is about client escalations, typically related to project delivery issues. The idea is that we could have some sort of form with core questions

      • Global Choice List share ownership

        I have created several forms that use one or more Global Choice Lists. These lists have been published to Org. I would like to allow one or more admins to edit the choices in these lists. Any help appreciated. Geoff

      • Setting up property management in Zoho Books

        Hi, I run a property management business that manages property complexes. There are multiple owners, some owning more than one property on the same complex. My role is to manage the fees they pay for maintenance of common areas, such as the swimming pool

      • How to prevent users from switching price lists in an order?

        Hi, I have Zoho Finance integrated with Zoho CRM. My team will be placing orders through the CRM using the Finance module. When creating a new customer I will assign it a price list, I don't want the sales rep to switch to a different Price List, other

      • Mollie Connect in Zoho Books

        Have noticed that Zoho Books is missing the most popular payment provider in Europe: Mollie. Knowing that Mollie has a solid Java SDK, why is this not yet implemented in Zoho Books for European customers?  More info: https://docs.mollie.com/oauth/ov

      • Unable to change Lookup field from Multi Select to Single Select

        I am trying to change a Lookup field in my Zoho Creator form from Multi Select to Single Select, but I am unable to find any option to do this.

      • Simple Callback Notifications Needed

        My team are terrible at remembering their CRM callbacks, often due to how long in the future they are set for. Is there a way i can set an e-mail notification for when a callback is due? For example we set it for 9am one day and five minutes before they

      • Personal Data (RODO), Cookies / Trackers - ePrivacy

        I have noticed several issues that should be addressed on the customer support page. Zoho Desk provides the support portal, but it currently lacks the following options: A GDPR and personal data processing consent checkbox before logging in, located in

      • How to set a multi-lookup field as mandatory?

      • Allow Multiple Scheduled Appointments with Zoho Support

        Dear Zoho Team, I hope you're doing well. First, thank you for introducing the option to schedule support calls via the Zoho CRM booking link. This has been a fantastic enhancement, eliminating the need for back-and-forth coordination when scheduling

      • Audit Log for Zoho One Admin Panel

        Dear Zoho One Team, We would like to request the addition of an Audit Log feature in the Zoho One Admin Panel. This log should provide visibility into any changes made within the Zoho One admin panel and directory, including but not limited to: Adding,

      • Personalize your booking pages with Custom CSS

        Greetings from the Zoho Bookings team! We’re introducing Custom CSS for Zoho Bookings, designed to give you complete control over the look and feel of your booking pages. With this new feature, you can upload your own CSS file to customize colors, fonts,

      • Bug: OAuth 2.0 State Parameter fails with Pipe Delimiters (RFC 6749 Non-Compliance)

        I've discovered a bug in how Zoho's API Console handles the OAuth 2.0 authorization flow when the state parameter contains pipe characters (|), and I'm hoping the Zoho team can address this in a future update. The Issue Zoho's OAuth 2.0 implementation

      • Custom Function to increment a value by one

        Hi, I'm trying to find a solution to set up a counter on CRM records that tracks how many times certain actions have taken place. As a specific example: We have a field on Deals called "Times Close Updated". This starts at 0 on record creation. I'd like

      • Customer address in Zoho Bookings

        Hello,  Is it possible to add customer address information to the Zoho bookings appointment screen? Or have it pull that information automatically from the CRM? We are wanting to use this as a field management software but it is difficult to pull the address from multiple sources when it would be ideal to have a clickable address on the appointment screen that opens up the user's maps.  It would also be advantageous for the "list view" to show appointment times instead of just duration and booking

      • Access token generate from the refresh token not working for API

        Dear Sir/Madam, When I use my refresh token to obtain new access_token, that token return INVALID_TOKEN when the same API is called. I made sure that my api site is correct and match the auth web site. However the original access_token work fine.

      • why does my campaign move back to draft?

        Every time I try to send my email campaign, it reverts back to Draft status.. this has happened three times in a row..how do i find out what the problem is? Thanks

      • Service and Parts Line Item Limitations

        Hi FSM Team, We work with clients who deliver large-scale field service projects. In many cases, a single work order can contain 200+ service and parts line items. Currently, Zoho FSM limits work orders to 30 service and parts line items. While this works

      • 【Zoho CRM】やりとりの要約機能リリースのお知らせ

        ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中から、新機能のやりとり要約機能をご紹介します。 Zoho CRMのやりとりの要約は、Ziaが生成する機能で、データに関連付けられた最近のやりとりを簡潔にまとめて提供します。 メール、通話、ミーティング、メモなどを1つの明確な要約にまとめ、重要なポイント、過去の話し合いの要点、結果、次のステップを表示することで、日常的な課題に対処します。 Ziaの強みは、単なる要約にとどまらず、内容の解釈も行える点です。やりとりの要約では、顧客との会話に潜む遅延、フォローアップ漏れ、約束、期限、感情、意図を特定します。

      • Recent post analytics not very useful

        Hi, I'm enjoying some aspects of the Zoho Social. like being able to schedule posts to multiple channels. However, the recent posts analytics is disappointing. For example, I put up a reel on Instagram and Facebook yesterday. Instagram insights shows

      • Zoho Commerce and Third-party shipping (MachShip) API integration

        We are implementing a third-party shipping (MachShip) API integration for our Zoho Commerce store and have made significant progress. However, we need guidance on a specific technical challenge. Current Challenge: We need to get the customer input to

      • What happens to the files created by user, whose account is deleted.

        Hello, I created a folder in the My folders names "Quote" I shared the same with my colleague. She created some files and folders in that folder over the period of 1 and half year. Now she left company, and I deleted her account from Zoho. What happens

      • Internal Error When Accessing Team Inbox.

        All our users are seeing this error in teaminbox. Because its a critical tool kindly resolve this issue ASAP.

      • 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 friendly

      • Zoho Mail Android app update: Calendar enhancements

        Hello everyone! In the most recent version of the Zoho Mail Android app update, we have introduced various new enhancements for the calendar module. Let's explore what's new. Drag and drop events to update the date and time We have enhanced the calendar

      • Enhance Delay Configuration in Zoho Flow

        Dear Zoho Flow Support Team, We are writing to request an improvement to the delay configuration process within Zoho Flow. Currently, users are required to manually enter the exact delay duration (e.g., "2 days") in the delay block. This can be time-consuming

      • Contratação ProdutivosX

        Bem-vindo(a) ao processo de contratação da ProdutivosX. Este formulário tem como objetivo coletar informações essenciais para análise de perfil profissional, alinhamento de competências e possível integração ao equipamento ProdutivosX. A ProdutivosX é

      • Zoho Books - How to Invoke a Custom Function in Schedulers

        We have multiple schedulers that send emails to customers in batches. Currently, we are maintaining the same code across several schedulers. Is it possible to use a custom function inside a scheduler script? If yes, how can we invoke the custom function

      • Next Page