In this page

What is the Excel Automation API?

The Excel Automation API is a standard REST API which implements a wide range of Excel automation use cases on the Jira Cloud platform.

Notes:

  • This page contains the technical documentation of the API. If you are not interested in the technical details, follow the configuration guides instead.
  • You can connect to the API from any Jira automation rule using the built-in Send web request action. With that action, you can also use smart values in the request headers and bodies to make them more flexible.

API resources

Note that at the moment, the API publishes a single universal endpoint to execute every type of Excel automations.

Execute automation

Executes an automation which exports a Excel file and executes actions on that.

URL: https://jxlsc.midori.systems/rest/v1/automation

Method: POST

Request headers

These required request headers must be present in every request. Note that they primarily contain information for authenticating the request.

Beware! When you copy the header name or value from this table to the Send web request action's configuration form, Chrome, Edge and other browsers may append unexpected spaces to the string, converting "Foo" to "Foo " for instance! Although the spaces are hard to notice, they will break the header and will result in a Bad Request (400) response. Therefore, remove all spaces from the end of the string after pasting it!

Header name Value (example) Notes
Content-Type application/x-yaml The content type of the request body.
Always use the exact value shown in the "Value" column.
X-Midori-Base-Url {{baseUrl}} The base URL of the Jira Cloud site.
Always use the exact value shown in the "Value" column. (It is a smart value that will be automatically resolved to the correct URL.)
X-Midori-User-Email john.doe@acme.com The email address of a user with access to the Jira Cloud site.
This user must own the API token defined by the next header.
X-Midori-Api-Token Create API token An Atlassian API token owned by the user account defined by the previous header.
For security reasons, check the Hidden option for this header.
Request body

The body of the request sent to the Excel Automation REST API is a human-readable YAML document. This YAML instructs the API about what to export to Excel and what to do with the exported file.

Here is an example that will export the list of issues created in the project FOO this week, then send the Excel file as an email attachment to two recipients:

client: {{rule.name}}
export:  # these configure what to export to Excel
  view: Excel (Current Fields)
  viewContext: ISSUE_NAVIGATOR
  jql: project = FOO and created > startOfWeek()
  columnConfiguration: USER_DEFAULT
actions:  # these configure what to do with the Excel file
  - name: Send Excel as email attachment
    type: SEND
    to: [ alice@acme.com, bob@acme.com ]
    subject: All FOO issues created this week
    body: >-
      Hi,<br/>
      <br/>
      Find the <b>Excel file</b> attached to this email with the list on {{now.jiraDate}}.<br/>
      Happy reading!
  - name: Call back to Jira Cloud Automation
    type: CALL
    url: https://automation.atlassian.com/pro/hooks/1234567890abcdef

See the details of each field in the next sections and a few practical ideas for writing efficient YAML in the YAML tips page.

Top-level fields

These fields are on the top-level of the YAML. They contain metadata about the Excel automation.

Field name Value (example) Notes
client {{rule.name}} The name of the Excel export rule which the Excel automation is part of. It is for identification purposes only.
Always use the exact value shown in the "Value" column. (It is a smart value that will be automatically resolved to the correct rule name.)
Export fields

These fields are under the export section of the YAML. They configure what to export to the Excel file which will then be processed by the actions.

We strongly recommend generating the export section instead of manually writing it!

Field name Value (example) Notes
view Excel (Current fields) The name or the ID of the Excel view to export.
Tip: if unsure, look it up in the generated YAML fragment.
viewContext Options:
  • SINGLE_ISSUE_VIEW
  • ISSUE_NAVIGATOR
  • JIRA_SOFTWARE (covers all backlog and board views)
