Spreadsheets with dynamic database queries

Abstract

Spreadsheets support formulas in cells that trigger queries of data sources. The parameters of the queries can include or depend on the values ​​of other cells in the spreadsheet. Thus, the exact query sent to the data source is dynamic and depends on the data and formulas in the spreadsheet. Furthermore, as query results are received, they are added to cells in the spreadsheet, which can be parameters of other queries defined in other cells. Changing the value of a single cell can automatically trigger updates of an arbitrarily deep hierarchy of calculations, which can include any number of data source queries.

Description

[Technical field]

[0001] This application claims the benefit of U.S. Provisional Application No. 62 / 900,434, filed Sep. 13, 2019, which is incorporated by reference. The described subject matter relates generally to spreadsheets, and more particularly to spreadsheets in which cells can query a database and use the returned results to dynamically update queries in other cells. [Background technology]

[0002] Spreadsheets provide an easy-to-use way of displaying and analyzing data. A typical spreadsheet is a two-dimensional matrix of cells divided into rows and columns. A user can enter data and formula relationships between the data in the cells. For example, a simple spreadsheet may contain a set of mortgage balances for a lender and a total of those balances. The total may be shown in a cell that contains a formula to sum the values ​​in the cells with the individual mortgage balances. Thus, if any of the individual balances are changed, the total balance may be automatically updated. However, as the amount of data and the complexity of the corresponding relationships increase, such spreadsheets become increasingly unmanageable. File sizes grow, system performance degrades, and it becomes increasingly difficult to identify and correct the source of errors resulting from incorrect data or formulas.

[0003] In contrast, relational databases store data in tables with rows and columns. Relational databases are configured to scale efficiently in both size and performance, allowing relatively quick access to data from large corpora. However, a typical relational database does not offer the intuitive, easy-to-use interface of a spreadsheet. To identify a particular data item, a user must define a query that specifies one or more parameters of the desired data. The database system processes the query and returns all records in the database that match the specified parameters. Databases also have limited computational power and often lack the computational capabilities that spreadsheet users expect to connect and analyze specific pieces of derived data in the context of other data. Summary of the Invention

[0004] These and other problems are solved by a spreadsheet that supports formulas in cells that trigger queries of a database. The parameters of the queries can include or depend on the values ​​of other cells in the spreadsheet. Thus, the exact queries sent to the database are dynamic and dependent on the data and formulas in the spreadsheet. Furthermore, as the results of the queries are received, they are added to cells in the spreadsheet, which can be parameters of other queries defined in other cells. Because the database queries are integrated with the spreadsheet's calculation engine, other queries can be automatically updated, which in turn can further update additional queries. In other words, changing the value of a single cell can automatically trigger updates of all of an arbitrarily deep hierarchy of calculations, which can include any number of database queries. This architecture can provide users with the power of a spreadsheet interface while leveraging the power of a database to provide scalable and efficient data storage and access.

[0005] In one embodiment, a method for updating a spreadsheet includes receiving a request to update a specified cell that includes a value or formula of the specified cell. The specified cell is updated to include the value or formula. The method further includes identifying additional cells that depend on the specified cell, obtaining a dependency hierarchy of the additional cells, and updating the additional cells according to the dependency hierarchy. Updating a first cell of the additional cells includes dynamically defining a database query using a current value of another cell in the spreadsheet and updating the first cell to include results returned by the database query. The method also includes providing a spreadsheet for displaying the updated values ​​and formulas of the specified cell and the additional cells. [Brief description of the drawings]

