Add reference methods to cell values to provide the user access to dynamic calculations based on the cell value. TheEntityCellValue andLinkedEntityCellValue types support reference methods. For example, add a method to a product entity value that converts its weight to different units.

The following screenshot shows an example of adding aConvertWeight method to a product entity value representing pancake mix.

TheDoubleCellValue,BooleanCellValue, andStringCellValue types also support reference methods. The following screenshot shows an example of adding aConvertToRomanNumeral method to a double value type.

Reference methods don’t appear on the data type card for the user.

Add a reference method to an entity value

To add a reference method to an entity value, describe it in the JSON using theExcel.JavaScriptCustomFunctionReferenceCellValue type. The following code sample shows how to define a simple method that returns the value 27.

const referenceCustomFunctionGet27: Excel.JavaScriptCustomFunctionReferenceCellValue = {   type: Excel.CellValueType.function,  functionType: Excel.FunctionCellValueType.javaScriptReference,  namespace: "CONTOSO",   id: "GET27" }

The properties are described in the following table.

When you create the entity value, add the reference method to the properties list. The following code sample shows how to create a simple entity value namedMath, and add a reference method to it.Get27 is the method name that will appear to the user. For exampleA1.Get27().

function makeMathEntity(value: number){  const entity: Excel.EntityCellValue = {    type: Excel.CellValueType.entity,    text: "Math value",    properties: {      "value": {        type: Excel.CellValueType.double,        basicValue: value,        numberFormat: "#"      },      Get27: referenceCustomFunctionGet27    }  };  return entity;}

The following code sample shows how to create an instance of theMath entity and add it to the selected cell.

// Add entity to selected cell.async function addEntityToCell(){  const entity: Excel.EntityCellValue = makeMathEntity(10);  await Excel.run( async (context) => {    const cell = context.workbook.getActiveCell();    cell.valuesAsJson = [[entity]];    await context.sync();  });}

Finally, the reference method is implemented by a custom function. The following code sample shows how to implement the custom function.

/** * Returns the value 27. * @customfunction * @excludeFromAutocomplete * @returns {number} 27 */function get27() {  return 27;}

In the previous code sample, the@excludeFromAutocomplete tag ensures the custom function doesn't appear to the user in the Excel UI when entering it in a search box. However, note that a user can still call the custom function separately from an entity value if they enter it directly into a cell.

When the code runs, it creates aMath entity value as shown in the following screenshot. The method appears in formula autocomplete when the user references the entity value from a formula.

Add arguments

If your reference method needs arguments, add them to the custom function. The following code example shows how to add an argument namedx to a method namedAddValue. The method adds one to thex value by calling a custom function namedAddValue.

/** * Adds a value to 1. * @customfunction * @excludeFromAutocomplete * @param {number} x The value to add to 1. * @return {number[][]}  Sum of x and 1. */function addValue(x): number[][] {    return [[x+1]];}

Reference the entity value as a calling object

A common scenario is that your methods need to reference properties on the entity value itself to perform calculations. For example, it's more useful if theAddValue method adds the argument value to the entity value itself. Specify that the entity value be passed in as the first argument by applying the@capturesCallingObject tag to the custom function as shown in the following code example.

/** * Adds x to the calling object. * @customfunction * @excludeFromAutocomplete * @capturesCallingObject * @param {any} math The math object (calling object). * @param {number} x The value to add. * @return {number[][]}  Sum. */function addValue(math, x): number[][] {    const result: number = math.properties["value"].basicValue + x;  return [[result]];}

Note that the argument name can be whatever you decide, as long as it conforms to the Excel syntax rules as specified inNames in formulas. Since we know this is a math entity, we name the calling object argumentmath. The argument name can be used in the body calculation. In the previous code sample, it retrieves themath.[value] property to perform the calculation.

The following code sample shows the implementation of theContoso.AddValue function.

/** * Adds x to the calling object. * @customfunction * @excludeFromAutocomplete * @param {any} math The math object (calling object). * @param {number} x The value to add. * @return {number[][]}  Sum. */function addValue(math, x): number[][] {    const result: number = math.properties["value"].basicValue + x;  return [[result]];}

Note the following about the previous code sample.

• The@excludeFromAutocomplete tag ensures the custom method doesn't appear to the user in the Excel UI when entering it in a search box. However, note that a user can still call the custom function separately from an entity value if they enter it directly into a cell.
• The calling object is always passed as the first argument and must by of typeany. In this case, it's namedmath and is used to get the value property from themath object.
• It returns a double array of numbers.
• When the user interacts with the reference method in Excel, they don’t see the calling object as an argument.

Example: Calculate product sales tax

