JIRA Data Extract into Google Sheets through an API

function callJIRAPI() {

// first, clear the sheet contents

        var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

var range = sheet.getRange("A1:G100");

range.clearContent();

// create the auth token for the API request to JIRA

var pair = "firstname.lastname@mydomain.com:<My API Key from JIRA>"

var base64 = Utilities.base64Encode(pair)

var authHeader = "Basic " + base64

var url = "https://mydomain.atlassian.net/rest/api/3/search?jql=<JQL Query here>"

// execute the query and parse the result as JSON

var response = UrlFetchApp.fetch(url, { headers: { Authorization: authHeader, ContentType: 'application/json' } });

var dataSet = JSON.parse(response.getContentText());

// Create an array to store the results and push the column headers. These should match to the columns extracted below

var rows = []

rows.push(["JiraID","CreatedBy","AssignedTo","DueDate","Status","Priority"]);

// now loop through the results and populate the array

for (i = 0; i < dataSet.issues.length; i++) 

{

data = dataSet.issues[i];

// only include items that are not resolved or closed

if ((data.fields.status.name != "Resolved") && (data.fields.status.name != "Closed"))

{

  var assignedTo = "<Unassigned>"

  if (data.fields.assignee != null)

assignedTo = data.fields.assignee.displayName

  rows.push([data.key, 

  data.fields.creator.displayName,

  assignedTo,

  data.fields.duedate,

  data.fields.status.name,

  data.fields.priority.name

])

}

}

// add the results to the sheet

sheet.getRange(1,1,dataSet.issues.length + 1, rows[0].length).setValues(rows);

// loop through the 'due date' column and colour code the cells if they are late

var date = Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "yyyy-MM-dd HH:mm")

var range = sheet.getRange(2,6,dataSet.issues.length + 1);

var cellRange = range.getValues();

for(i = 0; i<cellRange.length-1; i++){

var dt = new Date(cellRange[i][0].toString());

var columnDate = Utilities.formatDate( dt, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "yyyy-MM-dd HH:mm");

if(columnDate < date)

{

sheet.getRange(i+2,6).setBackground("yellow");

sheet.getRange(i+2,6).setFontColor('blue');

}

}

}