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!


      Zoho Campaigns Resources


        • Desk Community Learning Series


        • Digest


        • Functions


        • Meetups


        • Kbase


        • Resources


        • Glossary


        • Desk Marketplace


        • MVP Corner


        • Word of the Day


        • Ask the Experts


          Zoho CRM Plus Resources

            Zoho Books Resources


              Zoho Subscriptions Resources

                Zoho Projects Resources


                  Zoho Sprints Resources


                    Zoho Orchestly Resources


                      Zoho Creator Resources


                        Zoho WorkDrive Resources



                          Zoho CRM Resources

                          • CRM Community Learning Series

                            CRM Community Learning Series


                          • Tips

                            Tips

                          • Functions

                            Functions

                          • Meetups

                            Meetups

                          • Kbase

                            Kbase

                          • Resources

                            Resources

                          • Digest

                            Digest

                          • CRM Marketplace

                            CRM Marketplace

                          • MVP Corner

                            MVP Corner




                            Zoho Writer Writer

                            Get Started. Write Away!

                            Writer is a powerful online word processor, designed for collaborative work.

                              Zoho CRM コンテンツ








                                ご検討中の方

                                  • Recent Topics

                                  • Delay function execute

                                    I've got a workflow which uses a webhook to send information to Flow, which in return updates a record in Creator. Problem is, by the time this has executed, the rest of my script has run and can't find the (yet to be) updated info in the record. Is there

                                  • How to allow download of a file AFTER information is collected?

                                    I can't find this anywhere. Can someone help with what seems to be a pretty basic web function today? - File is stored in Workdrive - Prospect goes to a URL where the file is located - System requests prospect to enter "Name" and "Email" - An email is

                                  • Add Baseline methods to API

                                    There are no API methods for creating or querying baselines,  Use case: Customer is notified of the current project start date according to plan via another product/integration. We would like to auto create a baseline (Automated via API) at the point

                                  • Bulk Fill In & Edit PO/Bill/SO/Invoice

                                    Hello, I am adding stock in bulk on a PO, the system is automatically populating the Rate (price) and Tax from the item data. Problem is that the bill rate is different from the rate on the item data, so I have to manually erase each and enter the price.

                                  • Fixed Assets beg balance

                                    Hello, I trust you are well I'm a new zoho books user and I find it difficult to plug in the fixed assets balance and here's what I have done: 1- I imported the fixed assets data into the fixed assets module as per the instructions 2- the import process

                                  • Syncing with Google calendar, Tasks and Events

                                    Is it possible to sync Zoho CRM calendar, task and events with Google Calendar's tasks and events. With the increasing adoption by many major tool suppliers to sync seamlessly with Google's offerings (for instance I use the excellent Any.do task planning

                                  • CRM Deluge how can link attach file with workdrive file.

                                    Hi, We have create file at workdrive and would like to link to attachment at crm module by deluge. Right now can only upload it to attachment but can not link the file from workdrive. Thank you

                                  • Feature announcement: AI-powered document generator in Zoho Sign

                                    Hi there! We recently announced manual document creation by offering an editor within Zoho Sign. Today, we are excited to announce AI-powered document generation to help you draft agreements, purchase orders, and other important business documents in

                                  • Permanently deleted files

                                    Is there a way to get back permanently deleted files from your account if they were accidentally deleted and no longer recoverable from the recycling bin? I know I am probably screwed but I just figured I would ask.

                                  • Shared Mailbox in iOS app

                                    Hi, On the desktop I am using a Shared mailbox for our company's general mailbox that somebody else should be able to view. In the iOS app however I can't find it? Old sources on the internet says this isn't possible but that can't be right? Somebody

                                  • Tip #41- Simplify Compliance and Efficiency with Zoho Assist’s Data Cleanup Feature- 'Insider Insights'

                                    Managing device data efficiently is just as important as securing remote access. With Zoho Assist’s Data Cleanup feature, admins can easily remove outdated or unnecessary device records, keeping the console organized and secure. This action is carried

                                  • Revenue Management: #6 Revenue Recognition in Professional Services Industry

                                    If you run a consulting firm, agency, or any service-based business, you have a situation where you have signed the contract, maybe even sent the invoice, and have received some initial payments. So, can you consider this as revenue? What if the project

                                  • Zoho fsm scheduled maintenance dispatch

                                    Hi, I'm reaching out regarding an issue I'm encountering with the Scheduled Maintenance feature in Zoho FSM. I’m trying to configure it so that it automatically dispatches all maintenance jobs scheduled within the next 150 days, starting from today. However,

                                  • Template modifiactions

                                    Hello, I am struggling with the templates in ZOHO Books. Especially with the placement of some items, like company address, ship to, bill to etc.  For example: One item I like from template X (placement of ship to and bill to next to each other in the

                                  • zoho.inventory.getRecords returns no records

                                    I running a custom function (Deluge) in Zoho Flow with a connection to Zoho Inventory. To debug the problem, I created the test function below. It returns "Number of contacts fetched: 0". This confirms that there's no issue with the org ID or connection

                                  • Zoho Campaigns Event timestamps do not propagate to Zoho CRM

                                    We have integrated Zoho CRM and Zoho Campaigns. But when looking at Contact records, the Campaign event data is missing the actual timestamps: especially when a particular email was sent. They're not in the Campaigns related list, and the cannot be found

                                  • Feature announcement - Simplifying document creation with Zoho Sign

                                    Hi there, We are pleased to announce the release of our document creation feature, enabling you to create, edit, and finalize documents like agreements, purchase orders, and contracts without relying on external tools or applications. <br> This

                                  • Integrating File Attachments in Zoho Flow Email Notifications

                                    Hi, I would like to be able to send an email once an entry is made in a form, and in this email, I would like to attach a file that has been added to the form using the ImageUpload (or FileUpload) widget. So, I started by creating a flow that will trigger

                                  • Zoho Mail SMTP IP addresses

                                    We are using Zoho Mail and needs to whitelist IP for some redirections from your service to another e-mails. You can provide IP address list for Zohomail SMTP servers?

                                  • Zoho project purchases

                                    Hi Folks, I think having a purchase module in projects will help in putting together a project p and L. Project wise purchases and expenses.  Do let me know your thoughts.

                                  • Create Tasklist with Tasklist Template using API v3

                                    In the old API, we could mention the parameter 'task_template_id' when creating a tasklist via API to apply a tasklist template: https://www.zoho.com/projects/help/rest-api/tasklists-api.html#create-tasklist In API v3 there does not seem to be a way to

                                  • How can I restore all the deleted Time entries of a Task in Zoho Projects

                                    How can I restore all the deleted Time entries of a Task in Zoho Projects? Query: In Zoho Projects, I cloned a task and deleted the time entries from the cloned task. However, this also deleted the time entries from the original task. How can I restore

                                  • Is there a way to make an account inactive in ZoHo Desk

                                    We have a few Clients "Accounts" that we no longer do business with. It would be beneficial for them to not show up in lists. However, we want to go back and view tickets, time, etc.

                                  • How to set the value of the Phone field importing contacts in Zoho Desk

                                    Hi everyone, i'm new in Zoho Desk, we're setting up the environment and i'm importing contacts from another CRM using a file CSV and i'm getting a problem with phone numbers (italian): the leading zero is cut away, also if the value is inside double quotes.

                                  • Set Custom Icon for Custom Modules in new Zoho CRM UI

                                  • Deprecation Notice: OpenAI Assistants API will be shut down on August 26, 2026

                                    I recieved this email from openAI what does it means for us that are using the integration and what should we do? Earlier this year, we shared our plan to deprecate the Assistants API once the Responses API reached feature parity. With the launch of Conversations,

                                  • How to center a field inside a section?

                                    I’ve been trying to center a field inside a section in Zoho Canvas. When I align it visually, it looks centered in the editor, but after clicking Save, the field appears misaligned on the actual canvas. I also tried setting the field to full width, but

                                  • Kaizen #192 - Implementing Custom Token Persistence in Python SDK

                                    Welcome back to another week of Kaizen! Last week, we discussed how to implement Login with Zoho using OAuth 2.0 and saw how to bring it to life in a real-world application with the Zoho CRM Python SDK. We also discussed how Zylker Academy built a custom

                                  • Showing the map along with mileage expense

                                    When you use the GPS to track mileage, it shows you the map of the actual path travelled. It would be very useful and practical to save that map with the mileage expense, so that when the report is created, it provides a map of each mileage expense associated

                                  • Enable Validation Rule for Multi-Select Picklist Field

                                    Zoho, Please allow validation rules for multi-select fields.

                                  • File Upload field not showing in workflow

                                    Hi, I have added a field on Zoho CRM. I want to use it in a workflow where that particular field is updated based on another field, however it is not showing up in the field list to select it in the workflow. Why is this please?

                                  • CRM Custom function updating a module record shows the Super Admin user as the record modifier

                                    Dear Zoho CRM Team, Is there any way to update this so that when a custom function has updated a record the Super Admin user doesn't become the modifier? This happens on the record as a modifier and shows up in the audit logs. It would be more useful

                                  • Best practice importing items and matching assemblies

                                    Hi, I was wondering what would be the best practice to import items and composite items (assemblies) From my backup, what should I import first? The items or the composite items? I am on Zoho one, using inventory and books. Kind regards, Sabine

                                  • Best way to fetch employee names from Zoho People into Zoho Creator Inventory Stock Form field Employee Name Lookup

                                    Hi Team, I have a requirement in my Zoho Creator application (Inventory Stock Adjustment) where I need to fetch employee names from Zoho People and use them as a lookup in a form. Currently, I am considering using an integration field to fetch this data

                                  • get file api is returning Junk data

                                    I am working on extension development where at one point I need to retrieve attachments on records I found out I can use only invokeconnection and not invokeurl in extension development The invoke connection returns the image in raw binary format. When

                                  • Need help with message box

                                    End user updates many records at once. Each have unique serial number. They want a confirmation box that says starting number, ending number and qty. Is there any way to do this? I been searching and asking support but seems like no great solution.

                                  • [Webinar] The Transformative Power of Gen BI

                                    Traditional decision-making tools are no longer enough. The integration of generative AI into business intelligence (BI) is proving to be a true game changer—enabling businesses to make faster, smarter, and better informed decisions. Early adopters of

                                  • Backorder process review - Automating Removal of Sales Order from "On Hold" When PO is Received

                                    Hello Zoho Inventory Team, Currently, sales orders in On Hold status are released only when the bill for the purchase order is created. In our workflow, it would be much more efficient if the sales order could automatically move out of On Hold as soon

                                  • How to access the saved Query using API?

                                    I have created a query in setting. Is it possible to access it from a API? What's the endpoint? I tried /v8/queries/important_deals but didnt' work.

                                  • création d'une base de données

                                    base de donnee d'un lycée

                                  • Next Page