Google Sheets Apps Script单元格超链接无法定位单元格问题求助

Hey there, I see the issue with your hyperlink formula—it’s landing you on the right sheet but not zooming to the exact cell. That’s down to a couple of formatting quirks in your code. Let’s fix this step by step.

What was wrong with the original code? #

Your formula had two main issues:

1. You used & instead of plain &—that’s an HTML escape character, but we’re writing a Google Sheets formula, not HTML, so it breaks the URL structure.
2. The range parameter had messed-up quote nesting, which meant Sheets couldn’t parse the exact cell reference correctly.

Corrected Code #

Here’s the revised version that will properly jump to the target cell:

// Make sure these variables are defined in your script:
// const ss = SpreadsheetApp.getActiveSpreadsheet();
// const sheetname = ss.getSheetByName("YourTargetSheet"); // Or your target sheet object
// const Lrow = // Your target row number

// Get the cell you want to link to
const targetCell = sheet.getActiveCell();
const sheetId = ss.getActiveSheet().getSheetId();
const targetA1 = targetCell.getA1Notation();
const sheetName = targetCell.getSheet().getName();

// Build the correct HYPERLINK formula
const hyperlinkFormula = `=HYPERLINK("#gid=${sheetId}&range=${encodeURIComponent(`${sheetName}!${targetA1}`)}", "${targetA1}")`;

// Set the formula to your desired cell (column 9, row Lrow)
sheetname.getRange(Lrow, 9).setFormula(hyperlinkFormula);

Key Fixes Explained #

• encodeURIComponent(): This encodes the sheet name and cell reference to handle spaces, special characters, or weird sheet names (like "Q3 Sales!") that would break the URL otherwise.
• No HTML escapes: We use plain & instead of & because this is a spreadsheet formula, not web markup.
• Clean quote structure: Using template literals (backticks `) makes it way easier to build the formula without messing up nested quotes, which was a big source of your problem.
• Proper range format: The range parameter needs to be in SheetName!A1 format, and encoding ensures it’s parsed correctly by Sheets.

Quick Test Tip #

If you’re still having trouble, manually type a test formula in a cell to verify:

=HYPERLINK("#gid=123456&range=Sheet1!B5", "B5")

Replace 123456 with your sheet’s ID, Sheet1 with your sheet name, and B5 with your target cell. If this works, compare it to what your script generates—you’ll spot any formatting mismatches right away.

内容的提问来源于stack exchange，提问作者Oday Salim