Custom Google Spreadsheet Function Returning a Hyperlink or an Existing Formula

Question

I am currently entering hyperlinks in a Google Spreadsheet in the form:

=HYPERLINK("http://jira.com/browse/ISSUE-12345","ISSUE-12345")

I am duplicating "ISSUE-12345" each time. I would like to create a function JIRA(12345) which gives the same result as the above hyperlink. Is there a way to return a hyperlink in a script, such that something like

function JIRA(jiraNum) {
  // Returns JIRA hyperlink
  var link = ("http://jira.com/browse/ISSUE-"+jiraNum,"ISSUE-"+jiraNum);
  return link;
}

would work?

Answer 1

You can use rich text builder:

function onEdit(e){
      var jiraIDColumnID = 1;
      var jiraIDfirstRow = 5;
      var jiraBaseURL = "https://jira.tttttt.com/";
      if (e.value != null && 
          e.value.trim().length != 0 &&
          e.range.getColumn() == jiraIDColumnID && 
          e.range.getRow() >= jiraIDfirstRow ) 
      {
        var richValue = SpreadsheetApp.newRichTextValue()
          .setText(e.value)
          .setLinkUrl(jiraBaseURL + "browse/" + e.value)
          .build();
        e.range.setRichTextValue(richValue);
      } 
    }

Answer 2

You can alleviate the need to setForumla and have to deal with permissions, by setting the the cell value as follows:

=HYPERLINK(getJiraTicketLink(12345), getJiraTicketLabel(12345))

where 12345 of course can be a (hidden column-) neighboring cell instead of a hardcoded string.

Then you create two simple functions akin to the below:

var JIRA_BASE_URL = "http://jira.com/";
var JIRA_PROJECT_PREFIX = "ISSUE-";

function getJiraTicketLink(jiraNum) {
  return JIRA_BASE_URL + "browse/" + JIRA_PROJECT_PREFIX + jiraNum;
}

function getJiraTicketLabel(jiraNum) {
  return JIRA_PROJECT_PREFIX + jiraNum;
}