Add HYPERLINK Support

[thilgen]

Description

HYPERLINK is a formula provided by Google Sheets (documentation); MS Excel (documentation); and is included in the Open Document Format for Office Applications (documentation)

The syntax (copied from Google Sheets Documentation) is

HYPERLINK(url, [link_label])

url         The full URL of the link location enclosed
            in quotation marks, or a reference to a 
            cell containing such a URL.

link_label  [OPTIONAL: url by default] - The text to 
            display in the cell as the link, enclosed in
            quotation marks, or a reference to a cell 
            containing such a label.

Platform	Results
Google Sheets
MS Excel

Design and Prototype

• The nature of the HYPERLINK formula is unique in that not only does it transform cell inputs into a resulting VALUE, but when present (and is the root expression on a Cell) - it will change the appearance and formatting to follow the established UI patterns for hyperlinks per that platform's settings (commonly this is: blue text color, underlined text, mouse-over-cursor becomes a finger-pointer, accepts a mouse-click and navigation event)
• This poses an interesting challenge for HyperFormula - in that HyperFormula is a "headless" technology with no built-in UI

HyperFormula Changes (#1216)

• Add a new FunctionPlugin called HYPERLINK
  • This handles the parsing of link_label and url; and will set link_label to url if link_label is omitted
  • When this formula is processed - it stores the url on the ProcedurAst that represents the cells vertex
  • This is all the code that is necessary for HyperFormula to parse HYPERLINK formulas and populate the value of the cell with the display value
  • TBD on if the url should be pattern-matched against a valid uri
• Add and export a new getCellHyperLink() top-level function to HyperFormula
  • This function is a sibling to other top-level functions like hf.getCellFormula() and hf.getCellSerialized
  • The hosting application is expected to call this function whenever it receives the onDataModifed hook notification - and if a url is returned it should properly format the UI to align with expected design as a hyperlink
  • This function will access the cell from the dependencyGraph; determine if the root expression is a HYPERLINK formula; and return the previously stored url value from the `ProcedureAst'
• Prototype and Unit Tests: Add support for HYPERLINK (hyperformula) #1216

Handsontable Changes (handsontable/handsontable#10314)

• These changes would need to be implemented by any layer that is providing UI for HyperFormula
• Created a new HyperlinkRenderer
  • This renderer is only meant to be used internally and would not be exposed for use in the columns config options or the exported namespace where it could be inadvertently applied to a cell by an application hosting Handsontable
  • This renderer is a copy of the existing HTMLRenderer with the added functionality of automatically adding the clickable anchor tag to the TD element (which customers have been doing to roll their own own hyperlink support)
  • Note: Currently the link behavior opens HYPERLINK urls in the same tab - we would probably want to decide if this is the best behavior - or if links should be opened in new tabs - of if the behavior should be customized via a config option
• Update the onDataModifed hook notification handler (for the Formulas plugin) to query the Hyperformula engine to determine if the modified cell has a HYPERLINK
  • If the cell has a HYPERLINK the cell renderer is set to HyperlinkRenderer
  • If the cell does not have a HYPERLINK the cell renderer is set to TextRenderer
• Prototype Code, Unit Tests, and Example Application: Add support for HYPERLINK (handsontable) handsontable#10314

Platform	Results
Hyperformula and Handsontable

Links

[sequba]

@adamu-handsontable, we should discuss this issue.

At first, I thought that there is no place for HYPERLINK function in HyperFormula because it is about displaying formatted data (which is not the responsibility of our project) but the solution proposed by @thilgen makes a lot of sense and we should definitely consider it.

[AMBudnik]

Closed with #1221 for HyperFormula v2.5.0