Add hyperlink generation from cell references

[Jython1415]

Description

Create a formula to generate clickable hyperlinks from cell references in the format:

https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit#gid=[SHEET_ID]&range=[CELL_REF]

Context

Follows up on #74, which introduced the CELLREF formula. This would enable creating dynamic links to specific cells within the spreadsheet.

Technical Feasibility

Challenge: Google Sheets formulas cannot access:

• Spreadsheet ID (the long string in the URL after /d/)
• Sheet GID (the numeric ID after gid= in URLs)

Limitation: Pure formula implementation is not possible. Only achievable via:

• Google Apps Script custom functions
• Manual parameter input (requires users to provide IDs)

Approach: If implemented, would need to accept spreadsheet_id and sheet_gid as parameters, which limits practical usability.

Research findings:

• No native formula can retrieve the spreadsheet ID or sheet GID programmatically
• These values are only available via Google Apps Script using SpreadsheetApp.getActiveSpreadsheet().getId() and getSheetId()
• Pure formula implementation would require users to manually provide these IDs as parameters, limiting usability

Related

• Depends on: Shorthand for getting a relative cell reference, given a value or range passed in #74
• Related to: Add sheet name support to CELLREF formula #76

References

• Get Sheet ID Guide
• Apps Script Sheet Operations

[Jython1415]

Research Conclusion: Not Possible with Pure Formulas

After thorough research, this feature is impossible to implement within the constraints of Google Sheets' native formula system.

Findings

Google Sheets provides no native formula functions to retrieve:

• The spreadsheet ID
• The sheet GID (internal sheet identifier)

These are metadata values stored at the API level, completely inaccessible to the formula evaluation engine.

Investigated Approaches:

• Metadata functions: CELL(), INFO(), FORMULATEXT() provide no access to IDs
• IMPORTRANGE self-references: Requires ID as input, cannot extract it
• URL parsing: URLs aren't accessible to formula functions
• Error messages/circular references: No exploitable metadata
• Recent updates (2024-2026): No new functions for metadata retrieval

The Only Solutions

1. Manual Extraction from URL

Users must copy from the browser address bar:
https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit#gid=[SHEET_GID]

2. Apps Script

function SPREADSHEETID() {
  return SpreadsheetApp.getActiveSpreadsheet().getId();
}

function SHEETGID() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
}

Both approaches violate the project constraints:

• Manual extraction requires user input beyond cell references
• Apps Script is outside repository scope (LET/LAMBDA only)

Recommendation

This feature cannot be implemented as a pure formula. Users needing this functionality must use Apps Script custom functions or manually extract IDs from the spreadsheet URL.

Sources

• Get spreadsheet ID and sheet GID with Apps Script
• Google Sheets function list
• IMPORTRANGE documentation

Closing as: Won't Fix - Google Sheets Platform Limitation