Streamlining Project Management with Jira and Google Sheets Integration

Project management is the success mantra for providing productivity at work pace today. jira and google sheets integration are among the two most effective tools to improve project management. By integrating these tools, you can streamline your reporting workflows and create weekly and monthly reports tailored to your needs.

In this article, we will show you how Jira integration with Google Sheets and Jira Query Language (JQL) can utterly change your reporting.

What is Jira Integration?

Jira integration is nothing but how we connect the Jira, which is a popular project management and issue tracking tool to other software applications or services. This integration promotes seamless data exchange between Jira and other tools to help teams streamline workflows, enhance efficiency, and increase collaboration across teams.

JIRA is a popular tool for tracking bugs, issues, and project management. Still, using Jira together with other tools will promote some additional functionalities like more detailed reporting, automated workflows, or better data mining.

Why Integrate Jira with Google Sheets?

One of the most popular tools used for project management, jira and google sheets can be an asset to any software development team for task tracking, bug tracking and project progress monitoring. Many teams prefer Jira reporting, but most find Google sheets more flexible to manipulate and visualize data. You can integrate Jira with Google Sheets

• Automated Reporting: Once linked, data can automatically be imported for you to schedule as per your requirements removing the need to enter manual data. No need to update anything constantly: This ensures that you always have the most current information at your fingertips.

• Customisation: Google sheets allows you to customise the data in any way you want. From filtering specific issue types, reviewing data over particular time intervals to creating custom-defined metrics, Google Sheets enable you to customize your reports exactly how they best suit your team.

• Collaboration Made Simple: Sharing reports in Google Sheets is extremely easy and collaboration with multiple users can happen simultaneously. Share the live and current reports with your team or stakeholders to receive immediate feedback and collaborative decisions regardless of where your team is located.

• Sophisticated Data Visualization: You can quickly convert your raw Jira data into visual reports using Google Sheets custom charting and graphing tools. Hence, it helps to provide an easily accessible and understandable data here among the project members.

Setting Up Jira and Google Sheets Integration

Jira and Google Sheets integration is a very simple process that could exponentially improve your project management experience. Through integration of these two tools, you can auto-generate reports, personalize data displays, and expedite collaboration. This is step by step process how to configure the integration

1. Install the Add-on

• First, you need to add the Jira Cloud for Google Sheets Add-on. Follow these steps:

• Open Google Sheets and go to Extensions menu in the top menu bar.

• Select Add-ons > Get add-ons.

• Search Jira Cloud for Google Sheets in Google Workspace Marketplace

• Click the add-on and Install.

2. Authorize the Add-on

Once you have installed the add-on you will need to authorise it with your jira account:

• After the add-on is installed, navigate to Extensions > Jira Cloud for Sheets > Open.

• This will bring up a new sidebar on the right side of the screen. Click Authorize.

• Step through the prompts to log into your Jira account and grant permission to the add-on. Grant it permissions to access your Jira data. 

3. Import Data:

With the integration configured, go ahead and import Jira data directly into your Google Sheets. There are plenty of other options for getting a data source, and it mostly depends on how low-level or high-level your data pull would be:

• JQL: Use Jira Query Language for advanced and custom filtering.

• Basic: Utilize a simple and guided interface for quick data retrieval.

• Filter: Select from pre-existing filters within your project.

• =Jira(): Use the =Jira() function directly in Google Sheets for real-time, automated data imports.

3.1 Using JQL:

You can customize the filters you want based on JQL (Jira Query Language). Return all issues for a project that are in status "In Progress". Here's how to do it:

• Using JQL (Jira Query Language) Filter Your Data You Required For instance to get all issues in progress for the project ABC, you can use:

• For example: filter project = "ABC" AND status IN ( "In Progress", "Code Review", or “Blocked” ) It returns only the issues created in last 30 days and knows if it has any of these property values with respect to issue:

project = ABC AND status in ("In Progress", "Code Review", "Blocked")

AND created >= -30d

AND (assignee in (Kiran, Arun) OR

(reporter in (Mbrown, Kwhite) AND priority = High) OR

(labels in ("urgent", "critical") AND duedate <= endOfWeek() AND NOT resolution = "Won't Fix"))

3.2 Using Basic:

• Open the Import Tool: Begin by opening the Google Sheets "Import" option.

• Select Basic Import: Choose the "Basic" import type for a simple, guided experience.

• Choose Your Project: Select the specific project from which you want to import data.

• Select Issue Types: Choose from options like Bug, Enhancement, Epic, Task, and Sub-task.

• Select Status: Choose status from the dropdown that is “Done”, “Cancelled”, “In Code Review”, “In Progress” or “On Hold”. You can also use a "Contains Text" option to make your selection, too.

• Order By: Select the order in which you want the data to be displayed from your reporting requirements.

• Import Data: Click "Get Issues Now" to import the filtered data into your sheet. This Basic option is ideal for those who prefer a straightforward approach without needing to write custom queries.

3.3 Using Filter:

If you have custom filters in Jira (e.g., issues resolved this week), then you would be able to import data through those filters as well:

• Click on Filters on the side bar

• Use a filter that is already configured inside your Jira project.

• Press Get Issues Now to stream the data from that filter into your Google Sheet.

• Once authorized, you can start importing data from Jira.

Use the filter of your choice as shown below:

• For example, choose a filter from your project as shown below. We chose Week Resolved then submit get issues now button.

3.4 Using =Jira():

• Enter the Formula: In a cell in your Google Sheet, enter =Jira().

• Define Parameters: Inside the parentheses, specify your query parameters, such as project name, issue type, and status. For instance, you might use =Jira("project = ABC AND status = 'In Progress'").

• Fetch and Display Data: You enter the data you need to retrieve, press Enter and it will show up in that cell (or selected cells) of your spreadsheet.

• Real-Time Updates: The data refreshes at real-time with whatever parameters you set and is especially useful for automated, fresh reporting in Google Sheets.

4. Fields:

• Use checkboxes to select fields (like Key, Resolved, Created, Time to resolution and Updated) as per requirement to include them in the report. This makes it quite customizable to extract the data extraction matching different reporting needs.

• Readable style for reports after sorting fields – records can be saved

• Custom fields help avoid redundancy in data so that the data can be analyzed and interpreted better.

5. Schedule Imports:

• One of the major advantages of this google sheets integration with jira is that you can schedule automatic data import and all your reports will be updated automatically without manual intervention.

• Open the Schedule tab in the sidebar

• Specify the frequency with which you want the data to refresh (hourly, daily, weekly).

• Select the expiry for scheduled imports (i.e. 12 months)

• Click on Save and your data will refresh automatically as per the defined schedule.

Data refresh can be customized as per our needs. Below, you can see the import is set to hourly and the expiration is set to 12 months.

 With your data imported into integrate jira and excel, you can now leverage its features to create customized reports by editing and analyzing as per our needs.

Here’s a visual representation of importing a Jira filter into Google Sheets. We can modify the imported data in Sheets to fit our requirements.

 Conclusion

Connecting Google Sheets with Jira is a great tool to expand the reporting for your project. Using JQL to filter the data and Google sheets to manipulate and visualize it, you can effectively build customized reports that give you different insights on your project. Then connecting jira to google sheets would help in reducing time along with better collaboration and decision-making based on data.