Value functions in XLSX templates

Modern templating engine

Still using Classic syntax? See the Classic documentation.

You can use functions to add complex logic to values rendered in your templates. For example, you can change value format, hide content, join arrays, and more.

This article covers functions for XLSX templates.

You can also chain multiple functions in a single token:

{{token|function1(...)|function2(...)|function3(...)}}

Functions are applied from left to right. The output of each function becomes the input for the next one.

format

This function formats a token value. You can use it with or without parameters:

format formats a date value as a short date string.
format(val) formats the current value using the specified format string. For example, use N2 for a number with two decimal places.
format(val, locale) formats the current value using the specified format string and locale. For example, use format(C, fr-FR) to apply French currency formatting.

This function uses standard format strings and supports different locales. For example, you can place tokens with different locales in a template:

{{num1|format(C, en-US)}}
{{num2|format(C, fr-FR)}}

For more information about using locales, see the Microsoft documentation:

Note

Special characters and spaces are supported, but you’ll need to enclose the tag in square brackets inside the curly braces:

{{[date value]|format(dd.MM.yyyy)}}

Examples

Template	Data	Result
Date: {{date\|format(dd.MM.yyyy)}} Date: {{[date value]\|format(dd.MM.yyyy)}} Date: {{date\|format(MM/dd)}} Date: {{date\|format(U)}} Number: {{num\|format(C)}} Number: {{num\|format(C, fr-FR)}} Number: {{num\|format(P)}} Number: {{num\|format(N2)}}	{ "date": "2012-04-21T18:25:43-05:00", "date value": "2021-05-25T10:44:00-02:00", "num": 8 }	Date: 21.04.2012 Date: 25.05.2021 Date: 04/22 Date: Saturday, April 21, 2012 11:25:43 PM Number: $8.00 Number: 8.00 € Number: 800.00% Number: 8.00

Template

Data

Result

Date: {{date|format(dd.MM.yyyy)}}
Date: {{[date value]|format(dd.MM.yyyy)}}
Date: {{date|format(MM/dd)}}
Date: {{date|format(U)}}
Number: {{num|format(C)}}
Number: {{num|format(C, fr-FR)}}
Number: {{num|format(P)}}
Number: {{num|format(N2)}}

{
    "date": "2012-04-21T18:25:43-05:00",
    "date value": "2021-05-25T10:44:00-02:00",
    "num": 8
}

Date: 21.04.2012
Date: 25.05.2021
Date: 04/22
Date: Saturday, April 21, 2012 11:25:43 PM
Number: $8.00
Number: 8.00 €
Number: 800.00%
Number: 8.00

Combine this function with others like sum to address more advanced use cases.

map

map accepts an unlimited number of values and maps a specific value to a specific output.

For example, a property may store delivery types like these:

EmailShipping
ElectronicShipping

And we want to display them in a readable format:

Email shipping
Electronic shipping

Template	Data	Result
{{deliveryType\|map(“EmailShipping” => “Email shipping”, “ElectronicShipping” => “Electronic shipping”)}}	{ "deliveryType": "EmailShipping" }	Email shipping

Note

All values in double quotes are recognised as strings. If you want to map numbers, you don’t need to use double quotes in the condition.

For example, {{tag|map("Yes" => 1, "No" => 0)}}.

Combine this function with others like title-case and replace to address more advanced use cases.

Template	Data	Result
{{paymentStatus\|replace(_, )\|title-case\|map(“Status Success” => “Order Paid”, “Status Failed” => “Payment rejected”)}}	{ "paymentStatus": "status_success" }	Order Paid

filter

This function evaluates a condition against all elements in an array and returns a new array containing only the elements that meet that condition.

Syntax:

For objects in array: {{filteredObjects = objects|filter(@value.objectProperty != false)}}

For simple array: {{filteredValues = values|filter(@value != "condition")}}

Available operators:

== equal to,
!= not equal to,
> more than,
< less than,
>= more than or equal to,
<= less than or equal to,
|| logical “or”,
&& logical “and”,
() group.

Examples

