Automatic translation of spreadsheets into scripts

• US 10,180,934 B2
• Filed: 07/24/2017
• Issued: 01/15/2019
• Est. Priority Date: 03/02/2017
• Status: Active Grant

• Current Assignee: Palantir Technologies Incorporated
• Original Assignee: Palantir Technologies Incorporated
• Inventors: Ball, Eliot, Janatpour, Dustin, White, Nicholas
• Primary Examiner(s): Stork, Kyle R
• Assistant Examiner(s): Level, Barbara M
• Application Number:
  US15/658,218
• Publication Number:
  US 20180253413A1
• Time in Patent Office: 540 Days
• CPC Class Codes: G06F 40/103 Formatting, i.e. changing of presentation of documents automatic justification G06F40/189; automatic line break hyphenation G06F40/191
• CPC Class Codes: G06F 40/177 of tables; using ruled lines
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 40/205 Parsing
• CPC Class Codes: G06F 8/40 Transformation of program code
• CPC Class Codes: G06F 8/427 Parsing

Abstract

Systems and methods are provided for automatically translating one or more electronic spreadsheets into scripts. One or more electronic spreadsheets containing data arrays and formula arrays may be accessed. The electronic spreadsheet(s) may implement various logic using the data arrays and the formula arrays. The range(s) of the formula arrays may be determined. The range(s) of the data arrays may be determined based on the range(s) of the formula arrays. Conversion ranges may be determined based on the range(s) of formula arrays and the range(s) of data arrays. One or more dependencies between the conversion ranges may be determined and used to generate source code modeling the logic implemented by the electronic spreadsheet(s).

Claims

1. A method being implemented by a computing system including one or more physical processors and storage media storing machine-readable instructions, wherein the method is performed using the one or more processors, the method comprising:

accessing one or more electronic spreadsheets, the one or more electronic spreadsheets containing one or more data arrays and one or more formula arrays, wherein the one or more electronic spreadsheets implement one or more logic using the one or more data arrays and the one or more formula arrays;

determining one or more ranges of the formula arrays, the determining further comprising;

determining at least one set of cells in the electronic spreadsheet that each include a same formula, wherein the formula in each cell of the least one set of cells includes a cell reference that differs by a value of one from a cell reference in an adjacent cell of the at least one set of cells; and

identifying the set of cells as a range of a first formula array;

determining one or more ranges of the data arrays based on the one or more ranges of the formula arrays, the determining further comprising;

determining that one or more cells included in the set of cells corresponding to the range of the first formula array reference a set of different cells in the electronic spreadsheet;

determining that each cell in the set of different cells corresponds to a cell reference that differs by a value of one from another cell in the set of different cells that is adjacent to the cell in the electronic spreadsheet; and

identifying the set of different cells as a range of a first data array;

determining conversion ranges based on the one or more ranges of the formula arrays and the one or more ranges of the data arrays;

determining one or more dependencies between the conversion ranges; and

generating source code based on the conversion ranges and the one or more dependencies between the conversion ranges, the source code modeling the one or more logic implemented by the one or more electronic spreadsheets.

2. The method of claim 1, wherein the one or more ranges of the data arrays include one or more ranges of cells referenced by the one or more formula arrays.

3. The method of claim 1, further comprising exposing the one or more dependencies through an interface, wherein the interface enables a user to modify the one or more dependencies to change the source code to more accurately model the one or more logic implemented by the one or more electronic spreadsheets.

4. The method of claim 3, wherein the one or more dependencies are exposed using a dependency graph defined by the one or more dependencies.

5. The method of claim 1, wherein the one or more formula arrays include a dragged formula array and a range of the dragged formula array is determined by diffing pairs of cells in the one or more electronic spreadsheets.

6. The method of claim 1, further comprising merging two or more ranges of the data arrays, two or more ranges of the formula arrays, or two or more ranges of the data arrays and the formula arrays.

7. The method of claim 1, further comprising determining names for the conversion ranges based on cells containing a string above the conversion ranges.

8. The method of claim 1, wherein the source code is formatted in the Python programming language.

9. A system comprising:

one or more physical processors; and

a memory storing instructions that, when executed by the one or more physical processors, cause the system to perform;

