Synthesizing programs in spreadsheet programming language

Abstract

To describe techniques for automatically synthesizing programs that include one or more functions in a spreadsheet programming language.SOLUTION: A method includes the steps of: receiving a first example including input provided in a first cell in a spreadsheet; automatically synthesizing a plurality of candidate programs including a first set of candidate programs consistent with the first example, each candidate program in the first set of candidate programs comprising at least one function in a spreadsheet programming language and, when the candidate program is executed, the candidate program generating output that matches the first example; ranking the plurality of candidate programs; and storing a highest-ranked program of the plurality of candidate programs in association with the first cell in the spreadsheet.SELECTED DRAWING: Figure 2

Description

[Technical field]

[0001] This application relates to composing programs in spreadsheet programming languages. [Background technology]

[0002] A spreadsheet application may implement a spreadsheet programming language that includes various functions that operate on data in cells or ranges of cells in the spreadsheet and / or on other inputs to generate various outputs. Non-limiting examples of functions in a spreadsheet programming language include a sum function, a count function, an average function, a length function, a concatenate function, a maximum function, a minimum function, and a lookup function.

[0003] A user may manually enter data into a spreadsheet that can be retrieved by the program. For example, a user may manually enter a last name into a column in a first table that can instead be retrieved by using a function in a spreadsheet programming language to extract the last name from a column in a second table that stores first and last names. However, this manual approach can be an inefficient use of the user's time and computer storage resources, as it can result in excessive data entry, use of the client device, and duplication of the same data in multiple places in the spreadsheet. In addition, if the underlying data changes in one place (e.g., the user's last name changes), the change must be manually communicated to all places in the spreadsheet where the data is used.

[0004] Users who are not programmers or who are not experienced spreadsheet application users may not know or be comfortable using functions in a spreadsheet programming language available to programmatically obtain data for inputting into a particular cell in a spreadsheet. In addition, users may not understand how to combine multiple functions in a spreadsheet programming language to form a program that obtains data for inputting into a particular cell in a spreadsheet. Other users may be able to write their own programs that use multiple functions in a spreadsheet programming language to obtain data, but the user-written programs may be inefficient solutions that require excessive processing and memory resources compared to more efficient solutions for obtaining the same data. Furthermore, manually creating a program can often take a long time (e.g., to identify the appropriate functions, due to trial and error, etc.), which may result in excessive utilization of the power resources and / or other computational resources of the client device used in manually creating the program. This may be exacerbated when the client device has a relatively small display and / or software keyboard, both of which may increase the time required to create a program. Summary of the Invention [Means for solving the problem]

[0005] Implementations disclosed herein relate to automatically synthesizing a program that includes at least one function in a spreadsheet programming language and that, when executed, generates an output that matches one or more user-provided output examples. For example, a user input in a first cell in a spreadsheet may be used as a first example, and a plurality of candidate programs in an underlying spreadsheet programming language including a first set of candidate programs may be automatically synthesized such that each program in the first set of candidate programs, when executed, generates an output that matches the first example. The candidate programs are then ranked based on one or more criteria, and the highest ranked program is selected and stored in association with the first cell, e.g., as a surrogate for the user input.

[0006] In some implementations, user input in a second cell in the spreadsheet may be used as a second example, and the automatically synthesized plurality of candidate programs may include a second set of candidate programs that, when executed, generate output consistent with the second example. Prior to ranking the candidate programs based on the predetermined criteria, candidate programs that are not included in both the first set of candidate programs and the second set of candidate programs may be removed from the candidate programs. In this manner, automatically generated programs that do not generate output consistent with both user-provided examples are excluded from the candidate programs. In other implementations, user input in additional cells in the spreadsheet may be used as additional examples, and automatically synthesized programs that do not generate output consistent with the additional examples may also be removed from the candidate programs.

[0007] In other implementations, additional cells in a spreadsheet associated with the first cell may be identified, and the highest ranked programs may be stored in association with each of the additional cells. For example, additional cells in a column in a particular table in a spreadsheet may be identified as associated with a first cell in the column in the particular table, and the highest ranked programs may be stored in association with each of the additional cells in the column.

[0008] In some implementations, the automatically synthesized program can use as input data from one or more other cells (e.g., data source cells) present in one or more tables in the spreadsheet. In other implementations, the automatically synthesized program can use as input data from an external data source, such as a corporate knowledge base. The external data source can be included in an external data source mapping associated with the spreadsheet that includes a reference (e.g., a uniform resource locator or other link) to the external data source.

[0009] In some implementations, a data type of the first example may be determined and execution of a program having an output data type that does not match the data type of the first example is avoided. Thus, system resources may be conserved by avoiding invocation of a program that cannot produce output that matches the user-provided example.

[0010] In some implementations, the data type of the first example may be determined to be a numeric data type and execution of a program having an output data type that does not match the numeric data type may be avoided. In other implementations, the data type of the first example may be determined to be a date data type and execution of a program having an output data type that does not match the date data type may be avoided.

[0011] In some implementations, program input data may be obtained from a data source cell in a spreadsheet or from an external data source, and a data type of the program input data may be determined. Execution of a program having an input data type that does not match the data type of the program input data may be avoided. Thus, system resources may be conserved by avoiding the invocation of a program that cannot utilize as input the type of data contained in the program input data.

[0012] In some implementations, a range of acceptable output values ​​may be determined and execution of programs having output data values ​​that are outside the range of acceptable output data values ​​may be avoided. Thus, system resources may be conserved by avoiding the invocation of programs that are unable to produce outputs that are within the range of acceptable values.

[0013] In some implementations, candidate programs with more frequently used functions may be ranked higher than candidate programs with less frequently used functions. For example, a score may be assigned to each function in the candidate program based on the frequency of use of the function from past spreadsheet data (e.g., frequency of use when written manually), and an overall score of the candidate program may be determined based on the scores assigned to each of the functions in the candidate program. The candidate programs may then be ranked based on the overall score. Thus, candidate programs that include more frequently used functions may be ranked higher, resulting in a higher likelihood of being selected, utilized, and presented to the user. More frequently used functions of the candidate programs may be easier to understand by the user, which may result in computationally efficient review and / or editing of those programs.

