Using Google Apps Script to have JIRA API pull in issue ticket field values to Google Sheet

[Jeff A]

I wanted to automate a Google Sheet that I have been creating manually for a weekly meeting which shows the issues in a project that have not been updated in the last 15 days. When running the script, but nothing writes to the Google Sheet despite no error showing up. What I did note was that after running a "console.log(response.getContentText())" before the IF, I see that my query is returning a Cloudflare error

Is there something improper in my construction and/or use of the JIRA API to cause this?

function getJiraIssues() {
  var username = "<my_jira_username>";
  var apiToken = "<my_jira_password>";
  var jqlQuery = "project=xyz AND issuetype = Bug)";

  var sheet = SpreadsheetApp.getActiveSheet();
  var headers = sheet.getRange(1, 1, 1, 8).getValues()[0];

  var options = {
    'method': 'get',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Basic ' + Utilities.base64Encode(username + ':' + apiToken)
    },
    'muteHttpExceptions': true
  };

  var url = 'https://<my_jira_instance>.jira.com/rest/api/2/search?jql=' + encodeURIComponent(jqlQuery);

  var response = UrlFetchApp.fetch(url, options);

  if (response.getResponseCode() === 200) {
    var data = JSON.parse(response.getContentText());

    var issues = data.issues.map(function(issue) {
      var issueData = {};

      issueData['Issue Summary'] = issue.fields.summary;
      issueData['Assignee'] = issue.fields.assignee ? issue.fields.assignee.displayName : '';
      issueData['Issue Key'] = issue.key;
      issueData['Issue Type'] = issue.fields.issuetype.name;
      issueData['Component/s'] = issue.fields.components.map(function(component) {
        return component.name;
      }).join(', ');
      issueData['Status'] = issue.fields.status.name;
      issueData['Created'] = issue.fields.created;
      issueData['Updated'] = issue.fields.updated;

      return issueData;
    });

    var row = sheet.getLastRow() + 1;
    var numRows = issues.length;
    var numCols = headers.length;

    sheet.getRange(row, 1, numRows, numCols).setValues(issues.map(function(issue) {
      return headers.map(function(header) {
        return issue[header];
      });
    }));
  }
}

[David Ashlock]

@Jeff A did you ever figure out the solution to this?  I am attempting to do the same thing and wondered if I could use your code as a starting point.

[Jeff A]

Hey @David Ashlock 

Feel free to use the code. Maybe you can get it to work where I gave up. I am pretty sure my organization has a Cloudflare restriction preventing the two platforms from talking. I made an IT ticket that bounced around for a month and then moved on to other projects. 

Remembered it a few months ago and saw someone did end up commenting on the ticket, but ServiceNow never sent me a notification. Their response didn't indicate they understood the requirements. I just let it go, but it looked like it should work. 

[Oday Rafeh]

Hi @Jeff A

It seems that the JQL query string you're using has an extra closing parenthesis at the end, which is causing the Cloudflare error.

Please try to use this : 

function getJiraIssues() {
var username = "<my_jira_username>";
var apiToken = "<my_jira_password>";
var jqlQuery = "project=xyz AND issuetype=Bug";

var sheet = SpreadsheetApp.getActiveSheet();
var headers = sheet.getRange(1, 1, 1, 8).getValues()[0];

var options = {
'method': 'get',
'contentType': 'application/json',
'headers': {
'Authorization': 'Basic ' + Utilities.base64Encode(username + ':' + apiToken)
},
'muteHttpExceptions': true
};

var url = 'https://<my_jira_instance>.jira.com/rest/api/2/search?jql=' + encodeURIComponent(jqlQuery);

var response = UrlFetchApp.fetch(url, options);

if (response.getResponseCode() === 200) {
var data = JSON.parse(response.getContentText());

var issues = data.issues.map(function(issue) {
var issueData = {};

issueData['Issue Summary'] = issue.fields.summary;
issueData['Assignee'] = issue.fields.assignee ? issue.fields.assignee.displayName : '';
issueData['Issue Key'] = issue.key;
issueData['Issue Type'] = issue.fields.issuetype.name;
issueData['Component/s'] = issue.fields.components.map(function(component) {
return component.name;
}).join(', ');
issueData['Status'] = issue.fields.status.name;
issueData['Created'] = issue.fields.created;
issueData['Updated'] = issue.fields.updated;

return issueData;
});

var row = sheet.getLastRow() + 1;
var numRows = issues.length;
var numCols = headers.length;

sheet.getRange(row, 1, numRows, numCols).setValues(issues.map(function(issue) {
return headers.map(function(header) {
return issue[header];
});
}));
}
}

the corrected query string is

var jqlQuery = "project=xyz AND issuetype=Bug";

[Jeff A]

That's a good catch, thank you very much. Unfortunately, I am still getting the same Cloudflare Error even after correcting it.