Skip to content

Instantly share code, notes, and snippets.

@rtrcolin
Last active January 8, 2024 13:59
Show Gist options
  • Save rtrcolin/4455097 to your computer and use it in GitHub Desktop.
Save rtrcolin/4455097 to your computer and use it in GitHub Desktop.
Super simple Google Docs integration with Jira Issues/Tickets
// 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;
}
}
}
@PravinrajV
Copy link

I still face the unauthorised 401 error.
I tried the var encCred = Utilities.base64Encode("[email protected]:jirapassword");
Still same error.
Any help appreciated.

@PravinrajV
Copy link

How to fetch a multi select custom field?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment