Add sheet name support to CELLREF formula

[Jython1415]

Description

Enhance the CELLREF formula to optionally include the sheet name in the cell reference output (e.g., 'Sheet1'!N6 instead of just N6).

Context

Follows up on #74, which introduced the basic CELLREF formula for converting cells to A1 notation.

Technical Feasibility

Challenge: Google Sheets has no native formula to get the current sheet name. The ADDRESS function accepts a sheet name as the 5th parameter, but obtaining it programmatically requires:

• Google Apps Script custom function (e.g., =sheetName())
• Manual input as a parameter

Approach: Accept an optional sheet_name parameter that users can provide manually or via a custom Apps Script function.

Research findings:

• Google Sheets has no native formula to get the current sheet name (unlike Excel's CELL function)
• The only workaround is Google Apps Script: function sheetName() { return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); }
• Custom functions are cached and don't auto-update when sheets are renamed

Related

• Depends on: Shorthand for getting a relative cell reference, given a value or range passed in #74
• Related to: Add hyperlink generation from cell references #77

References

• Get Sheet Name in Google Sheets
• Apps Script Solution

[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 has no native formula function to retrieve the current sheet name. This is a fundamental platform limitation that has remained unresolved since 2013.

Investigated Approaches:

• CELL("address"): Only returns cell reference like $A$1 without sheet name
• CELL("filename"): Returns #N/A error (works in Excel but not Google Sheets)
• ADDRESS(): Can format with sheet name, but requires manual input as 6th parameter
• Other metadata functions: FORMULATEXT(), INDIRECT(), IMPORTRANGE() provide no access to sheet names
• Recent updates (2024-2026): No new functions addressing this limitation

The Only Solution: Apps Script

The only way to retrieve sheet names is through Google Apps Script custom functions:

function SHEETNAME(dummyCell) {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

However, this is outside the scope of this repository, which focuses on named functions using LET and LAMBDA, not Apps Script extensions.

Recommendation

Keep CELLREF as-is (returning references like N6). Users who need full sheet-qualified references will need to implement custom Apps Script functions.

Sources

• How to Get the Sheet Name in Google Sheets
• Get current sheet name - Google Community (unresolved since 2013)
• CELL function documentation
• ADDRESS function documentation
• Google Sheets function list

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