Automating Jira Service Management Reporting with Power Automate

Generating recurring Jira Service Management (JSM) reports can be labor-intensive, especially when dealing with pagination, custom fields, and CSV formatting requirements. This guide provides a step-by-step walkthrough for building an automated reporting workflow using Power Automate, Jira Cloud API, and SharePoint.

This article is written for IT professionals who want to implement this solution without exposing sensitive organizational details. All examples use sanitized placeholders that you can replace with your own values.

---

Architecture Overview

---

What We Are Going to Build

A Power Automate flow that:

1. Runs on a fixed schedule (e.g., every 2 weeks)
2. Calls the Jira Cloud REST API with proper pagination handling
3. Collects all issues from a saved Jira filter (even if there are 500+ issues)
4. Flattens nested JSON fields (including custom fields) into a CSV file
5. Saves the CSV to SharePoint
6. Sends an email with the CSV attached

---

Demo & Live Example

Want to see what this automation can achieve?

Check out the final result showcase with real-world dashboards and KPI visualizations:

JSM Sales Performance Dashboard - GitHub Repository

This repository showcases:

• KPI dashboards built from automated Jira reports
• Real-world use case: Sales team performance tracking
• Data visualization examples: How CSV reports become actionable insights
• Production deployment: Live implementation in enterprise environment

---

Prerequisites

Before starting, ensure you have:

• Jira Cloud with:
  • A saved filter (we will use its filter ID)
  • A Jira API token for authentication
• Microsoft 365 with:
  • Power Automate access
  • SharePoint Online
  • Outlook mailbox (for email delivery)

---

Step 1: Get Your Jira Filter ID

How to Find the Filter ID

1. Open Jira in your browser

2. Go to Filters → View all filters

3. Click the filter you want to use for the report

4. Look at the browser URL:

   https://yourtenant.atlassian.net/issues/?filter=12345

5. The number after filter= is your filter ID (in this example: 12345)

In the API request body, you will use:

"jql": "filter = 12345"

Replace 12345 with your actual filter ID.

---

Step 2: Generate a Jira API Token

1. Go to https://id.atlassian.com/manage-profile/security/api-tokens
2. Click Create API token
3. Give it a label (e.g., "Power Automate Integration")
4. Copy the token and save it securely
5. You will use this token as the password in HTTP Basic Authentication

---

Step 3: Understanding the Jira Search API

API Endpoint

POST https://<YOUR_TENANT>.atlassian.net/rest/api/3/search/jql

Request Body (Example)

{
  "jql": "filter = 12345",
  "maxResults": 100,
  "fields": [
    "key",
    "summary",
    "status",
    "duedate",
    "resolution",
    "created",
    "customfield_10001",
    "customfield_10002",
    "customfield_10003",
    "assignee",
    "reporter",
    "priority",
    "description"
  ],
  "nextPageToken": ""
}

Key Parameters

Parameter	Description
jql	Your JQL query (using filter ID is recommended)
maxResults	Number of issues per page (recommended: 100)
fields	Array of field IDs to retrieve (include customfield_XXXXX)
nextPageToken	Empty string for first call, then use value from response

API Response (Simplified)

{
  "issues": [ /* array of issue objects */ ],
  "isLast": false,
  "nextPageToken": "eyJwYWdlIjoyfQ=="
}

Critical fields:

• issues - Array of issue objects (up to 100 per page)
• isLast - Boolean indicating if this is the last page
• nextPageToken - Token to fetch the next page

---

Step 4: Build the Power Automate Flow

Power Automate Flow Architecture

Before we start building, here's the complete flow structure:

Flow Sections:

Section	Color	Actions	Purpose
Initialization	Blue	Recurrence + 3 Variables	Set up trigger and storage
Pagination Loop	Yellow	HTTP + Parse + Append + Update	Collect all issues across pages
Data Transformation	Green	Select + CSV	Flatten JSON to CSV
Output	Purple/Red	SharePoint + Email	Deliver results

Key Points:

