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 owner 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
- Navigate to Tools > Custom Functions and click Manage Connections.
- Click Create Connection. Select the Default Services tab under Pick Your Service.
- Select the Zoho OAuth service from the list of services.
- Enter a suitable Connection Name. Here, we named it crm_oauth_connection. The Connection Link Name will be auto-filled accordingly.
- 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.
- Click Create and Connect. You'll be redirected to the service authentication page.
- Click Connect, then click Accept to allow DRE to access data in your Zoho account. The required connection is now created.
- The CONNECTION SUMMARY page will display your connection details.
2. Create custom function
- Navigate to Tools > Custom Functions and click Create Custom Function.
- Enter a valid function name.
- 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).
- 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.
- Click Create and your custom function will be created.
3. Script using Deluge
- Navigate to Tools > Custom functions > View Deluge Editor.
- Select the added custom function (DEALS_BETWEEN), write the following script in the editor, and click Save.
- //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.
- list DEALS_BETWEEN(date StartDate, date EndDate)
- {
- //Use toString to convert the input dates to accepted date formats in Sheet.
- start_date = StartDate.toString("yyyy-MM-dd");
- end_date = EndDate.toString("yyyy-MM-dd");
- //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.
- query_map = Map();
- query_map.put("select_query","select Deal_Name, Amount, Closing_Date from Deals where Closing_Date between '" + start_date + "' and '" + end_date + "'");
- //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.
- response = invokeurl
- [
- url: "https://www.zohoapis.com/crm/v3/coql"
- type: POST
- parameters:query_map.toString()
- connection:"crm_oauth_connection"
- ];
- //resultList is the variable to declare a list.
- resultList = List();
- response_data = response.get("data");
- //The below "for" statement parses the records inside the Deals module and fetches the specified details
- for each record in response_data
- {
- resultMap = Map();
- resultMap.put("Deal Name",record.get("Deal_Name"));
- resultMap.put("Amount",record.get("Amount"));
- resultMap.put("Closing Date",record.get("Closing_Date"));
- resultList.add(resultMap);
- }
- //Returns the response in the format expected by Zoho Sheet.
- return resultList;
- }
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!
Access your files securely from anywhere
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Meghaa Pradyumnan
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho DataPrep Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho Campaigns Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
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. GeoffDomain Transfer
I have a Godaddy domain, how i can transfer it to Zoha? and how i can move my website to Zoho server? With my best wishes.Project Templates & Reminders
I am getting projects all set up to work for our company and am running into a problem that I'm hoping is easily fixable. I have created a project template and within that project, there are reminders set on certain tasks. When I create a project fromKaizen #126 - Circuits in Zoho CRM - Part 1
Hello everyone! Welcome back to another week of Kaizen! Today, we will discuss an exciting topic—Circuits in Zoho CRM. For starters, we will discuss what Circuits are, how beneficial they are for businesses, different views of a Circuit, and the differentCreate customized SLAs for your customer base with support plans
Managing customer expectations, prioritizing critical issues, and resolving customer inquiries on time is quite a juggle. Without a clear timelines or defined priorities, a support team may struggle with delays in response, SLA violations, and pendingZoho Flow or Schedules
I have a process where we text our leads 7 times over a 14 day with different content for each text. I created one flow in Zoho Flow to do this, but wondering if there is a more efficient way to accomplish this via Schedules. It goes on for 6 more timesFree webinar: Zoho Sign 2024 wrap-up - Everything that is new and has changed
Hello, Are you looking up to catch up on all the updates made to Zoho Sign in 2024? Or are you still figuring out how you can use Zoho Sign better to get business paperwork done more efficiently? If so, we invite you to join us this Thursday, DecemberHow to Customize Task Creation to Send a Custom Alert Using JavaScript in Zoho CRM?
Hello Zoho CRM Community, I’m looking to customize Zoho CRM to send a custom alert whenever a task is created. I understand that Zoho CRM supports client scripts using JavaScript, and I would like to leverage this feature to implement the alert functionality.Workflow - Execute Based on Date
Hello, I have trouble understanding the documentation for Execute Based on Date or Date Time Field's Value. I want to send an email every time I have a Case opened for more than three days with its status unchanged. I set : This rule will be executed 3 days after [date]. Condition : Status is [New]. Instant Action : Send an email notification. However, I'm not sure I follow this part of the documentation: "For all the records matching the rule criteria, rule will be triggered either monthly or yearlyCan we set a BCC address as default to show while sending emails?
Two things inside ZohoCRM are annoying me because it's a repeated work. First one is that I always need to click manually to add the BCC field while sending an email to a lead. Can we set a default address so when I click to send a new email the BCC addressMake collecting payments from your customers in Bigin easier with payment links
Greetings, Efficient payment collection is crucial for business success. Bigin already helps your businesses manage and sell products effectively, but we can further enhance this by making payment collection easier. This integrated payment feature letsSend email is not authenticated
Hi, I’m getting an error in Gmail, when receiving an email from my account in zoho, my email is already authenticated in my domain, and I don't know why I keep receiving this message... also testing in outlook, the message goes directly to "junk".Inbox Preference - Saved replies based on message repsonse with specific word
Hi There, Can one create a workflow where the Save Reply will be generic for all messages that contains a specific word, various channels,. The saved reply should contain a link for download etc. that is a response to the word entered in the message containedBasic Price List Functionality Still Missing
I am having a problem with the most simple imaginable pricing scenario - you buy cheap, add profit, then sell high. Or in less simplistic terms: business buys a product at a given cost, then adds predetermined percentage markup, and finally sells thatHow do I hire employees????
Hi! I own a bookkeeping company, where a few of my clients use Zoho Books as their accounting platform . I started utilizing Zoho Practice to work on the books of my Zoho clients, some have Zoho One and some have Zoho Books plans. I just hired an employee,Automate data upload process like reports
I'll start with the end in mind. I want to basically keep certain creator tables updated with data that are in a sql database/tables in our office (employees, active jobs, employee positions) so I can reference that data and not have to duplicate it by hand every time someone adds a new job or employee in the office desktop software. Here are some thoughts I had about how to do this, but am unsure as to whether any of them are actually possible and how to go about it from there: Is there any wayGreylisted, try again after some time
Can you check my ip, i send to duyna@vietlinkjsc.vn but have an error; my ip is 112.213.94.12 Here is log: 2018-01-09 09:40:29 H=mx.zoho.com [204.141.32.121] SMTP error from remote mail server after RCPT TO:<duyna@vietlinkjsc.vn>: 451 4.7.1 Greylisted, try again after some time 2018-01-09 09:40:32 H=mx2.zoho.com [204.141.33.55] SMTP error from remote mail server after RCPT TO:<duyna@vietlinkjsc.vn>: 451 4.7.1 Greylisted, try again after some time 2018-01-09 09:40:32 duyna@vietlinkjsc.vn R=lookuphostEmails linked to Deal
Hello everyone, I’d like to ask a question to see if someone can help me out. We are requesting availability from suppliers by sending emails directly from the Opportunity. These emails we send are logged within the Opportunity; however, when we receiveHow to transfer all my mails from Zoho to Gmail or Office 365
is there any option to move my emails from zoho to gmail or office 365. i would like to export more than 25k emails from zoho to office 365 or gmail. can anyone help me to guide properly. this will help me to access my emails easily i have both account and can easily do it with office 365 or gmail. i want two options. direct from zoho to office 365 or exported eml files from zoho to gmail. please suggest me both if possibleInquiry Regarding Image Display Issue in Campaign Duplication
We are currently using Zoho Campaigns for email distribution to our clients. I would like to inquire about an issue we encountered. When duplicating a previously created and sent campaign from the "All Campaigns" section, the images used in the headerNew integrations for Bigin: Zoho Sign, SalesIQ, and Marketing Automation
Greetings, We're excited to share new integrations that make Bigin more powerful and useful for your business! Zoho Sign for Bigin Zoho Sign now integrates seamlessly with Bigin, enabling you to sign, send, and manage contracts or agreements without leavingAdd multiple users to a task
When I´m assigning a task it is almost always related to more than one person. Practical situation: When a client request some improvement the related department opens the task with the situation and people related to it as the client itself, the salesmanWhat is Attendee Status 0 and 1?
Hi there, I recently stumbled upon the API to get the attendee list and in the return value, there is a parameter called "status", and 0 supposed to mean not_attending, and 1 means attending. I cannot find this representation anywhere in the attendeeZOHO BackStage
How to get list of events, using ZOHO BackStage APIs. Is it possible OR not?How to Customize Task Creation to Send a Custom Alert Using JavaScript in Zoho CRM?
Hello Zoho CRM Community, I’m looking to customize Zoho CRM to send a custom alert whenever a task is created. I understand that Zoho CRM supports client scripts using JavaScript, and I would like to leverage this feature to implement the alert functionality.Zoho Meeting iOS app update - Join breakout rooms, access polls, paste links and join sessions, in session host controls
Hello, everyone! In the latest iOS version(v1.7) of the Zoho Meeting app, we have brought in support for the following features: Polls in meeting session Join Breakout rooms Paste link in join meeting screen Foreign time zone in the meeting details screen.Calculate hours between 2 date/time fields
Hi, Does anyone know if it is possible to get the number of hours between 2 date/time fields in a zoho crm custom function? Thanks, MichaelExternal ID validation.
I added an external ID field as below in one of my custom modules: When creating records via the API using some value (eg: 762115b2-097e-43b2-bdba-f3924a5371a6) for this field, it works without any problem. I can create and even see the records on theCRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive
Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage filesRemove Address from credit card payment
I would like to remove the need to add address when paying by credit card. I only want the customer to have to add their credit card details.Create a button that executes a customized function
Hello, I have created a summary view in which I combine the data from my items table and suppliers table. I would like to know if there is the possibility of adding a button somewhere in the view to be able to execute a function when clicked on it. ICustom field doesn't fill when converting sales order to invoice
Hi, When I convert a Sales Order to an Invoice one of the custom fields on a product line names "Subsidie" does not seem to fill in automatically. I manually have to select the product again by clicking on the product name in the order line en re-selectCan I use ZOHO calendar to schedule a Youtube video that is already in my youtube account, but listed as private or unlisted?
I am creating Youtube videos and shorts and then uploading them to our Channel so others can view and approve. Once approved I would like to just schedule them over the next few days within Zoho. So far it looks like I have to re-upload the video to ZohoHow to Display a Logo Image on a Public Form?
I would like to display a logo image in the header of a form. To achieve this, I added an Add Notes field to the form. The code below works perfectly for Zoho users accessing the form. However, when the form is made public, the image does not load properly:Advice for my first project in Zoho
Hello, how can I design and implement a customized ERP and CRM system using Zoho to automate and manage core business functions, including customer relationship management, property inventory, sales tracking, and financial processes. This is one of myAssociate Email API Internal Error
I am trying to associate an already existing email within a function using the Related Emails API. To provide more context, I also have admin permissions and have ensured that the fields are correct and that I have admin permissions when associating theProfit on Sales order
Hi, would it be possible to implement a column at the Sales order overview of Purchase amount? So a field with the amount of all purchase related to this Sales order? This is very usefull so you will see the profit you made on this deal. I tried to getSubform Data in v2 REST API
What is the mechanism for adding subform data in the Creator v2 REST APIs? There is nothing documented in the Data APIs documentation (https://www.zoho.com/creator/help/api/v2/). I was able to determine how to GET the subform data by adding it to theIs the filename of an attachment restricted to only certain languages?
Hi, I tried to create a file card in a notebook, but I encountered an issue: when the filename of an attachment is in languages other than the main ones (such as English, Japanese, or Simplified Chinese......), syncing stops or the filenames become garbledHow to create comparison time periods like these examples
In a Pivot Report I would like to be able to select any date range and show a set of metrics for that date range e.g. revenue, orders, units sold. I would then like to be able to compare to the previous period based on the amount of days on the selectedNext Page