Last active
January 8, 2024 13:59
-
-
Save rtrcolin/4455097 to your computer and use it in GitHub Desktop.
Super simple Google Docs integration with Jira Issues/Tickets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// URL for Jira's REST API for issues | |
var getIssueURL = "https://[Your Jira host]/rest/api/2/issue/"; | |
// Personally I prefer the script to handle request failures, hence muteHTTPExceptions = true | |
var fetchArgs = { | |
contentType: "application/json", | |
headers: {"Authorization":"Basic [Your BASE64 Encoded user:pass]"}, | |
muteHttpExceptions : true | |
}; | |
/** | |
* Add a nice menu option for the users. | |
*/ | |
function onOpen() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var entries = [{ | |
name : "Refresh Ticket Data", | |
functionName : "refreshTickets" | |
}]; | |
sheet.addMenu("Jira", entries); | |
}; | |
/** | |
* Make a request to jira for all listed tickets, and update the spreadsheet | |
*/ | |
function refreshTickets(){ | |
// Pull the bits and pieces you need from the spreadsheet | |
var sheet = getTicketSheet(); | |
var rows = sheet.getDataRange(); | |
var numRows = rows.getNumRows(); | |
var values = rows.getValues(); | |
// Show the user a friendly message | |
Browser.msgBox("Jira Tickets","Updating " + numRows + " tickets",Browser.Buttons.OK); | |
for (var i = 1; i < numRows; i++) { | |
var row = values[i]; | |
var ri = i + 1; // Set the cell row index | |
var httpResponse = UrlFetchApp.fetch(getIssueURL + row[0], fetchArgs); | |
if (httpResponse) { | |
switch(httpResponse.getResponseCode()){ | |
case 200: | |
var data = JSON.parse(httpResponse.getContentText()); | |
// Check the data is valid and the Jira fields exist | |
if(data && data.fields) { | |
// Set some basic ticket data in your spreadsheet | |
rows.getCell(ri, 2).setValue(data.fields.issuetype.name); | |
rows.getCell(ri, 3).setValue(data.fields.reporter.displayName); | |
rows.getCell(ri, 4).setValue(data.fields.assignee.displayName); | |
rows.getCell(ri, 5).setValue(data.fields.summary); | |
} | |
else { | |
// Something funky is up with the JSON response. | |
resetRow(i,"Failed to retrive ticket data!"); | |
} | |
break; | |
case 404: | |
rows.getCell(ri, 5).setValue("This ticket does not exist"); | |
break; | |
default: | |
// Jira returns all errors that occured in an array (if using the application/json mime type) | |
var data = JSON.parse(httpResponse.getContentText()); | |
rows.getCell(ri, 5).setValue("Error: " + data.errorMessages.join(",")); | |
break; | |
} | |
} | |
else { | |
Browser.msgBox("Jira Error","Unable to make requests to Jira!", Browser.Buttons.OK); | |
break; | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
How to fetch a multi select custom field?