Automatic translation of spreadsheets into scripts
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).
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;
determining one or more ranges of the data arrays based on the one or more ranges of the formula arrays;
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, the determining one or more dependencies further comprising:
determining a first conversion range of the conversion ranges includes a first formula array that references a first data range included in a second conversion range of the conversion ranges;
identifying the first conversion range as depending on the second conversion range based on the first formula array of the first conversion range referencing the first data range of the second conversion range;
creating a dependency graph based at least partially on the dependency of the first conversion range on the second conversion range; and
generating source code based on the conversion ranges and the dependency graph, 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
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;
determining one or more ranges of the data arrays based on the one or more ranges of the formula arrays;
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, the determining one or more dependencies further comprising:
determining a first conversion range of the conversion ranges includes a first formula array that references a first data range included in a second conversion range of the conversion ranges;
identifying the first conversion range as depending on the second conversion range based on the first formula array of the first conversion range referencing the first data range of the second conversion range;
creating a dependency graph based at least partially on the dependency of the first conversion range on the second conversion range; and
generating source code based on the conversion ranges and the dependency graph, 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;
determining one or more ranges of the data arrays based on the one or more ranges of the formula arrays;
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, the determining one or more dependencies further comprising:
determining a first conversion range of the conversion ranges includes a first formula array that references a first data range included in a second conversion range of the conversion ranges;
identifying the first conversion range as depending on the second conversion range based on the first formula array of the first conversion range referencing the first data range of the second conversion range;
creating a dependency graph based at least partially on the dependency of the first conversion range on the second conversion range; and
generating source code based on the conversion ranges and the dependency graph, 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.