[0006] [Figure 1] FIG. 1 is a block diagram of a networked computing environment suitable for providing spreadsheets with dynamic database queries, according to one embodiment. [Diagram 2] FIG. 2 is a block diagram of the server of FIG. 1 according to one embodiment. [Diagram 3] FIG. 3 is a flow chart of a method for updating a spreadsheet that contains an arbitrarily deep hierarchy of related database queries, according to one embodiment. [Figure 4A] FIG. 4A is a screenshot of an exemplary user interface for interacting with a spreadsheet that contains an arbitrarily deep hierarchy of related database queries, according to one embodiment. [Figure 4B] FIG. 4B is a screenshot of an exemplary user interface for interacting with a spreadsheet that contains an arbitrarily deep hierarchy of related database queries, according to one embodiment. [Figure 4C] FIG. 4C is a screenshot of an exemplary user interface for interacting with a spreadsheet that contains an arbitrarily deep hierarchy of related database queries, according to one embodiment. [Figure 4D] FIG. 4D is a screenshot of an exemplary user interface for interacting with a spreadsheet that contains an arbitrarily deep hierarchy of related database queries, according to one embodiment. [Figure 4E] FIG. 4E is a screenshot of an exemplary user interface for interacting with a spreadsheet that contains an arbitrarily deep hierarchy of related database queries, according to one embodiment. [Figure 4F] FIG. 4F is a screenshot of an exemplary user interface for interacting with a spreadsheet that contains an arbitrarily deep hierarchy of related database queries, according to one embodiment. [Diagram 5] FIG. 5 is a block diagram illustrating an example computer suitable for use in the networked computing environment of FIG. 1, according to one embodiment. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0007] The drawings and the following description illustrate specific embodiments by way of example only. Those skilled in the art will readily recognize from the following description that alternative embodiments of structures and methods may be used without departing from the principles of the invention described. Whenever practicable, similar or similar reference numbers are used in the drawings to indicate similar or similar functionality. When elements have a common numeral followed by another letter, this indicates that the elements are similar or identical. Reference to numerals alone generally refers to any one or any combination of such elements, unless the context dictates otherwise.

[0008] [Example System] FIG. 1 illustrates one embodiment of a networked computing environment 100 suitable for providing spreadsheets with dynamic queries to a scalable data source (e.g., a database). In the illustrated embodiment, the networked computing environment 100 includes a server, a first client device 140A, and a second client device 140B connected via a network 170. In other embodiments, the networked computing environment 100 includes different or additional elements. In addition, functionality may be distributed among the elements in a manner different from that described. For example, although two client devices 140 are shown, the networked computing environment 100 may include any number of such devices. Furthermore, although a client-server architecture is described, functionality may be provided by a standalone computing system, which may or may not be connected to the network 170.

[0009] The server 110 is one or more computing devices that store and manage spreadsheets. For each spreadsheet, the server 110 stores the data and formulas contained within the cells. The server 110 may also store a dependency graph that shows the relationships between the cells in the hierarchy. Thus, when a cell's value is updated, the server 110 can identify other cells that change as a result and automatically update those cells as well. A formula in a cell may define a query to a scalable data source (e.g., a relational database), which may depend on data stored in other cells. To provide a simple example, a first cell may indicate a state of the United States, while a second cell may contain a formula that generates a query to determine the number of records in the data source that are associated with that state and display that number in a second cell. If a user updates the state identified in the first cell, the query is automatically updated to reflect the new state, and the displayed number changes to indicate the number of records in the data source that are associated with the new state. This new value may be used to define further queries. Thus, a change in the value of a single cell can automatically propagate through an arbitrarily deep hierarchy of formulas in other cells, some or all of which may contain data source queries. Various embodiments of the server 110 are described in more detail below with reference to FIG.

[0010] Client device 140 is a computing device configured to allow a user to access one or more spreadsheets managed by server 110. Exemplary client devices 140 include desktop computers, laptop computers, tablets, smartphones, and any other computing devices that may access and display spreadsheets. In one embodiment, client device 140 allows a user to interact with a spreadsheet through a user interface provided by server 110. For example, a user may access the user interface using a web browser. Alternatively, client device 140 may include a dedicated application for interacting with spreadsheets stored by server 110. In either case, assuming users have the appropriate access permissions, they may view the spreadsheets, edit data, and define formulas for cells in the spreadsheets (including formulas that define data source queries).

