Custom functions in Zoho Sheet | Zoho Sheet Help Guide

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.

  1. Customize the variable names and types as per the function to be created.
  2. 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:

  1. 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 .
  2. 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.
  1. All custom functions used in a file will run using the spreadsheet owner's auth token.
  2. When sharing a spreadsheet, the custom functions used in the respective spreadsheet will become accessible to collaborators. 
  3. 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.
  4. 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 CRM Training
      Redefine the way you work
      with Zoho Workplace

        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 CRM Training

          Create, share, and deliver

          beautiful slides from anywhere.

          Get Started Now


            Get started with Zoho Sign

            in a few quick steps!

            Download Help Guide





                        Still can't find what you're looking for?

                        Write to us:  support@zohoforms.com


                              




                            

                          Zoho Marketing Automation

                            Zoho Sheet Resources

                             




                                Zoho Forms Resources


                                  Secure your business
                                  communication with Zoho Mail


                                  Mail on the move with
                                  Zoho Mail mobile application

                                    Stay on top of your schedule
                                    at all times


                                    Carry your calendar with you
                                    Anytime, anywhere




                                          Zoho Sign Resources

                                            Sign, Paperless!

                                            Sign and send business documents on the go!

                                            Get Started Now


                                                Zoho SalesIQ Resources



                                                    Zoho TeamInbox Resources



                                                            Zoho DataPrep Resources



                                                              Zoho DataPrep Demo

                                                              Get a personalized demo or POC

                                                              REGISTER NOW


                                                                Design. Discuss. Deliver.

                                                                Create visually engaging stories with Zoho Show.

                                                                Get Started Now











                                                                                      • Related Articles

                                                                                      • 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 ...
                                                                                      • What are all the cell formats available in Zoho Sheet?

                                                                                        Zoho Sheet supports the following cell formats: General Number Accounting Currency Date Time Percentage Fraction Scientific Text Regional And custom made formats.
                                                                                      • About Zoho Sheet

                                                                                        Zoho Sheet is a cloud-based spreadsheet application. It allows you to create, share, and work together on spreadsheets online, in real time. You can analyze, visualize and publish data using Zoho Sheet across multiple devices and browsers. ​
                                                                                      • Zoho Sheet supported browsers

                                                                                        Zoho Sheet supports the following browsers: Mozilla Firefox - Version 55+ Chrome - Version 55+ Safari - Version 9+ Opera - Version 50+ Edge - Version 12+ Incase you're trying to access Zoho Sheet via an unsupported browser or version, you'll face an ...
                                                                                      Wherever you are is as good as
                                                                                      your workplace

                                                                                        Resources

                                                                                        Videos

                                                                                        Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                                                        eBooks

                                                                                        Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                                                        Webinars

                                                                                        Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                                                        CRM Tips

                                                                                        Make the most of Zoho CRM with these useful tips.



                                                                                          Zoho Show Resources