Spreadsheet Formula Evaluation via Cell Reference Indirection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current electronic spreadsheet applications have limitations in performing calculations without resorting to complex external resources like macros, and they struggle to pass functional data effectively, restricting user functionality and increasing security vulnerabilities.
Innovation Solution
A method and apparatus that allow passing functional spreadsheet data by reference, where a processor identifies and calculates the result of a formula in one cell based on a function call with a cell identifier, enabling dynamic calculations without complex programming languages, using techniques like the INDIRECT and EVAL functions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If spreadsheet applications use complex external resources like macros to perform calculations, then calculation functionality is improved, but security vulnerabilities increase and ease of operation deteriorates
Solution Approach 1:
The patent introduces an intermediary evaluation mechanism that acts as a mediator between spreadsheet formulas and external code execution. The evaluation module intercepts formula evaluation requests, determines whether they require external resources, and if so, evaluates them in a controlled manner without directly executing macros. This intermediary layer enables enhanced calculation functionality while maintaining security by preventing uncontrolled macro execution.
2Adaptability or versatility
If spreadsheet applications use complex external resources like macros to perform calculations, then calculation functionality is improved, but ease of operation deteriorates
Solution Approach 1:
The patent implements a self-service evaluation system where the spreadsheet application automatically determines whether a formula requires external resource evaluation and handles the evaluation process without user intervention. The evaluation module autonomously intercepts formula requests, assesses their complexity, and executes appropriate evaluation strategies. This self-service approach enables advanced calculation functionality while maintaining ease of operation by abstracting away the complexity from the user.
3Reliability
If spreadsheet applications pass functional data by value instead of by reference, then data integrity is improved, but device complexity and loss of information increase
Solution Approach 1:
The patent extracts the evaluation logic from the standard formula processing pipeline and implements a separate evaluation module that selectively intercepts and evaluates formulas requiring external resources. This extraction approach allows the system to maintain simple pass-by-reference data handling for most cases while applying pass-by-value evaluation only when necessary, thereby reducing overall device complexity while maintaining data integrity for critical operations.
4Reliability
If spreadsheet applications pass functional data by value instead of by reference, then data integrity is improved, but loss of information increases
Solution Approach 1:
The evaluation module serves as an intermediary that selectively applies pass-by-value evaluation only to formulas that require external resource access. For standard formulas, the system maintains pass-by-reference behavior, preserving data integrity and avoiding information loss. The intermediary mechanism ensures that data is copied by value only when necessary for security or functionality, thereby minimizing information loss while maintaining data integrity where required.
Data Source
AI summary
A method of processing data of a collaborative electronic document having a tabular structure includes identifying a first cell in the tabular structure of the collaborative electronic document, the first cell having a first cell identifier and including a formula having a first function call, the first function call including an argument comprising a plurality of formula elements, wherein the plurality of formula elements comprise a second cell identifier and a first string. The method also includes identifying, based on the second cell identifier, a second cell in the tabular structure of the collaborative document, the second cell associated with a function identifier, identifying a value of the second cell in the tabular structure of the collaborative electronic document, wherein the value of the second cell in the tabular structure of the collaborative electronic document is a second string corresponding to a function name that corresponds to a second function call, determining a result of the formula using the function identifier, the first string and the second string, and causing the result of the formula to be displayed in the first cell in the tabular structure of the collaborative electronic document.