accessing one or more electronic spreadsheets, the one or more electronic spreadsheets containing one or more data arrays and one or more formula arrays, wherein the one or more electronic spreadsheets implement one or more logic using the one or more data arrays and the one or more formula arrays;

determining one or more ranges of the formula arrays, the determining further comprising;

determining at least one set of cells in the electronic spreadsheet that each include a same formula, wherein the formula in each cell of the least one set of cells includes a cell reference that differs by a value of one from a cell reference in an adjacent cell of the at least one set of cells; and

identifying the set of cells as a range of a first formula array;

determining one or more ranges of the data arrays based on the one or more ranges of the formula arrays, the determining further comprising;

determining that one or more cells included in the set of cells corresponding to the range of the first formula array reference a set of different cells in the electronic spreadsheet;

determining that each cell in the set of different cells corresponds to a cell reference that differs by a value of one from another cell in the set of different cells that is adjacent to the cell in the electronic spreadsheet; and

identifying the set of different cells as a range of a first data array;

determining conversion ranges based on the one or more ranges of the formula arrays and the one or more ranges of the data arrays;

determining one or more dependencies between the conversion ranges; and

generating source code based on the conversion ranges and the one or more dependencies between the conversion ranges, the source code modeling the one or more logic implemented by the one or more electronic spreadsheets.

10. The system of claim 9, wherein the one or more ranges of the data arrays include one or more ranges of cells referenced by the one or more formula arrays.

11. The system of claim 9, wherein the instructions further cause the system to perform exposing the one or more dependencies through an interface, wherein the interface enables a user to modify the one or more dependencies to change the source code to more accurately model the one or more logic implemented by the one or more electronic spreadsheets.

12. The system of claim 11, wherein the one or more dependencies are exposed using a dependency graph defined by the one or more dependencies.

13. The system of claim 9, wherein the one or more formula arrays include a dragged formula array and a range of the dragged formula array is determined by diffing pairs of cells in the one or more electronic spreadsheets.

14. The system of claim 9, wherein the instructions further cause the system to perform merging two or more ranges of the data arrays, two or more ranges of the formula arrays, or two or more ranges of the data arrays and the formula arrays.

15. The system of claim 9, wherein the instructions further cause the system to perform determining names for the conversion ranges based on cells containing a string above the conversion ranges.

16. The system of claim 9, wherein the source code is formatted in the Python programming language.

17. A non-transitory computer readable medium comprising instructions that, when executed, cause one or more processors to perform:

accessing one or more electronic spreadsheets, the one or more electronic spreadsheets containing one or more data arrays and one or more formula arrays, wherein the one or more electronic spreadsheets implement one or more logic using the one or more data arrays and the one or more formula arrays;

determining one or more ranges of the formula arrays, the determining further comprising;

determining at least one set of cells in the electronic spreadsheet that each include a same formula, wherein the formula in each cell of the least one set of cells includes a cell reference that differs by a value of one from a cell reference in an adjacent cell of the at least one set of cells; and

identifying the set of cells as a range of a first formula array;

determining one or more ranges of the data arrays based on the one or more ranges of the formula arrays, the determining further comprising;

determining that one or more cells included in the set of cells corresponding to the range of the first formula array reference a set of different cells in the electronic spreadsheet;

determining that each cell in the set of different cells corresponds to a cell reference that differs by a value of one from another cell in the set of different cells that is adjacent to the cell in the electronic spreadsheet; and

identifying the set of different cells as a range of a first data array;

determining conversion ranges based on the one or more ranges of the formula arrays and the one or more ranges of the data arrays;

determining one or more dependencies between the conversion ranges; and

generating source code based on the conversion ranges and the one or more dependencies between the conversion ranges, the source code modeling the one or more logic implemented by the one or more electronic spreadsheets.

18. The non-transitory computer readable medium of claim 17, wherein the one or more ranges of the data arrays include one or more ranges of cells referenced by the one or more formula arrays.

19. The non-transitory computer readable medium of claim 17, wherein the instructions further cause the one or more processors to perform exposing the one or more dependencies through an interface, wherein the interface enables a user to modify the one or more dependencies to change the source code to more accurately model the one or more logic implemented by the one or more electronic spreadsheets.

20. The non-transitory computer readable medium of claim 19, wherein the one or more dependencies are exposed using a dependency graph defined by the one or more dependencies.