[0011] Network 170 provides a communication channel through which other elements of networked computing environment 100 communicate. Network 170 may include any combination of local area and / or wide area networks using both wired and / or wireless communication systems. In one embodiment, network 170 uses standard communication technologies and / or protocols. For example, network 170 may include communication links using technologies such as Ethernet, 802.11, Worldwide Interoperability for Microwave Access (WiMAX), 3G, 4G, 5G, Code Division Multiple Access (CDMA), Digital Subscriber Line (DSL), etc. Examples of network protocols used to communicate over network 170 include Multiprotocol Label Switching (MPLS), Transmission Control Protocol / Internet Protocol (TCP / IP), Hypertext Transport Protocol (HTTP), Simple Mail Transfer Protocol (SMTP), and File Transfer Protocol (FTP). Data exchanged over network 170 may be represented using any suitable format, such as Hypertext Markup Language (HTML) or Extensible Markup Language (XML). In some embodiments, all or some of the communication links of network 170 may be encrypted using any suitable technique or techniques.

[0012] 2 illustrates one embodiment of server 110. In the embodiment shown, server 110 includes a spreadsheet engine 210 and a database 220. Spreadsheet engine 210 manages one or more spreadsheets and includes a request processing module 212, a calculation module 214, a query module 216, and a spreadsheet store 218. In other embodiments, server 110 includes different or additional elements. Additionally, functionality may be distributed among the elements in a manner different from that described.

[0013] The request processing module 212 processes user requests for actions related to spreadsheets. In one embodiment, the request processing module 212 receives requests from a client device 140. If the request is to access a spreadsheet, the request processing module 212 provides some or all of the spreadsheet for display on the requesting client device 140 (e.g., by retrieving it from the spreadsheet store 218). If there is a request to modify a value or formula in a cell, the request processing module 212 makes the requested modification and notifies the calculation module 214.

[0014] The computation module 214 determines which additional cells, if any, are affected by the requested modification. In one embodiment, the computation module 214 maintains a computation graph that shows the relationships between cells that influence each other in a hierarchy. For example, a cell that directly depends on the value of a cell is referred to as the first generation child of that cell, while a cell that directly depends on the value of a first generation child is referred to as a second generation child. Similarly, a cell that influences the value of a cell is referred to as a first generation parent, while a cell that is dependent to determine the value of the first generation parent is referred to as a second generation parent.

[0015] Using the computation graph, when a requested modification is made to a cell's value or formula, the computation module 214 can identify additional cells to update by determining which cells have formulas that are affected (directly or indirectly) by the requested modification. In other words, the computation module 214 can identify child cells (of any generation) of the updated cell. The computation module 214 iterates through generations of child cells starting from the first generation and updates them to reflect the changes resulting from the user request. For those child cells that have simple computational formulas (i.e., not involving a query of the database 220), the computation module 214 can re-evaluate the formula using data in the spreadsheet. In contrast, if a child cell has a formula that involves a database query, the computation module 214 passes it to the query module 216.

[0016] The query module 216 constructs a query using the formula of the cell it is evaluating and any other data in the spreadsheet that the formula references. For example, a formula may retrieve the N highest assessed mortgage records in state X, where N is the number in a first cell and X is the state identified in a second cell. Thus, a user can change the query by modifying the first and second cells without editing the formula. In one embodiment, formulas in a spreadsheet are formatted using a syntax familiar to experienced spreadsheet users and may use functions for operations such as summing, averaging, sorting, and filtering inputs from other cells. The query module 216 generates a query in a database query language, such as Structured Query Language (SQL), from the formulas and cell values ​​that it references.

