Objective: To demonstrate how to use Sheetgo to connect JIRA to Google Sheets, enabling automated data transfers and real-time updates for efficient project management and reporting. Seamlessly Integrate JIRA with Google Sheets and automate your Project Management with Sheetgo.
To know more about Jira data extraction, refer to this community post.
In this guide, we’ll show you how to connect JIRA data to Google Sheets using Sheetgo. By the end of this, you’ll be able to automate data transfers from JIRA to Google Sheets, making project management and reporting a breeze.
Let’s get started 🚀
Step 1: Write the Google Apps Script to Retrieve JIRA Data
- Open the Apps Script Editor:
- In your Google Sheet, go to
Extensions > Apps Script to open the editor. 🖥️
- Copy and Paste the Following Code:
- This script retrieves data from JIRA using its API and prepares it in a JSON format for our GET endpoint.
function doGet() {
const query = 'project=your_project_name'; // Replace 'your_project_name' with your actual JIRA project key
const result = main(query); // Call the main function to fetch JIRA data
Logger.log(result); // Log the JSON response for debugging
return ContentService
.createTextOutput(JSON.stringify(result)) // Return the result as JSON
.setMimeType(ContentService.MimeType.JSON);
}
function main(query) {
let startAt = 0;
const maxResults = 100; // Maximum issues the API allows per call
let total = 0;
const options = {
'method': 'get',
'contentType': 'application/json',
'headers': {
'Authorization': 'Bearer your_api_key_here' // Replace with your JIRA API key
},
'muteHttpExceptions': true
};
const data = [];
do {
const url = `https://yourdomain.atlassian.net/rest/api/3/search?maxResults=${maxResults}&startAt=${startAt}&jql=` + encodeURIComponent(query);
Logger.log("Jira API URL: " + url); // Log the JIRA API URL
const response = UrlFetchApp.fetch(url, options); // API call to JIRA
if (response.getResponseCode() === 200) {
Logger.log("API call successful");
const responseData = JSON.parse(response.getContentText());
total = responseData.total; // Total number of issues to fetch
responseData.issues.forEach(item => {
const issueKey = item.key;
const parentLink = item.fields.parent ? 'https://yourdomain.atlassian.net/browse/' + item.fields.parent.key : '';
const labels = item.fields.labels.join(", ");
const worklogUrl = 'https://yourdomain.atlassian.net/rest/api/3/issue/' + issueKey + '/worklog';
const worklogResponse = UrlFetchApp.fetch(worklogUrl, options);
const worklogs = [];
if (worklogResponse.getResponseCode() === 200) {
const worklogData = JSON.parse(worklogResponse.getContentText());
worklogData.worklogs.forEach(log => {
const author = log.author.displayName;
const timeSpent = log.timeSpent;
const comment = log.comment ? log.comment.content.map(contentPiece => {
return contentPiece.content ? contentPiece.content.map(innerPiece => innerPiece.text).join(' ') : '';
}).join(' ') : '';
const startedDate = new Date(log.started).toLocaleDateString();
worklogs.push(`${author}: ${timeSpent} (${comment}) [${startedDate}]`);
});
}
const bucketType = item.fields.customfield_10102 ? item.fields.customfield_10102.value : 'N/A';
data.push({
"Issue URL": `https://yourdomain.atlassian.atlassian.net/browse/${issueKey}`,
"Issue Key": issueKey,
"Summary": item.fields.summary,
"Assignee": item.fields.assignee ? item.fields.assignee.displayName : '',
"Status": item.fields.status.name,
"Parent Summary": item.fields.parent ? item.fields.parent.fields.summary : '',
"Parent Link": parentLink,
"Time Spent": item.fields.aggregatetimespent ? item.fields.aggregatetimespent : 0,
"Custom Field Value": item.fields.customfield_10103 ? item.fields.customfield_10103.value : '',
"Created Date": item.fields.created ? item.fields.created : '',
"Worklogs": worklogs.join('~ '),
"Bucket Type": bucketType,
"Labels": labels
});
});
startAt += responseData.issues.length; // Prepare for the next page of results
} else {
throw new Error('Error fetching data. Status code: ' + response.getResponseCode());
}
} while (startAt < total);
return { data: data }; // Return the collected data as JSON
}
Explanation of the Code:
doGet() Function: This is the entry point for the script when it’s called as a web app. It executes the main function and returns the data in JSON format.
main(query) Function: This function handles the logic of fetching data from JIRA, iterating through all pages of results if necessary, and formatting the data into a JSON object.
Customization: Replace placeholders like your_api_key_here and yourdomain.atlassian.net with your actual JIRA API key and domain. Also, replace your_project_name with actual JIRA project key.
3. Deploy the Script as a Web App:
Click on Deploy > New Deployment. Choose Web app as the type. 🌐
Set the access level to Anyone to avoid authorization issues. 🔓
Copy the Web App URL - this is your GET endpoint URL. 📋
It’ll look somewhat like this below example :
https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID_HERE/exec
4. Connect the Web App to Sheetgo:
Create a New Workflow in Sheetgo: Open Sheetgo, click on `+ New workflow`, and select `Blank workflow`. 🆕
Add a Connection:
Select a Destination:
- In the next step, choose the destination sheet in Google Sheets where you want the data to be saved. 📄
Save and Run the Workflow:
That’s it! You’ve successfully connected JIRA to Google Sheets using Sheetgo. Now you can automate data transfers and keep your project management data up-to-date in real-time. This integration will help you streamline your workflows and improve efficiency.
Happy automating! 🚀