[0014] In some implementations, shorter candidate programs, e.g., candidate programs using a smaller number of total functions, may be ranked more favorably than candidate programs using a larger number of total functions. In other implementations, simpler candidate programs, e.g., candidate programs using fewer levels of nested functions, may be ranked more favorably than candidate programs using more levels of nested functions. Thus, system resources may be conserved by avoiding the use of overly complex programs.

[0015] In some implementations, a corpus of spreadsheets (e.g., a collection of spreadsheets created by users of a particular company or organization) may be obtained, and the corpus of spreadsheets may be analyzed to obtain a plurality of program idioms, each of which includes a plurality of functions in a spreadsheet programming language. Additional candidate programs may be automatically synthesized and included in a first set of candidate programs that match the first example, where each of the additional candidate programs includes one or more of the program idioms and, when executed, produces an output that matches the first example. In other implementations, the additional candidate programs may include functions in a spreadsheet programming language in addition to the program idioms. Additionally, in other implementations, in addition to or instead of decomposing the corpus of spreadsheets, online resources may be analyzed to obtain a plurality of program idioms.

[0016] In some additional or alternative implementations, a first example including a user input provided in a first cell in the spreadsheet and a second example including a user input provided in a second cell in the spreadsheet may be received. A first set of programs that match the first example and a second set of programs that match the second example may be automatically synthesized. Each program in the first set of programs, when executed, produces an output that matches the first example, and each program in the second set of programs, when executed, produces an output that matches the second example. Candidate programs may then be generated by determining an intersection between the first set of programs and the second set of programs. The candidate programs may then be ranked, and a highest ranked program of the plurality of candidate programs may be determined, and the user input provided in the first cell in the spreadsheet and the user input provided in the second cell in the spreadsheet may be replaced with the highest ranked program.

[0017] It should be understood that any combination of the foregoing concepts, and additional concepts described in more detail herein, is considered to be part of the subject matter disclosed herein, for example, any combination of claimed subject matter appearing at the end of this disclosure is considered to be part of the subject matter disclosed herein. [Brief description of the drawings]

[0018] [Figure 1] FIG. 1 illustrates a schematic diagram of an exemplary environment in which selected aspects of the present disclosure may be implemented, according to various implementations. [Diagram 2] 1 is a flow chart illustrating an exemplary method for implementing selected aspects of the present disclosure. [Diagram 3] 4 is another flow chart illustrating an exemplary method for implementing selected aspects of the present disclosure. [Figure 4] 1A-1C illustrate example applications of the techniques described herein, according to various implementations. [Diagram 5]FIG. 1 illustrates an example architecture of a computing device. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0019] FIG. 1 illustrates generally an exemplary environment 100 in which selected aspects of the disclosure may be implemented, according to various implementations. Any computing device illustrated in FIG. 1 or elsewhere in the figure may include logic such as one or more microprocessors (e.g., central processing units or "CPUs," graphical processing units or "GPUs") that execute computer-readable instructions stored in memory, or other types of logic such as application specific integrated circuits ("ASICs"), field programmable gate arrays ("FPGAs"), etc. Some of the systems illustrated in FIG. 1, such as spreadsheet system 110, may be implemented using one or more server computing devices forming what is sometimes referred to as a "cloud infrastructure," although this is not required.

[0020] In an implementation, environment 100 may include spreadsheet system 110 implementing a spreadsheet application accessible from various clients, including clients 130-1, ..., 130-n, which may be included within environment 100, either via a thin-client interface, such as a web browser (e.g., a web-based spreadsheet application), or a programmatic interface. In an implementation, the spreadsheet application implemented by spreadsheet system 110 may be a Software as a Service (SaaS) spreadsheet application. Spreadsheet system 110 and clients 130-1, ..., 130-n may communicate via computer network 160, which may be any suitable network, including any combination of a local area network (LAN), a wide area network (WAN), or the Internet. The spreadsheet system 110 may include a program composer 120 configured to perform selected aspects of the present disclosure, among other things, to automatically synthesize programs including one or more functions in a spreadsheet programming language for use in spreadsheets created, modified, and / or viewed using one or more of the clients 130-1, ..., 130-n.

[0021] Each of clients 130-1,...,130-n may be, for example, a user computing device used by a user to access a spreadsheet application via a spreadsheet application user interface, such as, for example, a SaaS spreadsheet application provided by spreadsheet system 110 via a web browser. In one example, clients 130-1,...,130-n may be a user computing device associated with an individual or an entity or organization, such as a business (e.g., financial institution, bank, etc.), a non-profit organization, a club, a university, a government agency, or any other organization that uses a spreadsheet application. For example, a business may operate a spreadsheet application to create, modify, and / or view one or more spreadsheets to manage financial records, business records, customer lists, etc.

[0022] In various implementations, environment 100 may include spreadsheet corpora 140-1, ..., 140-n accessible to clients 130-1, ..., 130-n via computer network 160 or another network. Each of spreadsheet corpora 140-1, ..., 140-n may include a number of spreadsheets created by one or more of clients 130-1, ..., 130-n, for example, using spreadsheet system 110. In one example, spreadsheet corpora 140-1, ..., 140-n may include a set of spreadsheets created, edited, or viewed by one or more users of clients 130-1, ..., 130-n associated with a particular entity or organization.

[0023] Program compositor 120 may be configured to utilize data entered into one or more cells in a spreadsheet to assist clients 130-1, ..., 130-n, by way of example, in automatically synthesizing a program including one or more functions in a spreadsheet programming language for use in a spreadsheet. For example, program compositor 120 may be configured to enable automatic synthesis of a program including one or more functions in a spreadsheet programming language without any human intervention, and optionally present an output of the automatically synthesized program to users of clients 130-1, ..., 130-n via a spreadsheet application user interface, as well as optionally present the automatically synthesized program itself to users of clients 130-1, ..., 130-n for review and / or editing via a spreadsheet application user interface.

[0024] In various implementations, environment 100 may include external data sources 150-1, ..., 150-m that store data that may be accessible to clients 130-1, ..., 130-n and / or program compositor 120 of spreadsheet system 110 via computer network 160 or another network. In implementations, the data in external data sources 150-1, ..., 150-m may be used as input to programs that are automatically synthesized by program compositor 120.

[0025] 2 is a flow chart illustrating an example method 200 for automatically synthesizing a program that includes one or more functions in a spreadsheet programming language, according to implementations disclosed herein. For convenience, the operations of the flow chart are described with reference to a system that performs the operations. The system may include various components of various computer systems, such as one or more components of the spreadsheet system 110. Furthermore, although the operations of the method 200 are shown in a particular order, this is not meant to be limiting. One or more operations may be rearranged, omitted, or added.

[0026] At block 210, the system may receive an example including an input provided in a cell in a spreadsheet. In an implementation, at block 210, the program compositor 120 of the spreadsheet system 110 may receive a first example including an input provided in a first cell in a spreadsheet. In one example, the program compositor 120 of the spreadsheet system 110 may receive a last name (e.g., "LastNameOne") as the first example in response to a user providing a last name as an input in a first cell (e.g., B2) in a spreadsheet via a spreadsheet application user interface displayed at one of the clients 130-1, ..., 130-n.

[0027] Still referring to block 210, the program compositor 120 of the spreadsheet system 110 may optionally receive one or more additional examples, such as a second example that includes the input provided in a second cell in the spreadsheet. In implementations, the program compositor 120 may receive any number of examples. In one example, the program compositor 120 of the spreadsheet system 110 may receive another last name (e.g., "LNTwo") as a second example in response to a user providing a last name as an input in a second cell (e.g., B3) in the spreadsheet via a spreadsheet application user interface displayed at one of the clients 130-1, ..., 130-n.

[0028] 2, in block 220, the system may automatically synthesize a plurality of candidate programs including a set of candidate programs that match the example. In an implementation, in block 220, the program synthesizer 120 of the spreadsheet system 110 may use a dynamic programming based search algorithm to automatically synthesize a first set of candidate programs that match the first example received in block 210. In an implementation, each candidate program in the first set of candidate programs may include at least one function in a spreadsheet programming language, and when the candidate program is executed, the candidate program generates an output that matches the first example.

[0029] Still referring to block 220, in an implementation, each candidate program, when executed, may use as program input data data stored in one or more data source cells in the spreadsheet and / or data from one or more of the external data sources 150-1,...,150-m. For example, while automatically synthesizing the candidate programs, the program synthesizer 120 may obtain program input data from one or more of the external data sources 150-1,...,150-m and then use the program input data as input to one or more of the candidate programs (e.g., as input to one or more functions in a spreadsheet programming language used in the candidate program). In an implementation, the external data sources may include corporate knowledge bases, databases, other spreadsheets, and / or any other data sources. In an implementation, the external data sources 150-1,...,150-m may be included in an external data source mapping that includes references (e.g., paths, uniform resource locators, links, etc.) to the external data sources 150-1,...,150-m associated with the spreadsheet. In an implementation, the program synthesizer 120 may use sampling-based search techniques to handle very large tables and knowledge bases.

[0030] In an implementation, for a given user, the external data sources 150-1, ..., 150-m used by the program compositor 120 can include (or be limited to) an enterprise knowledge base linked to the user's account. In one example, based on the link with the user's account, for a first user, the program compositor 120 can use all of the organization's enterprise knowledge base, while for a second user, the program compositor 120 can use only a subset of the organization's enterprise knowledge base. In an implementation, the link with the user's account can be included within the external data source mapping and / or based on the user's permissions / rights to the database.

[0031] In an implementation, the program synthesizer 120 may first search personal and / or corporate databases contained within the external data sources 150-1, ..., 150-m to identify or locate the program input data, and if the program input data is not identified (located) in the personal and / or corporate databases, may only search other general databases contained within the external data sources 150-1, ..., 150-m.

[0032] Continuing with the previous example, the program synthesizer 120 of the spreadsheet system 110 may automatically synthesize a first set of candidate programs that generate output that matches the last name received as the first example ("LastNameOne") in block 210. One or more of the candidate programs may use data stored in another cell in the spreadsheet as program input data, for example, data stored in a cell (e.g., cell A2) in a column that stores first and last names (e.g., column A) in the same row as the first cell corresponding to the first example. In this example, the program input data "FirstNameOne LastNameOne" may be stored in cell A2.

[0033] The first candidate program included in the first set of candidate programs may be a program that uses a "LEN" function in a spreadsheet programming language that returns the length of a string, a "FIND" function in a spreadsheet programming language that returns the position where a string is first found in a text, and a "RIGHT" function in a spreadsheet programming language that returns a substring from the end of a specified string. For example, the program RIGHT(A2, LEN(A2) - FIND(" ", A2)) may be included in the first set of candidate programs synthesized in block 210. When the first candidate program is executed using "FirstNameOne LastNameOne" as the program input data, "LastNameOne" is returned as the output, and thus the first candidate program produces an output that is consistent with the first example.

[0034] The second candidate program included in the first set of candidate programs may be a program that uses a "RIGHT" function in a spreadsheet programming language. For example, the program RIGHT(A2, 11) may be included in the first set of candidate programs synthesized in block 210. When the second candidate program is executed using "FirstNameOne LastNameOne" as the program input data, "LastNameOne" is returned as the output, and thus the second candidate program produces an output that is consistent with the first example.

[0035] Still referring to block 220, in an implementation, in response to one or more additional examples being received by the program synthesizer 120 of the spreadsheet system 110 in block 210, the program synthesizer 120 may use a dynamic programming-based search algorithm to automatically synthesize additional sets of candidate programs that match each of the additional examples. In an implementation, the program synthesizer 120 may synthesize several sets of candidate programs that match the number of examples received in block 210. For example, if a second example is received by the program synthesizer 120 in block 210, the program synthesizer 120 may use a dynamic programming-based search algorithm to automatically synthesize a second set of candidate programs that match the second example received in block 210. Additionally, if a third example is received by the program synthesizer 120 in block 210, the program synthesizer 120 may use a dynamic programming-based search algorithm to automatically synthesize a third set of candidate programs that match the third example received in block 210, and so on.

[0036] Continuing with the previous example, the program synthesizer 120 of the spreadsheet system 110 may automatically synthesize a second set of candidate programs that generate output that matches the last name received as the second example ("LNTwo") in block 210. One or more of the candidate programs may use as program input data data stored in another cell in the spreadsheet, for example data stored in a cell (e.g., cell A3) in the same row as the second cell corresponding to the second example and in a column that stores first and last names (e.g., column A). In this example, the program input data "FNTwo LNTwo" may be stored in cell A3.

[0037] The first candidate program included in the second set of candidate programs may be a program that uses a "LEN" function in a spreadsheet programming language that returns the length of a string, a "FIND" function in a spreadsheet programming language that returns the position where a string is first found in a text, and a "RIGHT" function in a spreadsheet programming language that returns a substring from the end of a specified string. For example, the program RIGHT(A3, LEN(A3) - FIND(" ", A3)) may be included in the second set of candidate programs synthesized in block 210. When the first candidate program is executed using "FNTwo LNTwo" as the program input data, "LNTwo" is returned as the output, and thus the first candidate program included in the second set of candidate programs produces an output that is consistent with the second example.

[0038] The second candidate program included in the second set of candidate programs may be a program that uses a "RIGHT" function in a spreadsheet programming language. For example, the program RIGHT(A3, 5) may be included in the second set of candidate programs synthesized in block 210. When the second candidate program is executed using "FNTwo LNTwo" as program input data, "LNTwo" is returned as output, and thus the second candidate program included in the second set of candidate programs produces output that is consistent with the second example.

[0039] With further reference to block 220, in an implementation, the program synthesizer 120 of the spreadsheet system 110 may synthesize a candidate program having incrementally more functions as the number of examples received in block 210 increases. In one example, if only one example is received in block 210, the program synthesizer 120 may use only the LEFT, RIGHT, and MID functions in synthesizing the candidate program, whereas if two examples are received in block 210, the program synthesizer 120 may use the IF and case conversion functions in synthesizing the candidate program.

[0040] Still referring to block 220, in an implementation, the program synthesizer 120 of the spreadsheet system 110, when automatically synthesizing a plurality of candidate programs including a set of candidate programs that match the examples, may determine a data type of the examples and avoid execution of a program having an output data type that does not match the data type of the examples. In one example, the program synthesizer 120 may determine that the data type of the first example is a numeric data type, and the program synthesizer 120 may avoid execution of a program having an output data type that does not match the numeric data type. In another example, the program synthesizer 120 may determine that the data type of the first example is a date data type, and the program synthesizer 120 may avoid execution of a program having an output data type that does not match the date data type.

[0041] Still referring to block 220, in an implementation, the program synthesizer 120 of the spreadsheet system 110, when automatically synthesizing multiple candidate programs including a set of candidate programs that match the examples, may obtain program input data from data source cells in the spreadsheet or from one of the external data sources 150-1, ..., 150-m, determine a data type of the program input data, and avoid executing a program having an input data type that does not match the data type of the program input data.

[0042] Still referring to block 220, in an implementation, the program synthesizer 120 of the spreadsheet system 110, when automatically synthesizing a plurality of candidate programs including a set of candidate programs that match the examples, may determine a range of acceptable output data values ​​(e.g., 1 to 31 for days of the month) and avoid executing programs having output data values ​​outside the range of acceptable data output values.

[0043] Still referring to block 220, in an implementation, the program synthesizer 120 of the spreadsheet system 110 may retrieve one or more of the spreadsheet corpora 140-1, ..., 140-n and analyze one or more of the spreadsheet corpora 140-1, ..., 140-n to obtain a plurality of program idioms (e.g., predefined programs) each including a plurality of functions in a spreadsheet programming language. In other implementations, the program synthesizer 120 of the spreadsheet system 110 may analyze one or more online resources (e.g., websites, tutorials, one or more of the external data sources 150-1, ..., 150-m, etc.) to obtain a plurality of program idioms. In an implementation, in automatically synthesizing a plurality of candidate programs including a set of candidate programs matching the example, the program synthesizer 120 may automatically synthesize additional candidate programs including at least one program idiom in each of the set of candidate programs and optionally including one or more additional functions in a spreadsheet programming language. In one example, the program synthesizer 120 may automatically synthesize additional candidate programs in the first set of candidate programs that match the first example, where each of the additional candidate programs, when executed, produces an output that matches the first example.

[0044] Still referring to block 220, in an implementation, in response to one or more additional examples being received by the program synthesizer 120 of the spreadsheet system 110 in block 210, after synthesizing a set of candidate programs that match each of the examples received in block 210, the program synthesizer 120 may remove candidate programs that are not included in all of the sets of candidate programs. For example, if the program synthesizer 120 receives two examples in block 210, the program synthesizer 120 may remove candidate programs that are not included in both the first set of candidate programs that match the first example and the second set of candidate programs that match the second example. Similarly, if the program synthesizer 120 receives three examples in block 210, the program synthesizer 120 may remove candidate programs that are not included in each of the first set of candidate programs that match the first example, the second set of candidate programs that match the second example, and the third second set of candidate programs that match the third example.

[0045] Still referring to block 220, in an implementation, for purposes of determining whether a particular candidate program is included in all of the sets of candidate programs, the program synthesizer 120 may determine that candidate programs that differ only with respect to the cells used for program input data are the same candidate program. For example, the program synthesizer 120 may determine that a candidate program RIGHT(A2, LEN(A2) - FIND(" ", A2)) included in a first set of candidate programs is the same as a candidate program RIGHT(A3, LEN(A3) - FIND(" ", A3)) included in a second set of candidate programs.

[0046] Still referring to block 220, in other implementations, the program synthesizer 120 may generalize the program inputs used in the candidate programs before determining whether a particular candidate program is included in all of the set of candidate programs. For example, the program synthesizer 120 may generalize the candidate program RIGHT(A2, LEN(A2) - FIND(" ", A2)) to RIGHT(A[current row], LEN(A[current row]) - FIND(" ", A[current row])) and the candidate program RIGHT(A3, LEN(A3) - FIND(" ", A3)) to RIGHT(A[current row], LEN(A[current row]) - FIND(" ", A[current row])), where [current row] is a placeholder for the current row in the spreadsheet.

[0047] Continuing with the previous example, the program synthesizer 120 of the spreadsheet system 110 may determine that the candidate program RIGHT(A[current row], LEN(A[current row]) - FIND(" ", A[current row])) is the only candidate program present in both the first set of candidate programs and the second set of candidate programs, and then remove all other candidate programs from the set of candidate programs generated in block 220. In other implementations, the program synthesizer 120 may determine that the candidate programs RIGHT(A[current row], 11) and RIGHT(A[current row], 5) are not present in both the first set of candidate programs and the second set of candidate programs, and then remove those candidate programs from the set of candidate programs generated in block 220.

