Creating and handling lambda functions in spreadsheet applications

• US 10,726,201 B1
• Filed: 06/29/2018
• Issued: 07/28/2020
• Est. Priority Date: 06/29/2018
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Inventors: Gross, Christopher John, Duzak, Jeffrey James, Becker, Andrew James, Russo, Claudio Vittorio, Gordon, Andrew Donald
• Primary Examiner(s): Kells, Asher D
• Application Number:
  US16/024,566
• Time in Patent Office: 760 Days
• CPC Class Codes: G06F 17/17 Function evaluation by approximation methods, e.g. inter- or extrapolation, smoothing, least mean square method G06F17/18 takes precedence ; interpolation for numerical control G05B19/18
• CPC Class Codes: G06F 40/103 Formatting, i.e. changing of presentation of documents automatic justification G06F40/189; automatic line break hyphenation G06F40/191
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 40/20 Natural language analysis semantic analysis of natural language G06F40/30
• CPC Class Codes: G06F 8/311 Functional or applicative languages; Rewrite languages

Abstract

Examples discussed herein relate to creating and handling lambdas in spreadsheet application, e.g., Microsoft Excel®. As discussed herein, lambdas are user defined functions (UDFs) that are natively creatable and maintainable through the use of a lambda registry in the spreadsheet application. In an implementation, a method of handling lambdas in a spreadsheet application is disclosed. The method includes detecting that a cell of a spreadsheet is populated with an expression that defines a lambda function and registering the lambda function with a lambda registry corresponding to the spreadsheet. The registration includes recording a home cell that identifies a location of the cell of the spreadsheet where the lambda function is defined. The method further includes replacing the expression that defines the lambda function with a reference to the registered lambda function.

Claims

1. An apparatus comprising:

one or more computer readable storage media;

program instructions stored on the one or more computer readable storage media including one or more components of a spreadsheet application, wherein the program instructions, when executed by one or more processing systems, direct the one or more processing systems to;

detect that a cell of a spreadsheet is populated with an expression that defines a lambda function;

register the lambda function with a lambda registry corresponding to the spreadsheet,

wherein the registration includes recording a home cell that identifies a location of the cell of the spreadsheet where the lambda function is defined; and

replace a representation of the expression that defines the lambda function with a representation of a reference to the registered lambda function.

2. The apparatus of claim 1, wherein the location of the cell of the spreadsheet where the lambda function is defined indicates a sheet and cell within a workbook.

3. The apparatus of claim 1, wherein the definition of the lambda function includes a body comprising a formula.

4. The apparatus of claim 3, wherein the formula refers to one or more cells, and to register the lambda function with the lambda registry, the instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

determine and record direct references to the one or more cells referred to by the formula.

5. The apparatus of claim 3, wherein the instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

identify an expression in a second cell that invokes the lambda function;

consult the lambda registry to identify the body of the lambda function including the formula;

calculate an output value of the lambda function using the formula; and

replace, in the cell of the spreadsheet, a rendering of the expression in the second cell with a rendering of the output value.

6. The apparatus of claim 5, wherein the formula refers to one or more cells, and wherein to calculate the output value of the lambda function using the formula, the instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

determine values for the one or more cells referred to by the formula.

7. The apparatus of claim 6, wherein at least one cell of the one or more cells referred to by the formula includes syntax indicating that the cell is relative to a cell from which the lambda function is invoked as opposed to the home cell.

8. The apparatus of claim 6, wherein the lambda function is invoked by reference to the home cell or by reference to a name of the lambda function.

9. The apparatus of claim 1, wherein the instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

detect a cell adjustment event identifying an adjustment to one or more cell locations;

access the lambda registry to identify one or more cells referred to by a body of the lambda function that are affected by the cell adjustment event; and

modify the one or more cells referred to by the body of the lambda function to account for the adjustment to the one or more cell locations.

10. An apparatus comprising:

one or more processing systems;

one or more computer readable storage media storing;

at least one lambda registry data structure; and

program instructions including one or more components of the spreadsheet application, wherein the program instructions, when executed by one or more processing systems, direct the one or more processing systems to;

detect that a cell of a spreadsheet is populated with an expression that defines a lambda function,

wherein the lambda function includes a body identifying a formula for calculating an output value;

determine a home cell that identifies a location of the cell of the spreadsheet where the lambda function is defined; and

register the lambda function, including the home cell, with the lambda registry data structure.

11. The apparatus of claim 10, wherein the program instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

replace a representation of the expression that defines the lambda function with a representation of a reference to the registered lambda function.

12. The apparatus of claim 10, wherein the location of the cell of the spreadsheet where the lambda function is defined indicates a sheet and cell within a workbook.

13. The apparatus of claim 10, wherein the formula refers to one or more cells, and to register the lambda function with the lambda registry data structure, the instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

determine and record direct references to the one or more cells referred to by the formula.

14. The apparatus of claim 10, wherein the instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

identify an expression in a second cell that invokes the lambda function;

consult the lambda registry to identify the body of the lambda function including the formula; and

calculate the output value of the lambda function using the formula.

15. The apparatus of claim 14, wherein the program instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

replace, in the cell of the spreadsheet, a rendering of the expression in the second cell with a rendering of the output value.

16. The apparatus of claim 10, wherein the program instructions, when executed by the one or more processing systems, further direct the one or more processing systems to:

detect a cell adjustment event identifying an adjustment to one or more cell of the spreadsheet;

access the lambda registry to identify one or more cells referred to by the body of the lambda function that are affected by the cell adjustment event; and

modify the one or more cells referred to by the body of the lambda function to account for the adjustment to the one or more cells.

17. A method comprising:

detecting that a cell of a spreadsheet is populated with an expression that defines a lambda function,

wherein the lambda function includes a body identifying a formula for calculating an output value;

determining a home cell that identifies a location of the cell of the spreadsheet where the lambda function is defined;

registering the lambda function with a lambda registry corresponding to the spreadsheet,

wherein the registration includes recording the home cell; and

replacing a representation of the expression that defines the lambda function with a representation of a reference to the registered lambda function.

18. The method of claim 17, further comprising:

identifying an expression in a second cell that invokes the lambda function;

consulting the lambda registry to identify the body of the lambda function including the formula; and

calculating an output value of the lambda function using the formula.

19. The method of claim 18, further comprising:

replacing a rendering of the expression in the second cell with a rendering of the output value.

20. The method of claim 17, further comprising:

detecting a cell adjustment event identifying an adjustment to one or more cells of the spreadsheet;

accessing the lambda registry to identify one or more cells referred to by the body of the lambda function that are affected by the cell adjustment event; and

modifying the one or more cells referred to by the body of the lambda function to account for the adjustment to the one or more cells.