I’m having trouble with Google Apps Script when trying to add hyperlinks to cells. My code looks like this:
spreadsheet.getRange(currentRow, targetCol).setValue('=HYPERLINK("' + dataObject.url + '","' + displayText + '")')
The problem is that when I run this script, the cell shows an apostrophe before the HYPERLINK formula. So instead of getting a clickable link, I see something like '=HYPERLINK("http://example.com","Click Here") in the cell.
I expected it to create a proper hyperlink but instead it’s treating the formula as text. The apostrophe appears automatically and I can’t figure out why. Has anyone encountered this issue before? I’m using the updated Google Sheets and this worked fine in older versions.
i feel u! i had this too, its super annoying lol. switched to setFormula() like ZoeStar suggested and it fixed it right away. no more apostrophes and hyperlinks work great now!
This happens because setValue() treats your input as text, not a formula. When you pass a string starting with =, Google Sheets automatically adds an apostrophe to prevent formula execution. Just use setFormula() instead:
spreadsheet.getRange(currentRow, targetCol).setFormula('=HYPERLINK("' + dataObject.url + '","' + displayText + '")')
I’ve hit this exact issue updating old scripts. setFormula() tells Sheets you want an actual formula, so it skips the apostrophe and creates the clickable hyperlink you’re after.
You could use setFormula() like ZoeStar said, but there’s a cleaner way that avoids Apps Script quirks entirely.
I’ve been using Latenode for Google Sheets stuff like this. Instead of fighting setValue vs setFormula differences, you set up an automated workflow that handles hyperlink creation properly - no apostrophe issues.
Latenode connects to Google Sheets natively and gets formula insertion right every time. Trigger it from webhooks, schedules, or other apps. Way more reliable than debugging Apps Script edge cases.
Switched our team’s sheet automation to Latenode after hitting the same formula formatting problems. Now hyperlinks just work without worrying about Google’s text vs formula detection.
Check it out: https://latenode.com
Had this exact same issue six months ago updating a client tracking spreadsheet. The apostrophe thing totally blindsided me - my old scripts worked fine until I moved to a new project. Turns out Google changed how setValue() handles formula strings in recent updates. Besides switching to setFormula(), you’ll want to validate your URL strings first. Special characters can break the formula even when it’s set correctly. I started wrapping my URLs with encodeURIComponent() just to be safe. setFormula() fixes the apostrophe problem, but checking your data formatting upfront saves you other headaches later.
The setFormula() approach everyone mentioned is spot on, but here’s something I learned migrating old Apps Script projects. If you’re batch processing multiple hyperlinks, use setFormulas() with a 2D array instead of calling setFormula() on each cell individually. I had about 500 hyperlinks and the individual calls kept timing out. I built an array where formula cells got the HYPERLINK string and empty cells stayed null, then applied everything at once with setFormulas(). Cut my execution time from 45 seconds to under 3 seconds. Kills the apostrophe issue and massively speeds up larger datasets.