Inserting Hyperlinks in the new Google Sheets with App Script

7
votes

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?

1
google-apps-script
Have you tried setFormula instead of setValue? – Henrique G. Abreu
That worked. I would not have thought of that. – Eamonn
Ok, I'll make this a proper answer just so this entry is marked as answered appropriately. – Henrique G. Abreu
Sorry it took me a few days to mark this as answered. I'm currently on holiday. Thanks again! – Eamonn

1 Answers

13
votes

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.