Connecting MS Excel Online with Jira Using Office Scripts

Integrating tools like Microsoft Excel Online and Jira can streamline workflows, eliminate manual data entry, and improve productivity. With Office Scripts in Excel Online, you can automate tasks and connect your Excel data to Jira, enabling seamless synchronization and management of project data. Here’s a step-by-step guide to achieving this integration.

Why Integrate Excel Online with Jira?

1. Enhanced Data Tracking: Sync project updates, tasks, or bug reports between Jira and Excel for real-time insights.
2. Automation Benefits: Reduce manual effort by automating repetitive tasks like updating issue statuses or creating new tasks.
3. Centralized Reporting: Use Excel’s analytical capabilities to generate insightful reports from Jira data.

Prerequisites

1. Microsoft 365 Subscription: Access to Excel Online with Office Scripts enabled.
2. Jira Account: With necessary permissions to access the REST API.
3. Basic Knowledge of JavaScript/TypeScript: To write and modify Office Scripts.
4. Personal Access Token in Jira: Required for secure API access.

I use Firefox ESR for all my web need. When I tried office script directly accessing Jira, I faced Cross-Origin Resource Sharing [cors] issue. Then I thought of adjusting browser settings but took different route because of security reasons.

I added cors-anywehre proxy. But it does not support adding credentials required for Jira. So added small application that pulls data from Jira and is supplied to ExcelWeb for representation.

Setting up cors-anywhere:
1. Installation:
Installation is easy process. Here is the command to do it

$ npm install cors-anywhere

2. Then add below script:

// Listen on a specific host via the HOST environment variable
var host = process.env.HOST || '0.0.0.0';
// Listen on a specific port via the PORT environment variable
var port = process.env.PORT || 8080;
var cors_proxy = require('cors-anywhere');
cors_proxy.createServer({
 originWhitelist: [], // Allow all origins
 removeHeaders: ['cookie', 'cookie2']
}).listen(port, host, function() {
 console.log('Running CORS Anywhere on ' + host + ':' + port);
});

and save it with name ‘proxy.js’

Here is sample flask app that pulls data from Jira

from flask import Flask, request, jsonify
import requests

app = Flask(__name__)

# Jira credentials and URL
JIRA_BASE_URL = "https://your-jira-instance.atlassian.net"  # Replace with your Jira instance URL
JIRA_USERNAME = "your-email@example.com"                    # Replace with your Jira username/email
JIRA_API_TOKEN = "your-api-token"                           # Replace with your Jira API token


@app.route('/get_filter', methods=['GET'])
def get_filter():
    try:
        # Get filter ID from query parameter
        filter_id = request.args.get('filterId')
        if not filter_id:
            return jsonify({"error": "filterId is required"}), 400

        # Jira API endpoint to get filter results
        #filter_url = f"{JIRA_BASE_URL}/rest/api/3/issue/AUTO-4998"
        filter_url = f"{JIRA_BASE_URL}/rest/api/3/filter/{filter_id}"
        issues_url = f"{JIRA_BASE_URL}/rest/api/3/search"

        # Get filter details
        auth = (JIRA_USERNAME, JIRA_API_TOKEN)
        filter_response = requests.get(filter_url, auth=auth)
        if filter_response.status_code != 200:
            return jsonify({"error": "Failed to fetch filter details", "details": filter_response.text}), filter_response.status_code

        filter_data = filter_response.json()
        jql_query = filter_data.get('jql', '')

        # Fetch issues using the JQL from the filter
        issues_response = requests.get(issues_url, auth=auth, params={"jql": jql_query})
        if issues_response.status_code != 200:
            return jsonify({"error": "Failed to fetch issues", "details": issues_response.text}), issues_response.status_code
        issues_data = issues_response.json()

        # Return the issues in JSON format
        return jsonify(issues_data["issues"])

    except Exception as e:
        return jsonify({"error": "An error occurred", "details": str(e)}), 500


if __name__ == '__main__':
    app.run(debug=True)

To run it use

$ python app.py

command

Now open ExcelWeb and select ‘Automate’ menu. Then click on New script. Add the below code in the code editor

async function main(workbook: ExcelScript.Workbook) {
 // Jira API endpoint
 const jiraUrl = `http://localhost:8010/http://localhost:5000/get_filter?filterId=33045`;
 // Fetch data from Jira
 const response = await fetch(jiraUrl);
 response.text().then(function (text) {
    console.log(text);
 });
}

When you run this code, it will print entire content of filter used.
It is just a demo code to that show ExcelWeb integration with Jira or any webpage using proxy.