Template	Data	Result
{{filteredColors = colors\|filter(@value != “Green”)}} Filtered colors: {{filteredColors}}	{ "colors": ["Red", "Green", "Blue"] }	Filtered colors: Red Blue
{{filteredColors = colors\|filter(@value.name != “Green”)}} Filtered colors: {{filteredColors.name}}	{ "colors": [ { "name": "Red" }, { "name": "Green" }, { "name": "Blue" } ] }	Filtered colors: Red Blue

Template

Data

Result

Filtered colors:

{{filteredColors}}

{
  "colors": ["Red", "Green", "Blue"]
}

Filtered colors:

Red
Blue

Filtered colors:

{{filteredColors.name}}

{
  "colors": [
    {
      "name": "Red"
    },
    {
      "name": "Green"
    },
    {
      "name": "Blue"
    }
  ]
}

Filtered colors:

Red
Blue

Combine this function with others like sort and sum to address more advanced use cases.

Template	Data	Result
Subtotal: {{sales\|filter(@value.price != 5)\|sum(price)}}	{ "sales": [ { "price": 3, "quantity": 90 }, { "price": 4, "quantity": 60 }, { "price": 5, "quantity": 30 } ] }	Subtotal: 7

sort

This function reorders the items in a list and saves the sorted values in a new calculated property.

Syntax:

{{sorted = items|sort(property, DESC)}}

Sorting parameters

ASC (Ascending): Sorts from A to Z or smallest to largest. This is the default and can be skipped.
DESC (Descending): Sorts from Z to A or largest to smallest.

Example

Template	Data	Result
Items: {{sorted = items\|sort()}} - {{sorted}}	{ "items": [987, 6534, 54] }	Items: - 54 - 987 - 6534
Employees: {{sorted = employees\|sort(lastName, DESC)}} - {{sorted.lastName}}, {{sorted.name}}	{ "employees": [ { "name": "James", "lastName": "Williams" }, { "name": "Jessica", "lastName": "Brown" }, { "name": "Robert", "lastName": "Jones" }, { "name": "Emily", "lastName": "Davis" } ] }	Employees: - Williams, James - Jones, Robert - Davis, Emily - Brown, Jessica

Template

Data

Result

Items: {{sorted = items|sort()}}
- {{sorted}}

{
  "items": [987, 6534, 54]
}

Items:
- 54
- 987
- 6534

Employees: {{sorted = employees|sort(lastName, DESC)}}
- {{sorted.lastName}}, {{sorted.name}}

{
  "employees": [
    {
      "name": "James",
      "lastName": "Williams"
    },
    {
      "name": "Jessica",
      "lastName": "Brown"
    },
    {
      "name": "Robert",
      "lastName": "Jones"
    },
    {
      "name": "Emily",
      "lastName": "Davis"
    }
  ]
}

Employees:
- Williams, James
- Jones, Robert
- Davis, Emily
- Brown, Jessica

Combine this function with others like filter to address more advanced use cases.

substring

Returns part of a string. You can use the function with one or two parameters:

substring(index) returns the part of the value starting at index.
substring(index, length) returns length characters starting at index.

Examples

Template	Data	Result
{{stringVal\|substring(6)}} {{stringVal\|substring(0, 5)}}	{ "stringVal": "Derek Clark" }	Clark Derek

sum

sum() - calculates the sum of values in an array.

{{items|sum()}} — sums the raw values in the array (e.g., [1, 2, 3] becomes 6),
{{items|sum(propertyName)}} — sums the specified property (propertyName) across all objects in the array.

Note

If you need to sum a calculated value like @value.quantity * @value.price, assign the expression to a calculated property first, then use that property with sum. See the example below.

Examples

Template	Data	Result
Total: {{items\|sum()}}	{ "items": [3, 2, 7] }	Total: 12
{{sales.totalAmount = @value.quantity * @value.price}} Total amount: ${{sales\|sum(totalAmount)}} Total quantity: {{sales\|sum(quantity)}}	{ "sales": [ { "price": 3, "quantity": 90 }, { "price": 4, "quantity": 60 }, { "price": 5, "quantity": 30 } ] }	Total amount: $660 Total quantity: 180

