TestRail & JIRA Automation with Apps Script

As much as we would love to have full end-to-end tests with CI/CD integration as QA Engineers, such things are not possible in all settings. Sometimes the project is shifting too quickly to make automation a reasonable solution. Sometimes the team is not equipped to make use of a complex automation framework.

No matter the reason, sometimes a 'low tech' automation solution is better suited for the situation. For such instances, in comes Google Apps Script.

Google Apps Script is a rapid application development platform that allows you to code right in a browser and has integration with Google products such as Sheets and Calendar. It can perform operations using data from these services, which can make creating test reports from Sheets a breeze.

Here is an overview of an automation solution I've used professionally to integrate TestRail and JIRA using data from the content team in Sheets.

1. Set up Apps Script authentication and parse Sheets data

var userProperties = PropertiesService.getScriptProperties();
var testRailUsername = userProperties.getProperty('TESTRAIL_USERNAME');
var testRailPassword = userProperties.getProperty('TESTRAIL_PASSWORD');
var jiraUsername = userProperties.getProperty('JIRA_USERNAME');
var jiraApiToken = userProperties.getProperty('JIRA_APITOKEN');
var login = Utilities.base64Encode(testRailUsername+":"+testRailPassword);

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sel = ss.getSelection();
var range = SpreadsheetApp.getActiveSpreadsheet().getActiveRangeList().getRanges();

2. Create TestRail test suite

function createTestplan() {
var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Basic ' + login
      },
    'payload' : JSON.stringify({
        'name': `YOUR NAME`,
        'description': 'YOUR DESCRIPTION',
        'milestone_id': milestone_id,
        'entries': entriesAr
    })
  };

  var response = UrlFetchApp.fetch('https://yoururl.testrail.com/index.php?/api/v2/add_plan/', options);
  var responseData = JSON.parse(response);
  var testplanURL = responseData.url;
  var testplanID = responseData.id;
  if(responseData.status=200){
    SpreadsheetApp.getActiveSpreadsheet().toast(`Testplan has been successfully updated.`);
  }

createParentTicket(milestone_id, testplanURL);
createSubtaskTickets(testplanURL);
updateTestSuiteDescription(testplanID, subtaskTicketID, parentTicketID)
}

3. Create JIRA Parent task ticket linked to test suite

function createParentTicket(milestone_id, testplanURL) {
var jiraCred = Utilities.base64Encode(jiraUsername + ':' + jiraApiToken);
var bodyData = {YOUR DATA}

var options = {
      'headers': {
          'Authorization': 'Basic ' + jiraCred
      },
      'method': 'post',
      'payload': JSON.stringify(bodyData),
      'muteHttpExceptions': true,
      'contentType': 'application/json;charset=UTF-8'
  };

  var response = UrlFetchApp.fetch(YOUR URL, options);
  var responseData = JSON.parse(response);
  parentTicketID = responseData.key;

  return parentTicketID;
}

4. Create JIRA Subtask tickets linked to parent ticket

function createSubtaskTickets(testplanURL) {
for (x = 0; x < num; x++) {
	bodyData[x] = {YOUR DATA};

    options[x] = {
      'headers': {
          'Authorization': 'Basic ' + jiraCred
      },
      'method': 'post',
      'payload': JSON.stringify(bodyData[x]),
      'muteHttpExceptions': true,
      'contentType': 'application/json;charset=UTF-8'
    };

    response[x] = UrlFetchApp.fetch(jiraUrl, options[x]);
    responseData[x] = JSON.parse(response[x]);
    subtaskTicketID[x] = responseData[x].key;

    SpreadsheetApp.getActiveSpreadsheet().toast(`Ticket(s) ${subtaskTicketID[x]} created.`);
  }
  
  return subtaskTicketID;
}
}

5. (Optional) Update Test suite with JIRA ticket information

function updateTestSuiteDescription(testplanDescript, testplanID, subtaskTicketID, parentTicketID) {
for (var i = 0; i < num; i++) {
    testplanDescript[i] += ` - Sub Task: https://yoururl.jira.com/browse/${subtaskTicketID[i]}`;
}
var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Basic ' + login
      },
    'payload' : JSON.stringify({
    		'suite_id': `${testplanID}`,
    		'description': `YOUR DESCRIPTION
Main Task: https://yoururl.jira.com/browse/${parentTicketID}
Sub Tasks: ${(testplanDescript.toString()).replaceAll(',', '\n')}`
	})
};

var response = UrlFetchApp.fetch(`https://yoururl.testrail.com/index.php?/api/v2/update_plan/${testplanID}`, options);
  var responseData = JSON.parse(response);
  if(responseData.status=200){
    SpreadsheetApp.getActiveSpreadsheet().toast(`Testplan has been successfully updated.`);
  }
}

Being able to make TestRail test runs and JIRA tickets straight from Google Sheets data is especially helpful in situations where the testing is being prepared for an external test team.

For daily or weekly manual tests and regular content updates this 'low-tech' automation solution can be spun up and taught to a QA team very quickly.