[0017] The query module 216 queries the database 220 using the generated query and imports the returned results into a spreadsheet. In one embodiment, assuming the returned results are a set of records (rows) each containing multiple attributes (columns), the query module 216 inserts them into the spreadsheet as values ​​of a block of cells with the same number of rows and columns, starting with the first parameter of the first record (i.e., the top left corner of the block of results) in the cell containing the formula. However, any suitable or desired location of the block of cells relative to the cell with the formula may be used. As previously mentioned, the value in one or more cells in the block may affect a query defined by another formula. In this case, the calculation module 214 automatically identifies that formula and passes it to the query module 216. The query module 216 then automatically generates an updated query, queries the database, and imports the results, which may lead to queries being automatically updated further in an arbitrary deep hierarchy of interrelated queries.

[0018] The spreadsheet store 218 is one or more computer-readable media that store spreadsheets. In one embodiment, for a given spreadsheet, the spreadsheet store 218 contains values ​​and formulas entered for cells separated by a first delimiter character (or set of characters) between columns and a second delimiter character (or set of characters) between rows. The spreadsheet store 218 may also contain a computational graph of the spreadsheet.

[0019] Database 220 is similarly stored on one or more computer-readable media. In one embodiment, database 220 is a relational database, although other forms of scalable data sources such as NoSQL databases and API implementations that allow abstraction (e.g., PRESTO™) may be used. Although database 220 is shown for convenience as a single entity that is part of server 110, a spreadsheet may contain formulas that query multiple databases, some or all of which may be stored and accessed remotely (e.g., via network 170) by different devices. Thus, any reference to database 220 should be understood to include multiple databases, as well as databases hosted by multiple devices, such as distributed databases, accordingly.

[0020] [Exemplary Method] 3 illustrates a method 300 for updating a spreadsheet that includes an arbitrarily deep hierarchy of related database queries, according to one embodiment. The steps of FIG. 3 are illustrated from the perspective of the server 110 performing the method 300. However, some or all of the steps may be performed by other entities or components. In addition, some embodiments may perform steps in parallel, perform steps in a different order, or perform different steps.

[0021] 3, method 300 begins with server 110 receiving 310 a request that includes a new value or formula for a specified cell in a spreadsheet. For example, the request may be generated by client device 140 in response to a user providing a new value or formula for the specified cell via a user interface of the client device. Alternatively, the request may be generated by the same device that hosts the spreadsheet (e.g., in the case of a standalone, non-network implementation).

[0022] The server 110 updates 320 the value or formula of the specified cell in the spreadsheet and identifies 330 additional cells to update. The additional cells are cells affected by formulas that depend on the cell specified in the request. The server 110 also determines the dependency hierarchy, meaning which of the additional cells are first generation children, second generation children, etc., relative to the specified cell. In one embodiment, the server 110 identifies 330 the additional cells and dependency hierarchy from an existing computation graph of the spreadsheet (e.g., stored in the spreadsheet store 218). Alternatively, the server may partially or completely identify 330 the additional cells on the fly by analyzing the formulas in the cells.

[0023] The server 110 updates 340 any first generation children of the specified cell. As previously mentioned, one or more of the first generation children may include a formula involving a database query that depends on the values ​​of one or more other cells in the spreadsheet. Any such database queries may be dynamically generated based on the current values ​​of the relevant cells in the spreadsheet, and the results of running the query on the database 220 are imported into the spreadsheet. The server 110 checks 350 whether there are additional levels of the dependency hierarchy to update (in this case, whether there are any second generation children) and, if so, updates 340 the additional cells in that level of the dependency hierarchy. This process of updating is repeated until there are no additional levels in the dependency hierarchy left.

[0024] The server 110 provides 360 the updated spreadsheet for display. In one embodiment, the server 110 sends the updated values ​​and formulas of the cells to the client device 140 that received the update request 310 so that the results of the requested updates can be displayed to the user. The server 110 may provide all updates at once, or provide updated cell values ​​and formulas as they are generated, at all levels of the dependency hierarchy. The former approach ensures that the user can see all the effects of the requested changes at once, while the latter may provide a better user experience when there are many complex changes, as the immediate effects are displayed and the more remote effects (i.e., changes to higher generation children) are still calculated.