Template

Data

Result

Total:
{{items|sum()}}

{
  "items": [3, 2, 7]
}

Total:
12

{{sales.totalAmount = @value.quantity * @value.price}}

Total amount:
${{sales|sum(totalAmount)}}

Total quantity:
{{sales|sum(quantity)}}

{
  "sales": [
    {
      "price": 3,
      "quantity": 90
    },
    {
      "price": 4,
      "quantity": 60
    },
    {
      "price": 5,
      "quantity": 30
    }
  ]
}

Total amount:
$660

Total quantity:
180

Combine this function with others like filter and format to address more advanced use cases.

Template	Data	Result
Total Price: {{sales\|sum(price)\|format(C, en-US)}}	{ "sales": [ { "price": 3, "quantity": 90 }, { "price": 4, "quantity": 60 }, { "price": 5, "quantity": 30 } ] }	Total Price: $12.00

avg

avg() - calculates the average of values in an array.

{{items|avg()}} — averages raw values in a simple array (e.g., [3, 2, 7] → 4),
{{items|avg(propertyName)}} — averages the specified property (propertyName) across all objects in the array.

Note

If you need to average a calculated value like @value.quantity * @value.price, assign the expression to a calculated property first, then use that property with avg. See the example below.

Examples

Template	Data	Result
Average: {{items\|avg()}}	{ "items": [3, 2, 7] }	Average: 4
{{sales.amount = @value.quantity * @value.price}} Average amount: ${{sales\|avg(amount)}} Average quantity: {{sales\|avg(quantity)}}	{ "sales": [ { "price": 3, "quantity": 90 }, { "price": 4, "quantity": 60 }, { "price": 5, "quantity": 30 } ] }	Average amount: $220 Average quantity: 60

Template

Data

Result

Average:
{{items|avg()}}

{
  "items": [3, 2, 7]
}

Average:
4

{{sales.amount = @value.quantity * @value.price}}

Average amount:
${{sales|avg(amount)}}

Average quantity:
{{sales|avg(quantity)}}

{
  "sales": [
    {
      "price": 3,
      "quantity": 90
    },
    {
      "price": 4,
      "quantity": 60
    },
    {
      "price": 5,
      "quantity": 30
    }
  ]
}

Average amount:
$220

Average quantity:
60

count

count() - counts the number of items in an array.

You can use this function in two ways, as shown below.

Examples

Template	Data	Result
{{items\|count()}} {{count(items)}}	{ "items": [3, 2, 7] }	3 3

join(separator, order, property) - joins array values with a separator (required) and sorts them in a specific order (optional). property (optional) can be used to access properties in a nested structure.

Examples

Template	Data	Result
{{arr\|join(", ")}} {{arr\|join(", ", ASC)}} {{arr\|join("-", DESC)}} {{arr\|join(;)}} {{arr\|join(:)}}	{ "arr": [2, 1, 3] }	2, 1, 3 1, 2, 3 3-2-1 2;1;3 2:1:3
{{reports\|join(", ", @value.year)}} {{reports\|join(", ", ASC, @value.year)}} {{reports\|join(", ", DESC, @value.year)}}	{ "reports": [ { "year": "1992" }, { "year": "1987" }, { "year": "2005" } ] }	1992, 1987, 2005 1987, 1992, 2005 2005, 1992, 1987

Template

Data

Result

{{arr|join(", ")}}

{{arr|join(", ", ASC)}}
{{arr|join("-", DESC)}}

{{arr|join(;)}}
{{arr|join(:)}}

{
    "arr": [2, 1, 3]
}

2, 1, 3

1, 2, 3
3-2-1

2;1;3
2:1:3

{{reports|join(", ", @value.year)}}
{{reports|join(", ", ASC, @value.year)}}
{{reports|join(", ", DESC, @value.year)}}

{
    "reports": [
        {
            "year": "1992"
        },
        {
            "year": "1987"
        },
        {
            "year": "2005"
        }
    ]
}

1992, 1987, 2005
1987, 1992, 2005
2005, 1992, 1987

offset

