Sheet—Custom functions help

Custom functions in Zoho Sheet

Though Zoho Sheet supports 350+ 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.

    • Customize the variable names and types as per the function to be created.

  6. Write the syntax for the desired function with the given variables.

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

Run custom function in Zoho Sheet

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!

Share this post : FacebookTwitter

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

Write to us: support@zohodocs.com