0

Is there a way to make a custom function return the same output like 

=HYPERLINK(url, [link_label])

Basically, I am computing a URL inside the custom function and want to return a hyperlink with a specific link label. So far I can only return a link of the form.

function calLink() {
  return 'https://www.google.com/calendar/event?action=TEMPLATE [...]'
}

This puts a valid link into the spreadsheet but does not allow me to specify a label for that link.

Coxer
  • 1,694
  • 2
  • 26
  • 44

1 Answers1

0

Of course you can. Use Range.setFormula().

function addHyperlink() {
  var range = SpreadsheetApp.getActiveSheet().getRange(1, 1);
  var cell = range.getCell(1, 1);
  cell.setFormula('=HYPERLINK("www.google.com","google")');
}
DDD
  • 1,462
  • 15
  • 19
  • 1
    This gives me an error `Error You do not have permission to call setFormula` – Coxer Jan 19 '16 at 11:21
  • 1
    Yup. That's listed in the ["Common errors" section](https://developers.google.com/apps-script/troubleshooting#common_errors) of the Troubleshooting guide. Has also been [asked here](http://stackoverflow.com/questions/11421055/). I'll leave the exercise to the reader... – DDD Jan 19 '16 at 12:23