offset(d) offsets a date or time value by d days.

offset(d.hh:mm:ss) offsets a value by a combination of d days, hh hours, mm minutes, and ss seconds.

Use the d.hh:mm:ss pattern and replace d, hh, mm, and ss with the required values.

Examples

Template	Data	Result
Without offset: {{date}} Plus 10 days {{date\|offset(10)}} Minus 10 days: {{date\|offset(-10)}} Plus 10 days, 1 hour, 5 minutes, 10 seconds: {{date\|offset(10.1:5:10)}} Minus 10 days, 1 hour, 5 minutes, 10 seconds: {{date\|offset(-10.1:5:10)}}	{ "date": "2012-04-21T18:25:43-05:00" }	Without offset: 4/22/2012 3:25:43 AM Plus 10 days 5/2/2012 3:25:43 AM Minus 10 days: 4/12/2012 3:25:43 AM Plus 10 days, 1 hour, 5 minutes, 10 seconds: 5/2/2012 4:30:53 AM Minus 10 days, 1 hour, 5 minutes, 10 seconds: 4/12/2012 2:20:33 AM

Template

Data

Result

Without offset:
{{date}}

Plus 10 days
{{date|offset(10)}}

Minus 10 days:
{{date|offset(-10)}}

Plus 10 days, 1 hour,
5 minutes, 10 seconds:
{{date|offset(10.1:5:10)}}

Minus 10 days, 1 hour,
5 minutes, 10 seconds:
{{date|offset(-10.1:5:10)}}

{
    "date": "2012-04-21T18:25:43-05:00"
}

Without offset:
4/22/2012 3:25:43 AM

Plus 10 days
5/2/2012 3:25:43 AM

Minus 10 days:
4/12/2012 3:25:43 AM

Plus 10 days, 1 hour,
5 minutes, 10 seconds:
5/2/2012 4:30:53 AM

Minus 10 days, 1 hour,
5 minutes, 10 seconds:
4/12/2012 2:20:33 AM

bool

bool(yes, no, maybe) converts a boolean value to yes, no, or maybe.

You can specify your own value for each state. The last parameter is optional. You can use it if your bool value can be null.

Examples

Template	Data	Result
{{boolVal1\|bool(yes, no, maybe)}} {{boolVal2\|bool(yes, no, maybe)}} {{boolVal3\|bool(yes, no, maybe)}}	{ "boolVal1": true, "boolVal2": false, "boolVal3": null, }	yes no maybe

is-empty

The is-empty() function simplifies conditions by evaluating the token’s content. It returns true if the value is null, an empty string, or an empty array.

Template	Data	Result
{{val1\|is-empty()}} {{val2\|is-empty()}} {{val3\|is-empty()}}	{ "val1": "Jessica Adams", "val2": "", "val3": [], }	false true true

repair-url

repair-url converts a URL to a fully qualified URL with an HTTP or HTTPS scheme if needed. If the resulting URL is invalid, it is removed from the document.

Template	Data	Result
{{value\|repair-url}}	{ "value": "picturesite.com/pics/picture.png" } { "value": "google.com" } { "value": ".net" }	https://picturesite.com/pics/picture.png https://google.com

keep-token

The keep-token function keeps tokens as they are.

It can be useful if your document contains other system tags in double curly braces (for instance, Adobe Sign text tags).

It is also useful when your document contains plain text enclosed in double curly braces.

Note

Special characters and spaces are supported, but you’ll need to enclose the token in square brackets inside the curly braces:

{{[Sig es :+signer+:signature]|keep-token}}

Template	Result
{{value\|keep-token}} {{Sig_es_:signer1:signature\|keep-token}} {{[Sig es :+signer+:signature]\|keep-token}}	{{value}} {{Sig_es_:signer1:signature}} {{Sig es :+signer+:signature}}

merge

This function merges a table cell with an adjacent cell in the specified direction if the given condition is met. To control the merge direction use:

