Super simple Google Docs integration with Jira Issues/Tickets

jiraRefreshTickets.js

// 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;
    }
  }
}

Hello,

Thanks for this code, it looks like its just what I need to create dashboards in Google Docs for our JIRA projects.

I am missing something though, is there additional code, I need to add to the spreadsheet. I keep getting getTicketSheet is not defined.

Tom

@tpackert getTicketSheet() would be a local function that gets the specific sheet you want to place these results on. if you only have one sheet in use, it could be replaced with SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

Hi and thank you for the code.

I always get an empty response and searching on Google I get lots of posts where most people reports that UrlFetchApp returns an empty response, at least when trying to get to Jira securely.

Is this code still working for you? Do you know what could be the problem?

Getting data from a non-secure web site is fine, but I can't from Jira.

Any help wil be much appreciated.

Miguel.

Thanks for this :)

Here's some pointers for others looking to use this in a google scripts/sheets:

Utilities.base64Encode is the function you can use for encoding the credentials as thus:
var encCred = Utilities.base64Encode("username@company.com:jirapassword");
Take note of the : between the username and password

As mentioned above you should specify a local function to , or you could point it to a specific tab in your spreadsheet, e.g. if you have a tab named "Jira Tickets"
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jira Tickets");

How do I define the JIRA project?

I started with your little snippet, and then i extended it to actual GAS Add-on.
Maybe peoples out there start contributing to improve the add-on even more.

Check it out, feedback welcome: https://github.com/ljay79/jira-tools/

I still face the unauthorised 401 error.
I tried the var encCred = Utilities.base64Encode("username@company.com:jirapassword");
Still same error.
Any help appreciated.

How to fetch a multi select custom field?