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:

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!