Custom functions in Zoho Sheet
Though Zoho Sheet supports around 400 functions by default, for simple and complex calculations, we understand that our users have their own business logic that requires personalized functions. Additionally, Zoho Sheet also allows you to bring in data from other applications using custom functions.
This page will guide you in creating and using your own custom functions with Deluge in Zoho Sheet.
Create a custom function
To create a custom function:
1. Go to Tools > Custom Function and click Create.
2. Enter a valid name i.e. a name that starts with [_, a-z, A-Z] and can contain [_,a-z, A-Z, 0-9], for the function without any special characters, spaces, or commas (the underscore is permitted).
3. Select the data type of return value for the specific function to be created from the Return Type drop-down.
4. You can also add the required arguments and their types for the function, in the Create dialog.
5. In the editor, drag and drop the expressions and conditions from the default list and into the editing space.
6. Customize the variable names and types as per the function to be created.
7. Write the syntax for the desired function with the given variables.
8. In the return statement, enter the variable that is to be displayed as the result in the spreadsheet cell.
Click here to learn more about creating functions using Deluge.
Here's an example function for calculating simple interest. To use this function, enter the function name "simple_interest" in the Create dialog and just copy-paste the below code in the editor:
int SIMPLE_INTEREST(int Principal, int Rate, int Years)
{
Simple_interest = Principal * Rate * Years / 100;
return Simple_interest;
}
8 .Click Save at the top of the dialog.
9. If you wish to test the function before closing the editor, click Run and execute the function with sample values.
Using a custom function is as simple as using any default function in Zoho Sheet, here's how.
Fetch data from other applications
From Zoho Products
To bring in data from other Zoho applications:
1. Go to Insert > Custom Function and click Create
2. Enter a valid name i.e. a name that starts with [_, a-z, A-Z] and can contain [_,a-z, A-Z, 0-9], function without any special characters, spaces, or commas (the underscore is permitted).
3. Select the data type of return value for the specific function to be created from the Return Type drop-down.
4. Drag and drop the expression zoho integration from the Integrations category.
- Customize the variable names and types as per the function to be created.
- Choose the desired application name, the action to be performed, and the params in the syntax.
Click here to learn more about integrating Zoho products using Deluge.
Here's an example function that brings in the data from Zoho CRM based on a given argument i.e. Email ID:
list CRM_INFO(string Email)
{
resp = zoho.crm.searchRecords("Leads","(Email:starts_with:" + Email + ")");
newList = List();
for each i in resp
{
newObj = Map();
newObj.put("Full Name",i.get("Full_Name"));
newObj.put("Company",i.get("Company"));
newObj.put("Lead Source",i.get("Lead_Source"));
newObj.put("Country",i.get("Country"));
newObj.put("City",i.get("City"));
newList.add(newObj);
}
return newList;
}
5. Read further to know how to bring in the data based on a given argument, otherwise skip to point 8.
6. Enter the desired argument name in the first statement of the function.
7. Enter the task expression specific to the application, for example, here's the syntax to search records based on given criteria in Zoho CRM:
<response>=zoho.crm.v1.searchRecords(<module_name>, <criteria>, [<from_index>],[<to_index>]);
8. Click Save at the top of the dialog.
9. If you wish to test the function before closing the editor, click Run and execute the function with sample input values.
Using application API
To fetch data using the URL specified in an application's API:
1. Once you're in the Deluge editor, drag and drop the webhook expression from the Integrations category and into the editing space.
2. Below is the syntax for webhook integrations:
response=invokeUrl [
url: <url>
type: <GET/POST/PUT/DELETE>
parameters: <parameter>
headers: <headers>
];
Click here to get help in using the webhook syntax. Here's an example function that brings in the data records from Trello using its API URL:
list TRELLO_ACTIVITIES() { outPut = List(); resp = getUrl("https://api.trello.com/1/boards/<board id>/actions?&key=<apikey>&token=<token>"); resplist = List(); for each activity in resp { resultMap = Map(); resultMap.put("Action", activity.get("type")); resultMap.put("Action Owner",activity.get("memberCreator").get("fullName")); resultMap.put("Date", activity.get("date")); resplist.add(resultMap); } return resplist; }
3. Click Save at the top of the dialog.
4. If you wish to test the function before closing the editor, click Run and execute the function with sample input values.
Note: All the above-mentioned integrations will work only on the spreadsheet owner's authtoken. Thus, to use the integration functions, the owner of the spreadsheet must hold an account in the respective applications. You can also parse the data from a JSON object or an XML document.
Function return data types
The return value of a custom function can be a string, integer, etc. that fills a single cell. You can tweak your function to bring in the result as a one- or two-dimensional array of values that fills the corresponding range of cells using the following steps.
One-dimensional array as result
To give a list of maps as input and receive a list of values as the output:
1. Go to Tools > Custom Function and click Create.
2. Enter a valid name i.e. a name that starts with [_, a-z, A-Z] and can contain [_,a-z, A-Z, 0-9], function without any special characters, spaces, or commas (the underscore is permitted).
3. Select the return data type as 'list' from the drop-down.
4. In the editor, specify the input type as 'list' and follow the syntax in the below example. Here, the input is taken as one map record at a time, that executes in a loop.
This function returns the list of values (vertically) of simple interest for a range of Principal, Year, and Rate values.
list INTEREST(list values)
{
outPutList = List();
for each recordMap in values
{
Principal = recordMap.get("Principal");
Years = recordMap.get("Years");
Rate = recordMap.get("Rate");
outPutList.add(Principal * Years * Rate / 100);
}
return outPutList;
}
Usage:
5. Write the syntax for the desired function with the given arguments.
6. In the return statement, enter the variable that is to be displayed as the result in the spreadsheet cell.
Two-dimensional array as result
To give a list of values as input and receive a list of maps as the output:
1. Go to Tools > Custom Function and click Create
2. Enter a valid name i.e. a name that starts with [_, a-z, A-Z] and can contain [_,a-z, A-Z, 0-9], function without any special characters, spaces, or commas (the underscore is permitted).
3. In the Create dialog, select the return data type as 'list'.
4. In the editor, make sure to add the input argument as a list, like the function below.
This function returns a two-dimensional array of data from Zoho CRM based on the Email ID given as input.
list LEADS_INFO(list EmailList)
{
resultList = List();
for each leads in EmailList
{
resp = zoho.crm.searchRecords("Leads","(Email:starts_with:" + leads + ")");
for each i in resp
{
newObj = Map();
newObj.put("Full Name",i.get("Full_Name"));
newObj.put("Company",i.get("Company"));
newObj.put("Lead Source",i.get("Lead_Source"));
newObj.put("Country",i.get("Country"));
newObj.put("City",i.get("City"));
resultList.add(newObj);
}
}
return resultList;
}
5. Drag and drop the desired integrations from the default list and into the editing space.
6. Customize the variable names and types as per the function to be created.
7. Enter the desired application/service name, action, and params in the syntax.
8. Click Save at the top of the dialog.
9. While executing the function, give a list of input values. The return values will be filled into the corresponding rows and columns based on the input field and the respective 'key' in the column header.
Usage:
Optimize for a quick response
Custom function in Zoho Sheet is powered by Deluge. Thus, every time a function is used in a spreadsheet, Zoho Sheet makes a separate call to the Deluge server. This might cause a slight delay in processing when too many such functions are present in the spreadsheet. To overcome this, if you have a huge data range over which the same function is to be used, you can tweak your function such that it receives an array of values as the input and gives a one- or two-dimensional array of values as the output, in a single call.
Edit a custom function
To edit a custom function in Zoho Sheet:
1. Go to Tools > Custom Function and click Manage.
2. Click Edit beside the function to be edited.
3. Make your desired changes to the function and click Save at the top.
4. If you wish to run or preview the changes before closing the editor, you can click Run instead.
Use a custom function
You can use your custom functions in any of the following ways:
- Type "=" followed by the name of the function in a cell. The custom functions are indicated using the icon , while the default functions are indicated using the
icon . - Select the desired cell, click the Functions icon (fx) on the right panel, and type the name of the function in the search bar. Select the function and click the Add icon (+) to insert it into your cell.
Note: When using custom functions, there are some restrictions based on the ownership of the specific spreadsheet file. Click here for details.
Delete a custom function
To delete a custom function in Zoho Sheet:
1. Go to Tools > Custom function and click Manage.
2. Click Delete beside the function to be deleted.
Note: Only the owner can delete custom functions from their spreadsheets. Functions once deleted cannot be restored.
User and spreadsheet level permissions
There are a few restrictions to how custom functions can be used based on the ownership of the spreadsheet file and function.
- All custom functions used in a file will run using the spreadsheet owner's auth token.
- When sharing a spreadsheet, the custom functions used in the respective spreadsheet will become accessible to collaborators.
- Collaborators can only use the custom functions that have already been used by the spreadsheet owner in that file. They cannot view or edit the function scripts. The customized function description and syntax alone will be visible to the collaborators.
- For published spreadsheets, functions will be available on the published copy. However, the function's code will not be accessible. When a viewer saves it to their account, the function will be removed and the cells containing it will display the #NAME error.
Note: Custom Functions is currently limited to 2000 webhook and integration tasks per day, exceeding which may lead to function failure.
If you're still having trouble with custom functions, write to us at support@zohosheet.com and we'll be there to help you out!
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Zoho DataPrep Resources
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
Working with Zoho Sheet
Here are a few things that we'd like for you to know about Zoho Sheet as you get started: Maximum file size allowed for import 25 MB Maximum number of rows 131072 Maximum number of columns 1024 Maximum number of cells 5 Million Maximum number of ...Insert functions and formulas
To insert functions or formulas: Enter = in any desired cell and type in the formula or function. Alternatively, go to the menu bar and tap Insert. Under the Insert menu, choose Function. Zoho Sheet supports 400+ functions and you can choose the ...SHEET
Returns the sheet number for a given cell reference. Syntax SHEET(cell_ref) cell_ref: The cell reference whose sheet number you want. If omitted, it returns the sheet number of the current cell containing the formula. E.g. SHEET.A1 Remarks The ...Print spreadsheets in Zoho Sheet
To print your spreadsheet or just a selected area, Click on the File menu, and click on the Print option. (or) Click on the printer icon on the tool bar. (or) Use the Cmd/Ctrl+P shortcut. Once on the print preview window, here are all the options ...Configure and enable OpenAI in Zoho Sheet
ChatGPT by OpenAI is a generative artificial intelligence tool that processes the given queries and provides detailed responses. Zoho Sheet's integration with OpenAI will enhance the way you analyze your spreadsheet data with advanced AI features. ...
New to Zoho LandingPage?
Zoho LandingPage Resources