[0048] 2, the system may rank the multiple candidate programs at block 230. In an implementation, at block 230, the program compositor 120 of the spreadsheet system 110 may rank the multiple candidate programs that were automatically synthesized at block 220. In an implementation, the program compositor 120 may rank candidate programs with more frequently used functions (based on historical spreadsheet data) higher than candidate programs with less frequently used functions.

[0049] Still referring to block 230, in one example, for each of the candidate programs generated in block 220 that remain after candidate programs not in all sets are removed from the set of candidate programs generated in block 220, the program synthesizer 120 may assign a score to each of the functions in the candidate program. In this example, a relatively high score may be assigned to functions in the candidate program that are used more frequently in one or more of the spreadsheet corpora 140-1, ..., 140-n, and a relatively low score may be assigned to functions in the candidate program that are used less frequently in one or more of the spreadsheet corpora 140-1, ..., 140-n. The program synthesizer 120 may then determine, for each of the candidate programs, an overall score for the candidate program based on the scores assigned to each of the functions in the candidate program. For example, the program synthesizer 120 may determine the overall score by averaging the scores assigned to each of the functions in the candidate program. Alternatively, the program synthesizer 120 may determine the overall score based on the lowest score of the scores assigned to each of the functions in the candidate program. The program synthesizer 120 may then rank the multiple candidate programs based on their overall scores.

[0050] Still referring to block 230, in other implementations, the program synthesizer 120 may rank candidate programs that use fewer total functions over candidate programs that use more total functions. In other implementations, the program synthesizer 120 may rank candidate programs that use fewer levels of nested functions over candidate programs that use more levels of nested functions. In other implementations, the program synthesizer 120 may rank candidate programs that use functions previously used by a particular user or organization over candidate programs that use functions not previously used by a particular user or organization. In other implementations, the program synthesizer 120 may rank candidate programs based on a character class-based tokenization mechanism that breaks down input and output strings. In other implementations, the program synthesizer 120 may rank candidate programs that generate tokenized output over candidate programs that do not generate tokenized output.

[0051] Still referring to block 230, in other implementations, the program synthesizer 120 may rank the candidate programs based on the source of the program input data. For example, the program synthesizer 120 may rank candidate programs that obtain program input data from an in-house spreadsheet or knowledge base higher than candidate programs that obtain program input data from a general knowledge base. In another example, the program synthesizer 120 may rank candidate programs that obtain program input data from more frequently accessed sources higher than candidate programs that obtain program input data from less frequently accessed sources. In other implementations, the program synthesizer 120 may use any other criteria or combination of criteria to rank the candidate programs generated in block 220.

[0052] Still referring to FIG. 2, in block 240, the system may store the highest ranked program of the plurality of candidate programs in association with a cell in the spreadsheet associated with the example. In an implementation, in block 240, the program synthesizer 120 of the spreadsheet system 110 may store the highest ranked candidate program of the plurality of candidate programs in block 230 in association with a first cell in the spreadsheet (i.e., the cell associated with the first example received in block 210). In an implementation, in block 240, a prompt regarding the automatically synthesized program (e.g., regarding the highest ranked program determined in block 230) may be displayed on a user interface displayed at the client 130-1, ..., 130-n, and storing the highest ranked program in block 240 may be performed in response to a user indicating acceptance of the automatically synthesized program, for example, by selecting a button in the prompt or by entering a keyboard shortcut.

[0053] Still referring to block 240, in an implementation, the program compositor 120 may replace the input provided (e.g., by a user) in the first cell (i.e., the first example) with the highest ranked program. In an implementation, the program compositor 120 may replace the input provided in the first cell with the highest ranked program, but the value displayed in the first cell (e.g., on a user interface displayed at the client 130-1, ..., 130-n) may remain unchanged because the highest ranked program matches the input previously provided (e.g., by a user) in the cell.

[0054] Continuing with the previous example, the program compositor 120 may determine that the program RIGHT(A[current row], LEN(A[current row]) - FIND(" ", A[current row])) is the highest ranked program in block 230, and the program compositor 120 may associate and store the program RIGHT(A[current row], LEN(A[current row]) - FIND(" ", A[current row])) with the first cell in the spreadsheet (i.e., B2, the cell associated with the first example received in block 210), replacing the input provided in the first cell ("LastNameOne"). The input "LastNameOne" provided in the first cell (i.e., B2) is replaced by the highest ranked program RIGHT(A[current row], LEN(A[current row]) - FIND(" ", A[current row])), which in this example may output "LastNameOne", and thus the value displayed in the first cell may remain unchanged.

[0055] Still referring to block 240, in an implementation, in response to one or more additional examples being received by the program compositor 120 of the spreadsheet system 110 in block 210, the program compositor 120 of the spreadsheet system 110 may store the candidate program ranked highest among the plurality of candidate programs in block 230 in association with a cell in the spreadsheet associated with the additional examples. For example, if a second example is received by the program compositor 120 in block 210, the program compositor 120 may store the candidate program ranked highest among the plurality of candidate programs in block 230 in association with a second cell in the spreadsheet (i.e., the cell associated with the second example received in block 210).

[0056] Still referring to block 240, in an implementation, the program compositor 120 may replace input provided (e.g., by a user) in a cell in a spreadsheet associated with an additional example (such as a second example) with the highest ranked program. In an implementation, the program compositor 120 may replace input provided in a cell in a spreadsheet associated with an additional example with the highest ranked program, but the value displayed in the cell associated with the additional example (e.g., on a user interface displayed at a client 130-1, ..., 130-n) may remain unchanged because the output of the highest ranked program matches the input previously provided (e.g., by a user) in the cell.

[0057] 2, in block 250, the system may determine additional cells in the spreadsheet that are associated with the cell associated with the example. In an implementation, in block 250, the program synthesizer 120 of the spreadsheet system 110 may determine one or more additional cells in the spreadsheet that are associated with the cell associated with the example received in block 210.