• Loop structure: Continues until isLast = true from Jira API
• Variable updates: Happen inside the loop after each page
• Parallel outputs: CSV goes to both SharePoint and Email simultaneously
• Safety limits: Loop has 60 iteration max and 1-hour timeout

---

4.1 Create a New Scheduled Flow

1. Go to Power Automate
2. Click Create → Scheduled cloud flow
3. Configure:
   • Flow name: Jira Biweekly CSV Report
   • Starting: Select your start date/time
   • Repeat every: 2 → Weeks
4. Click Create

4.2 Configure the Recurrence Trigger

Set these values in the Recurrence trigger:

Property	Value
Frequency	Week
Interval	2
Time zone	Your timezone (e.g., (UTC+08:00) Taipei)
At these hours	9
At these minutes	0

This means: Run every 2 weeks at 09:00

---

4.3 Initialize Variables

Add three Initialize variable actions:

Variable 1: varAllIssues (Array)

• Name: varAllIssues
• Type: Array
• Value: (leave empty)

Variable 2: varNextPageToken (String)

• Name: varNextPageToken
• Type: String
• Value: (leave empty - this represents "")

Variable 3: varIsLast (Boolean)

• Name: varIsLast
• Type: Boolean
• Value: false

---

4.4 Add a "Do until" Loop

Add action: Do until

Condition configuration:

• Left side: variables('varIsLast')
• Operator: is equal to
• Right side: true

Change limits:

• Count: 60 (maximum 60 iterations)
• Timeout: PT1H (1 hour)

This ensures the loop will:

• Continue until Jira indicates the last page (isLast = true)
• Have safety limits to prevent infinite loops

---

4.5 Inside "Do until" - HTTP Call to Jira

Action: HTTP

Property	Value
Method	POST
URI	https://<YOUR_TENANT>.atlassian.net/rest/api/3/search/jql
Headers	Content-Type: application/json
Authentication	Basic
Username	Your Jira account email
Password	Your Jira API token (from Step 2)

Body (use code view for easier editing):

{
  "jql": "filter = 12345",
  "maxResults": 100,
  "fields": [
    "key",
    "summary",
    "status",
    "duedate",
    "resolution",
    "created",
    "customfield_10001",
    "customfield_10002",
    "customfield_10003",
    "assignee",
    "reporter",
    "priority",
    "description"
  ],
  "nextPageToken": "@{variables('varNextPageToken')}"
}

Important notes:

• Replace <YOUR_TENANT> with your Jira tenant name
• Replace 12345 with your actual filter ID
• Replace customfield_XXXXX with your actual custom field IDs
• The @{variables('varNextPageToken')} expression will be evaluated by Power Automate

---

4.6 Inside "Do until" - Parse JSON

Add action: Parse JSON

Property	Value
Content	Dynamic content: Body (from HTTP step)
Schema	See schema below

JSON Schema (example):

{
  "type": "object",
  "properties": {
    "issues": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "id": { "type": "string" },
          "key": { "type": "string" },
          "fields": { "type": "object" }
        },
        "required": ["id", "key", "fields"]
      }
    },
    "nextPageToken": {
      "type": ["string", "null"]
    },
    "isLast": {
      "type": "boolean"
    }
  },
  "required": ["issues", "isLast"]
}

Tip: You can generate this schema automatically:

1. Run your HTTP call once manually (using Postman or similar)
2. Copy the response JSON
3. In Power Automate, click Use sample payload to generate schema
4. Paste your sample response

---

4.7 Inside "Do until" - Append Each Issue

Add action: Apply to each

Property	Value
Select an output from previous steps	body('Parse_JSON')?['issues']

Inside this Apply to each, add action: Append to array variable

Property	Value
Name	varAllIssues
Value	item() (expression)

Critical: Do NOT use union()

Never use:

union(variables('varAllIssues'), variables('varAllIssues'))

This causes a self-reference error. Always use Append to array variable.

---

4.8 Inside "Do until" - Update Pagination Variables

After the Apply to each (still inside the Do until loop), add two Set variable actions:

Set Variable 1: varNextPageToken

Property	Value
Name	varNextPageToken
Value	body('Parse_JSON')?['nextPageToken']