[0025] [User interface example] 4A-F show an exemplary user interface for interacting with a spreadsheet that contains an arbitrarily deep hierarchy of related database queries, according to one embodiment. The exemplary user interface is for a spreadsheet related to mortgage data, but it should be appreciated that the disclosed architecture and techniques can be used for spreadsheets that contain a wide range of data.

[0026] Figure 4A shows a view of a subset of mortgage data in a database. In this case, the database contains mortgage information for over a million properties, but only a small portion of that data is currently displayed. The user has selected a cell in the first row that displays a street address. It can be seen that the cell's value is defined using the CFDB() function, which is an exemplary function for defining a database query.

[0027] In one embodiment, the CFDB() function has two modes, a search mode and a select mode. In other embodiments, the CFDB() function may have different or additional modes, each with its own syntax.

[0028] In "find mode", the syntax used is CFDB("find", "mortgages", "mortgagesKey", Value, <comma-separated-field-list>)). "find" is the mode of the query. "Mortgages" is the name of the single (e.g., no joins allowed here) database table being queried. "MortgagesKey" is the name of the "single-field primary key" field of the mortgage table being queried. The database 220 typically has a unique index on this column. The value is a reference to another cell in the spreadsheet, hence the dynamic nature of the query that responds to changes in other cells. In the example shown in FIG. 4A, the key is the mortgage "property key" and the depicted example has a value of "m45678". The final parameter is a comma-separated list of columns in the "Mortgages" table whose values ​​are to be returned in the spreadsheet. In one embodiment, the "find mode" causes the function to return a single result row. In other embodiments, the "find mode" may return results from multiple rows.

[0029] In select mode, the syntax used is CFDB("select", <comma-separated-field-list> <comma-separated-database-table-list>, ,<arbitrarily complex WHERE / ORDER / GROUP-BY clause> ). "Select" is the mode of the query. "Select mode" is intended for cases where multiple result rows are expected (but it could be a single row). "Field list" can identify columns from any of the tables referenced in the third function argument, and can include mathematical operations on the data as would be performed by a database engine, such as SUM, MIN, MAX, etc. In the latter case, there may be a corresponding GROUP BY clause specified in the fourth function argument. "Table list" identifies the tables needed to satisfy the query. Tables may be aliased with a single letter or other short name to make the syntax easier to follow and / or to correct otherwise ambiguous column references. The final parameter may include join clauses between tables (thus turning the tables into ad-hoc "views"), filtering syntax that can be programmatically derived from other values ​​in the spreadsheet (such as the state value coming from the "find" value in the first example), and other result set aggregations, filters or limiters, such as GROUP BY, HAVING, ORDER BY, LIMIT, etc.

[0030] In Figure 4A, the CFDB() function is used in search mode to initiate a key value driven query and return the specified property in the identified row. In this case, the user enters the key value m45678 and the query returns the street address (in the selected cell) and additional information from the rows located to the right of the selected cell: state and zip code, property type, last assessed year, assessed value, mortgage balance, and the implied equity.

[0031] In FIG. 4B, the first generation child and parent cells of the selected cell are highlighted. In particular, the key value is identified as a first generation parent cell (because its value influences the query in the selected cell), and the cell containing additional information from the row is identified as a first generation child (because its value is directly generated by the query in the selected cell). Any suitable graphical indicator may be used to highlight these cells, such as fill color, fill intensity, outline color, outline intensity, fill pattern, etc. In one embodiment, the user interface includes controls for the user to select the number of generations of child and parent cells to highlight. The graphical indicators may also be customizable by the user (e.g., the user may be given the option to select which fill color corresponds to which generation of child and parent cells).

[0032] In FIG. 4C, the user has configured the user interface to highlight the second generation child and parent cells and the first generation cell. In particular, there is no second generation parent cell (the key value is entered by the user and does not depend on any of the other cells), and there are two second generation children of different types. and The second generation child cell holds the value of the implied equity associated with the identified mortgage, which can be calculated without further database queries by subtracting the mortgage balance from the appraisal.