[0058] Still referring to block 250, in an implementation, the program compositor 120 may determine the one or more additional cells based on the cell being included in the same column of a particular table in the spreadsheet as the cell associated with the example. In other implementations, the program compositor 120 may determine the one or more additional cells based on the cell being included in the same row of a particular table in the spreadsheet as the cell associated with the example. In other implementations, the program compositor 120 may use any other criteria (e.g., column or row headings or labels in the spreadsheet, existing data, etc.) to determine the one or more additional cells related to the cell associated with the example.

[0059] 2, in block 260, the system may store the highest ranked program in association with an additional cell in the spreadsheet that is associated with the cell associated with the example. In an implementation, in block 260, the program synthesizer 120 of the spreadsheet system 110 may store the highest ranked candidate program among the plurality of candidate programs in block 230 in association with each of the additional cells determined in block 250.

[0060] 3 is a flow chart illustrating an example method 300 for automatically synthesizing a program that includes one or more functions in a spreadsheet programming language, according to implementations disclosed herein. For convenience, the operations of the flow chart are described with reference to a system that performs the operations. The system may include various components of various computer systems, such as one or more components of the spreadsheet system 110. Furthermore, although the operations of the method 300 are shown in a particular order, this is not meant to be limiting. One or more operations may be rearranged, omitted, or added.

[0061] At block 310, the system may receive a first example including user input provided in a first cell in the spreadsheet and a second example including user input provided in a second cell in the spreadsheet. In an implementation, at block 310, the program synthesizer 120 of the spreadsheet system 110 may receive the first example including usage input provided in a first cell in the spreadsheet and the second example including user input provided in a second cell in the spreadsheet.

[0062] Still referring to FIG. 3, in block 320, the system may automatically synthesize a first set of programs that match the first example and a second set of programs that match the second example. In an implementation, in block 320, the program synthesizer 120 of the spreadsheet system 110 may use a dynamic programming based search algorithm to automatically synthesize a first set of programs that match the first example received in block 310 and automatically synthesize a second set of programs that match the second example received in block 310. In an implementation, each program in the first set of programs automatically synthesized by the program synthesizer 120 generates an output that matches the first example received in block 310 when the program is executed, and each program in the second set of programs automatically synthesized by the program synthesizer 120 generates an output that matches the second example received in block 310 when the program is executed. In an implementation, the programs in the first set of programs and the programs in the second set of programs may use data stored in one or more cells in a spreadsheet and / or data from one or more of the external data sources 150-1, ..., 150-m as program input data.

[0063] Still referring to FIG. 3, in block 330, the system may generate candidate programs by determining intersections between the first set of programs and the second set of programs. In an implementation, in block 330, the program synthesizer 120 of the spreadsheet system 110 may generate candidate programs by determining intersections between the first set of programs and the second set of programs that were automatically synthesized in block 320. In an implementation, the program synthesizer 120 may generalize (e.g., as described with respect to block 220) program inputs used in the programs (e.g., in the first set of programs and the second set of programs) before determining intersections between the first set of programs and the second set of programs. In an implementation, the program synthesizer 120 may then determine as intersections programs that exist in both the first set of programs and the second set of programs.

[0064] 3, the system may rank the candidate programs at block 340. In an implementation, at block 340, the program synthesizer 120 of the spreadsheet system 110 may rank the candidate programs generated at block 330. In an implementation, the program synthesizer 120 may rank the candidate programs based on any of the criteria described with respect to block 230, or based on any other criteria or combination of criteria.

[0065] 3, the system may determine the highest ranked program of the candidate programs in block 350. In an implementation, in block 350, the program compositor 120 of the spreadsheet system 110 may determine the highest ranked program of the candidate programs ranked in block 340.

[0066] 3, in block 360, the system may replace the user input provided in the first cell in the spreadsheet and the user input provided in the second cell in the spreadsheet with the highest ranked program. In an implementation, in block 360, the program synthesizer 120 of the spreadsheet system 110 may replace the user input provided in the first cell in the spreadsheet received in block 310 and the user input provided in the second cell in the spreadsheet received in block 310 with the highest ranked program determined in block 350.

[0067] Still referring to block 360, the program compositor 120 may replace the user input provided in a first cell with the highest ranked program, and the value displayed in the first cell (e.g., on a user interface displayed at the clients 130-1, ..., 130-n) may remain unchanged because the output of the highest ranked program matches the input previously provided (e.g., by a user) in the first cell. Additionally, in implementations, the program compositor 120 may replace the user input provided in a second cell with the highest ranked program, but the value displayed in the second cell (e.g., on a user interface displayed at the clients 130-1, ..., 130-n) may remain unchanged because the output of the highest ranked program matches the input previously provided (e.g., by a user) in the second cell.

[0068] 3, in block 370, the system may determine additional cells in the spreadsheet associated with the first cell and the second cell. In an implementation, in block 370, the program synthesizer 120 of the spreadsheet system 110 may determine additional cells in the spreadsheet associated with the first cell associated with the first example received in block 310 and the second cell associated with the second example received in block 310.

[0069] 3, in block 380, the system may store the highest ranked program in association with an additional cell in the spreadsheet associated with the first cell and the second cell. In an implementation, in block 380, the program synthesizer 120 of the spreadsheet system 110 may store the highest ranked program in association with an additional cell in the spreadsheet associated with the first cell and the second cell determined in block 370.

[0070] 4 shows an example of how a program containing one or more functions in a spreadsheet programming language may be automatically synthesized. At the bottom, a graphical user interface ("GUI") 400 is shown that may be used by a spreadsheet application user to view, create, or edit a spreadsheet (e.g., Spreadsheet 1). In this example, Spreadsheet 1 includes a first name column (A) and a last name column (B). The first name column may include various first and last names.

[0071] In some implementations, in response to a user entering data (e.g., a last name, such as "LastNameOne") into the last name column (B) in the second row 420, the programmatic compositor 120 of the spreadsheet system 110 may receive, as a first example, an input provided by the user in a first cell (B2) in the spreadsheet (e.g., as described with respect to block 210 of FIG. 2). Additionally, in some implementations, in response to a user entering data (e.g., a last name, such as "LNTwo") into the last name column (B) in the third row 430, the programmatic compositor 120 of the spreadsheet system 110 may receive, as a second example, an input provided by the user in a second cell (B3) in the spreadsheet (e.g., as described with respect to block 210 of FIG. 2).

