Skip to main content

Create Microsoft Excel Document from Template

Jake Rosenthal avatar
Written by Jake Rosenthal
Updated this week

Creates an Excel document (.xlsx) from a template by filling in placeholders with dynamic values.

Transform your static Excel spreadsheets into dynamic, data-driven templates that automatically populate with information from your workflows.

With Cassidy's Excel document templates, you can:

  • Generate standardized invoice reports that consolidate data from multiple PDF invoices into a consistent format

  • Create financial summaries with automatically calculated totals and formatted currencies

  • Produce inventory sheets that expand horizontally and vertically based on your data

  • Build dynamic pricing tables that adapt to product lists of any size

  • Generate expense reports with formulas that compute totals, taxes, and approvals

To explain how this feature works, we'll walk you through a specific example: creating an invoice summary report that takes PDF invoices in different formats and converts them into a standardized Excel format for easy analysis and tracking.

How to create template placeholders in Excel documents

1. Text Placeholders - Insert Dynamic Content

Use these to insert text, numbers, or formatted content from your workflow data.

Syntax: {variableName}

Examples from our invoice template:

2. Conditionals - Show Content When Conditions Are Met

Use these to display rows or cells only when certain conditions are true, or to show alternative content when conditions are false.

Syntax:

  • Standard: {#if:conditionName} … {/if:conditionName}

  • Inverse: {^if:conditionName} … {/if:conditionName}

*The beginning and end of the conditional must be on the same row but in different cells.

Example from our invoice template:

The VAT requirements row only appears when hasVAT is true.

3. Loops - Repeat Content for Lists of Items

Use these to create multiple rows/columns from arrays of data.

Syntax:

  • Vertical loops (for rows): {#loop:arrayName} … {/loop:arrayName}

    • *The beginning and end of the loop must be on the same row but in different cells.

  • Horizontal loops (for columns): {>loop:arrayName}

    • *There is no closing tag for horizontal loops. Place child properties in the same cell.

Example from our invoice template:

The invoice data repeats for each invoice in the array, with nested loops for line items within each invoice.

*To render correctly, items must be a list where each item in the list has the necessary properties (such as description in this case).

Understanding Variable Scope in Loops:

When you're inside a loop, you can access both properties of the current item and properties from outside the loop:

  1. Properties of the current item - Use the field names directly

  2. Properties from outside the loop - These remain accessible

Inner Loops - Multiple Values in One Cell

When the loop start and end are in the same cell, content repeats within that cell instead of creating new rows.

Template:

Output:

Horizontal Loops - Expand Columns

Use the > prefix to expand data horizontally across columns.

Template:

Output:

Nested Loops - Complex Data Structures

You can nest loops to handle multi-level data like invoices with multiple line items.

Template:

Output:

Data Needed For Loops

If your template has this loop structure:

{#loop:items}{description}{/loop:items}

Your data must have an array that matches the properties exactly:

{
  "items": [
    {"description": "Web Design Services"},
    {"description": "Monthly Hosting"}
  ],
  "grandTotal": "$2,500.00",
  "hasVAT": true
  ,...
}

How to set up and use Create Microsoft Excel Document from Template action

Follow these steps:

  1. Add a Create Microsoft Excel Document from Template action: While editing a Workflow, click the plus button between existing blocks. This will open a modal where you can select from Cassidy's pre-configured actions. Choose the Create Microsoft Excel Document from Template action to add it to your Workflow.

  2. Rename the action (if needed): If you want to make the action more descriptive or easier to reference as a variable later, enter a new name.

  3. Upload Excel Template: Click to upload your Excel document template (.xlsx file) that contains placeholders for dynamic content.

  4. Enter Output Document Name: Provide a name for your generated Excel document. This will be the filename when the document is created. You can use variables from previous steps.

    Variables are inputs from the trigger, or outputs from previous steps

  5. Configure Template Fields: Under the Template Fields section, configure any field mappings using variables from previous Workflow steps or trigger inputs. For our invoice example:

    • {invoices}: This is a loop which requires a list variable containing objects with "invoiceNumber", "totalAmount", "date", "dueDate", and "items" properties

    • {grandTotal}: This is a text placeholder that maps to a calculated total from your data processing

    • {hasVAT}: This is a conditional that maps to a boolean variable to determine whether to show the VAT requirements message

  6. Preview and download the document: After running the Workflow, you can preview the generated Excel document and download it by clicking the icons next to the action in the results panel.

  7. Optionally, attach the document to other actions: You can use the generated Excel document as an attachment in other actions like Send Email. Simply reference the Excel document output from this action when configuring email attachments.

Here's a sample of the type of output you can expect from our invoice example:

Troubleshooting

Tags not processed

If you see template tags like {invoiceNumber} in the final output, double-check that:

  • The variable names in your data match the tag names exactly (case-sensitive)

  • Your data is properly formatted JSON

Did this answer your question?