Set Variable 2: varIsLast

Property	Value
Name	varIsLast
Value	body('Parse_JSON')?['isLast']

Critical: Never hardcode true

Always use the API response value, not a hardcoded boolean. This ensures proper pagination handling.

---

Step 5: Flatten Issues to CSV Format

5.1 Select Action (Field Mapping)

After the Do until loop (outside of it), add action: Select

Property	Value
From	variables('varAllIssues')

Field mappings (add each row via GUI):

Column Name	Value (Expression)
IssueKey	item()?['key']
IssueId	item()?['id']
Summary	item()?['fields']?['summary']
Status	item()?['fields']?['status']?['name']
DueDate	item()?['fields']?['duedate']
Resolution	item()?['fields']?['resolution']?['name']
Created	item()?['fields']?['created']
CustomField1	item()?['fields']?['customfield_10001']?['value']
CustomField2	item()?['fields']?['customfield_10002']
CustomField3	item()?['fields']?['customfield_10003']?['displayName']
AssigneeName	item()?['fields']?['assignee']?['displayName']
AssigneeEmail	item()?['fields']?['assignee']?['emailAddress']
ReporterName	item()?['fields']?['reporter']?['displayName']
Priority	item()?['fields']?['priority']?['name']
Description	item()?['fields']?['description']

Important tips:

• Add each mapping row one by one through the GUI
• Do NOT paste a large JSON object - Power Automate will reject it
• Use the ? operator for safe null handling
• Adjust field paths based on your custom field structure

---

5.2 Create CSV Table

Add action: Create CSV table

Property	Value
From	Output of Select action
Columns	Automatic (or Custom if needed)

This action converts the JSON array into CSV format with headers.

---

Step 6: Save CSV to SharePoint

Add action: Create file

Property	Value
Site Address	https://contoso.sharepoint.com/sites/YourSite
Folder Path	/Shared Documents/JiraReports
File Name	JiraReport-@{formatDateTime(utcNow(),'yyyyMMddHHmmss')}.csv
File Content	Output of Create CSV table

Replace placeholders:

• contoso → your tenant name
• YourSite → your SharePoint site name
• JiraReports → your document library path

This creates timestamped files like:

JiraReport-20251121093015.csv
JiraReport-20251205093022.csv

---

Step 7: Send Email with Attachment

Add action: Send an email (V2)

Property	Value
To	reports@contoso.com
Subject	[Biweekly Jira Report] Filter 12345 - @{formatDateTime(utcNow(),'yyyy-MM-dd')}
Body	See HTML body below

Email body (HTML example):

<p>Hello Team,</p>
<p>Please find attached the latest Jira Service Management report for filter 12345.</p>
<p>This report includes:</p>
<ul>
  <li>All open and closed issues</li>
  <li>Custom field values</li>
  <li>Assignee and reporter information</li>
</ul>
<p>The CSV file is also stored in SharePoint at:</p>
<p><strong>/Shared Documents/JiraReports</strong></p>
<br/>
<p>Regards,<br/>Automation System</p>

Attachments:

Property	Value
Attachments Name	JiraReport-@{formatDateTime(utcNow(),'yyyyMMdd')}.csv
Attachments Content	Output of Create CSV table

---

Troubleshooting Guide

Common Issues and Solutions

1. Self-Reference Error on varAllIssues

Error message:

The action 'Set_variable' cannot use the variable 'varAllIssues' in its inputs

Cause:

Using union(variables('varAllIssues'), variables('varAllIssues'))

Solution:

Always use Append to array variable inside Apply to each

---

2. Parse JSON Keeps Failing

Error message:

Invalid type. Expected Object but got Array.

Cause:

Schema does not match the actual API response structure

Solution:

1. Use Postman or similar tool to get a real API response
2. Copy the entire JSON response
3. In Power Automate Parse JSON action, click Use sample payload to generate schema
4. Paste your real response
5. Review and adjust the generated schema

---

3. Select Action Shows "Enter valid JSON"

Error message:

Invalid expression: expected JSON object

Cause:

Pasting a whole JSON object into a single mapping field

Solution:

• Add each column mapping one by one through the GUI
• For each column, only enter one expression
• Example: item()?['fields']?['summary'] (NOT an entire object)

---

4. Flow Only Returns First 100 Issues

Problem:

Report always contains exactly 100 issues, even though the filter has 500+

Cause:

• Do until condition not set correctly
• varIsLast not being updated from API response
• nextPageToken not being passed in HTTP body

Solution checklist:

• Do until condition checks varIsLast = true
• varIsLast is updated with body('Parse_JSON')?['isLast']
• nextPageToken is passed in HTTP body as @{variables('varNextPageToken')}
• No hardcoded true or false values in variable updates

---

5. Authentication 401 or 403 Errors

Error message:

401 Unauthorized
403 Forbidden

Cause:

• Invalid API token
• Insufficient permissions
• Wrong username/password format

Solution:

1. Verify API token is valid (regenerate if necessary)
2. Confirm your Jira user has permission to:
   • View the filter
   • Access all issues in the filter
   • View all custom fields used
3. Ensure username is your email address (not display name)
4. Ensure password is the API token (not your Jira password)

---

Best Practices

Performance Optimization

1. Use maxResults = 100

   • This is the optimal batch size for Jira API
   • Larger values may cause timeouts
   • Smaller values increase API call overhead

2. Use saved filters instead of complex JQL

   • "jql": "filter = 12345" is more maintainable
   • Complex JQL can be tested in Jira UI before hardcoding

3. Set appropriate loop limits

   • Count: 60 (handles up to 6000 issues)
   • Timeout: PT1H (1 hour maximum runtime)

Security Considerations

API Token Management - Choose Your Security Level

Important: This tutorial uses Level 1 (Basic Protection) - storing the API token directly in Power Automate's HTTP action. This is a secure and practical approach for most use cases. Level 2 (Azure Key Vault) is optional and only needed for specific enterprise scenarios.

Level 1: Basic Protection (Used in This Tutorial) ✓

Store the API token directly in the HTTP action's Authentication field:

Property	Value
Authentication	Basic
Username	your.email@company.com
Password	ATATT3xFfGF0... (API Token)

Security features:

• Power Automate automatically encrypts the token
• Token is not visible in exported flow definitions
• Access controlled by Power Automate permissions

When to use:

• Personal projects or proof-of-concept
• Small teams (< 5 people)
• Non-regulated environments

Not recommended when:

• ❌ Multiple flows share the same token (rotation becomes difficult)
• ❌ Compliance requirements mandate centralized secret management
• ❌ Regular token rotation is required

---

Level 2: Azure Key Vault (Optional - Enterprise Requirements Only)

Note: This level is only needed if your organization requires centralized secret management. Most users can skip this section.

Store tokens in Azure Key Vault and retrieve them dynamically:

Step 1: Store token in Azure Key Vault

az keyvault secret set \
  --vault-name "YourKeyVault" \
  --name "JiraApiToken" \
  --value "ATATT3xFfGF0..."

Step 2: Add "Get secret" action in Power Automate

Before the "Do until" loop, add:

Action	Get secret (Azure Key Vault)
Vault name	YourKeyVault
Secret name	JiraApiToken
Output variable	body('Get_secret')?['value']

Step 3: Reference in HTTP action

Property	Value
Authentication	Basic
Username	your.email@company.com
Password	@{body('Get_secret')?['value']}

Benefits:

• ✅ Centralized token management
• ✅ Easy rotation (update Key Vault, all flows auto-update)
• ✅ Access audit logs
• ✅ Fine-grained access control with Managed Identity
• ✅ Meets compliance requirements (SOC2, ISO 27001)

Only use this if you have:

• ❗ Mandatory compliance requirements (SOC2, ISO 27001, HIPAA)
• ❗ More than 10 flows sharing the same credentials
• ❗ Strict token rotation policies (every 30 days or less)
• ❗ Dedicated Azure infrastructure team

For most users: Level 1 is sufficient and easier to maintain.

---

What NOT to Do ❌

