Jira Cloud for Google Sheets - need list of all projects meeting filter with any or none

[Jennie Neusch]

I'm trying to automate data from Jira into google sheets for all teams, whether they have data or not. For example, we have about 25 teams and I want to pull the data for all open customer escapes using Jira Cloud for Google sheets and set up a schedule. And then I want to be able to import that data to a heatmap that we use weekly. 

The problem that I'm running into is that it only returns the teams that have a value and I want to show the 0 counts for the teams that don't return any. I don't think that I can return that list with a JQL which would be the easiest route. 

I'm guessing that I can do conditional formatting to enter 0 if none match, and the value for the team if one was returned but I'm stuck on how to do that. 

Any suggestions out there! 

Jennie

[Mikel Garcia Bartolome · Accepted solution]

To automate the process of pulling data from Jira into Google Sheets and displaying zero counts for teams without data, you can follow these steps:

Step 1: Import Jira Data into Google Sheets

1. Install the **Jira Cloud for Google Sheets** add-on from the G Suite Marketplace.
2. Use the add-on to create a new import configuration:
   1. Set up your JQL query to fetch open customer escapes.
   2. Schedule the import to run regularly (e.g., daily, hourly).

Step 2: Write a Google Apps Script to Process the Data

• Create a complete list of teams
  • Maintain a list of all teams in a separate sheet/tab in Google Sheets.
• Write the script

function processJiraData() {
const sheetName = "JiraData"; // Name of the sheet with Jira data
const teamsSheetName = "Teams"; // Name of the sheet with the complete list of teams
const outputSheetName = "ProcessedData"; // Name of the output sheet

const ss = SpreadsheetApp.getActiveSpreadsheet();
const jiraSheet = ss.getSheetByName(sheetName);
const teamsSheet = ss.getSheetByName(teamsSheetName);
const outputSheet = ss.getSheetByName(outputSheetName);

// Get the list of teams
const teamsData = teamsSheet.getRange("A2:A").getValues().flat().filter(String);

// Get the Jira data
const jiraData = jiraSheet.getDataRange().getValues();
const header = jiraData.shift();

// Find the index of the team column
const teamColumnIndex = header.indexOf("Team");

// Create a map to count issues per team
const teamCounts = {};
for (let team of teamsData) {
teamCounts[team] = 0; // Initialize all teams with zero
}

// Count issues per team
jiraData.forEach(row => {
const team = row[teamColumnIndex];
if (teamCounts.hasOwnProperty(team)) {
teamCounts[team]++;
}
});

// Prepare data for output
const outputData = [["Team", "Open Issues"]];
for (let team in teamCounts) {
outputData.push([team, teamCounts[team]]);
}

// Clear the output sheet and write the new data
outputSheet.clear();
outputSheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
}

function setupTrigger() {
// Set up a daily trigger to update the data
ScriptApp.newTrigger('processJiraData')
.timeBased()
.everyDays(1)
.atHour(1)
.create();
}

• Run the script:
  • Open the script editor in Google Sheets (Extensions > Apps Script).
  • Copy and paste the above script.
  • Save and run the `setupTrigger` function to set up a daily trigger.

Step 3: Create the Heatmap

1. In your Google Sheets, use conditional formatting to create a heatmap based on the data in the `ProcessedData` sheet.
2. You can use the built-in conditional formatting options in Google Sheets to color the cells based on the number of open issues.

Explanation of the Script

• processJiraData: This function reads the data from the Jira data sheet and the list of teams, counts the number of issues per team, and writes the results to an output sheet.
• setupTrigger: This function sets up a daily trigger to run the `processJiraData` function, ensuring your data is updated automatically.

By following these steps, maybe changing some things, you should be able to automate the data import from Jira, include teams with zero counts, and visualize the data using a heatmap in Google Sheets.

[Jennie Neusch]

Thank you, Mikel! 

This was the key part here:

• Create a complete list of teams
  • Maintain a list of all teams in a separate sheet/tab in Google Sheets.

Once that was in place, I was able to use the =countif function and then set it up for each team. 

Thank you so much for your reply!

Jennie

[Josh_Unito]

@Jennie Neusch If you're looking for an on-going solution that doesn't require script maintenance or updating, Unito has a no-code integration for Google Sheets and Jira that can be set up in 15 minutes to keep issues and rows in sync bi-directionally. 

Each flow can populate a new sheet with issues from specific projects and check for manual changes in real-time to keep your sheet and Jira project up-to-date automatically.

If you have 25 teams, you could create one flow, then duplicate it 25 times for each unique project.

The nice thing about a solution like that, is you don't have to re-create the same code or configurations each time you want something to change since Unito is set up through a visual flow builder.