[0072] 4, in some implementations, after automatically synthesizing a plurality of candidate programs (e.g., as described with respect to block 220 of FIG. 2) and ranking the plurality of candidate programs (e.g., as described with respect to block 230 of FIG. 2), the program synthesizer 120 of the spreadsheet system 110 may store the highest ranked programs in association with a first cell (B2) and a second cell (B3) in the spreadsheet (e.g., as described with respect to block 240 of FIG. 2). In an implementation, in response to receiving a selection of a cell, such as the second cell (B3), the highest ranked program stored in association with the cell (e.g., =Right(A3, LEN(A3) - FIND(" ", A3))) may be displayed in the formula bar 410 in the GUI 400.

[0073] 4, in some implementations, the program synthesizer 120 of the spreadsheet system 110 may determine additional cells in the spreadsheet (i.e., B4 in the third row 440 and B5 in the fourth row 450) that are associated with the cells associated with the examples (i.e., B2 and B3) (e.g., as described with respect to block 250 of FIG. 2). In some implementations, after determining the additional cells in the spreadsheet that are associated with the cells associated with the examples, the program synthesizer 120 may store the highest ranked programs in association with the additional cells (i.e., B4 in the third row 440 and B5 in the fourth row 450) (e.g., as described with respect to block 260 of FIG. 2).

[0074] The scenario in Figure 4 is for illustrative purposes only. Programs can be automatically synthesized using the techniques described herein for any number of uses.

[0075] 5 is a block diagram of an exemplary computing device 510 that may be optionally utilized to perform one or more aspects of the techniques described herein. The computing device 510 typically includes at least one processor 514 that communicates with several peripheral devices via a bus subsystem 512. These peripheral devices may include, for example, a storage subsystem 524 including a memory subsystem 525 and a file storage subsystem 526, a user interface output device 520, a user interface input device 522, and a network interface subsystem 516. The input and output devices allow a user to interact with the computing device 510. The network interface subsystem 516 provides an interface to external networks and is coupled to corresponding interface devices in other computing devices.

[0076] The user interface input devices 522 may include a keyboard, a pointing device such as a mouse, a trackball, a touchpad, or a graphics tablet, a scanner, a touch screen integrated into a display, an audio input device such as a voice recognition system, a microphone, and / or other types of input devices. In general, use of the term "input device" is intended to include all possible types of devices and methods for inputting information into the computing device 510 or a communications network.

[0077] The user interface output devices 520 may include a display subsystem, a printer, a fax machine, or a non-visual display such as an audio output device. The display subsystem may include a flat panel display such as a cathode ray tube (CRT), a liquid crystal display (LCD), a projection device, or some other mechanism for creating a visual image. The display subsystem may provide a non-visual display, such as through an audio output device. In general, use of the term "output device" is intended to include all possible types of devices or methods for outputting information from the computing device 510 to a user or to another machine or computing device.

[0078] Storage subsystem 524 stores programming and data structures that provide the functionality of some or all of the modules described herein. For example, storage subsystem 524 may include logic for implementing the various components shown in FIG. 1 as well as for performing selected aspects of the methods of FIGS. 2 and 3.

[0079] These software modules are generally executed by the processor 514 alone or in combination with other processors. The memory subsystem 525 included within the storage subsystem 524 may include a main random access memory (RAM) 530 for storing instructions and data during program execution, and a read-only memory (ROM) 532 in which fixed instructions are stored. The file storage subsystem 526 may provide persistent storage for program and data files, and may include hard disk drives, floppy disk drives and associated removable media, CD-ROM drives, optical drives, or removable media cartridges. Modules implementing the functionality of a particular implementation may be stored by the file storage subsystem 526 within the storage subsystem 524, or may be stored within other machines accessible by the processor 514.

[0080] The bus subsystem 512 provides a mechanism for allowing the various components and subsystems of the computing device 510 to communicate with each other as intended. Although the bus subsystem 512 is shown generally as a single bus, alternative implementations of the bus subsystem may use multiple buses.

[0081] The computing device 510 can be of various types, including a workstation, a server, a computing cluster, a blade server, a server farm, or any other data processing system or computing device. Due to the ever-changing nature of computers and networks, the description of the computing device 510 shown in Figure 5 is intended only as a specific example for purposes of illustrating some implementations. Many other configurations of the computing device 510 are possible, having more or fewer components than the computing device shown in Figure 5.

[0082] Implementations may address problems related to excessive data entry, use of client devices, and duplication of the same data in multiple places in a spreadsheet by providing methods and systems for automatically synthesizing programs. In particular, some implementations may improve computer functionality by providing methods and systems for automatically synthesizing programs that include at least one function in a spreadsheet programming language and that, when executed, generate one or more user-provided example outputs. Thus, through the use of rules that improve computer-related technology, implementations enable computer execution of functions that were not previously computer-executable. In addition, implementations use techniques that, by definition, are rooted in computer technology (e.g., spreadsheet programming languages, spreadsheet user interfaces, etc.).

[0083] Although several implementations have been described and illustrated herein, various other means and / or structures for performing the functions and / or obtaining one or more of the results and / or advantages described herein may be utilized, and each such variation and / or modification is deemed to be within the scope of the implementations described herein. More generally, all parameters, dimensions, materials, and configurations described herein are meant to be exemplary, and the actual parameters, dimensions, materials, and / or configurations will depend on the particular application in which the teachings are used. Those skilled in the art will recognize, or be able to ascertain using no more than routine experimentation, many equivalents to the specific implementations described herein. Thus, the foregoing implementations have been presented by way of example only, and it should be understood that within the scope of the appended claims and their equivalents, implementations may be practiced otherwise than as specifically described and claimed. Implementations of the present disclosure are directed to each individual feature, system, article, material, kit, and / or method described herein. Additionally, any combination of two or more such features, systems, articles, materials, kits, and / or methods is included within the scope of the present disclosure, if such features, systems, articles, materials, kits, and / or methods are not mutually inconsistent. [Explanation of symbols]

[0084] 100 Environment 110 Spreadsheet System 120 Program Synthesizer 130-1, ..., 130-n clients 140-1, ..., 140-n Spreadsheet Corpus 150-1, ..., 150-m External Data Source 160 Computer Networks 400 Graphical User Interface ("GUI"), GUI 410 Formula Bar 420 Second Line 430 Third Line 440 Third Line 450 Fourth Line 510 Computing Devices 512 Bus Subsystem 514 processor 516 Network Interface Subsystem 520 User Interface Output Device 522 User Interface Input Devices 524 Memory Subsystem 525 Memory Subsystem 526 File Storage Subsystem 530 Main Random Access Memory (RAM) 532 Read-Only Memory (ROM)

