CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetshyperlink
elemjay19
elemjay19

Reputation: 1166

Use Google Apps Script to create hyperlink using cell text

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 
    + '")';
}

Upvotes: 1

Views: 1828

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • 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 + '")');
  }
}

Upvotes: 1

Related Questions