[0033] In contrast, the cell in the upper left corner of the table below the information about the identified mortgage is a second generation child that generates a different database query. In particular, this cell contains a formula that defines the query that populates the entire table (except for implied equity). This is shown in Figure 4D, where the third generation child is also highlighted. The third generation child cell uses the CFDB() function to query the database for the N highest appraised properties in Wyoming (the state in which the property subject to the mortgage identified by the key value is located). Because this query returns a set of M selected attributes for each of the N rows in the database, the returned results are all inserted into an M x N block of cells that are fourth generation children of the selected cell (except for the cell in the upper left corner of the block that contains the function that generated the query, which is a third generation child).

[0034] In Figure 4E, the user has selected the cell in the upper left corner of the table. Therefore, it is not the active cell, the previous 4th generation child cell is now the 1st generation child, and the selected mortgage status cell is the 1st generation parent. The cell showing the value of N (the number of properties to include in the table) is also the 1st generation parent and is therefore highlighted. In Figure 4E, it can be seen that the formula in the newly selected cell uses the CFDB() function in Select mode. In contrast to Find mode, which returns attributes from a single row, Select mode returns attributes from all rows that meet the specified requirements (in this case, the 10 most highly appraised properties in Wyoming).

[0035] Figure 4F shows the four generations of parent and child cells for the newly selected active cell, ranging from the provided key value, which is the active cell's fourth generation parent, to the average implied equity of the 10 most highly appraised properties in Wyoming, which is the active cell's fourth generation child. In other words, Figure 4F highlights nine different levels of the dependency hierarchy, including multiple cells whose values ​​are determined by dynamic database queries defined using values ​​in other cells in the spreadsheet.

[0036] [Computing System Architecture] FIG. 5 is a block diagram illustrating components of an exemplary machine 500 capable of reading instructions from a machine-readable medium and executing them on a processor (or controller). Specifically, FIG. 5 illustrates a schematic diagram of the machine 500 in an example forming a computer system on which program code (e.g., software or software modules) for causing the machine to perform any one or more of the above methodologies may be executed. The program code may be comprised of instructions 524 (e.g., software) executable by one or more processors 502. In alternative embodiments, the machine 500 may operate as a standalone device or may be connected (e.g., networked) to other machines. In a networked deployment, the machine 500 may operate in the capacity of a server machine, a client machine in a server-client network environment, or a peer machine in a peer-to-peer (or distributed) network environment.

[0037] The machine 500 may be a server computer, a client computer, a personal computer (PC), a tablet PC, a set-top box (STB), a personal digital assistant (PDA), a cell phone, a smart phone, a web appliance, a network router, a switch or bridge, or any machine capable of executing instructions 524 (sequential or otherwise) that specify actions to be performed by the machine. Additionally, while only a single machine 500 is illustrated, the term "machine" will also be interpreted to include any collection of machines that individually or cooperatively execute instructions 524 to perform any one or more of the methods described above.

[0038] The exemplary computer system 500 includes a processor 502 (e.g., a central processing unit (CPU), a graphics processing unit (GPU), a digital signal processor (DSP), one or more application specific integrated circuits (ASICs), one or more radio frequency integrated circuits (RFICs), or any combination thereof), a main memory 504, and a static memory 506, which are configured to communicate with each other via a bus 508. The computer system 500 may further include a visual display interface 510. The visual interface may include software drivers that enable a user interface to be displayed on a screen (or display). The visual interface may display the user interface directly (e.g., on a screen) or indirectly, such as on a surface, on a window (e.g., via a visual projection unit). For ease of explanation, the visual interface may be described as a screen. The visual interface 510 may include or interface with a touch-enabled screen. Computer system 500 may also include an alphanumeric input device 512 (e.g., a physical keyboard or a touchscreen keyboard), a cursor control device 514 (e.g., a mouse, trackball, joystick, motion sensor, touchscreen, or other pointing device), a storage unit 516, a signal generating device 518 (e.g., a speaker), and a network interface device 520, which are also configured to communicate over bus 508.

