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

        • Cyclic dependencies in many-to-many relationships...

          I have an application which includes a form for companies, and a form for contacts. Each company can be assigned 1 technical and 1 administrative contact. I have this working okay so far, but I want to copy the scripts used so far to a new empty application. When I import the scripts it fails with a message that says: Problem encountered while creating the application Error in resolving form dependency:Cyclic dependency among the forms:[Company, Contact] What can I do to resolve this? After all,

        • Zoho API to create ticket

          I'm developing an integration to create tickets via API, but, locally it works (send and recieve requests). In production it also works sending requests, but, my file don't recieve any response data. My URL is available in Zoho API Console and I have

        • Automate Timesheet Approvals with Multi-level Approval Rules

          Introducing Approval Rules for Timesheets in Zoho Projects. With this automation, teams can manage how timesheets are reviewed and approved by setting up rules with criteria and assigning approvers to handle submissions. Timesheet, when associated to

        • Labels Part 2

           Hey Zoho Mail Team, On the labels window on the left-hand pane, alphabetize label name display, rather than displaying in order created.  This one should be easy. Thanks, Drew

        • Building Toppings #1 - Serving your needs with Bigin Toppings

          Hey Biginners! We're excited to kick off our Developer Community series on building toppings for Bigin, and our goal is to provide an accessible, beginner-friendly, and relevant path for every developer. Imagine creating tiny pieces of software that unlock

        • Can we create Sprint with tasks from Multiple projects?

          Hi Team, We were using Zoho Sprints for quite sometime. Currently we have started the process of Sprint method. We couldnt create the active sprint board with the tasks from multiple projects. I would like to know whether this is possible or Any timeline

        • Tip of the Week #74– Create automated workflows in MS Power Automate

          Zoho TeamInbox now connects directly with Microsoft Power Automate, letting you streamline everyday routines tasks such as from sending emails to managing threads, with automated workflows. About the integration Zoho TeamInbox integrates with Microsoft

        • Account validation

          Hello everyone, I registered my account on ZeptoMail to use the system, but the problem is that the verification period on Zepto's end has already passed and I have limited functionality.

        • Paste issues in ZOHO crm notes

          Hi, since a week or so I have issues with the paste function in ZOHO CRM. I use "notes" to copy paste texts from Outlook emails and since a week or so, the pasting doesnt function as it should: some text just disappears and it gives a lot of empty lines/enters.....

        • Is it possible to add a gradient color to a newsletter im designing?

          From where i sit it looks like you can only choose a single color but not combine 2 colors?

        • New Feature: Audit Log in Zoho Bookings

          Greetings from the Zoho Bookings team! We’re excited to introduce Audit Log, a new feature designed to help you track all key actions related to your appointments. With Audit Log, you can maintain transparency, strengthen security, and ensure accountability.

        • Account disabled

          I have an issue I need help with. Whilst trialing ZOHO CRM I created the following: Account1 (-------------) using m__ame@m__rg___s__i__.___.__ and 2 personal emails Account2 (-------------) using a personal email and 2 users _al__1@______________._o_.__

        • Blocked Email

          We are a Zoho One subscriber and use Yahoo as our MX provider. A few times each year, for the past four years, CRM blocks one or more of my Zoho One users from receiving internal email from CRM. This includes "@mentions" in all modules, and emails from

        • message var is empty in bot mention handler

          Hi, I'm encountering a problem: in my bot's mention handler, I want to retrieve the text the user typed when mentioning the bot. Example: On the #tests-cyril channel, I send this message: “@Donna hello how are you ?” I expect the system variable "message"

        • Remembrance Day to Remember – Recalling Values

          The phrase “at the eleventh hour” refers to the 11th hour of the 11th day of the 11th month in 1918, when the hostilities of World War I came to an end—but it still holds meaning today. Remembrance Day (Veterans Day in the US) is observed on November

        • Enhancing Zia's service with better contextual responses and article generation

          Hello everyone, We are enhancing Zia's Generative AI service to make your support experience smarter. Here's how: Increased accuracy with Qwen One of the key challenges in AI is delivering responses that are both contextually accurate and empathetic while

        • How to display two measures (sales and price) divided by categories on one line chart

          Hi everyone, I’m having trouble figuring out how to display two columns on a line chart with category breakdowns. What I need is a line chart where one line represents Sales and the other represents Price. However, the Price data is divided into around

        • Create custom rollup summary fields in Zoho CRM

          Hello everyone, In Zoho CRM, rollup summary fields have been essential tools for summarizing data across related records and enabling users to gain quick insights without having to jump across modules. Previously, only predefined summary functions were

        • Introducing the locking option for CRM records

          Last modified on 06/04/2023: Record locking option in CRM is now available for all Zoho CRM users in all DCs. Note that it was an early access feature available only upon request. Hello All, Hope you're doing well! We're thrilled to introduce our latest

        • Two new enhancements in Zoho CRM: Introducing new criteria for user fields and displaying group information in users page

          Announcement moderated on 14th June, 2023. Dear All, These enhancements are opened for all users in all DCs. ------------------------------------------------------------------------------------- Dear All, Hope you're well! We are here with two useful

        • Tip #49- Navigating the Remote Support Dashboard in Zoho Assist- 'Insider Insights'

          The Remote Support dashboard in Zoho Assist is designed to help technicians quickly access, manage, and monitor all their support sessions from a single, intuitive interface. Whether you’re starting a new session, managing ongoing connections, or reviewing

        • BMI formula

          I've been trying for hours to calculate BMI using height and weight as my only inputs. It's a simple calculation and I even went to ChatGPT to help me figure this out in Zoho Forms, but it led me down a path of "try this" and "try this". None of my attempts

        • SEO on blogs

          Hello, google is not able to find my blogs. Can you advice me if I need to change some settings or anything else to make it retrievable via SEO many thanks, hans

        • Duplicated Notebooks

          Out of the blue, almost all of my notebooks got duplicated and the different copies contain different information. Some seem like older copies than the others. I use the linux desktop app and sometimes the Android app. I assume that the sync failed at some point and was unable to merge the two versions together. But I'm afraid to add anything else to my notebook because if it can't properly sync 5 notebooks with only a handful of notes each, what will happen when I have hundreds of notes and I lose

        • Mind mapping in Zoho Projects

          Good morning,   I would like to congratulate the Zoho team for building such an inovative and responsive application that fits in the daily challenges of so many work groups. I would like suggest you another functionality that helps a lot in project planning and development: mind mapping. Mind mapping would be of great help for brianstorming, knowledge management and other needs in online collaboration.   Thanks and wish you all the best! George Maha Empresa Júnior Multidisciplinar do Instituto de

        • Retainer invoice in Zoho Finance modlue

          Hello, Is there a way of creating retainer invoices in the Zoho Finance module? If not can I request this is considered for future updates please.

        • Documents don't sync properly

          Hello, My team recently moved to Zoho workDrive, some of my team, work remotely so we believed Zoho would be the best way to share and edit files as well as access files withing the team. However we are experiencing sync issues, when a file is uploaded in the file explorer and shows sync complete, same file can be seen on the Zoho web app but other team members can't see the said file because it is not syncing properly. i figured out that if i were to go to preferences in settings and resync the

        • We are looking for an experienced Zoho Developer

          Hi Everyone! We’re on the lookout for a skilled Zoho Developer with hands-on experience in the Zoho Developer Platform (The Vertical CRM Platform) and if you don't know what that is, then you are not the person we are looking for. You would also need

        • Initiate approval workflow after "on edit --> on success"

          Dear Community,  currently I am working on a initiative tracker which should handle approval workflows based on different initiative status. As far as I understand, an approval workflow can be triggered (only?) when data is entered for the input form to the database.  Example: A new initiative is created and submitted. --> Approval Workflow triggered Here comes my question: Is it also possible to trigger an approval workflow if the form was "simply" updated? (on edit --> on success) Imagine the initiative

        • Tax Deductible Donations to a Charity Organisation

          For Australia, what's the best/proper method for entering an expense that is a tax deductible donation to a charitable organisation. And thus is appears correctly in Accounts and BAS as a GST payable deduction?

        • Simultaneous use of WhatsApp Account in SalesIQ and ZohoDesk

          Hi, We have only one number registered in Meta, it's possible use same account for two apps? All times here we try is stopping in SalesIQ. Regards,

        • External lookup fields to synchronise with Analytics

          Hello. I suggest adding external lookup custom fields to the Zoho Analytics integration settings. Currently, these types of fields are not available for synchronisation, so they cannot be used to generate reports with related tables in Analytics. Thank

        • We should have an option to Toggle Off KB Article Links in AI Answer Bot Replies

          Hi everyone, I'm creating this topic to share a feature idea that I think would improve the customer experience when using the AI Answer Bot for self-service. The Current Behavior (The Problem) Currently, when the AI Answer Bot generates a summary or

        • Boot Loop After Removing Service from Client via Admin

          We have multiple laptops experiencing a boot loop / unable to login after the Zoho Remote service was removed from their machine remotely via the desktop client. The Windows sign-in screen is not available, and users are unable to login. This seems like

        • Request Backend Correction — Domains Mapped to Same Help Center (Multi-Brand Setup Failure)

          Hello Zoho Desk Support Team, I need assistance correcting a domain-to-brand mapping issue in our Zoho Desk instance (The Blue Ribbon Project). Both of our verified domains currently point to the same Help Center instead of separate brand portals. Current

        • is there any option to enable the participants interact - can write on the screen, while the screen is shared by the host?

          this is most expected features - for teaching children. is there any option to enable the participants interact - can write on the screen, while the screen is shared by the host?

        • How to disable the Login button in Desk Helpcenter

          Hey there, I'm testing out whether the help center within Zoho Desk could replace our existing Customer FAQ. I tried to customize the themes for the public help center frontend, but stumbled across the login button in the navigation bar. Is there any

        • Subforms in stateless forms

          I think the title says it all. We need to be able to add subforms to stateless forms. Currently the only workaround is to create a Form and delete each record upon submission of the form. I need to build an interface to update our inventory. Basically

        • Standard Payment Term is not pulled from account to quotation

          Hey Team There seems to be something off. I do have "Net 30" as my default payment term in Zoho Books for my customers. If, from the customer overview or quote section, I create a new Quotation, the payment terms field stays blank and doesn't get the

        • Zoho Analytics Export API

          Hi Team, I’m working on some integration tasks and wanted to confirm if it’s possible to retrieve a Zoho Analytics table as JSON data using a Deluge script. I’ve already stored my custom data from multiple sources and combined it into a single source.

        • Next Page