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


            Zoho Sign now offers specialized one-on-one training for both administrators and developers.

            BOOK A SESSION





                        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

                                                                                          • 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 65536 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 ...
                                                                                          • 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.
                                                                                          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