Use Google Apps Script to create hyperlink using cell text

0
votes

I have a google sheet that has a column of values that are Jira ticket names. I want to create a script insertJiraLink that accepts a cell reference and outputs a hyperlink where the text is the ticket name and the link goes to the actual Jira ticket.

I can create a jira link easy enough. But I cannot figure out how to return a hyperlink with custom text.

// returns a full jira hyperlink
function jiraLinks(reference) {
  return "https://jira.fanatics.com/browse/" + reference;
}

// returns text that reads "=HYPERLINK(etc etc)"
function jiraHyperlinks(reference) {
  return '=HYPERLINK("https://jira.fanatics.com/browse/' 
    + reference 
    + '", "' 
    + reference 
    + '")';
}
1
google-apps-scriptgoogle-sheetshyperlink
Is there anything that I can do for your question? If my answer was not useful for your situation. I have to apologize and modify it. If you can cooperate to resolve your issue, I'm glad. I would like to think of about the solution. - Tanaike
@Tanaike I tried your solution and it had the same result as my jiraHyperLinks function above. It prints the text "=HYPERLINK(etc etc)" into the cell. On further research it seems like this is something Google does not allow - I found examples of inserting formulas into other cells, but not converting content into a formula and using the result. - elemjay19
Thank you for replying. I apologize for the inconvenience. In my environment, I could confirm that the script worked. But from your replying, I modified the script a little. Could you please confirm it? If that was not the direct solution of your current issue, can you provide the sample Spreadsheet and the flow you did for replicating the issue? By this, I would like to confirm it. By the way, if there is the function of jiraHyperlinks() in the script editor, please modify the function name. - Tanaike

1 Answers

1
votes
  • You want to put a formula using the custom function.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • Unfortunately, the custom function cannot put the formula. I think that this is the specification of Google side. So in your case, it is required to use a workaround.
  • As a workaround, I would like to propose to put the formula using the OnEdit event trigger.
    • In this case, when =jiraHyperlinks(reference) is put to a cell, the value of cell is converted to the formula by the OnEdit event trigger.

Sample script:

function onEdit(e) {
  var value = e.value.split("\"");
  if (/^=jiraHyperlinks\(/.test(value[0])) {
    var reference = value[1];
    e.range.setFormula('=HYPERLINK("https://jira.fanatics.com/browse/' + reference + '", "' + reference + '")');
  }
}

Usage:

  • In order to run the above script, after the script is copied and pasted to the script editor and save it, please put the formula of =jiraHyperlinks("sample") to a cell. By this, =jiraHyperlinks("sample text") is converted to =HYPERLINK("https://jira.fanatics.com/browse/sample", "sample").

Note:

  • This is a simple sample script for testing this workaround. The script might be required to be modified for the situation. Please be careful this.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Edit:

  • In this sample script, the formula is directly retrieved from the edited range without using e.value.

Sample script:

function onEdit(e) {
  var range = e.range;
  var value = range.getFormula().split("\"");
  if (/^=jiraHyperlinks\(/.test(value[0])) {
    var reference = value[1];
    range.setFormula('=HYPERLINK("https://jira.fanatics.com/browse/' + reference + '", "' + reference + '")');
  }
}