[0039] The storage unit 516 includes a machine-readable medium 522 (e.g., a non-transitory machine-readable medium) on which are stored instructions 524 embodying any one or more of the methodologies or functions described herein. The instructions 524 may also reside, completely or at least partially, within the main memory 504, or within the processor 502 (e.g., within a processor's cache memory) during execution thereof by the computer system 500, with the main memory 504 and the processor 502 also constituting machine-readable media. The instructions 524 may be transmitted or received over the network 170 via the network interface device 520.

[0040] While machine-readable medium 522 is shown in the exemplary embodiment to be a single medium, the term "machine-readable medium" should be interpreted to include a single medium or multiple media (e.g., a centralized or distributed database or associated caches and servers) capable of storing instructions (e.g., instructions 524). The term "machine-readable medium" should also be interpreted to include any medium capable of storing instructions (e.g., instructions 524) for execution by a machine and causing the machine to perform any one or more of the methodologies disclosed herein. The term "machine-readable medium" includes, but is not limited to, data repositories in the form of solid-state memories, optical media, and magnetic media.

[0041] The types of computers used by the entities of Figures 1 and 2 can vary depending on the embodiment and the processing power required by the entities. For example, database 120 can be implemented as a distributed system including multiple blade servers working together to provide the described functionality. Additionally, the computers may lack some of the components described above.

[0042] [Additional considerations] Some portions of the above description describe embodiments in terms of algorithmic processes or operations. These algorithmic descriptions and representations are commonly used by those skilled in the computing arts to effectively convey the substance of their work to others skilled in the art. These operations, while described functionally, computationally, or logically, will be understood to be implemented by computer programs comprising instructions for execution by a processor or equivalent electrical circuits, microcode, or the like. Further, it proves convenient at times, without loss of generality, to refer to these arrangements of functional operations as modules.

[0043] As used herein, any reference to "one embodiment" or "an embodiment" means that a particular element, feature, structure, or characteristic described in connection with that embodiment is included in at least one embodiment. In various places in this specification, the appearances of the phrase "in one embodiment" do not necessarily all refer to the same embodiment. Similarly, the use of "a" or "an" preceding an element or component is done merely for convenience. This description should be understood to mean that there are one or more of the element or component, unless it is clear that this is meant otherwise.

[0044] When values ​​are described as "approximate" or "substantially" (or derivatives thereof), such values ​​should be interpreted as being accurate to + / - 10%, unless a different meaning is clear from the context. From the example, "about 10" should be understood to mean "within the range of 9 to 11."

[0045] As used herein, the terms "comprises," "comprising," "includes," "including," "has," "having," or any other variations thereof, are intended to cover a non-exclusive inclusion. For example, a process, method, article, or apparatus with a list of elements is not necessarily limited to only those elements and may include other elements not expressly listed or inherent in such process, method, article, or apparatus. Furthermore, unless expressly stated to the contrary, "or" refers to an inclusive or, not an exclusive or. For example, a condition A or B is satisfied by any one of the following: A is true (or exists) and B is false (or does not exist), A is false (or does not exist) and B is true (or exists), or both A and B are true (or exist).

[0046] Upon reading this disclosure, those skilled in the art will appreciate still further alternative structural and functional designs for systems and processes that provide an arbitrarily deep hierarchy of dynamic database queries within a spreadsheet. Thus, while specific embodiments and applications have been illustrated and described, it should be understood that the described subject matter is not limited to the precise structures and components disclosed. The scope of protection should be limited only by the following claims. < / comma-separated-field-list>

Claims

1. A method for a server to update a spreadsheet using a dynamic database query, comprising: Receiving, from a client device, a request to update a specified cell within the spreadsheet, the request including a value or formula of the specified cell; Updating the specified cell to include the value or formula; Identifying additional cells that depend on the specified cell; Obtaining a dependency hierarchy of the additional cells; Updating the additional cells according to the dependency hierarchy, wherein updating the first cell of the additional cells is: Generating an initial data source query using the value or formula within the specified cell; Importing the result returned by the generated initial data source query into another cell of the spreadsheet; Dynamically defining an updated second data source query using the current value imported into the another cell within the spreadsheet; Updating the first cell to include the result returned by the updated data source query; Including; Providing the spreadsheet to the client device for display; A method including the above.

2. Identifying the additional cells that depend on the specified cell includes accessing an existing computational graph of the spreadsheet, the existing computational graph indicating dependencies between cells, the method according to claim 1.

3. Updating the additional cells according to the dependency hierarchy includes: Updating one or more first-generation child cells of the specified cell; Determining whether there are any second-generation child cells; In response to determining that there is at least one second-generation child cell, updating the at least one second-generation child cell; Including, the method according to claim 1.

4. The method according to claim 1, wherein the current value is the updated value of the specified cell.

5. The data source query returns M attributes for each of N rows in a data source, M and N being integers greater than 1, and the M attributes for each of the N rows are inserted into a corresponding block of M×N cells within the spreadsheet, the method according to claim 1.

6. The upper left corner of the block of M×N cells is the first cell, and the result included in the first cell is the first parameter of the M attributes from the first row of the N rows. The method according to claim 5.

7. Providing the spreadsheet for display includes causing the first generation of child cells of the selected cell to be displayed in a highlighted manner with respect to the selected cell. The method according to claim 1.

8. Providing the spreadsheet for display further includes causing the second generation of child cells of the selected cell to be displayed in a visually distinguishable highlighted manner with respect to the selected cell and the first generation of child cells. The method according to claim 7.

9. Providing the spreadsheet for display includes providing a control configured to enable a user to select several generations of child and parent cells to be highlighted, and causing the selected several generations of child and parent cells to be displayed in a highlighted manner with respect to the selected cell. The method according to claim 7.

10. When executed by a computing device, the computing device receives a request to update a specified cell in the spreadsheet, the request including a value or formula of the specified cell, updates the specified cell to include the value or formula, identifies additional cells that depend on the specified cell, obtains the dependency hierarchy of the additional cells, updates the additional cells according to the dependency hierarchy, and updating the first cell of the additional cells can generate a data source query using the value or formula in the specified cell, import the result returned by the generated data source query into another cell of the spreadsheet, dynamically define an updated second data source query using the current value imported into the other cell in the spreadsheet, update the first cell to include the result returned by the updated data source query, including providing for displaying the spreadsheet, A non-transitory computer-readable storage medium including instructions for causing an operation including.

11. The non - transitory computer - readable storage medium according to claim 10, wherein a server executes the operation, the request is received by the server from a client device, and the server provides the client device with the spreadsheet for display.

12. Identifying the additional cells that depend on the specified cell includes accessing an existing computational graph of the spreadsheet, and the existing computational graph shows dependencies between cells. The non - transitory computer - readable storage medium according to claim 10.

13. A server, Receiving a request to update a specified cell in a spreadsheet, the request including a value or formula of the specified cell, Updating the specified cell to include the value or formula, Identifying additional cells that depend on the specified cell, Obtaining a dependency hierarchy of the additional cells, Updating the additional cells according to the dependency hierarchy, and updating the first cell of the additional cells, Generating a data source query using the value or formula in the specified cell, Importing the result returned by the generated data source query into another cell of the spreadsheet, Dynamically defining an updated second data source query using the current value imported into the other cell in the spreadsheet, Updating the first cell to include the result returned by the updated data source query, Including, Providing for displaying the spreadsheet, A spreadsheet engine configured to perform, Receiving the data source query and the updated data source query, Identifying one or more results in response to the data source query and the updated data source query, Returning the one or more results to the spreadsheet engine in response to the data source query and the updated data source query, A data source configured to perform, A server comprising.

Images