The following code shows how to implement a custom function that calculates the sales tax for the unit price of a product.

/** * Calculates the price when a sales tax rate is applied. * @customfunction * @excludeFromAutocomplete * @capturesCallingObject * @param {any} product The product entity value (calling object). * @param {number} taxRate The tax rate (0.11 = 11%). * @return {number[][]}  Product unit price with tax rate applied. */function applySalesTax(product, taxRate): number[][] {  const unitPrice: number = product.properties["Unit Price"].basicValue;  const result: number = unitPrice * taxRate + unitPrice;  return [[result]];}

The following code sample shows how to specify the reference method and includes theid of theapplySalesTax custom function.

const referenceCustomFunctionCalculateSalesTax: Excel.JavaScriptCustomFunctionReferenceCellValue = {   type: Excel.CellValueType.function,  functionType: Excel.FunctionCellValueType.javaScriptReference,  namespace: "CONTOSO",   id: "APPLYSALESTAX" }

The following code shows how to add the reference method to theproduct entity value.

function makeProductEntity(productID: number, productName: string, price: number) {  const entity: Excel.EntityCellValue = {    type: Excel.CellValueType.entity,    text: productName,    properties: {      "Product ID": {        type: Excel.CellValueType.string,        basicValue: productID.toString() || ""      },      "Product Name": {        type: Excel.CellValueType.string,        basicValue: productName || ""      },      "Unit Price": {        type: Excel.CellValueType.formattedNumber,        basicValue: price,        numberFormat: "$* #,##0.00"      },      applySalesTax: referenceCustomFunctionCalculateSalesTax    },  };  return entity;}

Exclude custom functions from the Excel UI

Use the@excludeFromAutoComplete tag in the comments description of custom functions used by reference methods to indicate that the function will be excluded from the autocomplete drop-down list and Formula Builder. This helps prevent the user from accidentally using a custom function separately from its entity value.

The@excludeFromAutoComplete tag is processed during build to generate afunctions.json file by theCustom-Functions-Metadata package. This package is automatically added to the build process if you start with yo office and choose a custom function template. If you aren't using this package, you'll need to add theexcludeFromAutoComplete property manually to thefunctions.json file.

The following code sample shows how to manually describe theAPPLYSALESTAX with JSON in thefunctions.json file. TheexcludeFromAutoComplete property is set totrue.

{    "description": "Calculates the price when a sales tax rate is applied.",    "id": "APPLYSALESTAX",    "name": "APPLYSALESTAX",    "options": {        "excludeFromAutoComplete": true,        "capturesCallingObject": true    },    "parameters": [        {            "description": "The product entity value (calling object).",            "name": "product",            "type": "any"        },        {            "description": "The tax rate (0.11 = 11%).",            "name": "taxRate",            "type": "number"        }    ],    "result": {        "dimensionality": "matrix",        "type": "number"    }},

For more information, seeManually create JSON metadata for custom functions.

Add a function to a basic value type

To add functions to the basic value types ofBoolean,double, andstring, the process is the same as for entity values. Describe the function with JSON as a reference method. The following code sample shows how to create a double basic value with a functionAddValue() that adds a valuex to the basic value.

/** * Adds the value x to the number value. * @customfunction * @capturesCallingObject * @param {any} numberValue The number value (calling object). * @param {number} x The value to add to 1. * @return {number[][]}  Sum of x and 1. */export function addValue(numberValue: any, x: number): number[][] {  return [[x+numberValue.basicValue]];}

The following code sample shows how to add theaddValue reference method to a simple number in Excel.

const referenceCustomFunctionAddValue: Excel.JavaScriptCustomFunctionReferenceCellValue = {   type: Excel.CellValueType.function,  functionType: Excel.FunctionCellValueType.javaScriptReference,  namespace: "CONTOSO",   id: "ADDVALUE" } async function createSimpleNumber() {  await Excel.run(async (context) => {    const sheet = context.workbook.worksheets.getActiveWorksheet();    const range = sheet.getRange("A1");    range.valuesAsJson = [      [        {          type: Excel.CellValueType.double,          basicType: Excel.RangeValueType.double,          basicValue: 6.0,          properties: {            addValue: referenceCustomFunctionAddValue          }        }      ]    ];    await context.sync();  });}

Optional arguments

The following code sample shows how to create a reference method that accepts optional arguments. The reference method is namedgenerateRandomRange and it generates a range of random values.

