Inserting Hyperlinks in the new Google Sheets with App Script

Question

In the current version of Google Sheet, to insert a hyperlink in to a cell, you would do this

sheet.getRange(row, selectedColumn).setValue('=hyperlink("' + ontologyObject.accession + '";"' + ontologyObjectTerm + '")')

In the new version of Google Sheets, the hyperlink insertion is different, you do this.

sheet.getRange(row, selectedColumn).setValue('=HYPERLINK("' + ontologyObject.accession + '","' + ontologyObjectTerm + '")')

However, I have a problem in that the inserted value in the spreadsheet is not what I expect. In this image showing the inserted link, there is a ' inserted before the =HYPERLINK - I've no idea where this is coming from. Any ideas?

Answer 1

When setting formulas to a Sheet range you should always use setFormula (or its variants) instead of setValue. Even though some formulas may work when using setValue, there's some quirks as you saw.