The context (screen) where the export is initiated from.
Although the automation rule executes without any UI, this value may affect the exported file if the Excel template behaves differently depending on the context. Note that the Excel view set in the view field must have this context enabled.
Tip: if unsure, look it up in the generated YAML fragment.
jql project = FOO and created > startOfWeek() The JQL query that selects the issues to export.
Also see the partly overlapping filterId field.
filterId (ISSUE_NAVIGATOR context only!) 10002 The ID of the saved filter that selects the issues to export.
If both jql and filterId are defined, then the jql query takes precedence over the saved filter's query, but the saved filter still determines the filename and the column configuration.
Tip: if unsure, look it up in the generated YAML fragment.
columnConfiguration (ISSUE_NAVIGATOR context only!) Options:
  • FILTER (use the saved filter's columns)
  • USER_DEFAULT (use the user's default columns)
The column configuration that selects the columns to export.
boardId (JIRA_SOFTWARE context only!) 42 The ID of the board to export the issues from.
Tip: if unsure, look it up in the generated YAML fragment.
boardType (JIRA_SOFTWARE context only!) Options:
  • AGILITY (next-gen)
  • KANBAN (classic Kanban)
  • SCRUM (classic Scrum)
The type of the board.
Tip: if unsure, look it up in the generated YAML fragment.
boardScreen (JIRA_SOFTWARE context only!) Options:
  • PLAN (backlog view)
  • WORK (board view)
The board screen to export from.
Tip: if unsure, look it up in the generated YAML fragment.
sprintId (JIRA_SOFTWARE context and the PLAN board screen only!) 35 The ID of the sprint to export the issues from.
Tip: if unsure, look it up in the generated YAML fragment.
actions The list of the actions.
You can add any number of actions.
Action fields

These fields are under the actions section of the YAML. You can configure each item in the actions array using these fields. They configure what to do with the Excel file which was produced by the export.

Field name Value (example) Notes
name Email quote to customer as Excel The name of the action.
It is for identification purposes only.
type Options: The type of the action.
CALL must be the last one in the action list.
"send" action parameters
to [ alice@acme.com, bob@acme.com ] The list of the recipients to the To field of the email.
cc [ cecil@acme.com ] The list of the recipients to the Cc field.
bcc [ david@acme.com ] The list of the recipients to the Bcc field.
subject All issues created in project FOO this week The subject of the email.
body Hi, find the <b>PDF file</b> attached to this email with the list (on {{now.jiraDate}}). The body content of the email.
(You can write multiple lines, use HTML formatting and line breaks!)
"attach" action parameters
toIssueKey {{issue.key}} The key of the issue to attach the Excel file to.
Tip: it can be a concrete issue key like "FOO-123" or the smart value in the left.
"call" action parameters
url https://automation.atlassian.com/pro/hooks/123 The URL of the webhook trigger defined in the Excel callback rule.
Response body

If the automation request was accepted, the end-point responds with status code 200 (OK). In this case, the body contains the identifier of the newly accepted automation which is already being worked on in the background:

{
  "messageId": "ea109079-6ce9-5068-8120-ceaee3abbab3"
}

You can access the identifier using the {{webResponse.body.messageId}} smart value.

If the automation request was rejected, the end-point responds with status code 400 (Bad Request). In this case, the body contains the error message (most likely about an invalid field in the request body):

"actions[0].toIssueKey" must be defined and not be blank!

If the automation responds with status code 500 or any other 50x, these mean a temporary system error. In this case, re-try submitting the automation request.

Callback body

After the Excel automation completed its execution, it will send a request to the Excel callback rule's webhook URL. The body of the request is a standard JSON document. This JSON carries details, most importantly the result, about the execution of the automation.

Here is an example about successfully exporting the Excel spreadsheet and sending that as an email attachment:

{
  "data": {
    "messageId": "8478ee90-6175-53bf-9ef2-b022a48ee6e4",
    "client": "Email quote to customer and backup",
    "fileName": "FOO-123.xlsx",
    "exportSuccessful": true,
    "actionsSuccessful": true,
    "successful": true,
    "message": null,
    "actionResult": [
      {
        "type": "SEND",
        "name": "Email quote to customer as Excel",
        "successful": true,
        "message": null
      }
    ]
  }
}

It is rather unlikely that you will directly work with the JSON, you will use the following smart values instead.

Smart values

These fields are within the data wrapper of the JSON. You can access them using the smart value syntax.

Smart value name Value (example) Notes
{{webhookData.messageId}} 8478ee90-6175-53bf-9ef2-b022a48ee6e4 Same as in the automation response.
Tip: it helps to find the automation request in the audit log that finished with this result.
{{webhookData.client}} Email quote to customer and backup Same as in the automation request.
Tip: it identifies the export rule that finished with this result if the callback rule is shared between multiple export rules.
{{webhookData.fileName}} FOO-123.xlsx The name of the exported Excel file.
{{webhookData.exportSuccessful}} true Indicates whether the Excel export was successful (boolean).
{{webhookData.actionsSuccessful}} true Indicates whether all automation actions were successful (boolean).
{{webhookData.successful}} true Indicates whether "everything" was successful by combining the previous two flags (boolean).
{{webhookData.message}} null The error message if the Excel export failed or null otherwise.
{{webhookData[0].actionResult...}} The list of the automation action results.
Indexing starts with 0.
{{...actionResult.type}} SEND Same as in the automation request.
{{...actionResult.name}} Email quote to customer as Excel Same as in the automation request.
{{...actionResult.successful}} true Indicates whether the action was successful (boolean).
{{...actionResult.message}} null The error message if the action failed or null otherwise.

Questions?

Ask us any time.