const referenceCustomFunctionOptional: Excel.JavaScriptCustomFunctionReferenceCellValue = {   type: Excel.CellValueType.function,  functionType: Excel.FunctionCellValueType.javaScriptReference,  namespace: "CONTOSO",   id: "GENERATERANDOMRANGE" }function makeProductEntity(productID: number, productName: string, price: number) {  const entity: Excel.EntityCellValue = {    type: Excel.CellValueType.entity,    text: productName,    properties: {      "Product ID": {...},      "Product Name": {...},      "Unit Price": {...},      generateRandomRange: referenceCustomFunctionOptional    },  };  return entity;}

The following code sample shows the implementation of the reference method as a custom function namedgenerateRandomRange. It returns a dynamic array of random values matching the number ofrows andcolumns specified. Themin andmax arguments are optional, and if not specified will default to1 and10.

/** * Generate a dynamic array of random numbers. * @customfunction * @excludeFromAutocomplete * @param {number} rows Number of rows to generate. * @param {number} columns Number of columns to generate. * @param {number} [min] Lowest number that can be generated. Default is 1. * @param {number} [max] Highest number that can be generated. Default is 10. * @returns {number[][]} A dynamic array of random numbers. */function generateRandomRange(rows, columns, min, max) {  // Set defaults for any missing optional arguments.  if (min===null) min = 1;  if (max === null) max = 10;  let numbers = new Array(rows);  for (let r = 0; r < rows; r++) {    numbers[r] = new Array(columns);    for (let c = 0; c < columns; c++) {      numbers[r][c] = Math.round(Math.random() * (max - min) ) + min;    }  }  return numbers;}

When the user enters the function in Excel, autocomplete shows the properties of the function, and indicates optional arguments by surrounding them in brackets []. The following image shows an example of entering optional parameters using thegenerateRandomRange reference method.

Multiple parameters

Reference methods support multiple parameters similar to how the ExcelSUM function supports multiple parameters. The following code sample shows how to create a reference function that concatenates zero or more product names passed in a products array. The function is shown to the user asconcatProductNames([products], ...).

/**  * @customfunction  * @excludeFromAutocomplete  * @description Concatenate the names of given products, joined by " | "  * @param {any[]} products - The products to concatenate. * @returns A string of concatenated product names.  */ function concatProductNames(products: any[]): string {   return products.map((product) => product.properties["Product Name"].basicValue).join(" | "); }

The following code sample shows how to create an entity with theconcatProductNames reference method.

const referenceCustomFunctionMultiple: Excel.JavaScriptCustomFunctionReferenceCellValue = {   type: Excel.CellValueType.function,  functionType: Excel.FunctionCellValueType.javaScriptReference,  namespace: "CONTOSO",   id: "CONCATPRODUCTNAMES" } function makeProductEntity(productID: number, productName: string, price: number) {  const entity: Excel.EntityCellValue = {    type: Excel.CellValueType.entity,    text: productName,    properties: {      "Product ID": {...},      "Product Name": {...},      "Unit Price": {...},      concatProductNames: referenceCustomFunctionMultiple,    },  };  return entity;}

The following image shows an example of entering multiple parameters using theconcatProductNames reference method.

Multiple parameters with ranges

To support passing ranges to your reference method such asB1:B3, use a multidimensional array. The following code sample shows how to create a reference function that sums zero or more parameters which can include ranges.

/**  * @customfunction  * @excludeFromAutocomplete  * @description Calculate the sum of arbitrary parameters.  * @param {number[][][]} operands - The operands to sum.  * @returns  The sum of all operands.  */ function sumAll(operands: number[][][]): number {   let total: number = 0;    operands.forEach(range => {     range.forEach(row => {       row.forEach(num => {         total += num;       });     });   });    return total; }

The following code sample shows how to create an entity with thesumAll reference method.

const referenceCustomFunctionRange: Excel.JavaScriptCustomFunctionReferenceCellValue = {   type: Excel.CellValueType.function,  functionType: Excel.FunctionCellValueType.javaScriptReference,  namespace: "CONTOSO",   id: "SUMALL" } function makeProductEntity(productID: number, productName: string, price: number) {  const entity: Excel.EntityCellValue = {    type: Excel.CellValueType.entity,    text: productName,    properties: {      "Product ID": {...},      "Product Name": {...},      "Unit Price": {...},      sumAll: referenceCustomFunctionRange    },  };  return entity;}

The following image shows an example of entering multiple parameters including a range parameter using thesumAll reference method.

Support details

Reference methods are supported in all custom function types, such asvolatile, andstreaming custom functions. Also, all custom function return types are supported (matrix, scalar, and error)A linked entity can’t have a custom function that combines both a reference method and a data provider. Be sure when developing linked entities to keep these types of custom functions separate.

See also

• Create custom functions in Excel
• Excel.CellValue
• LinkedEntityCellValue
• DoubleCellValue
• StringCellValue
• BooleanCellValue