Install this Apps Script code into your default Code.gs or create a new file to house it, and have Jira ticket numbers in your Google Sheets automatically turn into hyperlinks that will open the relevant ticket. Only works in cells where the ticket reference is the only content in the cell.

JiraTicketsIssueHyperlinker.gs

// Jira Tickets Issue Hyperlinker
// Created by Sohail Ahmed 
// Created on September 29, 2023
// Developed iteratively with the aid of OpenAI's GPT-4


// Instructions:
// PREFIXES: Define the prefixes for the Jira namespaces you might include in this file that need hyperlinking.
// SUBDOMAIN: Change this to the desired subdomain / Atlassian account token.
var PREFIXES = ['MYAPP', 'OTHERAPP']; 
var SUBDOMAIN = 'acme';  

// ------ CORE FUNCTIONS -----

function buildRegex(prefix) {
  return new RegExp(prefix + '-\\d+', 'g');
}

function buildUrl(match) {
  return "https://" + SUBDOMAIN + ".atlassian.net/browse/" + match;
}

function hyperlinkExistingMatches() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  // Loop through each prefix
  PREFIXES.forEach(function(prefix) {
    var re = buildRegex(prefix);
    
    for (var row = 0; row < values.length; row++) {
      for (var col = 0; col < values[row].length; col++) {
        var cellValue = values[row][col];
        
        // Check if the cellValue is of type string before proceeding
        if (typeof cellValue === 'string') {
          var matches = cellValue.match(re);
          
          if (matches && matches.length > 0) {
            var newTextValue = cellValue;
            
            for (var i = 0; i < matches.length; i++) {
              var match = matches[i];
              var url = buildUrl(match);
              var linkTag = '=HYPERLINK("' + url + '","' + match + '")';
              newTextValue = newTextValue.replace(match, linkTag);
            }

            sheet.getRange(row + 1, col + 1).setValue(newTextValue);
          }
        }
      }
    }
  });
}

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var cellValue = range.getValue().trim(); // Trim the cell value
  
  // Loop through each prefix
  PREFIXES.forEach(function(prefix) {
    var re = buildRegex(prefix);

    // Check if the cellValue is of type string before proceeding
    if (typeof cellValue === 'string') {
      var matches = cellValue.match(re);

      if (matches && matches.length === 1 && matches[0].length === cellValue.length) {
        var match = matches[0];
        var url = buildUrl(match);
        var linkTag = '=HYPERLINK("' + url + '","' + match + '")';
        range.setValue(linkTag);
      }
    }
  });
}