{{#merge-left-if condition}}
{{#merge-right-if condition}}
{{#merge-top-if condition}}
{{#merge-bottom-if condition}}

Note

This formatter doesn’t support merging cells in table ranges. Thus, if you want to use this formatter, apply it to regular Excel cells instead.

Examples

Template

Data

    {
        "collection": [
            {
                "name": "Derek Clark",
                "sold": null
            },
            {
                "name": "Jessica Adams",
                "sold": 14000
            },
            {
                "name": "Xue Li",
                "sold": 500
            },
            {
                "name": "Martin Huston",
                "sold": 9400
            },
            {
                "name": "Anton Frolov",
                "sold": 1000
            }
        ]
    }

-

Results

title-case

title-case - converts a string to title case, capitalizing the first letter of each word.

Template	Data	Result
{{text\|title-case}}	{ "text":"plumsail documents" }	Plumsail Documents

contains

contains(text, ignoreCase) checks if the token value contains the specified text.

This function is case‑sensitive by default and requires an exact match. Add the optional ignoreCase parameter (a boolean value: true or false) to match regardless of letter case.

Hint

You can use this function with logical operations, such as #if and #hide-if to build conditional logic.

Examples

Template	Data	Result
{{clauses\|contains(NDA)}} {{warrantyApplies = clauses\|contains(Warranty, true) && region == "EU"}} Warranty clause{{#if warrantyApplies == 'True'}} included{{#else}} not included{{/if}}.	{ "clauses": "NDA; Warranty", "region": "EU" }	true Warranty clause included

Template

Data

Result

{{clauses|contains(NDA)}}

{{warrantyApplies = clauses|contains(Warranty, true) && region == "EU"}}

Warranty clause{{#if warrantyApplies == 'True'}} included{{#else}} not included{{/if}}.

{
    "clauses": "NDA; Warranty",
    "region": "EU"
}

true

Warranty clause included

starts-with

starts-with(text, ignoreCase) checks if the token value begins with the specified text.

This function is case‑sensitive by default and requires an exact match. Add the optional ignoreCase parameter (a boolean value: true or false) to match regardless of letter case.

Hint

You can use this function with logical operations, such as #if and #hide-if to build conditional logic.

Examples

Template	Data	Result
{{price\|starts-with($)}} {{usdPrice = price\|starts-with($)}} {{#if usdPrice=='True'}} USD price: {{price}} {{#else}} Currency not specified: {{price}} {{/if}}	{ "price": "1099.00" }	false Currency not specified: 1099.00
{{trackingNumber\|starts-with(int, true)}}	{ "trackingNumber": "INT-P20250456" }	true

Template

Data

Result

{{price|starts-with($)}}

{{usdPrice = price|starts-with($)}}

{{#if usdPrice=='True'}} USD price: {{price}} {{#else}} Currency not specified: {{price}} {{/if}}

{
    "price": "1099.00"
}

false

Currency not specified: 1099.00

{{trackingNumber|starts-with(int, true)}}

{
    "trackingNumber": "INT-P20250456"
}

true

ends-with

ends-with(text, ignoreCase) checks if the token value ends with the specified text.

This function is case‑sensitive by default and requires an exact match. Add the optional ignoreCase parameter (a boolean value: true or false) to match regardless of letter case.

Hint

You can use this function with logical operations, such as #if and #hide-if to build conditional logic.

Examples

Template	Data	Result
{{customerEmail\|ends-with(@company.com)}} {{#if customerEmail\|ends-with(@company.com)}}Internal{{#else}}External{{/if}} recipient	{ "customerEmail": "John.Smith@company.com" }	TRUE Internal recipient
{{documentId\|ends-with(-FIN, true)}}	{ "documentId": "Report-2025-FIN" }	TRUE

Template

Data

Result

{{customerEmail|ends-with(@company.com)}}

{{#if customerEmail|ends-with(@company.com)}}Internal{{#else}}External{{/if}} recipient

{
    "customerEmail": "John.Smith@company.com"
}

TRUE

Internal recipient

{{documentId|ends-with(-FIN, true)}}

{
    "documentId": "Report-2025-FIN"
}

TRUE

replace

The replace function replaces one value or text fragment with another.

Note

This function is case-sensitive.

Template	Data	Result
{{text\|replace(before, after)}}	{ "text":"before using function" }	after using function
{{text\|replace(before, after)}}	{ "text":"Before using function" }	Before using function
{{value\|replace(10000, 10)}}	{ "value": 1000099 }	1099

Template

Data

Result

{{text|replace(before, after)}}

{
    "text":"before using function"
}

after using function

{{text|replace(before, after)}}

{
    "text":"Before using function"
}

Before using function

{{value|replace(10000, 10)}}

{
    "value": 1000099
}

Combine this function with others like title-case and map to address more advanced use cases.

regex

regex(pattern, replacement) - returns a new string with all matches of a RegExp pattern replaced by a replacement.

Hint

Regular expressions can be created and tested using services such as Regex101 or RegExr.

Examples

Template	Data	Result
{{name\|regex((\w+)\s(\w+), $2 $1)}} {{accountNumber\|regex(\d(?=.\d(?:\s\d){3}\s$), )}}	{ "name": "Martin Huston", "accountNumber": "1234567890" }	Huston Martin ******7890

length

The length() function returns the number of items in an array or the number of characters in a string.

Template	Data	Result
{{value\|length()}}	{ "value":"Plumsail Documents" }	18
{{value\|length()}}	{ "value": [1,2,3,4] }	4

top

The top() function returns the first specified number of items from an array.

{{topItems = items|top(3)}}

Examples

Template	Data	Result
{{topTasks = tasks\|top(2)}} Upcoming tasks: - {{topTasks.title}}	{ "tasks": [ { "title": "Prepare contract" }, { "title": "Send invoice" }, { "title": "Schedule meeting" } ] }	Upcoming tasks: Prepare contract Send invoice

Template

Data

Result

{{topTasks = tasks|top(2)}}

Upcoming tasks:

- {{topTasks.title}}

{
  "tasks": [
    {
      "title": "Prepare contract"
    },
    {
      "title": "Send invoice"
    },
    {
      "title": "Schedule meeting"
    }
  ]
}

Upcoming tasks:

Prepare contract
Send invoice

at

The at() function returns the item at the specified index from an array.

{{atObject = objects|at(2)}}

Note

Array indexes start from 0. For example, at(0) returns the first item in the array.

Example

Template	Data	Result
{{selectedEmployee = employees\|at(1)}} Selected employee: {{selectedEmployee.name}}	{ "employees": [ { "name": "John Smith" }, { "name": "Emma Davis" }, { "name": "Michael Brown" } ] }	Selected employee: Emma Davis

slice

The slice() function returns a portion of an array between the specified start and end indexes.

The item at the end index is not included in the result. The end index is optional.

{{slicedObjects = objects|slice(1, 3)}}

Note

Array indexes start from 0. For example, slice(1, 3) returns the second and third items from the array.

Example

Template	Data	Result
{{featuredProducts = products\|slice(1, 3)}} Featured products: - {{featuredProducts.name}}	{ "products": [ { "name": "Laptop" }, { "name": "Monitor" }, { "name": "Keyboard" }, { "name": "Mouse" } ] }	Featured products: Monitor Keyboard
{{recentOrders = orders\|slice(1)}} Recent orders: - {{recentOrders.number}}	{ "orders": [ { "number": "ORD-1001" }, { "number": "ORD-1002" }, { "number": "ORD-1003" } ] }	Recent orders: ORD-1002 ORD-1003

Template

Data

Result

{{featuredProducts = products|slice(1, 3)}}

Featured products:

- {{featuredProducts.name}}

{
  "products": [
    {
      "name": "Laptop"
    },
    {
      "name": "Monitor"
    },
    {
      "name": "Keyboard"
    },
    {
      "name": "Mouse"
    }
  ]
}

line-break-if

The line-break-if function inserts a line break at the specified position within a cell when the condition evaluates to true.

Important: Requires the Wrap Text option to be enabled on the target cell in the Excel template.

Example

Template	Data	Result
Before the break. {{#line-break-if isActive}} After the break.	{ "isActive" : true }	Before the break. After the break.

Calculated properties in XLSX templates

Object operations in XLSX templates