Custom Google Spreadsheet Function Returning a Hyperlink or an Existing Formula

google-apps-script, google-sheets

Solution

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;
}

Problem

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?

Original source