Claims

1. 1. A method implemented by one or more processors, comprising: receiving a first example including an input provided in a first cell in a spreadsheet; automatically synthesizing a plurality of candidate programs including a first set of candidate programs that are consistent with the first example, each candidate program in the first set of candidate programs including at least one function in a spreadsheet programming language, and wherein when the candidate programs are executed, the candidate programs generate output that is consistent with the first example; ranking the plurality of candidate programs; storing a highest ranked program of the plurality of candidate programs in association with the first cell in the spreadsheet; A method comprising:

2. receiving a second example including the input provided in a second cell in the spreadsheet; 2. The method of claim 1, wherein the plurality of candidate programs to be automatically synthesized further comprises a second set of candidate programs that match the second example, each candidate program in the second set of candidate programs includes at least one function in the spreadsheet programming language, and when the candidate programs are executed, the candidate programs generate output that is consistent with the second example.

3. prior to said step of ranking said plurality of candidate programs, removing from said plurality of candidate programs candidate programs that are not included in both said first set of candidate programs and said second set of candidate programs.

3. The method of claim 2, further comprising:

4. determining at least one additional cell in the spreadsheet that is associated with the first cell; storing said highest ranked program in association with each of said at least one additional cell in said spreadsheet related to said first cell; 4. The method of claim 1, further comprising:

5. obtaining program input data from at least one data source cell in the spreadsheet; 5. A method according to claim 1, wherein at least one candidate program in the first set of candidate programs, when executed, uses as input the program input data obtained from the at least one data source cell in the spreadsheet.

6. obtaining program input data from at least one external data source; 6. The method of claim 1, wherein at least one candidate program in the first set of candidate programs, when executed, uses as input the program input data obtained from the at least one external data source.

7. The method of claim 6 , wherein the at least one external data source comprises an enterprise knowledge base.

8. The method of claim 6 , wherein the at least one external data source is included within an external data source mapping associated with the spreadsheet that includes references to multiple external data sources.

9. In the step of automatically synthesizing the plurality of candidate programs including the first set of candidate programs that match the first example, A data type of the first example is determined; 9. The method of claim 1, wherein execution of a program having an output data type that does not match the data type of the first example is avoided.

10. the data type of the first example is determined to be a numeric data type or a date data type; 10. The method of claim 9, wherein execution of a program having an output data type that does not match the numeric data type or the date data type is avoided.

11. In the step of automatically synthesizing the plurality of candidate programs including the first set of candidate programs that match the first example, program input data is obtained from data source cells within said spreadsheet or from an external data source; A data type of the program input data is determined; 11. The method of claim 1, wherein execution of a program having an input data type that does not match the data type of the program input data is avoided.

12. In the step of automatically synthesizing the plurality of candidate programs including the first set of candidate programs that match the first example, A range of acceptable output data values ​​is determined; 12. A method according to any preceding claim, wherein execution of programs having output data values ​​that are outside the range of acceptable output data values ​​is avoided.

13. 13. The method of claim 1, wherein in the step of ranking the plurality of candidate programs, candidate programs having more frequently used functions are ranked higher than candidate programs having less frequently used functions.

14. said step of ranking said plurality of candidate programs further comprising: For each of the plurality of candidate programs, assigning a score to each of a plurality of functions in the candidate programs based on a frequency of use of the function; determining an overall score for the candidate program based on the scores assigned to each of the plurality of functions in the candidate program; ranking the plurality of candidate programs based on the overall score; 14. The method of claim 13, comprising:

15. 13. The method of claim 1, wherein in the step of ranking the plurality of candidate programs, candidate programs that use a smaller number of total functions are ranked more favorably than candidate programs that use a larger number of total functions.

16. 13. The method of claim 1, wherein in the step of ranking the plurality of candidate programs, candidate programs that use a smaller number of levels of nested functions are ranked more favorably than candidate programs that use a larger number of levels of nested functions.

17. obtaining a corpus of spreadsheets; analyzing the corpus of spreadsheets to obtain a plurality of program idioms, each of which comprises a plurality of functions in the spreadsheet programming language; automatically synthesizing additional candidate programs in the first set of candidate programs that match the first examples; Further comprising: each of the additional candidate programs includes at least one of the plurality of program idioms, and when the candidate programs are executed, the candidate programs generate output that is consistent with the first example; one or more of the additional candidate programs further comprises at least one function in the spreadsheet programming language; 17. The method according to any one of claims 1 to 16.

18. analyzing at least one online resource to obtain a plurality of program idioms, each of which includes a plurality of functions in the spreadsheet programming language; automatically synthesizing additional candidate programs in the first set of candidate programs that match the first examples; 18. The method of claim 1, further comprising: each of the additional candidate programs includes at least one of the plurality of program idioms, and when the candidate programs are executed, the candidate programs generate output that is consistent with the first example.

19. 1. A method implemented by one or more processors, comprising: receiving a first example including user input provided in a first cell in a spreadsheet and a second example including user input provided in a second cell in the spreadsheet; automatically synthesizing a first set of programs that match the first example and a second set of programs that match the second example, wherein each program in the first set of programs, when executed, generates output that matches the first example, and each program in the second set of programs, when executed, generates output that matches the second example; generating a plurality of candidate programs by determining an intersection between the first set of programs and the second set of programs; ranking the plurality of candidate programs; determining a highest ranked program of the plurality of candidate programs; replacing the user input provided in the first cell in the spreadsheet and the user input provided in the second cell in the spreadsheet with the highest ranked program; A method comprising:

20. 20. A computer program product comprising instructions that, when executed by one or more processors, cause the one or more processors to perform a method according to any one of claims 1 to 19.

21. 20. A computer-readable storage medium comprising instructions that, when executed by one or more processors, cause the one or more processors to perform the method of any one of claims 1 to 19.

22. 20. A system comprising a processor, a computer readable memory, one or more computer readable storage media, and program instructions collectively stored on the one or more computer readable storage media, the program instructions being executable to perform the method of any one of claims 1 to 19.

Images