Never embed tokens as plaintext:

// ❌ DON'T: Hardcode in JSON body
{
  "jql": "filter = 12345",
  "apiToken": "ATATT3xFfGF0..."  // Exposed in flow definition!
}

// ❌ DON'T: Store in variables as plaintext
Initialize variable: varApiToken = "ATATT3xFfGF0..."

// ❌ DON'T: Manually construct Authorization header
Headers: {
  "Authorization": "Basic base64encodedtoken..."  // Visible in logs!
}

Why these are dangerous:

• Tokens appear in flow export files
• Visible in run history logs
• Can be extracted by anyone with flow read access

---

Additional Security Best Practices

2. Use service accounts

• Create a dedicated Jira service account for automation
  • Example: automation-bot@company.com
• Easier to audit and rotate credentials
• Prevents personal account lockout from affecting automation
• Clear separation between human and machine identities

3. Limit SharePoint permissions

• Grant write access only to specific document libraries
• Use least-privilege principles
• Create a dedicated SharePoint app registration if possible

4. Enable audit logging

• Monitor flow run history for anomalies
• Set up alerts for authentication failures
• Review Key Vault access logs monthly

5. Implement token rotation

• For Level 1: Rotate manually every 90 days
• For Level 2: Automate rotation with Azure Functions
• Document rotation procedures

6. Secure the flow itself

• Limit flow edit permissions to authorized users
• Use environment-specific flows (Dev/Test/Prod)
• Version control flow definitions in Git

---

Migration Path: From Basic to Enterprise

If you start with Level 1 and need to upgrade:

Step 1: Create Azure Key Vault

az keyvault create --name "CompanyJiraVault" --resource-group "RG-Automation"

Step 2: Move existing token to Key Vault

az keyvault secret set --vault-name "CompanyJiraVault" \
  --name "JiraApiToken" --value "<your-existing-token>"

Step 3: Update flow (no need to recreate)

• Add "Get secret" action before HTTP call
• Update HTTP Authentication password field to reference the secret
• Test with manual trigger
• Delete old hardcoded token

Downtime: Zero - can be done during maintenance window

Maintainability

1. Use descriptive variable names

   • varAllIssues is better than var1
   • varNextPageToken is self-documenting

2. Add comments to complex expressions

   • Use the "Rename" feature on actions
   • Add notes explaining business logic

3. Version control your flows

   • Export flow definitions regularly
   • Store in source control (Git)

---

Expected Results

After successful implementation, you will have:

• Automated CSV reports generated every 2 weeks
• All issues captured regardless of result count (handles 500+, 1000+, etc.)
• Structured data with all custom fields flattened
• SharePoint archive of historical reports
• Email notifications to stakeholders
• Zero manual effort required after initial setup

---

Summary

This solution demonstrates how Power Automate can integrate with Jira Cloud API to create fully automated, production-grade reporting workflows. By properly handling:

• API pagination with nextPageToken
• Variable management without self-references
• Nested JSON transformation to flat CSV
• SharePoint integration for archival
• Email delivery for distribution

You can build robust integrations that scale across enterprise environments with thousands of issues.

Key Takeaways

Best Practice	Why It Matters
Use API pagination tokens	Ensures all records are captured
Use "Append to array variable"	Avoids self-reference errors
Build Select mappings via GUI	Prevents JSON parsing errors
Keep maxResults = 100	Optimal performance and reliability
Never hardcode isLast	Ensures proper loop termination
Use filter IDs in JQL	More maintainable than complex queries

---

Next Steps

Ready to implement this solution? Here are some resources:

See It in Action

Visit the JSM Sales Performance Dashboard GitHub repository to see:

• Live dashboard examples showing how automated reports drive business insights
• KPI visualization techniques for sales and support teams
• Real-world implementation in a production environment
• End-to-end workflow from Jira issues to executive dashboards
• Performance metrics tracking and trend analysis

Further Reading

• Jira Cloud REST API Documentation

---

All technical patterns shown here are production-tested and sanitized for public sharing. Feel free to adapt this architecture to your organizational needs.