Spreadsheet-based software application development

Abstract

The aspects described herein can be used with local spreadsheet applications, web and / or cloud-based spreadsheet solutions to create complex custom software applications. Spreadsheets themselves lack a conceptual framework for building custom or complex software applications as a platform tool. Using the methods and systems described herein using low-code / no-code technology, designers can use one or more spreadsheets as the basic blueprints for software applications to create custom and / or complex software applications. The resulting software application can be static / read-only, or can be interactive to allow users to dynamically add, delete, edit or otherwise modify application data, for example, via one or more online web pages or via mobile applications. Data transmission between the blueprint spreadsheet and the resulting software application can be unidirectional or bidirectional, allowing the modified data to be transferred back to the spreadsheet form from the software application.

Description

[0001] A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.

[0002] CROSS-REFERENCE TO RELATED APPLICATIONS

[0003] This application is a PCT of U.S. Application No. 15 / 829,979, entitled “Spreadsheet-Based Software Application Development,” filed on December 3, 2017, which is a continuation-in-part of co-pending U.S. Application No. 15 / 455,909, entitled “Spreadsheet-Based Software Application Development,” filed on March 10, 2017, and claims priority to U.S. Provisional Application No. 62 / 429,749, entitled “Spreadsheet-Based Software Application Development,” filed on December 3, 2016, the entire contents of each of the foregoing applications being incorporated herein by reference for all purposes. Technical Field

[0004] Aspects described herein generally relate to application and software development for computers and computer networks. More specifically, aspects described herein provide systems and methods for low-code and no-code software development of custom complex software such as interactive web applications. Background Art

[0005] Software development can be a difficult and time-consuming process. Specialized training and knowledge, and often years of experience, are usually required before an individual can develop complex and / or custom software applications. Those who do not possess these skills must hire a software developer or software development company to perform software development, which is often expensive. Furthermore, once the software development process begins, it can take weeks, months, or even years to develop custom and / or complex software.

[0006] Developing custom software applications for use online or with mobile devices typically involves programming in a high-level programming language, such as C# or Java. The more flexible and powerful the software application, the longer it takes for many organizations to fund or build it, and the higher the cost. Even when software application developers leverage pre-built components to share costs among multiple clients, building many projects is still time-consuming due to the unique requirements of each organization.

[0007] In some cases, software applications can be purchased off-the-shelf, but the organization is limited to the features of the off-the-shelf product. Customization is often difficult, time-consuming, and / or expensive, just like original software development.

[0008] Spreadsheets are now ubiquitous in business due to their incredibly powerful business analysis, record keeping, and organizational capabilities. Spreadsheets are used more in business than any other category of software, perhaps with the exception of word processors. However, spreadsheets themselves lack a conceptual framework to serve as a platform tool to build software applications. At best, existing spreadsheet software can allow users to incorporate program code to perform one or more custom functions. However, the program code still must be written in a high-level language, such as Visual Basic, which in turn requires advanced programming skills, capabilities, and resources. Summary of the invention

[0009] A brief summary of the various aspects described herein is presented below. This summary is not an extensive overview and is not intended to identify key elements or important elements or to delineate the scope of the claims. The following summary merely presents some concepts in a simplified form as an introductory prelude to the more detailed description provided below.

[0010] Spreadsheets are popular tools and unique conceptual models for non-technical users to perform tasks, such as calculations and printed reports. Spreadsheets are typically used in two ways: a table where each row is a record, or each record is made to contain an entire table or file. Although spreadsheets and spreadsheet applications themselves lack the conceptual framework for being used as platform tools for building software applications, aspects described herein include tools and functions that are operable to automatically generate complex interactive software and / or web applications using one or more spreadsheets as the basic blueprint for such systems. Such tools and functions or methods and / or systems configured with one or more tools and / or functions described herein may also be referred to as webifiers, webifier systems, or webifier software, etc.

[0011] To overcome limitations in the prior art as discussed above, and to overcome other limitations that will become apparent upon reading and understanding this specification, one or more aspects described herein are directed to facilitating creation of interactive web applications based on data and formatting templates contained in spreadsheets.

[0012] The first aspect described herein provides a method, system, and computer-readable medium for generating an interactive web application including at least one web page. The technology described herein may include analyzing an electronic form to identify one or more data sources, each data source having one or more data records, and identifying one or more user interface templates, each template including a data format of one or more data sources. The electronic form may include multiple worksheets (also referred to herein as "forms"), and the data source may include at least a portion of a first electronic form, and the user interface template may include at least a portion of a second electronic form. A web data store may be generated based on extracting data records from a data source identified within the electronic form and storing the data records in a web data store. The system may generate a specific web page of the interactive web application based on the extracted data records of the web data store, and the specific web page may include data records and / or references to data records, which are identified and formatted according to one or more user interface templates corresponding to the specific web page. The system may generate a presentation of a specific web page in response to a request for a page. In addition, the system may update the web data store in response to receiving user input via the web application.

[0013] The user interface template may define and / or include data formats for corresponding data sources and / or records, such as font formats, cell sizes, and / or any other suitable display formats. In some embodiments, the user interface template may define one or more data restrictions associated with a data source. Such data restrictions may be used to select data records from a data source. Alternatively and / or additionally, the user interface template may define one or more input validation controls associated with a data source. The system may also evaluate user input for input validation controls as part of or in addition to updating a web data store. According to some aspects, one or more user interface templates may be stored in a separate worksheet from one or more data sources. In other aspects, one or more user interface templates may be stored in the same worksheet as one or more data sources.

[0014] According to some aspects, the system can update the interactive web application based on one or more changes to at least one data format and / or data record in the identified electronic form. Changes to the data format in the user interface template can be implemented in the interactive web application without affecting the web data store. Changes to the content of the data record can be automatically reflected in the page of the interactive web application. The system can automatically operate to detect and / or analyze changes made to the data records and / or data formats in the electronic form. According to some aspects, the system can update the base electronic form based on the updates made to the web data store. Additionally and / or alternatively, the system can update the web data store based on the changes made to the data records in the electronic form. Similarly, the system can update the interactive web application based on the changes made to the data format in the user interface template of the electronic form.

[0015] According to some aspects, a system may implement permission controls to limit a user's ability to view and / or modify data stored in a web data store and / or a spreadsheet. A user's security permissions may be defined in a spreadsheet, such as in one or more user interface templates. The security permissions may be defined using a native formula of a spreadsheet application associated with the spreadsheet. Additionally and / or alternatively, the security permissions may be retrieved from a source other than the spreadsheet, such as a management policy server. The system may prevent a first user with a first security permission from modifying the web data store. The system may allow modification of the web data store in response to input received from a second user with a second security permission.

[0016] The electronic form can be generated using electronic form application software. For example, in some embodiments, the electronic form can be generated using MICROSOFT EXCEL. The electronic form can include data files and / or file collections that conform to any traditional electronic form data format. For example, the electronic form can conform to the XLSX, MS-XLS and / or MS-XLSB binary data formats used in MICROSOFT EXCEL. In some embodiments, the generated web data store can include an electronic form, and vice versa.

[0017] According to some aspects, an interactive web application can generate input and / or output web pages based on data records and / or user interface templates. In some embodiments, the system can generate a web input form based on a data source identified in one or more user interface templates, and can receive user input via the web input form to modify one or more records. The system can generate a data output web page for outputting data from a web data store based on a first user interface template, and can generate a data editing web page that can be used to edit data in a web data store based on a second user interface template. In some embodiments, generating a particular web page can include selecting a user interface template corresponding to a client device characteristic that is associated with the particular web page.

[0018] According to another aspect or embodiment, the webifier system can be configured to generate an interactive web application including at least one web page by determining one or more data sources within a spreadsheet (or multiple spreadsheets), each data source having zero or more data records, wherein the data source comprises a first portion of the spreadsheet; determining one or more user interface templates from within the spreadsheet, each user interface template comprising a data format of one or more data sources, wherein the user interface template comprises a second portion of the spreadsheet; generating a web data store comprising data records from the data sources identified in the spreadsheet; generating a specific web page of the interactive web application based on the one or more user interface templates, the one or more user interface templates corresponding to the specific web page, wherein the specific web page references the one or more data sources identified based on the one or more user interface templates corresponding to the specific web page; in response to a request to present the specific web page of the interactive web application, generating a presentation of the specific web page, the web page comprising one or more data records identified and formatted according to the one or more user interface templates corresponding to the specific web page; and in response to receiving user input via the web page of the interactive web application generated based on the spreadsheet, updating the web data store.

[0019] In some aspects, any combination of the following may be included within or incorporated into a combination by a webifier system: a web page of an interactive web application generated based on a spreadsheet is a specific web page; the spreadsheet includes multiple worksheets, and a first portion of the spreadsheet is located on a different worksheet than a second portion of the spreadsheet; the spreadsheet includes multiple worksheets, and one or more user interface templates are stored in the same worksheet as one or more data sources; at least one data format of the user interface template includes a font format of a corresponding data source; at least one data format of the user interface template is based on a cell size of a corresponding data source; the method may also include updating the spreadsheet based on updating a web data store; the method may also include analyzing the spreadsheet to identify one or more changes to at least one data format associated with one or more user interface templates, and updating the interactive web application based on the changed data format without affecting the web data store; the method may also include analyzing the spreadsheet to identify one or more changes made to data records of one or more data sources, and updating the web data store based on the changed data records (wherein such modifications may occur automatically at intervals based on user input, etc.); the method may also include analyzing the spreadsheet to identify one or more changes made to at least one data record associated with one or more data sources, and updating the web data store based on the changed data record (wherein such modifications may also occur automatically at intervals based on user input, etc.).

[0020] In one or more embodiments, and in combination with any other embodiments, the webifier system can include security controls to prevent a first user with a first security clearance from modifying a web data store, and to allow modification of the web data store in response to input received from a second user with a second security clearance. The first security clearance and the second security clearance can be determined based on one or more user interface templates. The security clearance can optionally be defined by a native formula of a spreadsheet application that generates the spreadsheet.

[0021] In one or more embodiments, and in combination with any other embodiments, the electronic form may include a generated web data store, or the web data store may be separate from the electronic form. Identifying or generating the web data store may include storing a record of the applicable electronic form or portion thereof as storing the applicable data.

[0022] In one or more embodiments, and in combination with any other embodiments, the webifier system can use a spreadsheet that conforms to the XLSX, MS-XLS binary data format, or MS-XLSB binary data format.

[0023] In one or more embodiments, and in combination with any other embodiments, the webifier system can, after generating the web data store, subsequently update the web data store based on one or more changes made to one or more data records in the electronic form.

[0024] In one or more embodiments, and in combination with any other embodiments, the webifier system can update a particular web page after the particular web page is generated based on one or more changes made to one or more user interface templates in the electronic form.

[0025] In one or more embodiments, and in combination with any other embodiments, the webifier system can include at least one user interface template that identifies one or more data restrictions associated with a data source, and the system can generate a presentation of a particular web page based on selecting data records that satisfy the one or more data restrictions. The one or more data restrictions can optionally include user-level security restrictions identified in the spreadsheet. Optionally, at least one of the one or more data restrictions can be defined by a native formula of a spreadsheet application associated with the spreadsheet.

[0026] In one or more embodiments, and in combination with any other embodiments, the webifier system can include at least one user interface template that identifies one or more input validation rules associated with a particular data source, and the system can also evaluate user input against the one or more input validation rules. Optionally, when the web data store identifies one or more input validation rules associated with a particular web page, and the system can evaluate user input against the one or more input validation rules.

[0027] In one or more embodiments, and in combination with any other embodiments, the webifier system can receive user input via a web application, and the system can generate a web input form based on data sources identified in one or more user interface templates, and receive user input via the web input form.

[0028] In one or more embodiments, and in combination with any other embodiments, an interactive web application can generate a data output web page for outputting data from a web data store based on a first user interface template, and generate a data editing web page that can be used to edit data in the web data store based on a second user interface template.

[0029] In one or more embodiments, and in combination with any other embodiments, the webifier system can generate a specific web page by selecting a user interface template that corresponds to client device characteristics associated with the specific web page, such as to modify a page specific to a mobile device, etc.

[0030] According to another aspect, a webifier system may include one or more processors, and a memory storing instructions that, when executed by the one or more processors, cause the system to generate an interactive software application (e.g., a web application). The webifier system may obtain one or more data sources within the electronic form based on evaluating the electronic form, each data source having one or more data records, wherein the data source includes at least a portion of a first worksheet of the electronic form. The system may obtain one or more user interface templates within the electronic form based on evaluating the electronic form, each template including a data format of the one or more data sources, wherein the user interface template includes at least a portion of a second worksheet of the electronic form. The system may optionally generate a web data store by extracting data records from data sources identified in the electronic form and storing the data records in the web data store, or the system may use the electronic form as a web data store (in this embodiment or in any other embodiment). The system may generate a specific web page of the interactive software application based on the extracted data records of the web data store, wherein the specific web page includes a reference to the data record, which is identified based on and formatted according to one or more user interface templates corresponding to the specific web page. In response to a request to present a specific web page of the interactive software application, the system may generate a presentation of the specific web page. The system may also update the web data store in response to receiving user input via the interactive software application.

[0031] In one or more embodiments, and in combination with any other embodiments, the webifier system can include at least one user interface template that identifies one or more data restrictions associated with a data source. The instructions, when executed by one or more processors, can also cause the system to generate a presentation of a specific web page based on selecting data records that satisfy the one or more data restrictions.

[0032] According to another aspect or embodiment, a webifier system may generate an interactive web application. The webifier system may obtain one or more data sources within the electronic form based on evaluating the electronic form, each data source having zero or more data records, wherein the data source includes at least a portion of a first worksheet of the electronic form. The system may obtain one or more user interface templates within the electronic form based on evaluating the electronic form, each template including a data format of one or more data sources, wherein the user interface template includes at least a portion of a second worksheet of the electronic form. The system may generate a web data store including data records from data sources identified within the electronic form. The system may generate a specific web page of the interactive web application based on the one or more user interface templates, the one or more user interface templates corresponding to the specific web page, wherein the specific web page includes a reference to at least one data source of the data source and is formatted based on the one or more user interface templates corresponding to the specific web page. In response to a request to present a specific web page of the interactive web application, the system may generate a presentation of the specific web page including one or more data records, the one or more data records being associated with at least one data source and formatted according to the one or more user interface templates corresponding to the specific web page. The system may update the web data store in response to receiving user input via the interactive web application. The first worksheet may be the same worksheet as the second worksheet or a different worksheet.

[0033] According to another aspect, any one or more embodiments may further include generating a spreadsheet user interface that presents at least a portion of a spreadsheet for display, wherein the spreadsheet user interface includes one or more controls that allow a user to identify one or more data sources within the spreadsheet and to identify one or more user interface templates within the spreadsheet. Then, generation of a particular web page of the interactive web application may be performed in response to receiving user input via the one or more controls, the controls identifying at least one or more user interface templates corresponding to the particular web page.

[0034] In one or more embodiments, and in combination with any other embodiments, the spreadsheet user interface includes a two-dimensional arrangement of cells. The cells of the spreadsheet user interface can support numerical values, text values, and formulas. Cell formulas in the spreadsheet user interface can allow users to specify mathematical calculations. Cell formulas in the spreadsheet user interface can allow references to multiple other cells. Cell formulas in the spreadsheet user interface can also support mathematical and string manipulation functions.

[0035] Optionally, cell formulas in a spreadsheet user interface can support references to named functions; cells of a spreadsheet user interface can support heterogeneous formats and formulas within a given row, a given column, or a given area of ​​the spreadsheet user interface; and / or cell formulas in a spreadsheet user interface can support references to other cells containing other formulas, numeric values, or text values.

[0036] In some aspects, the spreadsheet user interface provides a visual editing user interface that allows a user to edit and format cells of the spreadsheet user interface, and the visual editing user interface can provide visual feedback to the user based on format changes, formula changes, and formatting evaluation values ​​and / or rules.

[0037] In some aspects, a spreadsheet user interface may allow a user to indicate that the contents of a single cell may visually span multiple rows or columns without affecting whether the contents of other cells may visually span multiple rows or columns.

[0038] In some aspects, a spreadsheet user interface may allow a user to select cells and move cell contents to other cells by interacting therewith to highlight a single cell or a range of cells.

[0039] In some aspects, a spreadsheet user interface may allow a user to select one or more cells in order to automatically insert references to those cells into the formula of a cell currently being edited.

[0040] In some embodiments, the user input may include a signature and metadata associated with the signature.

[0041] In some aspects, a webifier system can generate a mobile software application and / or content displayed by a mobile device or mobile application. The webifier system can determine one or more data sources within a spreadsheet, each data source having one or more data records. The webifier system can determine one or more user interface templates within a spreadsheet, each template including a data format of one or more data sources. The webifier system can optionally generate a data store including data records from data sources identified within the spreadsheet, or can use a spreadsheet as a data store. The webifier system can generate multiple versions of a specific web page based on one or more user interface templates, the one or more user interface templates corresponding to a specific web page, wherein the specific web page references one or more data sources, the data sources are identified based on one or more user interface templates corresponding to the specific web page, and wherein at least one version of the specific web page is based on a mobile version of one or more user interface templates associated with a mobile client. The webifier system can receive a request from a mobile device to present a specific web page of an interactive web application, for example, wherein the request identifies the user device as a mobile client or device. In response to the request, the webifier system can generate a presentation of a mobile version of a specific web page including one or more data records, the data records being identified and formatted according to one or more user interface templates corresponding to the specific web page and one or more user interface templates associated with the mobile client. The webifier system may update the data store in response to receiving user input from the mobile device.

[0042] According to another aspect, and in combination with any other aspect or embodiment, the webifier system can generate a spreadsheet user interface presenting data records of a data file, wherein the spreadsheet user interface displays the data records in a row and column table including multiple cells, and wherein the spreadsheet user interface allows a user to define a formula to calculate a value of a first cell of the spreadsheet user interface based on a value of a second cell of the spreadsheet user interface.

[0043] A spreadsheet user interface may include a two-dimensional arrangement of cells that support numeric values, text values, and formulas. Cell formulas in a spreadsheet user interface allow a user to specify mathematical calculations, references to multiple other cells, references to named functions (and / or ranges), and / or mathematical and string manipulation functions.

[0044] In some aspects, cells of a spreadsheet user interface may support heterogeneous formats within a given row, a given column, or a given region of the spreadsheet user interface and / or references to other cells containing other formulas, numeric values, or text values.

[0045] In some aspects, the spreadsheet user interface provides a visual editing user interface that allows a user to edit and format cells of the spreadsheet user interface, and the visual editing user interface can provide visual feedback to the user based on format changes, formula changes, and formatting evaluation values.

[0046] In some aspects, a spreadsheet user interface may allow a user to indicate that the contents of a single cell may visually span multiple rows or columns without affecting whether the contents of other cells may visually span multiple rows or columns.

[0047] In some aspects, a spreadsheet user interface may allow a user to select cells and move cell contents to other cells by interacting therewith to highlight individual cells or ranges of cells, and / or allow a user to select one or more cells to automatically insert references to those cells into the formula of the cell currently being edited.

[0048] According to another aspect or embodiment, a webifier system may generate an interactive software application. The webifier system may analyze (or process, determine, obtain, etc.) an electronic form (or multiple electronic forms) to identify one or more data sources, each having one or more data records. The webifier system may analyze (or process, determine, obtain, etc.) the electronic form to identify one or more user interface templates, each template including a data format of one or more data sources. The webifier system may optionally generate a web data store by extracting data records from the data sources identified in the electronic form and storing the data records in the web data store, or the webifier system may use the electronic form as a web data store. In response to a request to create a specific web page within the interactive software application, the webifier system may identify one or more user interface templates corresponding to the specific web page, identify one or more data sources corresponding to the specific web page, and generate the specific web page based on the identified one or more user interface templates and based on the one or more identified data sources. In response to a request from a client device for a specific web page, the webifier system may retrieve one or more data records from the web data store based on the one or more identified data sources, and serve a specific web page including one or more retrieved data records, the retrieved data records being formatted according to the one or more identified user interface templates. In response to receiving user input via the web application, the webifier system can update the web data store (or spreadsheet, if applicable).

[0049] According to another aspect or embodiment, a webifier system can generate an interactive web application. The webifier system can analyze (or process, determine, obtain, etc.) a spreadsheet (or multiple spreadsheets) to identify one or more data sources, each having one or more data records. The webifier system can also identify one or more user interface templates within the spreadsheet, each template including a data format of one or more data sources. The webifier system can optionally generate a web data store by extracting data records from the data sources identified in the spreadsheet and storing the data records in the web data store, or the webifier system can use the spreadsheet as a web data store. In response to a first request for a specific web page within the interactive web application, the webifier system can use data records from the web data store (or spreadsheet) to compose the specific web page, wherein the data records are identified by and formatted according to one or more user interface templates corresponding to the specific web page. The webifier system can analyze the spreadsheet to identify one or more changes made after the web data store was generated, and update the interactive web application using the one or more changes made to the spreadsheet. In response to a second request for a specific web page, the webifier system can compose the specific web page, which contains one or more changes made to the spreadsheet.

[0050] In some aspects, the one or more changes made to the spreadsheet may include edits to a user interface template, and assembling a specific web page containing the one or more changes made to the spreadsheet includes generating a specific web page containing the edits to the user interface template without changing the data record.

[0051] In some aspects, where one or more changes made to the spreadsheet include edits to data records, then assembling a specific web page that includes the one or more changes made to the spreadsheet can include generating a specific web page that includes the edits to the data records without changing the user interface template.

[0052] Some aspects may provide a computer-implemented method for generating an interactive web application including at least one web page. The method may include determining at least one primary data source within an electronic form. The primary data source may correspond to a first worksheet of the electronic form. At least one secondary data source corresponding to a different second worksheet of the electronic form may be determined within the electronic form. The method may determine a relationship between records of the primary data source and records of the secondary data source. Automatically and based on the determined relationship, the method may generate a third worksheet, the third worksheet including at least a portion of the records of the primary data source and at least a portion of the records of the secondary data source. The third worksheet may include an indirect view of the records of the primary data source and / or the records of the secondary data source. The content of the third worksheet may be synchronized with the content of the first worksheet and the content of the second worksheet. The method may generate a specific web page of the interactive web application based on at least one user interface template corresponding to the specific web page. The specific web page may reference a record of the third worksheet identified based on at least one user interface template corresponding to the specific web page. The method may receive user input via an input control associated with the specific web page of the interactive web application, and update at least one record of the primary data source and / or the secondary data source based on the received user input and based on the determined relationship.

[0053] In some implementations, the method may determine a first user interface template within a spreadsheet that identifies a primary data source and a secondary data source. In response, the method may determine a relationship based on the first user interface template and generate a third worksheet based on the relationship. The method may include other ways of identifying the relationship between the primary data source and the secondary data source, such as by receiving additional information from a user and / or by automatically detecting the relationship based on information corresponding to one or more characteristics of the data (such as clues further described herein). Corresponding systems and computer-readable media are also disclosed.

[0054] It should be noted that any of the features described above may be used with any specific aspect or embodiment. Many combinations, modifications or variations of the features of the above embodiments and the features described herein will be apparent to those skilled in the art and are intended to form part of the present invention. Any feature specifically described in connection with one embodiment or example may be used in any other embodiment. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] This patent or application file contains at least one drawing executed in color. Copies of this patent or patent application publication with color drawing(s) will be provided by the Office upon request and payment of the necessary fee.

[0056] A more complete understanding of the aspects described herein and their advantages may be obtained by referring to the following description considering the accompanying drawings, wherein like reference numerals represent like features, and wherein:

[0057] Figure 1 A system architecture is shown that may be used with one or more illustrative aspects described herein.

[0058] Figure 2 A method of performing rapid software application development according to one or more illustrative aspects described herein.

[0059] Figure 3A and Figure 3B Illustrative software system architectures are shown, each of which can be used to implement one or more illustrative aspects described herein.

[0060] Figures 4 to 98 is a screen shot of a spreadsheet-based software application development system according to one or more illustrative aspects described herein. DETAILED DESCRIPTION

[0061] As a general introduction, aspects described herein may provide low-code / no-code methods and systems operable to automatically generate complex software applications using one or more spreadsheets as a base blueprint for the software application. According to some aspects, the resulting software application may be static (read-only), or may be interactive (read / write) to allow a user to dynamically add, delete, edit, or otherwise modify application data, e.g., via one or more online web pages. In some embodiments, data transfer between the blueprint spreadsheet and the resulting software application may be unidirectional or bidirectional, allowing modified data to be transmitted to or from the software application and spreadsheet form, which is transmitted back to the original spreadsheet or a new spreadsheet.

[0062] The electronic form used herein refers to the logical arrangement of the data presented to the user, regardless of the form of data storage. Electronic form software applications (e.g., MICROSOFT EXCEL, APPLE NUMBERS and GNOME GNUMERIC) are software of known types, which allow users to create and edit electronic forms, wherein data are arranged in rows and columns and can be operated and used in calculations. Electronic form software applications can also refer to interactive computer applications for organizing, analyzing and storing data in a tabular form, which can be used locally on a computer or in a cloud-based architecture. Electronic forms are also referred to as computer simulations of paper accounting worksheets. Electronic form software applications usually operate on the data entered in electronic form cells, wherein each cell of the electronic form may contain the result of digital data, text data or formulas, which automatically calculate and display values ​​based on mathematical formulas, the content of one or more other cells, or even external link data. According to an illustrative aspect, electronic forms can be defined as data files that can be used by specific electronic form software applications. One or more electronic spreadsheet documents or data files may include multiple separate and / or separate electronic spreadsheets (e.g., worksheets, labels), which may or may not reference data in other electronic spreadsheets / worksheets from the same and / or different files. In one aspect, electronic spreadsheets are different from databases in this regard, because databases generally require that data be stored in one or more index tables, while electronic spreadsheets support database purposes and non-database purposes at the same time. The illustrative aspects described herein may relate to local electronic spreadsheet application software. However, in other illustrative aspects, electronic spreadsheets may be presented to users via the user interface of a cloud-based system, and electronic spreadsheet functions and features may be presented to users through a web browser or other networked user interface, regardless of the back-end system architecture (e.g., GOOGLE SHEETS, MICROSOFT OFFICE ONLINE, EditGrid, ZKSpreadsheet, Sheetster, Smartsheet, DHTMLX Spreadsheet, Webix, eXpresso, ThinkFreeCell) providing functions or formats in which data is ultimately stored. The aspects described herein may be applied to local electronic spreadsheet application software implementations and cloud-based electronic spreadsheet implementations, or any other electronic spreadsheet implementations.

[0063] Spreadsheet applications generally have in common: a characteristic two-dimensional arrangement of cells in rows and columns when viewed by a user; possibly any additional dimensions, which are represented as additional two-dimensional arrangements; cells, which can contain numerical or text values ​​or formulas entered by the user; and cell formulas, which allow the user to specify mathematical calculations and / or reference multiple other cells by cell position within the two-dimensional arrangement. Some modern, sophisticated spreadsheet applications, including MICROSOFT EXCEL and GOOGLE SHEETS, sometimes in common, have one or more of the following further features: cell formulas, which may be mathematical or string manipulation formulas; cell formulas, which may refer to other cells containing other formulas or numeric or text values; the ability for users to format cells in a visual WYSIWYG ("what you see is what you get") editor; immediate visual feedback to the user of format changes and formula changes, as well as the resulting values ​​of formula evaluations; cell formulas, which may call named functions, such as "sum()" or "concatenate()"; cell references, which may be single cells or ranges of cells, such as "A1:B10" or "A1:A10,C1:C10"; cell ranges, which may be assigned text to be used for other cell references; Aliases for this; user interfaces that allow users to select cells and move cell contents to other cells by interacting with them to first highlight a single cell or range of cells; user interfaces that allow users to select one or more cells in order to automatically insert references to those cells into the formula of the cell currently being edited, allowing users to specify that a single cell or the contents of a single cell can visually span multiple rows and / or columns without also spanning other cells; minimal or no restrictions on the logical direction of data, so that rows and columns are interchangeable and data can therefore be entered in a pivoting direction; cells where formatting and values ​​and formulas do not need to remain consistent across a row or column or range of cells; and columns that can vary in width as a group. Not all of these features are required for an application to be a spreadsheet software application.

[0064] In the following description of various embodiments, reference is made to the accompanying drawings forming a part thereof, and various embodiments in which the aspects described herein can be practiced are shown by way of illustration in the accompanying drawings. It should be understood that other embodiments can be utilized and system or functional changes can be made without departing from the scope of the aspects and embodiments. The aspects described herein can have other embodiments and can be practiced or performed in various ways. Moreover, it should be understood that the wording and terms used herein are used for descriptive purposes and should not be considered as limiting. On the contrary, the phrases and terms used herein will be given their broadest interpretation and meaning. The use of "include" and "comprise" and its variants is intended to cover the items listed thereafter and their equivalents and additional items and their equivalents. The use of the terms "install", "connect", "couple", "position", "engage" and similar terms is intended to include direct and indirect installation, connection, coupling, positioning and engagement.

[0065] Figure 1 An example of a network architecture and data processing equipment is shown, which can be used to implement one or more illustrative aspects described herein. Various network nodes 103, 105, 107 and 109 can be interconnected via a wide area network (WAN) 101, such as the Internet. Other networks can also be used or alternatively used, including a private intranet, a company network, a LAN, a wireless network and a personal network (PAN) etc. Network 101 is used for illustrative purposes and can be replaced with less or additional computer networks. A local area network (LAN) can have one or more topologies in any known LAN topology, and one or more protocols in various different protocols can be used, such as Ethernet. Equipment 103, 105, 107, 109 and other equipment (not shown) can be connected to one or more networks via twisted pair, coaxial cable, optical fiber, radio waves or other communication media.

[0066] The term "network" as used herein and shown in the accompanying drawings refers not only to a system in which remote storage devices are connected together via one or more communication paths, but also to a stand-alone device that may be connected from time to time to such a system having storage capabilities. Thus, the term "network" includes not only a "physical network" but also a "content network" that includes data attributable to a single entity - which resides on all physical networks, for example, using a storage area network (SAN, not shown).

[0067] Components may include data server 103, web server 105, and client computers 107, 109. Data server 103 provides overall access, control, and management of databases and control software for performing one or more illustrative aspects described herein. Data server 103 may be connected to web server 105, where users interact with data through the web server and obtain requested data. Alternatively, data server 103 may be used as a web server itself and directly connected to the Internet. Data server 103 may be connected to web server 105 via network 101 (e.g., the Internet), via direct or indirect connection, or via some other network. Users may interact with data server 103 and / or web server 105 using remote computers 107, 109, for example, using a web browser. Client computers 107, 109 may be used together with data server 103 to access data stored therein, or may be used for other purposes. For example, from a client device 107, a user may access the web server 105 using an Internet browser as is known in the art or by executing a software application that communicates with the web server 105 and / or data server 103 over a computer network such as the Internet.

[0068] The server and application can be combined on the same physical machine and retain separate virtual or logical addresses, or can reside on separate physical machines. Figure 1 Only one example of a network architecture that may be used is shown, and the specific network architecture and data processing equipment used may vary and are auxiliary to the functions they provide, as further described herein. For example, the services provided by the web server 105 and the data server 103 may be combined on a single server.

[0069] Each component 103, 105, 107, 109 can be any type of known computer, server or other data processing device. The data server 103 can include, for example, a processor 111 that controls the overall operation of the rate server 103. The data server 103 can also include RAM 113, ROM 115, network interface 117, input / output interface 119 (e.g., keyboard, mouse, display, printer, etc.) and memory 121. The I / O 119 can include various interface units and drivers for reading, writing, displaying and / or printing data or files. The memory 121 can also store operating system software 123 for controlling the overall operation of the data processing device 103, webifier control logic 125 for instructing the data server 103 to perform one or more aspects described herein, and other application software 127 that provides auxiliary, support and / or other functions that can be used in conjunction with other aspects described herein, such as a spreadsheet software application. The webifier control logic may also be referred to herein as data server software, webifier system, webifier software, webifier plug-in or simply webifier. The functionality of the data server software may refer to operations or decisions performed automatically based on rules encoded into the control logic, operations or decisions made manually by a user providing system input, and / or operations or decisions performed based on a combination of automatic processing of user input (e.g., queries, data updates, etc.).

[0070] The memory 121 may also store data for performing one or more aspects described herein, including first data 129 (e.g., a spreadsheet document or file) and second data 131 (e.g., a web data store, as further described below). The devices 105, 107, 109 may have similar or different architectures as described with respect to the device 103. The functionality of the data processing device 103 (or devices 105, 107, 109) as described herein may be distributed across multiple data processing devices, for example, to distribute the processing load across multiple computers, to separate processing based on geographic location, user access level, quality of service (QoS), etc.

[0071] One or more aspects described herein may be embodied in computer-usable or readable data and / or computer-executable instructions, such as in one or more program modules, executed by one or more computers or other devices as described herein. Typically, program modules include routines, programs, objects, components, data structures, etc., which perform specific tasks or implement specific abstract data types when executed by a processor in a computer or other device. Modules may be written in a source code programming language that is subsequently compiled for execution, or may be written in a scripting language such as (but not limited to) HTML or XML. Computer-executable instructions may be stored on tangible and non-temporary computer-readable media, such as hard disks, optical disks, removable storage media, solid-state memory, USB drives, RAM, etc. As will be appreciated by those skilled in the art, the functions of program modules may be combined or distributed as needed in various embodiments. In addition, the functions may be embodied in firmware or hardware equivalents, such as integrated circuits and field programmable gate arrays (FPGAs), etc. Specific data structures may be used to more efficiently implement one or more aspects, and such data structures are contemplated within the scope of the computer-executable instructions and computer-usable data described herein.

[0072] According to one aspect, the webifier software may include a plug-in or module for use within an existing electronic spreadsheet software application, such as a plug-in within MICROSOFT EXCEL. Alternatively, the webifier software may include a standalone software application that accepts input and generates one or more electronic spreadsheet data files (e.g., MICROSOFT EXCEL.xls / .xlsx workbook files or any other electronic spreadsheet data files) and one or more software and / or web applications as described herein as output. MICROSOFT EXCEL is used for illustrative purposes only. The principles described herein are also applicable to other electronic spreadsheet software applications, as well as other electronic spreadsheet storage formats, modes, and / or standards. For example, COREL QUATTRO PRO, GOOGLE SHEETS, GNUMERIC, OPENOFFICE CALC, or any other electronic spreadsheet software, application, and / or document format may be used. As described above, aspects of this article may be equally applicable to local electronic spreadsheet application software implementations and cloud-based electronic spreadsheet implementations.

[0073] Figure 2An illustrative method of generating a software application according to one or more aspects described herein is shown. Initially, in step 201, a user (also referred to herein as a designer) can create and / or edit a spreadsheet using a spreadsheet software application of the designer's choice. The designer can set up and define a data source as a table, where each row is a record of the data source, and / or define the data source as a spreadsheet file, where each individual spreadsheet is a record of the data source. In this manner, the designer can define one or more data sources and enter data records in one or more data sources.

[0074] The webifier software may automatically or in response to user input analyze the spreadsheet to identify data sources and data records in step 203. When responding to user input, the webifier software may parse the spreadsheet in different ways depending on the type of user action requested, as described further below.

[0075] In step 205, the webifier software may analyze the electronic form to identify one or more user interface templates. The user interface template may include data formats for one or more data sources, such as fonts, sizes, colors, styles, etc., and / or may include selections or identifications of one or more data sources, each of which may include one or more data records. The user interface template may also include the data sources and records themselves.

[0076] Next, in step 207, the webifier software may generate a web data store using the data from the spreadsheet. The web data store may include all of the data from the spreadsheet, or only a subset of the data from the spreadsheet based on one or more criteria or constraints set by the designer in the spreadsheet itself or during the generation process. Alternatively, the web data store may include the spreadsheet itself, or may be included in the spreadsheet.

[0077] In step 209, the webifier may generate one or more web pages as part of a dynamic interactive web application. Each web page may be based on one or more user interface templates identified in step 205. Each user interface template may define and / or identify a data format and / or one or more data sources to include on a web page. When presented or generated for transmission to an end-user browser, each web page may be populated with data from a web data store that meets the standards set forth in the user interface template. In the case where the user interface template includes the data source and the record itself, the resulting web page may be displayed as a simple web table based on the data in a spreadsheet.

[0078] One or more web pages may be generated as interactive web pages, for example, as a result of a designer specifying interactivity as part of a user interface template, or as a result of a designer requesting interactivity when instructing the webifier software to generate corresponding web pages.

[0079] In step 211, when the web page is interactive, the web application may receive user input to edit one or more data records via the web page, e.g., using form fields, input boxes, or any other user input mechanism. In step 213, the web application updates the web data store based on the user input, e.g., adding, editing, deleting, and / or otherwise modifying data previously stored in the web data store. In step 215, the webifier software and / or web application may be used to update the data in the original blueprint spreadsheet that was used to first generate the web application, or the data may be exported to a new spreadsheet. When replacing data in the blueprint spreadsheet, the data format may be retained, and only the data may be replaced / modified.

[0080] The above method is described with respect to a web software application. However, instead of a web page, the webifier software can additionally and / or alternatively generate a stand-alone software application for direct use on a specific computer or computers, thereby alleviating the need for network access when using the software application. Therefore, the above method can be used to create custom and / or complex software applications. A "complex" application refers to an application with multiple functions, such as data entry, reporting, multiple users, per-user security access settings, workflow, automatic notification system, saving historical information, recording changes of all users, and / or file attachments, etc. A "custom" application can refer to software built to meet specific business needs, no matter how unique the needs are. Although custom applications can also usually do this, the result of such custom applications is not designed to perform a common operation of the ready-made (out-of-the-box) software. Therefore, the webifier software may not rely on a single purpose. On the contrary, the webifier software is dynamic and flexible, and can be used by anyone with ordinary skills using complex software applications such as productivity software groups, and thus used as an alternative to hiring custom software developers.

[0081] In the illustrative implementation, there are at least two types of users or participants in the system. The first is the designer, who is the person who designs the software application, web application or mobile application using the webifier software. The second is the visitor or end user, who is the person who accesses the final result web page, stand-alone application or uses the generated mobile application in the way intended by the designer.

[0082] Figure 3A An illustrative software architecture is depicted in which a target system 350 includes webifier logic 353 and memory 351, and a spreadsheet client application 354 (eg, MSEXCEL) installed with a webifier add-on 357. Add-on 357 may load its interface from target system 350 using http requests.

[0083] like Figure 3A As further shown, the designer can create or make a record form using a spreadsheet application 354 and template the form changes to a familiar worksheet, and observe 301a the data changes through an add-on 357, for example, using Excel's COM Interop library. When the designer completes the set of changes, the add-on 357 can send 302 the updated part or alternatively all of the spreadsheet definition to the webifier logic 353 of the target system 350 using a web service API call. The webifier logic 353 can process the new data and update 303 the data stored in the memory 351 (e.g., a web data store). The designer can create or make changes to the target page configuration using the add-on 357, which can be sent 302 to the webifier logic controller 353 as a web service API call. In addition, the designer can create or make changes in a browser 355 accessing the target system, which can be sent 301b to the webifier logic controller as an http request or web service API call.

[0084] In response to an http get request 304 from a visitor's browser 356 to the webifier logic 353 to provide a target page, the webifier logic 353 can retrieve 305 a spreadsheet definition from the memory 351. The webifier logic converts the spreadsheet definition into an html target page by evaluating and referencing values ​​and formats in the template table and evaluating and referencing values ​​in the record table identified based on the template table. The target page can be sent 306 to the visitor's browser 356. Figure 3A It is further shown that the visitor sees a page with: text labels found only in the template table, not in the record table; text labels originating from RecordSheet!A1; a value of "100" evaluating to RecordSheet!C2; and html input controls defined by the template table with values ​​from the RecordSheet, RecordSheet!A2 for the checkbox and RecordSheet!B2 for the textbox. Figure 3AThe visitor is shown checking a checkbox and clicking submit in the browser 356, causing the browser to send an http post request 307 to the webifier logic controller 353. The webifier logic 353 can reload the current spreadsheet definition 305 from memory. The webifier logic 353 processes the post request and updates the memory 351 with the updated spreadsheet definition. If the designer's add-on 357 still has an active session, the webifier logic 353 can send 308 a change event to the add-on 357, either the updated partial spreadsheet definition or enough to update the rendered spreadsheet definition, and then using Excel's COM Interop library, the add-on 357 can present 309 the updated version to the designer in the spreadsheet application 354, so that the designer's worksheet will then display "true" in cell A2.

[0085] Figure 3B An alternative illustrative software architecture is depicted that uses tight integration between the spreadsheet and webifier logic, described in more detail below in the section entitled "Tight Integration".

[0086] References will now be appended below Figures 4 to 98 Describing in greater detail additional details regarding illustrative aspects of the webifier system, Figures 4 to 98 Illustrative screenshots of a spreadsheet-based software application development system in accordance with one or more illustrative aspects described herein are shown. The depicted screenshots are illustrative in nature and are not intended to limit the scope of any aspect described herein. The illustrative screenshots are not meant to limit Webifier for use with a particular spreadsheet application or any particular system architecture. Rather, the screenshots illustrate various features and functionality of the Webifier system, whether used with a local or cloud-based spreadsheet application.

[0087] Figures 4 to 10 Here is a screenshot of a spreadsheet application with available formula functions specific to Webifier, with the add-on installed.

[0088] Fig.11 is a screenshot of the add-on page listing showing a pop-up thumbnail preview of the target page that a visitor might see when visiting that page.

[0089] Fig.12 is a screenshot of a spreadsheet application with an add-on installed, with an add-on menu added to the spreadsheet application toolbar, with a user logged in, and showing the add-on interface within a frame within the side of the spreadsheet application with a list of available web applications.

[0090] Fig.13 is a screenshot of a spreadsheet application with the add-on installed, wherein an add-on menu is added to the spreadsheet application toolbar, wherein the user is not logged in, and a login page is shown.

[0091] Fig.14 is a screenshot of a spreadsheet application with the add-on installed, where the additional menu option has been added to the right-click menu of a cell.

[0092] Fig.15 Here is a screenshot of the gamification user notification popup.

[0093] Fig.16 is a screenshot of a list of web applications organized by the application's origin.

[0094] Fig.17 is a screenshot of the activation action menu for an application on the web application list. Webifier limits the available application actions because web application permissions are restricted by their own designers, such as Fig.21 shown.

[0095] Fig.18 is a screenshot of an empty page list with the currently selected application "Test-Copy".

[0096] Fig.19 is a screen shot of an activated "App Information" dialog box on a list of web applications, showing summary information about a particular web application.

[0097] Fig. 20 is a diagram showing a source spreadsheet definition containing a legacy spreadsheet table, and the table is referenced as a record source in a report page, and the resulting target page defined using the spreadsheet definition is shown. The figure further illustrates that for a particular logged in visitor "Halee", the webifier has filtered the record set to only row 2, where cell A2 matches the logged in username and the formula, as shown in the upper right corner of the screenshot, "=ISLOGGEDINWEBUSER(A2)" returns true when evaluated on the target system. The figure also shows that the function ISLOGGEDINWEBUSER returns an alternating placeholder value at design time, but evaluates correctly when the logged in visitor is known when the target page is rendered.

[0098] Fig.21is a screenshot of the application that is shared with other users, and the designer specifies the permissions they wish the added designer to have for the web application.

[0099] Fig. 22 is a screenshot of the template menu with the option to click to copy the template into a new, modifiable web application.

[0100] Fig.23 is a screenshot of a user clicking on the upload option after creating a new spreadsheet in the spreadsheet application and is now presented with the upload dialog to name the new application with the name defaulting to the previously created spreadsheet file name.

[0101] Fig.24 is a screenshot of a shared tab for a target page named "API_Page" and the designer specifies whether login is required to access the page and a list of users who are allowed to access the page.

[0102] Fig.25 is a screenshot of the dialog box that opens from the Share tab to add a user to the list of users allowed to access the page based on the user's email address.

[0103] Fig.26 is a screen shot of a page of the API type being defined, which creates a web service API page that can be accessed programmatically and has inputs corresponding to the specific insert page specified by the designer.

[0104] Fig. 27 is a screen shot of a page of type calendar being defined. The spreadsheet shows the table of records, and the thumbnail shows the complex control as it would be seen by a visitor. The range controls are set to the corresponding columns on the table of records.

[0105] Fig.28 is a screenshot of the additional options available for the page that defines a calendar type.

[0106] Fig.29 This is a screenshot of a page of type container defined in simple mode.

[0107] Fig.30 is a screenshot of a page with a container type defined in advanced mode.

[0108] Fig.31 is a screenshot of a page with a container type defined in graphical designer mode.

[0109] Fig.32is a screenshot of the additional options available for the Define Container Type page.

[0110] Fig.33 is a screenshot of a page of type CSV Import being defined. The thumbnail demonstrates the complex group of controls that will be presented to visitors of the page, with the source set by name to the entire worksheet.

[0111] Fig.34 is a screenshot of the additional options available for the page that defines the CSV import type.

[0112] Fig.35 is a screen shot of the Details Report page being defined, where the designer selects a template table based on a named range / Defined Name. The screen shot shows the various options for the template source reference.

[0113] Fig.36 is a screen shot of a page of a detailed report type being defined, where the designer has selected the template form based on a custom range of B9:C12 selected from cells in a spreadsheet tool, and the designer has clicked the "use selected" button in the template definition portion.

[0114] Fig.37 is a screenshot of the additional options available for the page that defines a detailed report type. The screenshot also shows the light green overlay on the spreadsheet tool cells, and a list of functions that the designer can use in the template definition. Figures 4 to 10 The function details are defined in .

[0115] Fig.38 Here is a screenshot of a page of type Edit-Cells being defined.

[0116] Fig.39 is a screenshot of additional options available for a page that defines the type of cell to edit. The screenshot also shows a header range defined based on a custom range of B1:C1 cells currently selected in the spreadsheet tool.

[0117] Fig.40 is a screenshot of the various options for defining the title range for a page that edits a cell type.

[0118] Fig.41 is a screen shot of an Edit-Record type page being defined, in which the designer specifies that the orientation of the source record table is row-based, so that each row represents a different record.

[0119] Fig.42 This is a screenshot of the Edit-Record type of page being defined, with the designer selecting the More option (such as Page Options), specifying a custom Header Range, and then selecting Editable Range from the drop-down menu.

[0120] Fig.43 is a screenshot of a page of type Insert being defined, with the thumbnail showing a preview of the automatically generated html form that would be seen by a visitor of the target page.

[0121] Fig.44 is a screenshot of the various options for inserting a typed page. It further shows some of the data validators that webifier supports.

[0122] Fig.45 is a screenshot of a page of type Link being defined, where the thumbnail shows a preview of the hyperlink that will be generated, but where the formula function displays placeholder values ​​instead of actual values, and where the destination to which the visitor will be directed when clicking the link is specified as a specific calendar destination page.

[0123] Fig.46 is a screen shot of a page of the PDF type being defined, with a thumbnail showing a preview of the PDF with the form embedded in the PDF that will be generated based on the designer selecting a previously created Insert Target page as the basis for the PDF.

[0124] Fig.47 is a screen shot of a page of type Report being defined, where the report has no separately defined template table. The thumbnail shows that a row record table will result in the target page listing all records in the same cell format as the row record table.

[0125] Fig.48 This is a screen shot of a Report type page being defined, with the designer selecting More options such as Header Range, Auto-refresh time, and Page Options.

[0126] Fig.49 is a screen shot of a page of type Tabs Container being defined, where the designer has selected two previously created target pages to include in the tabs. The thumbnail shows the target page, which will have two visible tabs at the top of the page, with each tab having a previously created page as the basis for the content.

[0127] Fig.50 is a screenshot of a page of type Text being defined, where the designer can enter rich text using a WYSIWYG editor.

[0128] Fig.51 is a screen shot of a system presenting options for the type of page to be added after receiving a request from a designer to add a page.

[0129] Fig.52 is a screen shot of a page of type Report being defined, where webifier has automatically detected that a chart is available in the spreadsheet and webifier shows a drop-down list of charts that can be displayed on the report target page.

[0130] Fig.53 Is a screenshot of the add-on being notified by the target system, where changes from the visitor have been received and detected and will be automatically downloaded or the designer will be able to click on download data at the desired time to retrieve them.

[0131] Fig.54 is a screenshot of a page listing where the designer has clicked on the page's action menu and the add-on presents supported action options for the target page, such as duplicate or delete the page.

[0132] Fig.55 is a screenshot of the add-on showing the web application action menu from within a page list, with various action options for the entire web application, such as duplicate the web application.

[0133] Fig.56 It is a sample document for calling target system web service API functions that can be used for external systems or add-ons, which shows the typical use and request format of web service API calls.

[0134] Fig.57 is a screenshot of the target report page, which shows the result of a report page type defined using a template table to define the layout, thereby having row records listed on the left, some links at the top, and an icon that is clickable on the right to bring up the chat.

[0135] Fig.58 It is a screenshot of the target system retrieving the chat transcript and prompting the visitor to enter a new chat after the visitor visits the chat object.

[0136] Fig.59is a screenshot with an example spreadsheet where a chat object is being created using a formula function and a GUID is passed in as a parameter to indicate which chat history group is associated with the chat object. In this example, the visible placeholder text shown in the cell at design time is the GUID passed in.

[0137] Fig.60 is a screenshot with an example spreadsheet showing how multiple external supplementary data objects can be used in a spreadsheet: a chat object, a file object, and an image object. The screenshot shows how there is placeholder text, such as the GUID of the file or #None when no image is inserted, or other titles specified as arguments to formula functions (e.g., Figure 5 ), as in the case of a file object showing a size of "10KB". It also shows how two cells can reference the same external data object, in this case E7 and F7.

[0138] Fig.61 It is possible to Fig.60 Screen capture of the Target Report page created from the spreadsheet in . The page shows how the placeholder text has been replaced with an interactive chat object, and cells E7 and F7 now allow the page to show multiple properties of the external supplemental data object.

[0139] Fig.62 yes Fig.61 , which has the ability to accept attached image files via an upload dialog from a browser. The screenshot also shows that interacting with the image object on the target page causes the control to change from a simple text label to a label suitable for selecting a file, and that the system enables the designer to specify permissions for visitors to change the formula for the image object, allowing the visitor to change the file box to which the upload will go.

[0140] Fig.63 yes Fig.61 Screen shot of the Goals report page in , which shows the currently stored images for an image object after a visitor uploads an image to the image object via the goal page.

[0141] Fig.64 yes Fig.60 Screen capture of an example spreadsheet in which the arguments to the formula functions are arbitrary text strings instead of GUIDs.

[0142] Fig.65 is a screen shot of a target system administration page that allows the designer to specify which programmatic or automated clients are allowed to connect to the target system and what authentication parameters are required for the clients to do so.

[0143] Fig.66is a screen shot of a target system page with a navigation menu of other pages dropped down from the web application "Timesheet".

[0144] Fig.67 is a screenshot of the target system administration page, which allows the web application to be exported as a single self-contained file containing the spreadsheet definition, both the record form and the template form, as well as the sharing settings and notification settings.

[0145] Fig.68 is a screenshot of the target system admin page upload dialog, which allows uploading a spreadsheet definition with Fig.67 The same options for exporting the imported information.

[0146] Fig.69 is a screenshot of the Activate Actions menu for an application on the web application list. Fig.17 Differently, webifier provides access to additional operations, such as "add page", because the designer has appropriate permissions to the web application.

[0147] Figure 70 to Figure 73 is a screenshot of the target system administration page, which allows the designer to define Insert, Link, Report, and Text type pages, which are very similar to Fig.43 , Fig.45 , Fig.47 and Fig.50 , but no add-ons or third-party spreadsheet tools are required.

[0148] Fig.74 is a screen shot of a traditional spreadsheet table showing that the table can have controls to filter spreadsheet records based on values ​​in column C, and showing additional filtering of spreadsheet records using formulas (such as a date formula on column H).

[0149] Fig.75 is a screenshot of a target system administration page that supports a web page of type page to an arbitrary external URL and allows a variety of potential sources, not just necessarily URLs.

[0150] Fig.76 is a screenshot of the target system administration page, which shows that webifier has the ability to deeply nest containers.

[0151] Fig.77 is a screenshot of some additional options that the target system supports for editing a record type page with editability from the target system admin page.

[0152] Fig.78 Here is a screenshot of some additional options, similar to Fig.44 As shown in the options, the target system supports insert-type pages, which have the ability to edit from the target system administration pages instead of add-ons.

[0153] Fig.79 is a screenshot illustrating a target system that provides options for restricting user input, also known as data validation, and where data validation rules may be specific to the type of data value being stored.

[0154] Fig.80 is a screenshot showing a target system that provides the option of Restrict User Input where some of the data validation rules reference a cell range in the spreadsheet definition. In this example, the system supports an entire worksheet or a custom cell range within a worksheet.

[0155] Fig.81 is a screen shot of the target system supporting modification of Editable Ranges within various types of pages, making everything outside of that range non-editable. It shows support for aliases using named ranges, such as "Everything", as well as custom ranges specified by the designer.

[0156] Fig.82 is an example of HTML code that the target system can automatically generate in order to have the target page embedded within any external site page.

[0157] Fig.83 is a screenshot of a target system's notification settings page, showing various options for notification settings, support for web service API calls for notifications to external automation systems, and multiple types of notifications.

[0158] Fig.84 is a screenshot of the configuration options for a specific notification target system. It shows its webhook functionality as well as email capabilities via a URL specified by the designer.

[0159] Fig.85 is a screenshot of the configuration options for the target system of the email template for a specific notification.

[0160] Fig.86 is a screenshot of the Target CSV Import page, which shows that Webifier accepts a CSV file during import, is able to utilize metadata about the record tables linked to it by analyzing the spreadsheet, and is able to report the status of the request to insert the record table.

[0161] Fig.87 Yes Fig.86 , but showing that the system is able to enforce and report failures of data validation rules as data arrives via the CSV import form.

[0162] Fig.88 is a screenshot of the target edit cell page, showing the Fig.38 The result of the edit cell page type defined by the add-on interface shown in , and the controls of the target page change any cells that are switched into "edit mode" as a result of user interaction.

[0163] Fig.89 is a screenshot of a target edit record page showing the results of the defined edit record page type, the dynamic notification message to the user as a result of deleting a row, and the control modification to the template table definition that is initiated when the target system determines the "(Edit) Edit" and "(Delete) Delete" permissions, and the page definition has these options enabled.

[0164] Fig.90 When a visitor has clicked Fig.89 A screenshot of the target edit record page when the "Edit Raw" button is selected in the spreadsheet, showing the automatically generated input form used when no template form is defined, and showing that the input form can have controls, such as drop-down boxes, with options defined by the spreadsheet definition.

[0165] Fig.91 is a screenshot of a target insertion page before a visitor fills in any fields in the form, showing that the visitor is notified to confirm that the submission has modified the record form and showing that the target page can display fields with default values ​​and fields that are non-editable based on data validation rules.

[0166] Fig.92 is a screenshot of a complex interactive control displayed to a visitor of a target insert page when the visitor enters data for a field with the associated cell data type set to Date in a spreadsheet definition, such as Fig.91 shown.

[0167] Fig.93 is a screenshot of the target insertion page after the visitor inputs data that fails the validation rules, showing the modification to the interface to notify the visitor of the failure.

[0168] Fig.94is a screen shot of a target system administration page that allows a designer with server administration permissions to control access permissions related to administrative functions of the target system, and for a particular role, further illustrates the ability of a role to have sub-roles with cascading permissions.

[0169] Fig.95 is a screen shot of the target system management page, which shows the various granular management permissions of the target system.

[0170] Fig.96 is a screenshot of a target system administration page showing that a complex password policy with multiple options is possible for visitors logging into or creating accounts on the target system.

[0171] Fig.97 is a diagram showing a source spreadsheet definition, which is designed in a spreadsheet and has a number of formatting options specified by a designer to define the format of content on a target page generated for a visitor.

[0172] Fig.98 is a screenshot of a complex target page involving a tab container with three page tabs, and wherein the first page tab has a nested container page having multiple nested pages, the nested pages including an automatically generated input form from an input page, a report page, and a report page whose source is a chart.

[0173] Spreadsheets and Records

[0174] As an illustrative reference only, this article cites two spreadsheets using styles. Other styles may also be used. The first is a table record, where each record includes an entire table or an entire file that accommodates multiple tables. For example, many companies save a new sales quote spreadsheet file for each sales quote they create. The file may have multiple tables, such as one table for back-end calculations and one table intended to have a printable quote, and the printable quote table typically has multiple rows and columns, which contain text, numbers, and formulas, which make the text or numbers represent a single quote. They may also contain enough formatting to allow for a fairly readable printout.

[0175] Then there are row records and row record tables, where multiple records are specified together, usually in a single table (e.g. Fig.53 , columns A to D). A spreadsheet file can contain zero, one, and / or more row record tables. A row record table can be limited to a portion of the table (e.g., Fig.60 , columns A to B), which may have additional rows of records or unrelated data (e.g. Fig.60, column E), but for simplicity, we'll still call it a row record table as a whole. For example, many companies will have a spreadsheet with a list of clients that looks something like the following. The table may contain some column names in the top row 1, such as "Client" and "Industry" and "Client ID". Each row below can contain data related to a single client. For example, row 2 may have "Pleasant solutions" in column A, "Software Development Services" in column B, and a "1" in column C. Row 3 may have "NewCo" in column A, "Consulting" in column B, and a "2" in column C. Despite the name, a row record also means a list, and the purpose is to flip the rows and columns so the records are listed from left to right, just like the column records.

[0176] During steps 201 through 203 above, either or both techniques may be used, allowing the use of webifier software to create complex, multi-user, fully customized target software with no predetermined or pre-implied purpose. Standalone applications, web applications, or mobile applications (for convenience, web and mobile may be collectively referred to as "web applications") may be created without the need for programming or database experience. Some aspects described herein may be intended to replace the need to hire a custom software development company, even for complex scenarios involving data, custom user interfaces, varying viewing device capabilities, concurrent use, data and user workflows, automatic notifications, scheduled events, business logic, and more.

[0177] Template table

[0178] With reference to an illustrative implementation, steps 201 and 205 are now described in more detail with respect to a user interface template. Whether done in a traditional spreadsheet application, a cloud and web-based spreadsheet, or a simplified spreadsheet interface embedded within a web application, a spreadsheet tool can be used to make a spreadsheet that represents the desired user interface for a visitor or report layout. For example, a template can have a table with multiple rows and multiple columns containing values ​​and formulas related to a single quote: a large font text header of the customer near the top, followed by a row and cell to the right with the first value being "Invoice Number", and the cell to the right of it will be used to display the actual invoice number. The following rows can have cells that span multiple rows and multiple columns and are used to display comments in italics. Near the bottom of the table, a cell labeled "Subtotal" and the cells to its right have cell formats to specify currency with a "$" symbol and are intended to have a number representing the total for that particular invoice (where the cell has a position of C15 on the grid). The next few rows could have cells related to taxes and after-tax totals, but instead of intending to have the values ​​placed there later, the tax and total cells would have spreadsheet formulas that reference the subtotal cell location, such as "=C15*10%", where changes to C15 were intended to affect totals based on those formulas.

[0179] Template tables can be nested, such as in a many-to-many relationship between a parent template and nested templates, and / or nested to multiple levels of depth ( Fig.76 ). According to one aspect, the webifier software may define a spreadsheet formula function, such as "USETEMPLATE()", which accepts a cell range reference or template identifier of what template table should be used. The cell in which the formula is used may implicitly provide some information, such as the location at which a nested template table is inserted based on the location of the cell, or the width and height assigned to the nested template based on the cell size. If the source records used to populate the template are different from the parent template, parameters may be passed into the USETEMPLATE function which will allow the template to know which source data is appropriate for certain page types in much the same way as non-nested template tables (as further described below). This allows scenarios such as an invoice template which contains a list of "invoice items" that need to be populated from records on different record tables. Typically, such a nested template table would utilize the traditional spreadsheet "merge cells" function to allow it to be displayed with sufficient width and height without affecting the row or column alignment of the parent template, as well as utilizing traditional table functions to provide record filtering (as described below) to only those invoice items that belong to a specific invoice.

[0180] The Webifier software may allow template table cells to have formulas that reference other cells within the template table, cells within other template tables, record table data, or a mixture of all of the above within the same cell. The described techniques also allow one or more templates for input functions and one or more different templates for output functions to be simultaneously made available to a visitor of a target system, even if the underlying source references are the same or partially overlapping. Formulas are typically declarative in nature, but any formula or equation supported by any spreadsheet tool and / or new format or syntax may be used.

[0181] Table templates with recording formulas (template instructions) or reference table data

[0182] When designing a template table in a spreadsheet tool, cells intended to represent a specific record or an aggregate value derived from multiple records may evaluate to empty and / or have a placeholder value that has no effect on the system rather than indicating a format to the designer. An external list or database may contain cross-references to cell locations (such as C15) and their intended use, such as displaying a subtotal for an invoice. Alternatively, if record table cells are referenceable from a template table, formulas or placeholder formulas may be inserted in those cells that reference those records. For example, a client header cell on a template may have a formula such as "=ClientList!A1", which may be evaluated by the spreadsheet tool at design time as a placeholder to the text "Client", or the formula may be "=ClientList!A2", which may be resolved by the spreadsheet tool at design time as a placeholder to "Pleasant Solutions". Additionally, a ref may be "=ClientList!A:A" or "=ClientList!A1:A1000", which results in an empty, erroneous, or unexpected value. In each of the above cases, the text produced at design time is merely a design-time placeholder and is not as important as the formula itself, which has useful information about which column in the ClientList to use to fill in the template to assist the webifier, such as a visitor, during the actual evaluation at a later point. For convenience, this concept may generally be referred to as template directives.

[0183] The template indication formula can be typed in by the designer or obtained by dragging and dropping from a list of available or relevant data columns. Formulas are typical for cell references in spreadsheet tools (such as "=ClientList!A1" or "=ClientList!Client" in the case of a named table), and typical function call syntax triggers specialized actions for this purpose ("=GetRowRecordData("Client(customer)")"), or they can be a specialized separate format, such as "[[customer]]". The formulas can be stored in typical cells of a spreadsheet, or they can be stored in a special external list of formulas, along with information about which cells they will be applied to.

[0184] Multiple data sources

[0185] When the designer wishes to have a master record set in a row record table and one or more "auxiliary records" (auxiliary data sources) in another table, multiple relationships between the record sets are possible. When the designer specifies one or more auxiliary data sources, the webifier may ask the designer to provide additional information to determine the type of relationship in order to determine how to populate the references found within the template worksheet.

[0186] If the template uses only a single record from the auxiliary data source and may remain the same even if the selected master record for populating the template varies, the system can allow the designer to specify one or more criteria for selecting a single auxiliary record from the auxiliary data source to be used by the template. For example, if the master record set is a list of invoices and the template is the presentation of any specific invoice selected by the visitor, the designer can specify an auxiliary data source of the "Logo" table to provide the company name and logo to adorn the template with the company that issued the invoice, and require the criterion for auxiliary column A to be set to "Invoice Logo" such that only one record from the Logo table will be used.

[0187] The criteria can be based on the selected master record, or reference data from the master data source in the same way as the template, or indirectly reference cells on the template that reference the master data source. For example, an invoice template may have an auxiliary data source from the "Logo" table where the criterion will be set to require auxiliary column A to match the "Customer Country / Region" such that the template will use 1 auxiliary record but vary depending on the invoice selected.

[0188] If the template will use one or more records from a secondary data source, the criteria may result in multiple records being selected from the secondary data source. A reference to a data column on a secondary data source may be evaluated as a concatenation of several record values ​​(only the first record value found), or the template may specify that each of a plurality of cells is filled with one of the secondary data values. The template may be specified by referencing a secondary data source within a cell that spans multiple rows or columns. Optionally, the template may specify a nested template for defining the presentation of the secondary data values. Nested templates may be specified by using a spreadsheet formula function (such as "USETEMPLATE()") within the template, by dragging and dropping a floating or anchored control onto the template, by specifying the location within the template to insert the nested template using options in a webifier associated with the template, a combination of the above, and / or other means. The boundaries of a nested template may be specified by merging cells, as an argument to the USETEMPLATE function, specifying options outside the table but within a webifier associated with the nested template or parent template, color coding the cells, dragging borders around cells, and / or selecting a cell range designated as representing the boundaries of the nested template. Nesting of templates may result in cells being inserted and existing template cells being shifted down and / or to the left to provide appropriate space to display all applicable auxiliary data values.

[0189] Autogen and virtual tables

[0190] According to some aspects, webifier can automatically generate tables based on other tables and / or templates to facilitate the work of designers. As described above, a row record table can organize potentially many records in a single table. A row record table automatically generated by webifier or a spreadsheet application can be referred to as an "autogen" table. Although described herein with reference to webifier, a person of ordinary skill in the art will understand that an autogen table having the following features further described below can find use in spreadsheet applications other than webifier.

[0191] Webifier can automatically generate row record tables ("autogen" tables) in response to various designer actions, including but not limited to selecting multiple data sources when defining a page, referencing multiple data sources within a template or indirectly through nested templates, identifying relationships between row record tables and / or through explicit requests. In response to appropriate designer actions, Webifier can generate autogen tables in a variety of ways as further explained below.

[0192] Designer-defined and / or autogen tables can be fully virtual, with each cell having the properties described below, or they can be partially virtual, with only certain properties and / or only applying them to certain cells, columns, and / or rows. Traditional spreadsheet applications typically allow two methods of using one cell when defining another cell. These traditional methods can all be called "non-virtual." In the first method, if cell A1 is defined as "=1+Z1", and B1 is to be based on A1, the traditional spreadsheet tool will allow the user to copy and paste the definition of A1 to B1 (by defining a one-time use), so that B1 will now have the definition "=1+Z1"; however, in this case, subsequent changes to A1 will not be reflected in B1. In the second type, a traditional spreadsheet tool will allow a user to enter the formula "=A1" into B1 (for reference use), and subsequent changes to A1 will be reflected in B1; however, there are situations where the formula in B1 becomes complicated for the user to declare, read, and maintain, such as when the selection of cells to use varies depending on a lookup result or relationship, in part because the entire selection and use of cells is being defined within B1's formula. In both non-virtual methods, any modifications to B1 will not automatically affect A1, and formatting changes will not be reflected in either direction.

[0193] The aspects described herein may allow a third method of using cells to define another, which may be referred to as a "virtual view" or simply a "virtual" reference method. In this virtual reference method, a cell used as a virtual view of another cell may be automatically synchronized to display the contents of the corresponding cell, and all changes may be equally reflected to the corresponding cell (such as changes in the formula of the cell, formatting, or values ​​updated in response to changes to the referenced cell). In the virtual method, subsequent changes to A1 may be reflected in B1, and the user is not so complex in maintaining B1, changes to B1 may be reflected in A1, and because changes to B1 may affect A1, there may be multiple virtual views that can change any number of views to automatically affect all views. Like the autogen table, a person of ordinary skill in the art will understand that the virtual references and virtual tables described herein have uses and applications beyond the webifier exemplary implementation discussed here. When defining a virtual table, the designer can specify options that control whether all, partial, or no formatting changes are reflected in the virtual view, as well as options that control which direction changes are allowed to be reflected or overwritten using direct data values ​​or formulas so that the overwritten cell is no longer virtual.

[0194] According to some aspects, a designer-defined and / or autogen table can be a virtual table with cells that provide virtual views for other cells in a spreadsheet. The contents of the virtual table are automatically synchronized to be the same as the cell contents of another table and / or the same table. The virtual table may include or consist of indirect views of other data sources (such as one or more other row record tables, external database sources, and / or instances of table records) and / or columns with direct data values ​​and formulas. According to some aspects, the virtual table can be stored persistently and / or temporarily independently of other data sources as a completely redundant copy of the data, can be stored with any direct data only as a definition of the view, can be displayed to the designer for easy reference and may not be stored at all, or can be completely hidden from the designer until the designer instructs to view them. The virtual table may exclude and / or hide columns that are repeated between multiple data sources. Using any number of common detection methods that are obvious to those skilled in the art, any redundant copies of source data in the virtual table can be static, updated periodically, or can be automatically updated in real time when other data sources are detected to have changed.

[0195] Autogen and / or virtual tables may and / or may not allow extensive modification by the designer, including adding new columns with data or formulas that reference the rest of the table or other tables, deleting columns, changing formatting, adding new records, and / or overwriting specific record values. They can be used as a foundation for designers to build on, that is, cells can be referenced individually as regions or in an aggregated manner by other tables, formulas, or page definitions.

[0196] When an autogen and / or virtual table has instances of a table record as its source data, webifier may obtain from the designer a list of cells or regions from the table record to be included in the new table. Additionally and / or alternatively, webifier may also analyze the table record to locate all cells of a specified color, such as cells with an orange (or other color) background, typically suggested by spreadsheet tools as indicating input cells, and then automatically select those cells to be included in the new table. webifier may allow an import functionality where the designer may select and / or upload any number of spreadsheet files, or select any number of specific tables from one or more files, and all imported tables will have the selected cells represented on the new table. In the absence of a virtual view, a traditional alternative to, for example, aggregating all B10 cells of many table record instances is to manually create a potentially lengthy formula, such as "=InstanceA!B10+InstanceB!B10+InstanceC!B10+InstanceD!B10..", which may often run the risk of being out of date if the number of instances changes. A virtual table or virtual column may analyze or monitor the existence of data source instances to automatically add or update additional virtual cells.

[0197] Webifier may skip importing a table, or warn the user about importing a table, where there are any inconsistencies or differences in content other than cells converted to a table of virtual row records. Webifier may analyze the inconsistencies and determine one or more that do not affect the import, or that the import can accommodate the inconsistency. For example, font size and line width may be inconsistencies that are ignored. A variation in the number of blank rows between an expected 2 and an actual 3 may cause Webifier to remove or skip the extra rows so that important content can proceed, whereas an inconsistency between an expected 1 and an actual 0 may not be ignored. There are a number of standard spreadsheet diffing methods on the market, which typically involve analyzing the position of a number of content cells relative to another table: these standard methods can be used to pinpoint cell content that has changed absolute position compared to a previously imported table, but is still able to reliably locate and extract the value. When using this standard analysis, Webifier may ignore the value difference of the cell in question and focus on the positioning of nearby cells, so that the cell in question can still be located even if the value is consistently inconsistent.

[0198] Likewise, page definitions may also be autogenerated and / or virtual. Autogen pages and / or virtual pages may use either designer-defined templates or autogenerated templates, and pages and templates may use manually defined, autogenerated, and / or virtual tables as sources. Triggers for generating page definitions and / or templates may be similar to triggers for autogen tables, or may be triggered as a result of the creation of autogen and / or virtual tables. Optionally, when a designer creates a page associated with a record table, webifier may present a list of previously defined templates in order from most relevant to least relevant. Relevance may be determined by analyzing whether the table is a template that has been used with a different data source (indicating lower relevance), whether the table is structured like a traditional row record table with minimal formatting and columns rather than values ​​of a consistent type in the template (indicating lower relevance), and / or the similarity of the content of the intended table to the selected record table of the page (such as the number of words that appear in both the column headers of the row record table and the cells of the intended template table).

[0199] The automatic generation process of a page or form may or may not involve certain human factors, such as confirming with the designer whether to perform the automatic generation, requesting additional information from the designer, and / or explicitly confirming the result by the designer.

[0200] Webifier can provide a read-only virtual autogen table for current user information (such as current visitor username, current visitor attributes, roles, last login time, etc.), including user-specific or browser session-specific attributes such as the browser brand used. This will allow any formulas on other tables to reference information about the current user. A similar approach can be applied to other contexts available to the target system.

[0201] Webifier can provide a read-only virtual autogen table with change history, where each row within the virtual table represents a change, each column represents any single cell on any table, and several cells can have history available within the same virtual table. One of the columns can be a timestamp column to represent when the value of that cell occurred.

[0202] Template instructions

[0203] As an example, a designer may create an insert template "InvTemplate" for invoices in a spreadsheet tool without any reference to a data source, but with an indication of where a reference to the data source will be added. The designer may reference this template to create a detail type for a page. Webifier may recognize all the indications in the template and may automatically generate a virtual row record table VirtInvSheet without further intervention by the designer, wherein a column with a column name such as "InvTemplate_A1" is assigned to each indication if the A1 cell of the template has an indication. Webifier may also automatically generate a virtual report page using the VirtInvSheet as a data source and using a column of navigation links for the visitor to navigate to the details of any selected invoice. If the InvTemplate has a nested template for entering invoice items, Webifier may automatically generate a second virtual row record table VirtInvItemSheet with columns related to the indications on the InvTemplate, the indications previously specified by the designer being unique for each invoice and may be considered a master key. Webifier may then also generate a virtual report page using the VirtInvItemSheet as a data source and using similar navigation links. Webifier can also automatically generate templates and pages based on columns within VirtInvSheet or VirtInvItemSheet using aggregate formulas to make the automatically generated pages report the sales frequency of each invoice item and the number of invoices inserted per month in this example. The designer can then reference or modify all or part of the automatically generated virtual tables and reports to continue building other tables and pages.

[0204] For nested row record tables, as an alternative to identifying the primary key columns of the parent data table to be referenced by the nested data records, the webifier can have automatically generated and / or virtual columns in the nested table corresponding to each column of the parent data table, in fact each column or each column that meets basic criteria (such as data type or length) that may be part of the primary key. The parent template utilizing the nested table can automatically filter the nested records to match the parent record displayed by the parent template using criteria for all of these columns on the nested records. As another alternative, when the designer provides criteria in the method described above to reference and filter auxiliary data sources including nested data sources from the template, the columns for filtering the auxiliary data sources specified in the criteria can be used to indicate to the webifier which columns should be added to the nested table.

[0205] Designer defined, autogen and / or virtual tables may have additional automatically generated metadata associated with elements such as tables, columns, rows or individual data values. The metadata may include or consist of a unique identifier for the element, a timestamp, an identifier of the associated page, or an identifier of other tables of interest. The metadata may be used to identify the trigger or purpose of the element's creation, to cascade the modification or deletion of other elements, to be used as a foreign key to other elements, pages and / or tables, to be used as a master key for the element, or as a basis for further analysis or reporting of the relationship between the element and other pages and / or tables. Such metadata may be visible to the designer as an additional column or row for ease of reference, and / or may be further used by spreadsheet formulas, page definitions, or in any manner using other spreadsheet data values. Optionally, the metadata may be accessible to the designer by means of a "RESOLVER" spreadsheet formula. Such metadata may also be used to detect potential ambiguities in relationships between records and / or to trigger the collection of further information from the designer or visitor.

[0206] As an extension to the previous example, a VirtInvItemSheet may actually have all the columns of a VirtInvSheet, such that the designer does not need to specify any primary key columns. Webifier may add a virtual column to the VirtInvSheet with a GUID that serves as the primary key for the invoice, and add a column to the VirtInvItemSheet to serve as a foreign key to that primary key. When a visitor creates an invoice item using an InvTemplate on the page, which in turn uses a VirtInvSheet, the foreign key column of the VirtInvItemSheet will be automatically populated with the VirtInvSheet primary key. Webifier may then use the foreign key and primary key columns in a typical manner known to those skilled in the art of relational programming, or selectively confirm that an invoice item is associated with an invoice, such as if there are two invoices with all values ​​identical, such that including substantially all of the columns of the VirtInvSheet into the VirtInvItemSheet is not sufficient to uniquely identify the invoice to which the invoice item belongs. Webifier may then warn the designer that the invoice lacks uniqueness due to data integrity issues or data structure reasons, and suggest various recommended best practices to the designer.

[0207] Automatic detection of relationships

[0208] Webifier can automatically detect the existence, type, and / or details of relationships between record tables and between data sources in general or within a specific context (such as one or more templates or page definitions). Webifier can use or analyze any combination of available information (which can be referred to as "clues") to assist in the automatic detection of relationships, including designer actions, visitor actions, record data entered by the designer or visitor, record structure, formatting on record tables, template structure and formatting, page definitions, global whitelists, static blacklists, relationships declared to spreadsheet tools or spreadsheet tool plug-ins that support it (such as Microsoft PowerPivot), and / or others. Multiple clues can be analyzed together using various static or dynamic weighing algorithms or point systems to improve the reliability of the analysis conclusions. The reliability level can be measured by many characteristics of the clues, including the number of available clues and the consistency of each clue's conclusion. For example, clues can be combined to obtain a reliability level, such as by counting the number of clues that indicate a specific relationship minus the number of clues that indicate a different relationship and / or the lack of that specific relationship. The reliability level can be used to determine whether a relationship should be silently accepted by the webifier, the designer should explicitly confirm the conclusion as a recommendation in one or more recommendations ranked by the reliability level, or the analysis should be ignored because it is insufficient to draw a conclusion.

[0209] Clues can include parameters supplied to spreadsheet functions, such as "VLOOKUP()" or "COUNTIFS()". For example, a template or row record table containing the formula "=VLOOKUP(DetailSheet!C5,ParentSheet!B:F,3,false)" can indicate that column C on the DetailSheet is a foreign key to the primary key column B on the ParentSheet. Clues can include formatting of spreadsheet cells, such as text or number formatting. For example, values ​​formatted with dollar signs would typically indicate that the column containing the dollar sign is neither a foreign key nor a primary key, and indirectly indicate that neither is any column that uses those dollar value columns in a way such as an aggregate. Clues can include column naming, even if there are zero records in the record table. For example, a column named "uid" or "invoice number" is more likely to be a key than the blacklisted column name "comment". Clues can include the uniqueness of the data contained in the column. For example, if a column always has the same value, it may still be a foreign key, but it is not a primary key and is unlikely to be part of a composite primary key. Clues can include the length of the data within the column. For example, a memo column that typically contains multiple words is unlikely to be a key, even if each value is unique, as is a blank column for some values. Clues can include consistency of data within a column. For example, if a column has both numeric values ​​and certain words, it is less likely to be a key column than a column with consistent data types and lengths. Clues can include defining a template as having nested templates or referencing nested tables. For example, if an invoice has a list of invoice items, and the designer indicates that the invoice items displayed by the nested column "BelongsToInvoice" that matches the InvoiceID that matches the currently displayed record are to be filtered, then BelongsToInvoice is likely to be a foreign key, InvoiceId is likely to be a primary key, and the likelihood that an association exists between invoices and invoice items is increased by the fact that one is used in a nested manner relative to the other. Clues can include analyzing a virtual primary key and several non-autogenerated columns to identify the columns that are most closely associated with the virtual primary key. Clues can include counting occurrences of visitor searches that specify a particular column or specify a value that matches a value in a particular column. For example, on a list report page, if visitors frequently search for text such as "Inv0511", the column containing values ​​such as Inv0511 is likely to be the primary key column. Clues can include the order of the data. For example, a column with values ​​from 1 to 100 without any gaps and the presence of similar columns on different tables (where both tables are used by the same template) indicate that the two tables have a relationship and that the two columns also have a relationship. Clues can include the distribution of values ​​of a column on one table relative to a column on a second table.For example, if table F column A always has a 1:1 distribution of values ​​on table S column A, then the second table S is likely a required detail record for records on table F. If it has a 1:0-1 distribution, then it is an optional detail record for records on table F. If it has a 1:0 distribution with all other columns, then there is probably no relationship between table F and table S. Clues can include the positioning of columns. For example, the first column in a table of records is more likely to be a master key than a middle column, or columns that are immediately adjacent to each other are more likely to form a composite master key than columns that are farther apart.

[0210] Further Uses of Relationships

[0211] Many of the traditional formula functions of spreadsheet tools can be used to reference information from secondary data sources. Webifier can use recognized relationships, such as those that are automatically detected, to further simplify functions and methods for accessing data from secondary data sources. As an extension of the previous example "=VLOOKUP(DetailSheet!C5,ParentSheet!B:F,3,false)", if the relationship between the detail table and the parent table has been defined, then by any means, Webifier can allow a cell on the detail table (such as Z5) to use the simplified function "=WEBIFIERVLOOKUP(ParentSheet!D)" if called from the detail table, because the relationship between the detail table and the parent table is known, as is the relationship between the foreign key in column C of the detail table and the primary key in column B of the parent table. Function names may differ from this example, or they may be overloads, sharing the same VLOOKUP() function name, but with optional parameters.

[0212] Another example is the optional version of SUMIFS, which is particularly suitable for composite master keys. Similar to the traditional usage of "=SUMIFS(DetailSheet!F:F,DetailSheet!A:A,CONCAT('=',C5),DetailSheet!B:B,CONCAT('=',D5))", in this example, the aggregation of column F on the detail table is limited to row records where the composite master key of C5:D5 matches the composite foreign key of DetailSheet!A:B, which can be replaced with the optional version "=SUMIFSWEBIFIER(DetailSheet!F:F)" to achieve the same result.

[0213] Similarly, functions may be added that indicate parameters to filter before passing data to traditional functions. For example, instead of SUMIFSWEBIFIER, webifier may evaluate "=SUM(FILTER(DetailSheet!F:F))" as equivalent. In order to filter based on a key as well as additional criteria, the FILTER function may accept additional parameters such that "=SUM(FILTER(DetailSheet!F:F,DetailSheet!C:C,'>100'))" will still filter by the composite key, but will also filter by column C having values ​​greater than 100. The format of the additional parameters may match the syntax seen traditionally, such as in SUMIFS(), may be a SQL-style query string, may be an optional syntax used in other commonly used software, or may be webifier-specific, such as "=SUM(FILTER(@F,@C>100))". Optionally, as is the case throughout webifier, ranges may also be specified by traditional structured references such as "TableName[ColumnName]". Webifier can then process this as part of its formula parsing engine, can convert the syntax to legacy syntax before storing in the spreadsheet tool, can require quotes to make it partially compatible with the spreadsheet tool's expected syntax, or use differentiation techniques handled in the spreadsheet tool for the designer to preview and process in the target system.

[0214] In the case of page types that involve visitor input, such as insert or edit pages, relationships defined by any of the foregoing methods may be used to allow pass-through insertion or editing of records on a secondary data source, as well as automatic population of foreign key columns for records inserted into the secondary data source. Any indication on an input page template that contains a formula that references a specific record of a secondary data source utilizing a relationship but is also specified as being rendered on the page as an input control, may result in a referenced record table cell on the secondary data source being edited or a new record being inserted into the secondary data source. For example, in the case of a relationship between a table of records InvSheet and a table of records InvSheetExtended, where each record on InvSheet has a corresponding record on InvSheetExtended, when a visitor submits an insert page template that references both InvSheet and InvSheetExtended to the target system, Webifier inserts one record into InvSheet and one record into InvSheetExtended, and any columns associated with the key defining the relationship between them will be automatically populated. Similarly, if the insert page template references a virtual table InvSheetCombined that includes InvSheet and InvSheetExtended columns, the webifier will resolve the virtual reference so that one record is inserted into InvSheet and one record is inserted into InvSheetExtended. In cases where pass-through editing is possible and edits to records that the primary data source should reference on the secondary data source, the webifier can obtain additional information from the designer through formula function parameters, page options, and / or through pop-up information requests to the designer. For example, on the InvItemTemplate detail template used to edit a record page, if a cell has the formula "=WEBIFIERVLOOKUP(Invoices[InvName]))" and the appropriate relationship declared in the webifier, or a traditional VLOOKUP where the relationship is explicitly described in the function's parameters, a visitor editing the template cell using that formula may intend to change the parent invoice to which the invoice item belongs, or may intend to pass-through edit the invoice name. The webifier can then have an "Allow pass-through editing" option on the page that the designer can set to determine which change occurs when the corresponding input control of the template cell is edited. If the edit changes a record that the primary data source references on a secondary data source, the webifier may change the type of input control that appears on the target input page to facilitate input and / or restrict the values ​​that the control will accept to those that are valid under the given relationship.Continuing with the previous example, instead of a text control suitable for entering an invoice name, the rendered control could be a drop-down list with a list of all invoice names, where selecting an invoice name would change the parent of the invoice item to the invoice associated with that name.

[0215] Spreadsheet definitions go into the target system

[0216] Once the spreadsheet is configured with the appropriate data as needed, refer to Figure 2 Step 207 may further include the designer initiating the process of building the target system using the webifier software, which is described herein with respect to one or more illustrative embodiments. The user may log into the target system ( Fig.13 ) and access an administrative web page that accepts spreadsheets. For example, the webifier software may have a page that accepts uploading an xlsx spreadsheet file ( Fig.68 ) input form, for example, data 129 ( Figure 1 Alternatively, webifier can be integrated to retrieve spreadsheet definitions from cloud and web-based spreadsheet tools via a web service API, a set of tables at a time (if the API allows) or even cell by cell (if the API requires). For brevity, all of these methods are referred to herein as "uploading", or uploading a spreadsheet definition or data file to a webifier data store, e.g., database 131 ( Figure 1 ). It is worth noting that uploading records and uploading interface templates do not need to be separate steps, they can happen in one step from the designer from a spreadsheet definition, or they can happen at different times.

[0217] The webifier may temporarily store a spreadsheet definition, replace it with an alternative and more efficient format, or replace it with an intermediate format that is or is no longer considered a spreadsheet. An example of achieving a balance of efficiency and simplification is converting a table into a database entry, where each cell in the table is a database record in a database table with 4 fields representing the location of the cell and content: table, column, row, content. The content may match the original schema of the spreadsheet definition, but the cells may be efficiently looked up in the database. The system may also store it permanently in the format received, thereby preserving visitor input on the target system page by directly modifying the spreadsheet definition, as described herein.

[0218] Creating a Web Page

[0219] As described above with respect to step 209 ( Figure 2), the resulting software may include a web application that includes one or more web pages, discussed herein with respect to an illustrative implementation. The designer may create a web application in the webifier software's management interface ( Fig.18 , Fig.69 and Fig.55 ) and choose the Add Page function from the various page types ( Fig.51 ) and configuration options ( Fig.52 ). Cell data or metadata in the spreadsheet definition may contain instructions or directives for creating new pages, what types of pages the designer anticipates, and their various configuration options; however, in the absence of this information, the designer may be prompted. Various page types may be created on the target server. Typically, menus for all generated target pages are displayed as menus on all target pages to facilitate easy navigation throughout the Web application ( Fig.66 , the "Timesheet" web application menu lists all three target pages for this particular example), and the target pages can be grouped by "apps", which are usually grouped by which spreadsheet definition or file they came from and / or by the designer who created them (the logged-in designer, or other designers who shared the spreadsheet definition with the logged-in designer, or other groupings) ( Fig.16 ). Designers can modify the order of target pages in the target system menu by dragging and dropping the pages to change their order in the add-on.

[0220] Add page (report page) + visible source range

[0221] One illustrative type of web page that the webifier software can create is the "report" page type. The title of the report can be specified by the user or generated from spreadsheet data or headers ( Fig.52 ). You can also specify a source. The source can be the name of a table in a spreadsheet definition (similar to the source control of the CSV_Import page type, such as Fig.33 ), a typical spreadsheet cell range reference such as "=B9:C12" ( Fig.36 ), the chart name defined in the spreadsheet ( Fig.52 ), or a spreadsheet "named range" (sometimes called a "defined name"), which is a range of cells that can be referenced by a name, such as "Total Column" or "Printable Area" or "Employee List:Employee" ( Fig.36 ). In general, ranges do not have to be contiguous blocks, they can include multiple blocks and cells. For example, "A1:A8, A10, A30:B30". Many pages can reference the same source cell, a source cell can partially overlap with a source from another page, or a page can have a series of sources to be combined. This can be a sufficient configuration for the target system to display an HTML web report or mobile screen to a visitor based on a report designed by a designer in a spreadsheet tool. For example, a ClientList table can be specified, where all row records in the list are now accessible to the visitor. Many optional configurations can be specified, such as "Show Page Title" ( Fig.28 ). You can also specify permission rules on an application or page to control whether you need to log in to view the page ( Fig.24 ) which logged-in users or user groups or roles are allowed to view the page and from which devices, networks, or times of day. Fig.25 A simplified example is shown which shows which logged in users are allowed to view on the page. The end result is a web page displaying the report that looks similar to the original template spreadsheet that provided the data, including the layout and format of the data ( Fig.97 ) without requiring the user to have relevant knowledge of web design.

[0222] The source data of the target page, rather than a cell range, may be linked to a specific element within the spreadsheet definition, such as a chart positioned to float over a cell.

[0223] Some page types may have options that automatically add several pre-configured controls to the target page. For example, "allow search" ( Fig.48 ) or an "allow PDF export" (not shown) option is available on certain page types (such as the calendar page type), which may result in the target page having not only the expected calendar, but also multiple controls such as a search text box and a search submit button to draw the user's attention to specific data through methods such as scrolling and focusing search results. The processing of search results can be performed on the client or server side.

[0224] Add insert page + default form generation

[0225] According to an illustrative embodiment, another type of page that a webifier may create is an "insert" page type, such as, for example, in step 211 ( Figure 2 ),above( Fig.43). Similarly, the source of the ClientList can be specified, and if the designer wishes, indications of rows or columns can be inserted by the visitor. The cell range representing the header row can be automatically identified or detected and presented to the user for overlay ( Fig.39 ). For example, you can base the cell's position on the Fig.40 The target system can provide a list of other pages as options to automatically redirect the visitor after the visitor completes the insertion ( Fig.44 ). The target system can then display an automatically generated input form to the visitor, with labels based on the columns in the header row next to their respective input controls, and allow the visitor to insert the data ( Fig.91 ). The formatting of cells in the spreadsheet's ClientList may affect the cell labels and html formatting of input controls on the automatically generated input form. The label of each input field can be determined by the "Header Source" and is usually the first row of the source range, a defined name or a user-selected range.

[0226] In addition to specifying restrictions in the spreadsheet definition, the input types on the form can be manually specified or further restricted by the designer using the "restriction user input" option for each field on the More Settings tab for the add-on or target system administration page ( Fig.78 ): For example, User of Page and Current Date restrictions ( Fig.44 ) may display a non-editable field containing the logged-in user's name or the current date filled in ( Fig.91 ); some columns may have formulas copied from previous records and are also not editable; "Whole Number" and "Decimal" have current number inputs that only allow numbers of the selected type; "List" can display a drop-down box input containing values ​​in a specified range; "Date" displays a calendar for selecting a date ( Fig.92 ), "Time" displays a 24-hour time picker, and "None" and "Text Length" display a text field.

[0227] Add insert page + custom UI (template source)

[0228] According to an illustrative embodiment, if the inserted page has more complexity or customization requirements, the designer can specify a "Template Source" ( Fig.35 ). It will still perform an insert into the customer list, but the input form UI can be defined by the template source. Whenever a template directive is found on the template source, the display of the cell can replace the automatically generated input control, where the format of the input control is taken from the format of the template directive cell.

[0229] Add details page + details list navigation

[0230] According to an illustrative embodiment, another page type is a "details" page based on specifying a record source and a template source. The target system can place controls on top of the results of presenting a templated report, which allows the visitor to select the records to which the template should be applied. As an alternative, the details page can be used in conjunction with the report page. Both the report page and the details page will have the ClientList spreadsheet as the source. The ClientList spreadsheet may have an added column named "Go ToDetails" and each row will have a formula such as "=NAVIGATETO('DetailsPage', A3:Z3, Blue, LinkStyle)", which will indicate to the target system that when displaying the main report page listing all customers, a hyperlink-style navigation control should be visible on each row, and the rows will take the visitor to the DetailsPage populated by row 3 or the corresponding row that was clicked. As another alternative, a configuration option during report page creation can prompt the designer to specify the navigation links to be generated rather than specifying them directly within the ClientList spreadsheet. The format of cells evaluating record values ​​on detail pages can be a mix of the template indicated cell format combined with the format for any given row of cells that populate the template indicated cells with record data, with one format overriding the other based on the target system default behavior and the designer's selected preferences, with the format taking precedence over the designer's selected preferences if both cells have conflicting formats with combinable formats (e.g. a color change on a record combined with a larger font size on a template). The target system can also analyze records to ensure that the most common formats do not override template formats, but some rare and therefore intentional format changes can be allowed to override template formats.

[0231] Adding edit cell and edit record pages

[0232] For a visitor to edit a cell within a target page on a target system, a designer may create an edit cell page according to an illustrative embodiment ( Fig.38) or edit a record ( Fig.41 ) pages. They can be created similarly from the Page Creation web page by entering a title and selecting the page type "Edit-Cells" or "Edit-Record". The Edit-Cells page allows the visitor to edit a single cell, but displays a page very similar to the Report page until the visitor begins interacting with visible elements ( Fig.88 ). The Edit Record page allows visitors to browse records ( Fig.89 ), edit and / or delete rows or columns, and can be similar to the insert page with the default table generation ( Fig.90 ). The page source can be a range of cell values ​​to be displayed on the page, and by default, a second range that specifies which cells can be edited ( Fig.42 ) matches the page source so that all cells are editable. Ranges can be entire worksheets, defined names, and / or custom ranges. The target system queries the source range to display every cell with data and the cells in between that have data. Both edit pages can also have additional rows and / or columns of empty space to allow the user to expand the record set by inserting. A defined name or custom range will only include cells referenced by that range. The target system can implement the custom range option by creating a defined name after the designer specifies the range. The Edit Record page type also has an additional selection of rows or columns ( Fig.41 ), which determines the edit buttons available on the page.

[0233] In the More section there are additional settings for both types of edit pages ( Fig.39 and Fig.42 ). The designer can specify a Header Range using the first row / column or using a defined name or a custom range. If a header range is selected, the designer can specify whether these headers can be edited by the visitor. The header will appear as the first column of the page ( Fig.90 ) or columns to edit the top row of a record page. The designer can also select an editable range, which must be contained in the main page source. The editable range can be all cells that can be edited by the user. There are also some options to show the page title to allow searching the page, show grid lines between cells, and show row and column numbers / letters.

[0234] For the Edit Record Type page, there may be additional options to allow deletion and editing ( Fig.42 ). Both are enabled by default. This can cause additional controls to be displayed on the target page next to each record ( Fig.89). If "allow delete" is selected and all cells in the row / column are in the editable range, the row / column can only be deleted from the page. If "allow edit" is selected and at least one cell in the row / column is in the editable range, the row / column can only be edited from the page. In this case, according to some aspects, only the cells that are actually in the editable range can be edited.

[0235] According to an illustrative embodiment, when viewing an edit cell type target system page, a user can click on any editable cell (the editable cell range is defined when creating / editing the page) to edit it. The contents of the table cell can be temporarily hidden and replaced with an input element that relies on the cell number format, with the current contents of the cell as the default value ( Fig.88 ). Save and Close buttons are also visible. Text cells will show a text field, number formats (such as decimal or currency) will show a number picker with the option to type in text, dates show a date picker, and times show a time picker. If traditional spreadsheet data validation is set for the cell, this may also affect the input elements that are displayed, with list validation showing a drop-down box, and numbers showing a number picker. Target system validation applied to the page also has options for date, time, or map / GPS. Date and time will show a date picker and time picker, respectively. Map / GPS will allow the user to enter GPS coordinates, or click on a map to generate them. Changing these values ​​and saving will change the value / formula of the cell in the spreadsheet definition in the same way as it would in the spreadsheet tool. If the cell contains a file reference using the WEBIFIERINSERTFILE formula function, the user will be given the option to upload a new file or image ( Fig.62 ). After Save is selected, the new data is sent to the server and a string value is returned to replace the contents of the edited cell. The Save and Close buttons can be hidden, and the cell displays its value as usual. If a file was uploaded, the page is refreshed. After clicking the Save button, the following can be sent to the server: the page ID, the new cell value, the row and column of the cell, the cell type describing the data type, and a Boolean value indicating whether the target system page is embedded in another web page. If a file was uploaded, the file key, file ID, and file data are also sent. After the server processes the edit request, a JSON object is sent back to the client that specifies the new text to be displayed in the cell.

[0236] Add a calendar page

[0237] You can create a calendar page in the add-on ( Fig. 27), but according to the illustrative embodiment, they can also be created through the website. In order to create a calendar page, three data can be used: event date (required, date formatted cell), event time (optional, time formatted cell) and event title (required). The user selects the Add Page button and then selects Calendar as the page type. Two options are available for how to enter the cell range for each parameter: defined name and custom range. Defined name allows the user to select from a list of defined name options extracted from the attached workbook. Custom range allows the user to specify the range by pressing the "Use current selection" button or manually entering the selector. After specifying these parameters, the page can be saved. The date of the event can be the only required field, events without titles can default to "Event", and events without times can default to "All Day". After uploading the changes to the server, the calendar page view will display a calendar widget containing the specified events. The three specified parameter sources are extracted and used to construct event objects consisting of title, date and time properties. These objects are then displayed on the calendar widget, usually as a list of titles within their appropriate dates. The thumbnail on the design page reflects and matches the rendered calendar widget.

[0238] Adding a nested container page

[0239] A container page is a page that can contain one or more instances of another page. They are created during page creation by selecting 'Container' from the Type drop-down box. There are at least three different modes that can be used to create container pages: Easy, Advanced, and Graphical Designer, which can be selected via buttons in the UI ( Fig.29 ).

[0240] Simple mode can render subpages as a grid layout. It may require two pieces of data: the number of columns and the subpages that will be contained in the container ( Fig.29 ). The number of rows in the final layout will depend on how many pages you choose for the container.

[0241] During easy mode creation, each subpage is selected via a drop-down box. The drop-down boxes are laid out in a grid that reflects where each subpage will appear on the rendered page. As pages are added, new blank rows of drop-down boxes may be appended so that more additional pages may be added. There may also be an option to create a new page and add it to the container, which may be saved when the container itself is saved. Likewise, if the number of columns is changed, drop-down boxes may be added or removed to reflect the new number of columns. The number of columns in a container created in easy mode may be limited to a certain number, such as three.

[0242] Advanced mode enables the use of a WISYWYG - "what you see is what you get" - rich text editor that allows more control over the display of the container page ( Fig.30 ). The editor allows advanced formatting such as font size and color, bold text, bulleted lists, etc. Users can include any text data they want in the container. When saved, the text data is saved as an HTML template.

[0243] According to an illustrative embodiment, including subpages within a created container via advanced mode can be accomplished by typing a page name surrounded by the text "{{" and (and) "}}". For example, typing the phrase "{{DataPage}}" will include a page named "DataPage" in the container. When the container page is rendered, the phrase is searched for between the "{{" and (and) "}}" tags in the template. If the phrase matches a valid page name in the system, the phrase is replaced with the HTML that renders the matching page. The advantage of advanced mode is that multiple pages can be laid out with interleaved text without creating multiple text type pages. It also allows for greater control over the final appearance of the container page.

[0244] Graphical Designer mode is similar to Advanced mode, but includes a full HTML text editor and preview capabilities ( Fig.31 ). The editor in the Graphics Designer can switch between Edit HTML, Preview, and Editor views using the tabs located at the top of the editor.

[0245] The 'Editor' tab contains the same editors as the Advanced Mode Editor. The HTML Editor is located beneath the 'Edit HTML' tab. Using the HTML Editor, the user can enter arbitrary markup to allow even greater control over the final appearance of the container page. The 'Preview' tab functionality takes the current markup and renders it, so the user can quickly see what the rendered template will look like without having to save and view the page normally.

[0246] Both Advanced and Graphic Designer have a list of current pages on the right hand side. When you click on a page from this list, the page can be automatically inserted into the template at the current cursor position.

[0247] Advanced and Graphical Designer modes may also have a button to "Import From Easy Mode". It may be necessary to first enter data using Easy Mode before this feature can be used. When pressed, the Advanced and Graphical Designer editors will be pre-populated with a template that matches the original appearance of the container when it was in Easy Mode.

[0248] For all container modes, one or more rules may restrict creation. One rule is that a container page may not contain the same insert type page more than once. A second rule is that a container may not contain itself or another container page that would result in an infinite loop (example of a container loop: page 'A' includes page 'B', but page 'B' also includes 'A'). These rules are checked on save time, and if violated, creation of the container may be prevented.

[0249] According to the illustrative embodiment, Webifier allows both the record list and the user interface for visitor actions (such as inserting) to be largely defined by a non-technical designer within a spreadsheet tool. All of these report types can be sourced as a source or template spreadsheet with formulas representing aggregate data for the records, charts representing aggregate data, or essentially anything visible in a spreadsheet ( Fig.98 ). For example, an insert page could have reference information about previously inserted records, or a chart of all records so far to help the visitor enter the next record. The nature of the relationship between data and interface can be very broadly defined by the designer. The flexibility of using a spreadsheet of source data and a combination of spreadsheets for user interface layout and design is surprisingly powerful, providing new functionality to millions of users with no previous software development experience or training.

[0250] The container page type can also be a tab container type ( Fig.49 ). Rather than positioning multiple subpages on the same page, for example in multiple columns, each subpage will appear as a separate tab when the target page loads to the visitor ( Fig.98 ).

[0251] Nested container pages for formulas

[0252] The container page can have an attachment mode, whereby the definition of the container (e.g., the number of columns of sub - pages to be displayed and what sub - pages to show) can be defined by a container template table. The cells of the container template table can be just the names of the sub - pages or other unique identifiers, and according to some aspects, the number of columns used in the table will represent the number of columns of sub - pages to be displayed on the container page. This provides great flexibility for the designer. For example, in a familiar way consistent with the way data and other templates are defined, the designer can merge cells across multiple columns, such as cells A1:A3, place a border around them, and then set the cell content to the text "Title Page" to indicate that the title sub - page will have a border and span 3 columns. The designer can also use formulas to indicate which sub - pages should be shown to the visitor. A formula such as "=IF(Items.Count(A:A)>50,'Line Graph Page','Bar Chart Page')" will change the displayed sub - page so that when there are many items in the count, a line graph is shown, but when there are few items, a bar chart is shown. A formula such as "=IF(Item.A1<EDATE(NOW(), - 1),'Readonly Page','Editable Page')" can allow editing of records until they are one month old, after which the page will show a read - only view instead of edit controls.

[0253] The webifier can allow the cells of any of various types of templates (including container pages) to contain HTML - tagged text, which will be passed by the target system to the visitor's browser as HTML tags rather than as text. The designer can specify which pages should be rendered using the HTML tags that are detected and passed as tags, such as via page configuration options. Additionally and / or optionally, the designer can specify which specific cells should be rendered by indicating to the webifier that specific cells or regions should be allowed HTML - tag rendering, and the webifier can store such preferences as metadata and / or separately from the table.

[0254] Add CSV Import Page

[0255] According to an illustrative embodiment, the CSV import page ( Fig.86 ) allows a visitor to insert multiple rows from a CSV file into a spreadsheet at once. The designer can select a worksheet, a defined name, or a custom range as the source for the CSV import page ( Fig.33). This source specifies where on the page new data will be entered. It may or may not limit the number of columns allowed in the imported CSV file. For example, a defined name source covering the range $A$1:$C$1 will only allow the CSV file to have 3 columns, and the data will be inserted into the highest available row spanning columns A, B, and C. Alternatively, it may ignore extra columns.

[0256] When using the page, the visitor can be told the expected number of columns to be inserted. They can also have the option to exclude the first row of the CSV file. The visitor then selects the CSV file using a standard file upload control, and submits the page. The page can restrict the upload control to only accept a .csv extension. If the page contains no data or contains malformed data, an error is returned ( Fig.87 ).

[0257] Each row can be inserted one at a time into the columns specified by the source in the same manner as the Insert Records page. Data validation still applies, as described below, so if any value does not meet the restrictions set on the cell, the import will be rejected, restricted, or otherwise modified, and the visitor will be alerted. If the insert is successful, the visitor can receive a success message showing the number of rows inserted.

[0258] Add an embedded spreadsheet page

[0259] According to an illustrative embodiment, another page type that can be specified with a title and a source range is an embedded spreadsheet page. This page type displays an embedded spreadsheet interface to the visitor and allows the visitor to edit the values ​​and formulas of the designer-specified portion of the spreadsheet in the same manner as the designer. The implementation of the spreadsheet control does not need to be comprehensive, nor does it require the visitor to have the ability to reference data outside the spreadsheet. The implementation can use one of many commercially available spreadsheet web controls that accept data in a spreadsheet definition format and, when the visitor saves, produces results in a spreadsheet definition format, where cell formulas or cell values ​​can be easily copied one by one to or from a temporary table provided to the spreadsheet control.

[0260] Additional Page Types

[0261] According to an illustrative embodiment, webifier can support a variety of other page types in a similar manner. For example, similar to the "report" page type described above, but the target system can use a commercially available converter or print to PDF library to perform the additional step of converting or printing the report page's html into a PDF document, which is downloaded and opened by a browser or mobile application when a visitor accesses the URL of the PDF page.

[0262] Another example page type is embedding an external web page in a frame on the target page, where the frame's URL is specified in the page configuration when the page is created, or obtained by dereferencing a specified cell in a spreadsheet definition, which allows it to be provided by a static value or formula. This can be particularly useful in cases where the spreadsheet record data should define the web page that the visitor is directed to.

[0263] Extract Spreadsheet Definition

[0264] According to an illustrative embodiment, once a spreadsheet is defined, the webifier can generate a target system page based on the spreadsheet definition. In an example where the spreadsheet definition is persistently stored on the target system, the process begins by extracting and making available the definitions of cells that fall within the applicable source range ( Figure 2 Step 207 in ). If the spreadsheet definition starts in the xlsx file format, the file can be unpacked to obtain the internal content in XML format. Various XML parsing options (from basic string parsing to complex parsing usually done by calling functions in XML parsing libraries) are used to provide convenient access to iterate all individual data items, including cells and their properties, and load them into alternative or intermediate formats, such as in-memory object-oriented classes and instances. Alternatively, commercially available spreadsheet file format parsers compatible with xlsx, MS-XLSB, SpreadsheetML, ODS formats, and similar schemas can be used to expose the data of the spreadsheet in object-oriented classes that are conveniently specialized for properties and formats typically found in spreadsheet data.

[0265] In examples where the spreadsheet definition is not stored persistently, but rather persists in an intermediate or database format, some of the steps above still apply, but these steps may be performed for all forms on the system at an earlier stage, shortly after the designer provides the spreadsheet definition, rather than on demand as the system receives a request for a target system page. The intermediate format may be included in an earlier stage and partially followed on a per-request basis. For example, an early stage may accept an xlsx file, unzip it, and convert the necessary files into a database record representing the entire form without further parsing. In this case, there are fewer steps during each page request to the target system.

[0266] Conversion of formats and attributes

[0267] According to an illustrative embodiment, during step 207 and / or step 209 ( Figure 2), the data may be converted to an intermediate format, which may make it easier to access relevant data attributes in a class that is more akin to a CSS class or HTML class than a spreadsheet class. For example, the property names on the class might match those of CSS, or there might be methods on the class, such as "ObtainCSSDefinition()". However, the convenience of an intermediate format is not needed, because the webifier may optionally store the data according to the output format of the target page's CSS and HTML, or may store the data in the original spreadsheet format and later stream directly to the output form with no intermediate storage. For example, to handle the width of a column, the value of the "width" attribute of the XML fragment "" may be read, converted to units supported by CSS, and the result stored in a "csswidth" attribute of the column class, or it may be streamed out as "width:100px;" as part of the CSS output streaming functionality.

[0268] Conditional formatting entries found within the spreadsheet definition (which are common to traditional spreadsheets) can cause the target system to perform formula evaluation on the formula that the specified designer associated with the formatting definition of the cell. If the formula evaluates to true when the target system responds to a request from a visitor to the target system, the formatting contained in the conditional formatting entry will also be converted to CSS so that the target page will have differently formatted elements.

[0269] Vary output by page type

[0270] According to an illustrative embodiment, as part of serializing the properties of an object into a stream of CSS text definitions that will be sent to a visitor, the target system may take into account factors such as what type of page and what type of visitor device features are applicable to a given target page. For example, if the page is of type "report", the webifier may output a cell with a border as an html TD cell, such as the following "Cell Text", and some associated CSS, such as "td{border:1px solid black;}". However, if the page is of type "edit details", and the cell falls within a range designated as a cell that can be edited, the webifier may instead output the cell with the " <input type＝text value＝”Cell Text”> "Same cell and different CSS depending on the style of the input control desired.

[0271] Other types of output can be more specialized. For example, instead of trying to render cells, a calendar's page type can be rendered in a string format that can be used by controls such as the calendar control ( Fig. 27, within thumbnails) which are displayed interactively with features such as browsing the month or switching to a weekly view or agenda view, as specified by the visitor on the client. The target system may also render a tabular string with a name column and a GPS coordinate column as a comma-delimited list which is accepted by a third-party mapping application widget which interacts with the third-party server to render a map with the specified locations.

[0272] Data Validation

[0273] Sometimes, spreadsheet tools include data validation capabilities. For example, data validation rules can be specified in a spreadsheet tool where a cell should only allow integers, only allow decimal numbers, only allow numbers within a specified minimum and maximum range, only allow dates or times that may be within a range of minimum and maximum values, limit the maximum number of characters allowed, or limit the minimum number of characters required, etc. Data validation can also be set to only allow a cell to contain one of the values ​​specified in a list. The list can be provided as a simple text list of values ​​during the data validation definition, or it can specify a reference to a range of cells in a spreadsheet, where the tool will obtain a list of acceptable values ​​from the spreadsheet, and it can cause a drop-down control to be displayed in the spreadsheet tool for that cell. Data validation can also be set to only allow values ​​that cause a typical spreadsheet formula to return true. For example, for cell B1, the formula can specify "=OR(B1>=0,AND(A1=0,A2<40))", in which case B1 can be a positive number, or it can be a negative number if cell A1 is zero and A2 is a decimal. Some tools allow exceptions to be specified to allow null values ​​to pass validation. According to an illustrative embodiment, the target system can read all of the above data validation types and options that the designer has specified from the spreadsheet and the spreadsheet cell definition. Data validation can be on the cell definition XML, or the data validation rules can be defined elsewhere as a group in the spreadsheet definition and a list of cells to which the rules apply. The target system can extract and process this information from the spreadsheet definition. During input, such as inserting or editing a value from the target system that corresponds to a cell in the spreadsheet to which the data validation rules are applied, the target system will then evaluate the visitor's input value compared to these validation rules. The evaluation can be done on the server, or it can be done by passing the rules to client-side JavaScript, for example, which will evaluate the user's input without the need for a request to the server. If data validation fails, a label or pop-up warning can be displayed to the visitor to provide them with an opportunity to correct the value. If the data validation rule involves a cell reference from which a list of acceptable values ​​is obtained, or if the data validation rule involves a formula that evaluates to true, the system can dereference other cell values ​​required to evaluate the formula or other cell values ​​required for the system to know the list of acceptable values, which in turn will allow or not allow access to the user's input. If the spreadsheet definition specifies a date-based rule, the target system may modify the control output to be an insert or edit form such that the user has a popup calendar control to select a date from, and optionally the typing text box control itself will be disabled to prevent invalid values ​​specific to the beginning, rather than allowing invalid values ​​to be typed in with a warning but not submitted to the server.

[0274] To go beyond spreadsheet style validations such as "A1=0", the target system has additional options during page creation where the designer can add validations such as "Current User" or "Current Date" which automatically set the value of the cell based on information retrieved from the server. It is also possible to specify map or GPS coordinate restrictions where the user can enter GPS coordinates or select somewhere on a map to generate coordinates and they will also be validated as the correct GPS format for the value. GPS coordinates can also be validated more deeply by specifying polygons as boundaries or predefined polygons such as "Europe" where minimum and maximum areas are specified to the target system providing the bounding rectangle. Other convenience data validators are also available, even if they have the same effect as traditional spreadsheet tool validators. For example, allowing the designer to specify a list of valid values ​​( Fig.80 ) is available, and the validator causes the drop-down control to be rendered on the insert page. A second example is a date validator, which can restrict the selected date ( Fig.79 ).

[0275] The target system can enforce data validation rules regardless of the visitor's input method, including input methods such as input forms ( Fig.93 ), CSV import page ( Fig.87 ) or external automated webifier API calls.

[0276] Transient data

[0277] With webifier, all types of forms may contain transient data, which are parallel versions of the worksheet data that are inconsistent across all users over the long term. There are many types of transient data, such as data specific to a single target page being delivered, data per visitor browser session, periodic data such as per day, data per visitor but persistent between browser sessions, data per role or user group but permanent, and / or combinations of the above (such as per visitor per day). The target system may require the visitor to continue using or reset transient data at the beginning or end of a visitor's browser session so that the transient data applies to a group of browser sessions. For all types of transient data, the data can be stored within the target system, access logs, available for review and modification by designer users, and available to other components of the webifier system to use in the same way as non-transient data.

[0278] Designers can declare data as transient at various levels of granularity, including per application, per page, per table, per column or row, per range, and / or per cell. This declaration can be provided by the designer using a variety of methods, such as a configuration option presented on a target system management page (such as an application or page configuration option), automatically (such as for an autogen table), through a configuration table with an editable list of all transient ranges, and / or by the designer selecting a table, range, or composition of a table to indicate its transient type. The declaration can be stored in a variety of methods, including with the configuration options of the target system and / or as metadata on the table or table contents.

[0279] For example, a designer of a mortgage calculator application for a bank that allows visitors to enter their financial parameters into a web page to see a graph of their mortgage payments can set the entire application to transient through the target system's application options and indicate that the transient scope is individual browser sessions. The resulting application can be such that each visit starts with the original data, and all changes made by the visitor will not affect other visitors or future visits by the same visitor. Another example is a "to-dos" page where the underlying record table is transient and the transient scope is per-user, so that each user has their own persistent list of to-dos in an edit cell or report page type that they can access from multiple devices, while other users accessing the same page will have separate lists without sharing the to-dos between users. Another example would be an application where a page is shared between all employees to indicate whether each employee is in the office or out of the office today, and the In / Out columns are transient and scoped to a date, such that an employee would indicate to all other users that they have arrived each day by setting themselves as "In" each morning, and the situation where an employee forgets to mark themselves as "Out" the previous day would not result in an erroneous indication that they did not arrive until the next day, as the value would likely reset to blank within two days.

[0280] Another example demonstrates a technique for building additional navigation on a visitor's landing page, where the visitor can specify a country from a drop-down list and then display data specific to that country. The designer creates a page container that contains two child pages, one of which is a report page and the other is an edit page with a drop-down menu that serves as a navigation control for the report page. The template for the edit page references spreadsheet cell A1 in the country drop-down list, with the per-page delivery scope set to transient; the report page template has a column containing a spreadsheet formula with conditional logic that references cell A1 and changes the formula's result based on the value of A1. For example, if the formula is "=IF(A1='Canada','north',IF(A1='US','south','unknown')", a visitor who sets the dropdown to "Canada" will see the report page display "North", and changing the dropdown to "US" will display "South". Many cells of the report may vary based on the transient value in the dropdown to provide a complete report on the country, but many concurrent visitors to the page will each see the value corresponding to their own navigational country selection and will not be affected by other users' navigational selections or their own selections in previous browser sessions. Nevertheless, in this example, the target system may still store the transient navigational selection.

[0281] Similarly, transient data can be referenced by a formula that dynamically calculates a range of cells, which in turn can be used by the page's source configuration to specify a range to display and edit, such that a visitor can see and edit values ​​that affect only the country the visitor has currently selected, but the edited values ​​themselves will be non-transient. Often, the value and flexibility of the ability to combine transient and non-transient user interfaces and / or data using a spreadsheet as the primary method is greater than the sum of its parts.

[0282] In some implementations, webifier can provide transient functionality by a simplified approach of replicating the entire application for each transient scope. Additionally and / or alternatively, for higher performance, webifier can utilize a SQL database to store the contents of each spreadsheet cell, which are stored in a separate record of the table. A simplified example database record format that will support several types of transient data storage may include the following columns: guid ID, int row, int column, guiduserScope, guid SessionScope, text content, text formatting, DateTime createdTimestamp, and DateTime lastModifiedTimestamp. This database record format can support multiple parallel versions of any given cell. For example, to obtain the master non-transient version of any cell, for example, the SQL query can include "userScope is null and SessionScope is null" in the where clause. If several layers of transient options are enabled, the SQL query can obtain the most relevant cell value in a single nested query (the nested query returns any record with SessionScope set, otherwise, if not found, returns the record with userScope set), otherwise, if not found, the master non-transient version. With this kind of SQL database storage, if the designer inserts a row above the cell in question, a SQL update query can increment the rows of all cells below the inserted cell, and also increment all transient versions of the cell using the same single query.

[0283] Webifier can give designers access to transient versions of data for viewing, creating reports, using in summary formulas, creating edit pages that allow editing of transient values ​​per user and / or use in various ways that non-transient cells can be used. Webifier can allow designers to specify any instance of a range when opening any table with transients. For example, if the designer indicates to open table ABC with a per-user range, the webifier can ask the designer whether the non-transient version should be viewed and modified, or to open the transient version of ABC for the user by selecting the user from the provided list. Webifier can also allow designers access by using methods that provide autogen and / or virtual tables to the designer, where the table has a row for each transient version and a column for each cell address. For example, in the case where the transient range is a transient range of cells in BaseRecordSheet!A1:B2 for each user, the column headers on the autogen table AutoGenTransientSheet can identify columns with labels "user", "cellA1", "cellA2", "cellB1", "cellB2", followed by a row for a blank user to indicate a non-transient value, followed by a row for each user with a transient value. For each value in a column like cellA1, the value, formula, and even formatting may be different. The designer can then create a new table with a label "Highest Value" and a traditional spreadsheet formula "=MAX(AutoGenTransientSheet!B3:B1000)", which will exclude non-transient rows, and can create an associated report page that will show the maximum value set by all users for cell RecordSheet!A1. The designer can then also create an edit cell page, with a data source for the entire AutoGenTransientSheet, allowing visitors to the target page to modify other users' transient values.

[0284] The webifier may also allow access to designers by extending the traditional sheet!startcell:endcell syntax to a scope reference of transientScope!!!app!!!sheet!startcell:endcell, where transientScope may be a concatenation of a scope keyword (such as "user") and a scope identifier (such as a username or GUID), and app may be a simple name or GUID. For example, a cell may have the formula "=AVERAGE(group-employees!!!Forecast!!!projects!B1:B1000)". The target system will parse the syntax in the usual way to evaluate the formula, and the Add-On may cause the spreadsheet tool to display placeholder values ​​to the designer, or the Add-On may make an API call to the target system to provide the actual values ​​to the spreadsheet tool. Optionally, parameters may be provided to an additional overloaded version of the spreadsheet function, such as "=AVERAGEEX(transient scope text,app text,sheet!startcell:endcell)".

[0285] A webifier may allow for the migration of transient versions of tabular data from one scope to another. For example, in the case of a page with a per-user transient scope, a webifier may provide a URL link to a visitor, John, that can be shared with another visitor, Jane, so that Jane's visit to the page will display a transient view that belongs to John rather than her own. The URL link may have limited access rights to the transient view, such as read-only or an inability to navigate to other pages that display other transient data that is scoped to John. The webifier may also provide Jane with the option to take John's transient data and modify it in Jane's own transient scope. The webifier may also allow transient scopes to be bookmarked, or URL links to be reused, enabling visitors to access, maintain, and selectively restore one of several transient scopes.

[0286] Even if the designer does not specify any transients, webifier may still provide visitors with the option to enter or exit sandbox mode and the option to select the scope of transients. The results will be similar to if the designer specified transients. If the visitor indicates that they wish to sandbox a single value, a page of values, or an entire application, the target system may create a transient version of the associated data and allow the visitor to edit the data without affecting the non-transient version of the data. If the visitor subsequently indicates that they wish to exit sandbox mode, the transient data may be discarded and the visitor's view returned to a normal state where further edits affect the non-transient data, or the visitor may be given the option to apply their sandbox changes to the non-transient data, effectively providing the visitor with a transition area for changes. webifier may also provide designers with a similar sandbox mode as a transition area for changes.

[0287] When references to cells, ranges, and / or other aspects do not explicitly specify transient or non-transient, the target system may automatically attempt to use a transient version with a narrow scope that matches the scope of the current request context, such as the visitor browser session, and in the absence of any narrow scope, may attempt to use a transitional version with a wider scope, such as visitor, and so on, until, in the absence of any applicable transient version, it may fall back to a non-transient version.

[0288] Parallel and formula engines

[0289] Many visitors can log in to the target system and request read-only pages in parallel. For requests involving editing, according to an illustrative embodiment, the server can serialize the core code of all incoming requests or requests to modify records into a queue, which effectively looks parallel given today's processing capabilities. Suitable for many different page types, the target system can parse cell references and formulas derived from the spreadsheet definition into simple text and numerical values ​​to display to the user or recursively use in other formulas in the spreadsheet definition or other areas of the target system. The parsing of formulas can be completed in a variety of ways. The same core processing engine or library as the spreadsheet tool can be reused, or a custom parsing and formula processing engine can be built to be embedded in the target system. In any case, the engine can have one or more callbacks that parse cell references based on row and column index values, or it can pass the spreadsheet definition or access a database that stores cell definitions. The incoming spreadsheet definition may be a regenerated definition that is simplified or otherwise modified, which is different from the incoming original spreadsheet definition, or the original spreadsheet definition can be passed in. In the event of any conflicts between edits, the system can select or designate the accepting edit based on time (eg, last person wins) or any other desired heuristic.

[0290] Workflow and filtering results

[0291] According to an illustrative embodiment, webifier may allow certain forms of a workflow to be defined using variations of the report page type and other page types. On a row record table, a designer may add columns to affect visitor permissions on a per-row, and therefore per-record basis, where the result of a cell formula in a particular column causes the target server to include or not include that row in the output results. For example, imagine an employee timesheet spreadsheet where column A contains the employee name, and column B contains the hours, and a report page type that contains the source area for that table. For each row representing a record, the added column C may have a formula similar to "=IsLoggedInUser(A1)", where A1 will change to A2 for the second row, and so on. Although not able to be parsed at design time to display the value to the designer, the target system will evaluate the formula column when a visitor accesses the timesheet list report page. During iteration, if the employee specified in column A of that row is not a visitor, the target system will skip rendering of the visitor's row. If the row matches a visitor in column A, the row will continue to be rendered. The end result is that an employee can access a timesheet web page based on the spreadsheet definition, which will only display rows for time records relevant to that employee ( Fig. 20 ). Similarly, if a table has a column that specifies a supervisor, the supervisor timesheet overview page might list only time records within that supervisor's authority.

[0292] If the page type is an edit page, the same evaluation of the formula can skip rows, thereby not only hiding the information, but also preventing input controls from being provided to prevent the user from editing certain rows while allowing other rows to be edited. An unintended effect of putting these components together is the ability to allow users to define workflows, such as employees seeing only their own timesheets on a timesheet page, and being able to change the "ready to submit" column to true for one or more records, having supervisors see those records displayed on a page with a formula that limits row viewing based on the "ready to submit" column, having supervisors be able to change the "approved" value only for the employees they supervise, and finally having managers see only approved records and the aggregated total approved time. In short, the aspects described herein allow for the creation of custom and complex workflows that previously required the help of an experienced software developer.

[0293] Button

[0294] For visitor convenience, according to an illustrative embodiment, metadata may be specified on a cell, such as a cell comment using the comment function of a spreadsheet tool, which includes a label that the target system treats as a signal to change a text edit of a value to a button control. For example, if a designer requires an "approved" value on each data record, the designer may prefer that the visitor be able to indicate approval of the record by clicking a button to change the value of the cell from false to true, rather than presenting the visitor with a text box to type the string "true" and where the visitor can change to return "true" to "false". This results in an easier to use system and provides assistance in situations where changes to values ​​are one-way (e.g., from false to true, but the designer does not consider it valid for the record to continually change back to false).

[0295] For the insert page, even the target system's default submit form button can have an appearance and position defined by a cell with a webifier definition or a formula function understood by the webifier, such as "=SubmitButton(Done,Green)" placed somewhere in the spreadsheet template. If the spreadsheet tool supports floating buttons, dragging and dropping the floating control can also be used to position the floating button, which the target system will use as the basis for the position of the submit button on the target page. In this way, the actual "hard-coded" interface of the target system (i.e., the interface not defined by the spreadsheet) is minimized on the target page.

[0296] Security+Indirect (Cascade) Filtering

[0297] Preventing input may involve disabling or hiding controls for pages where the designer has specified that the source is a record form, or having the server not send controls to the visitor at all. For security purposes, according to the illustrative embodiment, the server may also confirm based on the same logic whether submitted input posted using an HTTP POST request is allowed, regardless of whether the control is presented to the user, because the visitor may be a malicious terminal rather than a normal browser. Other target pages that use other template spreadsheets (which are used to fill various row records) also use the same logic to evaluate tolerances. For example, if a target system visitor is accessing an invoice detail report page with a template source of an invoice detail template, and invoice 205 is being filled on row 2 on the invoice record list form, the target system may check the invoice record form for a column that specifies whether row 2 has a formula that restricts access to row 2. If so, the target system will also restrict access to the invoice detail report page for invoice 205. In the case of nested templates or when multiple source tables are used in a single template and other such complex scenarios, a portion of the target page may be filled or rendered and not a portion, but the underlying cascading method still exists.

[0298] A single formula instead of an entire column

[0299] According to an illustrative embodiment, an alternative to adding a column with a per-record formula is to enter a separate single formula into a designated or referenced location on the table or external to the table and store within the target system. In this alternative, the target system will then iterate through each row and apply the formula relative to the current row or record being iterated. Other areas of the webifier may use the same pair of alternative formulas.

[0300] For example, in an earlier example related to permissions per row, the document gives an example of a column C with a formula such as "=IsLoggedInUser(A1)" within C1, a formula such as "=IsLoggedInUser(A2)" for row 2, and so on. The designer could then replicate the formula for all records, with the Logged In User parameter referencing column A of each corresponding row. This would work, but could be cumbersome. An example of an alternative would be to have "=IsLoggedInUser(A)" or a different syntax, rather than the usual spreadsheet formula syntax, that is defined separately relative to the table as an option during creation of the page that references the source record. The target system could then understand that the "A" parameter (which typically refers to a column) is equivalent to "the value in column A for the row representing the current record of focus." In other words, it could understand that the formula is equivalent to having column C above. As another example alternative, the "=IsLoggedInUser(A)" formula could be placed in a single cell somewhere on the same table as the row record, perhaps to the far right of the row record information in cell "Z1", and a field could exist during the page definition step that specifies which single cell represents the formula for each row of permissions and in this example could be set to "Z1".

[0301] Overlay Table

[0302] Sometimes, a single record table is the basis for many target system pages, which have different permissions and different expected visitors. In this case, according to an illustrative embodiment, multiple columns can be used to contain different formulas to affect the permissions of the corresponding target pages. As an alternative, the target system can add an overlay table to the electronic form definition. The overlay table may initially be a copy of the row record table with all the same values. It may be marked as an overlay table by the target system, where the metadata is in the electronic form definition of the table because the table is intended to be based on the row record table and is intended to be page-specific. Then, the designer can add column C to the overlay table, and the target system can use this column C in a manner roughly the same as above, but it can evaluate the formula as if it is added to the row record table instead, which makes it process the latest row record list in case the list changes after the overlay table is created. Different overlay tables for different target pages may have different column C formulas. The overlay table can be updated whenever the row record table is changed to keep the basic content synchronized, but only the expected new column C is retained as an example difference.

[0303] Overlay tables may make no changes to the cell positions or any new cells inserted, but may only include visual formatting changes to the cells. For example, an overlay table may change font color, font size, or also use the traditional "hide columns" function of a spreadsheet tool to hide the corresponding content in the target page. To avoid confusion with a regular table, an overlay table may be indicated to the user as an overlay in various ways. For example, the spreadsheet background may be set to a specified color throughout the table, such as light blue ( Fig.39 ).

[0304] Sometimes template or overlay sheets can be "hidden sheets" if the spreadsheet tool supports hiding sheets from the table list, or if template sheets can be listed separately from other types of sheets. When the designer is viewing a particular page, these additional sheets will automatically be unhidden before the view is completed.

[0305] Traditional spreadsheet table

[0306] According to an illustrative embodiment, rather than the target system having a field indicate during page creation that column C specifies row-based permissions, an alternative is to have the spreadsheet definition imply this. Spreadsheets may have traditional "spreadsheet tables" that typically contain cell ranges and may have spreadsheet tools coloring, filtering and sorting options on the header row, and other features that manage the display of rows in the table when viewed in a traditional spreadsheet tool ( Fig.74 ). The webifier can check whether a traditional spreadsheet table exists in the spreadsheet definition of the source row record table for the page or in the overlay table for the page, and if the table exists, the webifier can look at the persisted state of that table for filtering, sorting, etc. If the designer sorts the cells of the table by column A, the target system will also sort the output by column A. For the earlier example of selecting row-based filtering permissions based on a formula in column C, the designer can set the table to filter on column "C=true" and the target system will filter the resulting output using the same logic. In this way, there is no need for the creator of the page to indicate that column C has row-based permissions that need to evaluate to true to view the row.

[0307] Applicable to any of the situations or methods described herein where the permission is to filter records, including the traditional table approach above as an example, the target server can optionally render report pages and other page types that rely on row record tables with aggregate functions in spreadsheet formulas to exclude disallowed records. Using this approach, the permissions for each record will also affect, for example, report pages with aggregate charts. The designer can also disable this feature on a per-page basis so that the aggregate chart is not affected by the underlying row record permissions, which effectively provides the visitor with aggregate information, but continues to retain the granular per-record information.

[0308] Extract updated spreadsheet definition

[0309] According to an illustrative embodiment, after the target system has used the designer's spreadsheet definition to provide the target system's visitor with read-only and / or inputtable forms and content, and after the target system's visitor may have modified or entered records, the target system may provide the designer with an updated, unified or multi-part representation of all data and templates in a spreadsheet format that is fully compatible with the spreadsheet tool. For convenience, this may be referred to as extraction, as may be described in step 215 ( Figure 2 The extraction can be provided as a file download or by updating the spreadsheet tool using a target system add-on or plugin ( Fig.54 , "Download data" button). In most cases, the designer then receives a spreadsheet with not only the updated records, but also a report showing the actual data based on the record table, all without further involvement of the target system.

[0310] The extraction can be performed similar to how the spreadsheet definition is first placed into the target system, but in reverse. In one example, if the target system persists the spreadsheet definition and makes all modifications as requested, the target system only needs to perform the opposite operation of the original upload file by the designer; that is, provide the xlsx data as a file for download by the browser. In another example, if the target system persists the spreadsheet definition after decompressing the xlsx data and storing each contained decompressed file in the database, the system can reverse these steps by creating a decompressed file for each record, compressing all data into an xlsx file, and then providing the file as a download to the designer. As a third example, if the database table stores one record in each cell but does not change the mode of the cell content stored in the database, then in turn it can include iterating all database entries and serializing the cell content into XML, and reintroducing each table definition stored in other database tables to generate a decompressed table file, and then continuing the steps mentioned in other examples. As a fourth example, if the source spreadsheet definition is provided by a web service API call to obtain a definition, then in turn it can include an API call to deliver a new spreadsheet definition to replace another definition.

[0311] In some cases, a designer or other user may perform an extraction where the designer obtains one or more spreadsheet files or one or more spreadsheet tables having one or more form templates populated by one or more records. For example, such an extraction may result in a spreadsheet having 100 forms, and where each form is an invoice displayed in the form of one of the form templates. The steps and examples for packaging the extraction for the designer may be similar, with the difference being that the target system will need to follow the steps for populating the template forms, just as in the earlier stages of generating the report page, but then redirect the results of the extraction steps provided above, rather than converting the cells to other formats and then iterating with other records that populate the template forms.

[0312] Via typical push notification or polling methods, add-ons (plugins) can provide live reloading and update the designer as the target system makes them available. Typically, this means that the extraction step is limited to be performed on any tables or cells that have changed due to visitors, and the add-on only updates those tables or cells, while leaving the rest untouched. Add-ons can take a hybrid approach, which checks in real time for updates, but downloads and saves any new spreadsheet definition as a local file on the hard drive, and then triggers the spreadsheet tool to open it, all with minimal or no user involvement.

[0313] Continue editing the spreadsheet definition

[0314] According to the illustrative embodiment, whether extracted as a file or into a spreadsheet tool via an add-on or otherwise, the designer is now able to use the target system to make further modifications to the template form and / or record form as they did before. In fact, the designer may have chosen to place an empty or nearly empty spreadsheet definition into the target system and then only begin to build the spreadsheet after extracting the spreadsheet to take advantage of any target system functionality that is relevant at design time. As a simple example, because the target system displays this new web application in the application list, other designers are made aware for coordination purposes that the user is designing a specific new spreadsheet that indicates a specific new web application.

[0315] At the time the designer has indicated that the visitor is editing a spreadsheet or in the case where the add-on has informed the target system server that the designer is editing, the target system may have the option of locking or blocking all access from the visitor, allowing only read access, or only read and insert access. This blocking is typically limited to tables related to what the designer is currently editing by determining whether the visitor's page requires a table that originally came from the same file as the table the designer is editing, or possibly by determining that it is from the same file or whether there are any direct or recursive references that seek from the visitor's table to the designer's table being edited. This prevents accidental conflicts between the visitor's changes and the designer's changes, or prevents the visitor from seeing a page exported by the designer's ongoing and incomplete set of changes. Another technique to help avoid these situations is for the designer to indicate that they are about to make changes to the spreadsheet definition, again manually specifying it or automatically detecting it via the add-on, to cause the target system to copy its stored representation to a staging area, where the designer's changes only affect the staging area until the designer indicates that they have completed the set of changes and are ready for the changes to take effect, at which time the staging area is copied back to the live web application. During this time the live web application is completely blocked, set to read-only or allowed to proceed, but changes made by the designer while working on the staging area are merged as a group into the live application while preserving the live application through the same methods, i.e. individual designer edits can be merged with the live web application's records, or new spreadsheet definitions can be uploaded and merged with the live web application's records.

[0316] The merging of the latest changes from the visitor of the web application, and the merging of the designer's changes can employ many popular and commonly used methods to merge data, as can be done when both parties are in the form of a spreadsheet definition. Commercially available libraries and tools can be used to identify differences between spreadsheets and allow iteration over these differences to accept or reject each change, as well as libraries and tools that compare three spreadsheets: the original, the changed version A, and the changed version B. They iterate and handle changes that do not conflict, and either reject conflicts entirely or provide conflict information to the user to decide as to which version wins.

[0317] One approach to merging is to combine multiple other techniques in a way that is most conducive to the intended use of the webifier software. For example, a designer may be allowed to begin making changes to a template form, and the target system does not block the visitor's changes to the record form. It is rare for the target system to have a visitor change a template form, although it is certainly possible because the target system can generally process all forms, especially when the designer selects a worksheet to report on. Nevertheless, by iterating how the designer references the form during page creation, the target system can default to blocking only changes to the template form, knowing that it is a template form, whether or not it is referenced as a template source form. If the designer wants to preview the effects of their proposed changes, the target system can automatically set up a staging area. If the designer is detected as modifying a record form, the webifier system can prompt the designer to block other related parts of the web application from concurrent input. In this approach, conflict resolution prompts can be avoided for the designer, and the designer is still able to update the user interface of the target system via template form changes without the target system discarding any previous or new records it contains.

[0318] In the case of inserts and some other types of edits, insert-type edits can even be made to records in parallel on the target system and the designer's tool without prompting to resolve conflicts. A simple approach is to keep a copy of the original spreadsheet definition that is sent to the designer at the beginning of their session or after they have indicated their intention to do design work, and compare the designer's changes to the original at the next upload to determine which records are new and have the target system add each of the records with a short lock to allow the target page to continue to operate and insert while merging these newly inserted records from the designer. In a similar manner, the pre-design original can be saved and compared with the designer's next upload to determine which records are missing and therefore intended to be deleted, modified, and intended to be updated. In addition, the same method of comparing with the pre-design original can reveal the addition or deletion of columns from the record table, and the corresponding template table changes to match and indicate the designer's changes to the web application structure. In all cases, the old records on the target system and the records newly added by the visitor or modified by the visitor remain unchanged, and the end result is a reasonable merge between the designer and the visitor's changes to the records, while being able to update the interface sent to the visitor by the target system and the structure of the records, for example, in one step.

[0319] The challenge of allowing continued editing of the spreadsheet definition is that the designer may make changes to columns or rows such that any references to ranges stored by the target system may become out of date. One solution to this is to use an add-on to monitor changes to the position, row, and column index of the referenced spreadsheet cells, and update the target system accordingly on a per-event or per-event group basis. For example, if the target system has a range reference of "A5:A10" and a row is inserted at the top, then the webifier add-on nested into the corresponding event listener would notify the target system to move any ranges below row 1 down, such as in this case, to "A6:A11".

[0320] To allow the designer the further flexibility of being able to modify the spreadsheet definition offline and on a computer that does not have the add-on installed (essentially a computer that only has an unmodified copy of the third-party spreadsheet tool and the spreadsheet definition) (referred to as "disconnected" design), the challenge is deepened and the above solutions may not be sufficient. In this case, the target system may be piggybacked with the compensation of the traditional named range feature of the spreadsheet, as the spreadsheet tool updates the named ranges based on designer changes, and such changes to the named ranges are observable by the webifier system. This approach allows for this desired flexibility of modifying the spreadsheet definition while disconnected that affects the target system.

[0321] It is worth noting that uploading records and uploading interface templates do not need to be separate steps, they can happen in one step, either by the designer or automatically, and where a spreadsheet definition is provided to the target system. Of course, separate steps can also be used if desired.

[0322] The designer can be notified from the designer or the visitor, such as through the "Server Changes as of <date>(Server changes as of <date>)" pops up, indicating that the target system has received additional changes ( Fig.53 ). This can be accomplished using public push notifications or polling of the target system. This allows designers to reduce concurrent editing conflicts after the designer has completed editing.

[0323] Named Ranges

[0324] According to an illustrative embodiment, when completed by an add-on, the webifier system can retrieve named ranges from a spreadsheet (e.g., via a COM Interop library, as described below) or from a target system server and pass the names to the web-based user interface of the add-on using JavaScript. Webifier can create new named ranges to capture the currently selected cells of the data source when creating or editing a target page or when any other field or target system requires a reference to a spreadsheet cell and the target system needs access to the reference to process a visitor request. For example, the names of these ranges can be "WebifierCustomRange" and are appended with randomly generated numbers from 0 to 2,147,483,647. If a range with that name already exists, a new random number may be generated until the created name is not appended to an existing range. Alternatively, an incremental number may be used. If a custom range for the selected cell already exists, the system may choose to use an existing name instead of creating another one. Operations performed in the spreadsheet tool (such as inserting a row or inserting a column) will use the extended named range according to the normal spreadsheet tool. If a record is inserted via an Insert or CSV Import page that has a named range as its source, it will expand that range to include the inserted record. If a named range referenced by any webifier web page is deleted from the spreadsheet definition, then when the worksheet is uploaded the affected pages will be marked as having an invalid source and will be inaccessible until the source is edited or the deleted named range is restored. Named ranges can be used to define the boundaries of Insert, Edit Cell, Edit Record, CSV Import, Report, Web Page, Calendar, and Detail Report pages. Named ranges can also be used to define the title range for Insert, Edit Cell, and Edit Record pages, as well as to set the editable range for Edit Cell and Edit Record pages ( Fig.81 ). Using the Edit Record page, you can automatically delete a named range if you delete all cells in the range.

[0325] As an example, an insert page with an automatically generated form interface needs to reference the source range of the row record table normally. If the designer adds columns to the row record table while disconnected, but later uploads the updated definition, the target system will be able to see the updated named range containing the newly inserted columns. In this example, when the insert page sends the form to the visitor, the page will now generate the extra labels and html input controls, and in this case the visitor will begin generating a new record containing the new column of information without further action required by the designer.

[0326] Tracking by Cell

[0327] According to an illustrative embodiment, if the amount of cells to be tracked is very high, the named range can be combined with or completely replaced by a unique cell ID generated by the target system and inserted into each cell of the extracted electronic table definition with additional properties. Although the electronic table tool does not understand the property usage, many electronic table tools retain this property as metadata even when the cell is modified or moved or copied in the electronic table tool. This provides many of the same advantages of named ranges, but the designer is unlikely to see them in the user interface of the electronic table tool. The target system then extracts the unique ID in a very similar way to other cell properties to obtain knowledge about the changes made by the user when disconnected. This knowledge can then be used to update the references on the target system to these cells, or in some cases, those unique cell IDs may be sufficient as a method for the target system to reference cells instead of using traditional row and column indexes. Even ranges can be specified as UID:UID, which represents the upper left and lower right corners of the range.

[0328] In a similar manner, other metadata can be stored in other parts of the spreadsheet definition that are hidden from the designer. For example, a link to a target page that requires a table could be stored in an attribute in the XML definition of the table. If the spreadsheet tool supports this scenario, where there are ranges that automatically get updated like named ranges, but where the ranges are not visible to the user, then this functionality could also replace named ranges.

[0329] Configuration table for the target system

[0330] According to an illustrative embodiment, an alternative method to named ranges (called a "configuration table") can also be substituted for named ranges. This method can include an additional table named "DestinationSystemRanges", which may be hidden if the spreadsheet tool supports hidden tables, where the table has 2 columns, Name and Range. The target system will then iterate over this table during upload or on each visitor request and find the range that the target system is seeking to resolve by the value in the first column. For example, "Page1_SourceRange" could be one of the names. The target system can then get the value or formula of the cell in the second column and use it in much the same way as a named range definition. The advantage of this method is that the value can actually be a traditional spreadsheet formula, and therefore even the range definition can be affected by other elements of the spreadsheet definition. This provides an unexpected amount of power and flexibility. For example, a formula can be specified where if the invoice total is > $5000, then the detail page should contain an additional 5 rows in its page source configuration, which may have additional fine printing that is required to display only that invoice to the visitor. At the same time, the spreadsheet tool will automatically keep the ranges synchronized in much the same manner as the named ranges. This allows the designer to modify the configuration of a custom report particularly efficiently with minimal or no additional effort or training.

[0331] The data and metadata information (such as page titles) required by the target system to serve the target page can be stored in a database separate from the spreadsheet definition. Named ranges can be replaced in much the same way as the configuration table, all data specific to the target system can be contained in the spreadsheet definition, and importantly, it can be edited by the designer in a familiar spreadsheet interface and method. Another example is the page title defined in the configuration table, which will allow the page title to be the result of any formula chosen by the designer. If the cell is >= $0, then the page title such as "Profit / LossReport" can be "Profit Report", if the cell is <= $0, then the title will be "Loss Report". Another example is an entry into the notification system. A formula such as "=IF(OtherSheet!A1>10,"Thomas","Patrick") can change the target of a notification based on the values ​​in a record table. It can also change the delivery time or any other notification attribute based on a formula specified by the designer, and utilize the basic concepts of the invention in a circular way so that it can be configured by the designer himself. Some spreadsheet tools are able to accept cell metadata, where the cells are read-only when accessed in the tool. In this case, the configuration table may mark certain cells as read-only to reduce the possibility of the designer making accidental or incompatible modifications to the configuration table. For example, except for the second column on the DestinationSystemRanges table described above, the configuration table can make all cells read-only.

[0332] Ultimately, implementing the method consistently, the entire definition of the target system's web application may be portable and configurable when disconnected. At the same time, the entire definition of the web application may be formula driven, arbitrarily definable in terms of other parts of the definition, or otherwise have unlimited and unpredictable connections to function results of record tables, template tables themselves, current dates, etc.

[0333] External supplementary data: Files

[0334] According to an illustrative embodiment, a file may be uploaded to a target system using the WEBIFIERINSERTFILE function ( Figure 7 ). When you encounter a field containing a WEBIFIERINSERTFILE column when building a form for an insert or edit record page, a file upload control may be displayed for that field ( Fig.62 ). Similarly, when editing a cell containing the WEBIFIERINSERTFILE function on the Edit Cell page, the designer has the option to edit the formula or upload a new file. When a WEBIFIERINSERTFILE is encountered in a spreadsheet tool with the add-on, it can display a key that can be used to retrieve information about the file using the WEBIFIERFILEATTRIBUTES function ( Fig.64 ).

[0335] If a WEBIFIERINSERTFILE is encountered in a report or edit cell page, the file can be displayed on the target page. Visitors can view these uploaded files in different ways depending on the file type. If the file is an image, it can be displayed within the cell ( Fig.63 ). If the file is an audio file, the visitor can use the HTML5 audio player to listen to the file. If the file is a video, the visitor can use the HTML5 video player to watch the file. If the file is any other type of file, a link is displayed, and the visitor can follow the link to download the file ( Fig.61 ).

[0336] From the target system's administration pages, the designer might be able to view and sort the files on the system and get details such as the file key, size, and which web application each file belongs to. Files can also be viewed by web application to easily see which files are associated with a web application. Permissions can also be applied to files so only a specific user or group of users can access the file. The target system could have these permissions based on who can access any cell that references that group of files in a formula, or the target system could have a separate configuration interface that lists groups of files, allows modification of their sharing settings, and specifies a default set of permissions for new sets of files.

[0337] Uploaded files can be stored in actual file directories, or in a database virtual file list, shaped and associated with the web application to which the files were uploaded, in a directory named after a key provided or generated by webifier. The type of file stored can be determined by the MIME content type of the file and recorded to display the file. Multiple files can be stored under the same key and can be accessed via the index parameter of the Webifier file functions.

[0338] The WEBIFIERINSERTFILE function can accept zero, one, or two arguments consisting of values ​​or formulas ( Figure 7 ). The first parameter is an optional string parameter which is a key that can be used later to retrieve the uploaded file. If a key is not specified, a globally unique identifier (GUID or guid) can be generated and used as the key. If a key is specified, the user can also include an optional integer parameter which can be the index of the file under that key to be displayed. If the index is not specified, or the index is greater than the number of files, the function can display the most recently uploaded files when displayed. When uploading files in the insert page, the WEBIFIERINSERTFILE function can be used to automatically fill the newly inserted cells. The first parameter in the formula can be the same key as the previous cell. If the previous cell key parameter is a cell reference, it can be automatically incremented to reference the cell below. The second parameter can be the new number of files so that when the cell is viewed on the website, it can display the newly uploaded files.

[0339] The WEBIFIERFILEATTRIBIUTES function can accept one, two, or three arguments consisting of values ​​or formulas ( Figure 5 ). The first parameter is the string key of the file; the second parameter (which may be optional (not shown)) is the index of the file stored under the key to use to obtain the property of the last parameter; the last parameter (also optional) is the name or number of the property to retrieve. "Name" or 0 represents the file name, "Size" or 1 represents the file size in KB, "DateUploaded" or 2 represents the date the file was uploaded, and "Link" or 3 represents the URL to view the file on the website. If the index is not specified, or the index is greater than the number of files, the function may obtain the properties of the most recently uploaded file. If the property is not specified, the function may default to displaying the name of the file.

[0340] All of the above variations and features of the external supplementary file system can apply either a single file per cell variation or multiple files and folders per cell variation.

[0341] External Supplementary Data: Signature

[0342] Signatures may be uploaded to a target system using essentially the same methods described above for file-based external supplemental data for visitors uploading images. However, in the case where the signature is in the form of external data, the file upload dialog and controls may be replaced with controls that allow the user to enter a signature. For example, a table of records may have columns where each row record has a formula similar to "=WEBIFIERINSERTSIGNATURE(A2,'thumbnail')", where column "A" has data that may be used as a unique identifier, such as an employee's name, and the "2" in A2 changes to the appropriate row, and where the "presence" indicates that the cell displays a thumbnail of the signature if available, or the text "n / a" if no signature is available. At design time, the add-on may fetch such thumbnails for each record in order to display them in the cell. For visitors to a report page with the source set to the entire table of records, signature thumbnails may be presented next to each record row displayed on the target page. The thumbnails may be clickable to open a pop-up view of the majority of the captured signature along with metadata such as the date and time the signature was captured. On the insert or edit target page, the large pop-up view may also have a large canvas area, where the user's mouse movement, inkless pen on a digital tablet, or finger on a touch screen will cause the canvas area to process with the movement of the pen and draw corresponding lines on the canvas. There are commercially available modules for capturing motion, such as signing in an image file through such a canvas, usually using JavaScript code to do this, and providing an html input form with image data to be uploaded to the server on form submission. Continuing with this example, the target system can store an image file representing the signature in a database to retrieve in a subsequent request to view the signature data of the record on the report page. As an alternative to the visual signature, data representing the digital signature can be obtained from one of the many third-party issuers of the signature of the PGP signature and a text summary or token manual signature using a handwriting-like font through client-side JavaScript, where such a digital signature can be displayed in place of the signature image. Visitors may be required to enter authentication information, such as a username and password pair or a fingerprint on a thumb scanner of a mobile device, using a commercially available integration point for such hardware so that the third-party issuer can issue a digital signature.

[0343] External supplementary data: chat

[0344] According to an illustrative embodiment, the chat object feature allows a chat discussion stream to be embedded or associated with one or more cells of a spreadsheet. The chat object allows visitors to add messages to cells that other visitors can then view, as well as view messages left on cells by others or themselves ( Fig.58 ). A chat object represents a list of message objects, where each message object typically consists of at least three parts: the message text, a timestamp indicating when the message was submitted, and the username of the user who submitted the message. Each chat object may also have a unique identifier, i.e., an identifier that is different from other chat objects (e.g., Fig.59 , "6ea6cc..."). The identifier can be user-assigned or system-assigned. The webifier can use the identifier of the chat object to reference the chat object and distinguish it from other chat objects.

[0345] A chat object can be created by entering the insert chat formula function named WEBIFIERINSERTCHAT, which is a custom formula function defined by the webifier system ( Figure 6 ). To enter this function into a cell, it may be required that the user has permission to set a formula for that cell. This can be done in various ways, including via a webifier API call that allows it, or via a spreadsheet tool with the webifier add-on installed.

[0346] To create a chat object in a cell, the designer can set the cell's formula to an Insert Chat function with no parameters (i.e., '=WEBIFIERINSERTCHAT()'), to an Insert Chat function with a standard hyphen-delimited string representing a globally unique identifier (GUID) as the only parameter (e.g., '=WEBIFIERINSERTCHAT("DC40FCA2-D72A-4AFD-80E4-76782B47D960")'), or to a formula that evaluates to a unique identifier, whether a GUID or a string, such as "Pleasant Solutions", which would represent a chat stream associated with records for that company. Multiple cells on different worksheets or the same sheet can pass the same parameters to WEBIFIERINSERTCHAT, allowing multiple target pages or locations in a page to reference the same shared chat stream.

[0347] When a user sets up an Insert Chat formula for a cell and saves it to a target system (for example, if they are using a spreadsheet tool and add-on, by uploading the spreadsheet definition changes to the target system), webifier performs several steps. First, it may replace every instance of the parameterless version of the Insert Chat formula with a version that contains a single GUID parameter. It may generate each instance of the GUID parameter using a version 4 (IETF RFC 4122) algorithm, meaning that the generated parameter is not actually the same as the GUID parameter of the Insert Chat function in any other cell. It then uploads the updated spreadsheet definition to the target system.

[0348] Visitors can use the chat object on a page that allows them to view the cell in which the chat object is embedded ( Fig.57 ). For example, if a chat object is embedded in a cell on a specific table in a spreadsheet, and a visitor has access to a report page whose source is that table, the visitor can interact with the chat flow through the target page.

[0349] When the target system renders a page containing a cell whose formula is the Insert Chat function, it resolves the function's GUID argument and checks its database to see if there is a chat object whose unique identifier matches the resolved GUID. If there is no match, it creates a new chat object whose identifier is the resolved GUID and whose message object list is empty, saves it to its database and uses this new chat object as the chat object for the cell. If there is a match, it uses the matching chat object as the chat stream for the cell. In either case, the target system displays a chat bubble icon and the word "Chat" inside the cell. If the visitor clicks on the icon, it displays a modal window on the page that displays the past message objects (if any) for that chat object in chronological order. Each past message object is displayed with the full date and time it was posted, followed by the username of the user who posted it, and then the message text. Below the list of past messages, it displays a text box in which the visitor can write a new message and a button to submit the new message.

[0350] If a visitor writes a new message in the text box below the chat partner's past message list ( Fig.58 ) and submits this message, the target system creates a new message object, sets its timestamp to the system time, sets its username to the visitor's username, and sets its message text to the text submitted in the text box. It then associates the chat object with the message object and saves the message object and associated information in the database. Finally, it returns the new message object to the client, which can use JavaScript to display the new message object below the list of past message objects but above the text box in the modal window, as well as clear the text box, all without refreshing the page. The visitor can write more messages in the text box, and submitting these more messages can trigger the same steps again. If the visitor does not wish to enter any more messages, they can click anywhere outside the modal window, which can close the modal window and display the page behind it, where they accessed the chat object through this page.

[0351] Since the GUID parameter of the Insert Chat formula is used as a unique identifier of the chat object, if the Insert Chat formulas of several different cells have the same GUID parameter, a message object added to a chat object embedded in one cell can be displayed in the message object list of a chat object embedded in other cells, because the embedded chat objects of these cells are all the same chat object.

[0352] There may be several external supplementary data systems. The above two are just examples of the method, and many of the features described for one of the above examples are applicable to the other example.

[0353] According to another aspect, a video chat module may also or alternatively be used. A video chat object may be created by entering an insert video chat formula function named WEBIFIERVIDEOCHAT, which is a custom formula function defined by the webifier system. To enter this function into a cell, the user may be required to have permission to set a formula for that cell. This may be done in a variety of ways, including through a webifier API call that allows it, or through a spreadsheet tool with a webifier add-on installed.

[0354] To create a video chat object in a cell, a designer may set the cell's formula to an Insert Video Chat function with no parameters (e.g., '=WEBIFIERVIDEOCHAT()'), to an Insert Video Chat function with a standard hyphen-delimited string of a globally unique identifier (GUID) representing a unique parameter (e.g., '=WEBIFIERVIDEOCHAT("GC40FCA2-D73A-4AFD-80E4-76782B47D960")'), or to a formula that evaluates to a unique identifier, whether a GUID or a string, such as "Pleasant Solutions," which would represent a video chat associated with records for that company. Multiple cells on different worksheets or the same sheet may pass the same parameters to WEBIFIERVIDEOCHAT, allowing multiple target pages or locations in a page to reference the same video chat. In some aspects, the WEBIFIERVIDEOCHAT function may also require input of a target at design time to connect to when a video chat is initiated by an end user.

[0355] When generating the target page, the webifier logic can then include, merge or reference a video chat module on the web page, which, when activated by the user, establishes a video chat between the end user and a predefined endpoint (e.g., a target identified by the designer at design time). Video chat modules are readily available, such as Drupal Video Chat, AVChat, Big Blue Button, etc.

[0356] Impact of visitor input

[0357] The process and effects of visitor input on the target system are described below in an illustrative use case scenario. When a visitor submits an insert page form generated by the webifier system, the target system can first confirm that the page is public or that the user has been granted appropriate permission to use it. If the user is allowed, the target system retrieves the spreadsheet definition associated with the application containing the page and initiates the insert process. If the user is not allowed to use the insert page, their submission can be discarded and the system can notify the user.

[0358] Before inserting the record into the spreadsheet definition, date, time, and GPS coordinate data can be converted to a string representation suitable for the spreadsheet tool, and uploaded external supplemental files can be represented by a formula referencing the WEBIFIERINSERTFILE function. When viewed in the spreadsheet tool, the function can return a key that can be used to later retrieve the file from the target system using the WEBIFIERFILEATTRIBUTES function.

[0359] Once all data is in the appropriate format to be saved, the target system will find the row or column immediately after the source range or the next empty row or column if the entire table is used as a source and new data is inserted into the spreadsheet. At this point, the spreadsheet defined validations can be evaluated and then created in the target system respectively. If any of the validations fail, the user can be notified and given the opportunity to correct their submission. If there are any rows or columns in the range that contain Webifier formulas, they can be automatically populated with the appropriate formula even if they have been hidden by the overlaying table.

[0360] After the data has been successfully inserted, all references to the range of cells that make up the data set into which the new data has been inserted will need to be updated. This may include the source of the page (overwriting the table holding the sort and filter range if it is a named range) and any chart series that display the extended data range. Once all updates have been completed, the modified spreadsheet can be saved to the database along with any newly uploaded files. The ranges are converted to R1C1 string format so that they can be parsed to obtain a numeric representation of which rows and columns are currently contained. The range values ​​are then incremented by one row for a row insert or one column for a column insert, converted back to an R1C1 string and saved back to the spreadsheet.

[0361] The target system updates any page thumbnails that will be displayed in the target system admin pages or within the add-on based on the data in the inserted page, and raises the insert and page changed events used by the notification system to send email alerts. If a "Next Page" is configured for the inserted page, the user will be redirected to the page.

[0362] Dynamic page selection

[0363] As described herein, multiple templates and multiple pages can exist simultaneously for the same record and similar or identical purposes. The purpose can be any purpose or page type, including both read-only and visitor input pages. According to an illustrative embodiment, the target system can use information from a browser or mobile client to help determine which template to display. For example, the information can include any number of device attributes or capabilities, such as screen size, screen resolution, OS version, browser brand and version, preferences from visitors about which template sets are preferred manually specified, and whether it is mainly a mouse, keyboard or touch screen device. In the case of a web application, most of this information can be obtained from the http "User Agent" header or by executing JavaScript on the browser, and the browser transmits this information temporarily stored as session information by the web server in-band or out-of-band to the target server. In the case of a mobile application, the application can be designed to collect and transmit this information to the target system at the first login authentication.

[0364] The designer can specify a rule set on the page to specify the criteria required for each page to be accessible to the visitor, as well as a fallback default. The target system can allow the designer to group such pages in a way that indicates that they have a common purpose, to achieve a single URL or page identifier, and have a request for that single location trigger the target system to choose which template to select. In the case of such grouping, the target system will have the ability to enable the designer to order the list with the rules so that the system knows the priority order for each page, with the last one being the default.

[0365] Gamification of the creation of spreadsheet software

[0366] As described herein, building complex software using tables is much easier for non-technical people than by other means. Nevertheless, according to illustrative embodiments, the webifier system and / or add-ons (plug-ins) can observe various actions or groups of actions by the designer that can be considered part of the achievement. If these actions meet various criteria, a visual pop-up window can be briefly displayed to the designer to indicate that they have achieved the achievement ( Fig.15 ). The pop-up can relay the value of what they just created, such as "Your web-app is now ready to receive new records" or "You are done making your first web-appreport". The criteria can include multiple criteria, such as one of the last two times the designer took these actions, the designer has not completed such actions in the past few months, the visitor has also visited a target page related to the designer's actions, the user has completed some actions in a shorter time than the previous time such steps were timed, or the user tried a different workflow or a different way to accomplish something. These actions can involve not only interactions with the user interface of the target system or the user interface of the add-on, or interactions of the visitor associated with the target page created by the designer, but can also be based on analysis of the designer's spreadsheet. For example, the gamification system might look to see if the designer created a traditional spreadsheet table, if they used traditional conditional formatting on the template table, or even if they used basic aggregate functions in formulas, such as "=SUM(A1:A10)".

[0367] Designers can have points associated with their user account, or several types of points, that increment based on these achievements. Accumulating enough points over time can allow the designer to use new features in the target system or add-on. Various actions or levels of points can trigger the target system's user interface or the add-on's user interface to be modified to "advanced mode". This might involve adding additional controls, removing tooltips, changing controls for more complex controls (such as text boxes with validation instead of drop-down boxes), or changing its workflow by changing the order of pages and the location of buttons that the designer will display so that it can be more rapid in form development. Reaching certain point levels can also change the permission rules surrounding the use of the webifier, for example to allow a large number of visitors (anonymous or created with user accounts) to access target pages created by the designer.

[0368] notify

[0369] In the target system, according to the illustrative embodiment, notification rules provide procedures whereby the occurrence of certain events can trigger specified actions ( Fig.83 ). Ordinary users can set up notifications for themselves. Administrator or designer users can set up notifications for themselves, as well as for users in their admin domain. In order to handle multiple events and / or perform multiple actions, users can set up multiple notifications. The setup of each notification rule requires the user to specify details of the event that should trigger the notification and details of the action that should be performed when the notification is triggered ( Fig.84 ). You can disable a notification rule while retaining all its other details, so that you don't have to re-enter the settings if you need to re-enable the rule.

[0370] When an event is propagated to the server, a notification can be triggered based on an action entirely within the spreadsheet tool add-on / plug-in, or a web browser can trigger a notification request sent to the server, but not until the action itself has been propagated or persisted to the server. Example events include when data is inserted into a page, when data on a page is edited, when the page configuration is changed, and when the configuration of the application is changed. For clarity, example events can be in the following classes: visitor high-level event triggering (the submit button of the inserted page is pressed), designer high-level event (a new page is created), cell event triggering (a cell has changed), region event triggering (at least one cell in a cell group has changed), table event triggering, and can be direct (the formula of a cell has changed) or indirect (the result of a cell formula has changed, even if the cell formula itself has not changed). When such an action occurs, the action can raise a domain event corresponding to the action.

[0371] The same domain events that can be used for notifications can also be used as the basis for a logging program. Administrators can choose to log more events than people are interested in being notified of, such as all CRUD (create, read, update, delete) operations on table data, page configuration, or application configuration, as well as events such as access denied. These logs can be categorized, filtered, or used for statistical analysis. The target system contains a notification service that handles these events.

[0372] The webifier service can filter the list of notification rules to those currently enabled, whose settings specify that they should be triggered by event types that match the action that occurred and the specific page or application on which the action was taken. For each such notification rule, the target system performs the action specified by the rule. Possible actions include email actions and webhook actions (a notification rule can specify multiple actions), as described further below.

[0373] To perform an email action, the target system may first determine whether an email should be sent for the rule. If not, it will not proceed with the remaining email action steps. If the email should be sent, the system uses the template specified by the rule to construct the various parts of the email message (subject, body, etc.), replacing information about the event with any corresponding placeholders that may exist. If the event specifies a target page and the rule specifies a PDF copy of the page that should be sent with the email, it can generate a PDF copy of the associated page and add it as an attachment to the email message. Alternatively, the target page HTML may be embedded in the email, including any insert forms or edit forms contained by the page. The HTML may or may not be modified to take into account viewing it in an email client that supports HTML, such as removing page navigation menus, removing background images, removing JavaScript references, or using more standard and simplified controls for input fields instead of specialized controls, such as standard text boxes instead of pop-up calendars to select dates. Submitting the embedded form will result in an http post request to the target system. When rendered without an embedded email, the URL of the post request can be different to the same target page and can trigger different user authentication, whereby no login is required because the authorization token is embedded in the form and http post, or different data validation rules can be used to consider input controls to be more unpredictable free-form input. Webifier then sends the message to the email recipients listed in the rules.

[0374] The content of the email can be specified in an email template form, which includes options for subject lines, WYSIWYG editing of rich text templates, fallback for email systems that don't handle HTML, multiple recipients (including Cc and Bcc), replies, and attachments ( Fig.85 ). Alternatively, commercially available email template modules may be used. In addition, the email content itself may be specified using a table template, which the target system will output in HTML format, just as it does for the target page, but will also output in a fallback, simplified, plain text format for email clients that do not process HTML.

[0375] To execute a webhook action, the target system may first determine whether the webhook should be called for the rule. If not, it will not proceed with the remaining webhook action steps. If it should, it will send an HTTP POST request to the webhook URL specified by the rule, including information about the event sent in the body of the POST request. The webhook information may be in, but is not limited to, JSON format.

[0376] All notification actions can be queued and processed asynchronously so that the event that triggered the action can continue and the user who triggered it can continue to perform other functions on the system, even if the email or webhook action fails or takes an extended time to complete. Because they are asynchronous, notifications can also be stored for batch processing (for example, a user may prefer to receive a daily or weekly summary of all events rather than a continuous stream of notifications).

[0377] Process Action

[0378] Possible actions for notification events (e.g., regarding the above-mentioned buttons and notifications, etc.) can include procedural actions for inserting, deleting, and setting values ​​of cells on any type of table (such as record tables, template tables, virtual tables, transient tables, configuration tables, and all other types of tables in webifier). The details of these procedural actions can be declared using syntax similar to traditional declarative spreadsheet formulas (such as in "=SetValues(source, destination, params)"), nesting within other traditional formula functions (such as in "=IF(A1=100,SetValues(A1:A2,B1:B2,false)"), and / or syntax common to procedural programming (such as "dest=source". The source and / or destination can be an entire table, a table range (including named ranges), a formula that references other table data, or a reference to a control on the insert page (which has the same effect as referencing the base cell and will be affected by the insert page control). In the case of the source, it can also be a static value such as 1, "hello", or 1+1. If the source is a static value and the target is a range, you can set a static value for each cell in the target range. When inserting values, the parameters of the procedure function SetValues ​​can specify whether to overwrite existing values, insert and move down existing values, insert and move left, etc. The procedure function DeleteValues ​​can specify whether to delete and move up, delete and move left, leave blank, etc. The parameters can also specify whether any formulas in the source cells should be resolved as values ​​(which can be called copy as values), or whether the target copy should retain the formula definition of the source cells (which can be called copy by formula).

[0379] Possible actions on notification events also include more complex procedures, such as loops or complex conditionals, which can be specified by the designer using VBA scripting syntax familiar to spreadsheet users, procedural programming languages ​​(such as C#), and / or a variety of common or custom scripting languages. For example, a designer might associate the following code with an insert event for a table named VentureCapital: "for(int x=0; x<10; x++){SetValues(Offset(DepartmentSizes!A1,x,0), Offset(DepartmentSizes!A1,x,0)*2, SetValueParams.Overwrite|SetValueParams.CopyByValues);}". When a new venture capital record is inserted, the associated action will double the department size values ​​in A1 through A10, and if any cells initially have formulas, they will then have simple numeric values. The webifier may limit the execution time of a script, limit the available functions or features, and take other security measures unique to systems that allow script logic. Webifier can execute scripts only on the target system and can avoid and / or prevent script execution within the Excel Add-On. Webifier can allow scripts to have a variety of capabilities, such as accessing metadata of a table, modifying off-webifier data such as page options, sending web HTTPS requests, performing API calls, connecting to a SQL database to perform SQL queries, and / or triggering other webifier notification event actions, such as sending an email (e.g., by executing a script function such as ExecEvent(guid)). Using these process actions to change a table may indirectly trigger an add-on notification due to events previously associated with such changes.

[0380] Other possible triggers for notification events include date or time rollover, such as midnight or an interval specified by the designer in the form of a cron expression or a button click defining a button in a cell using the formula "=ButtonEvent(buttonLabelText,wait or queue)", wait or queue specifies to the target system whether the visitor clicking the button should be made to wait for the associated events to all complete execution, or whether the event should be queued and the visitor should be allowed to continue using the webifier in the meantime. The notification event list will uniquely identify the specific button referenced by the location of the specific cell found, such as "Button Labelled Reset at FirstSheet!A1".

[0381] Immutability

[0382] A user of webifier might want to build an application where some data is declared using a spreadsheet formula, but the data does not change after it is initially created, or is frozen after some point in the future, which can be collectively referred to as immutability.

[0383] Immutability can be achieved through the process capabilities of webifier already described. For example, a designer can create an invoice line record table InvRecords and associated invoice insert templates and detail report templates. The line record table can have several columns consisting of calculation formulas such as tax and total calculations, and the tax formula can reference a single value on a second table called GlobalVariables that is set to a static 6%. In order to allow the tax rate to be updated to 4% without changing the tax and total for all historical invoices, the designer can have a notification event setup that is triggered by the insert page, which when executed executes a script with "SetValues(InvRecords,InvRecords,SetValueParams.CopyByValues|SetValueParams.Overwrite)" or the simplified equivalent "FreezeValues(InvRecords)". As a result, every time any invoice is inserted, all formulas in past invoices are resolved to static values ​​due to the CopyByValues ​​parameter, including cells with formulas that are not directly visible or editable on both templates.

[0384] The ability to combine a spreadsheet representation of records with record input through the use of user interface templates and an event model with the ability to process form changes allows webifier to be used effectively in a large number of additional scenarios with a flexibility greater than the sum of its parts, resulting in the need for fewer separate workflows and functions. Nonetheless, webifier may provide an optional method of specifying immutability using the "Make Values ​​Immutable" page option on an insert or edit page, which will resolve all formulas to static values ​​after a visitor submits data using that page.

[0385] Mobile considerations

[0386] According to an illustrative embodiment, the target system is preferably responsive and fully compatible on all device platforms and integrated with a variety of standard browser features such as HTML and file transfer. The target system can also be directly integrated into native applications through standard web service API access methods to the target system, allowing users to access the data contained in the target system in the native application experience. The target system implements data presentation in this method through additional coding in the native SDK environment or other development frameworks (such as AndroidStudio, Apple Xcode, Xamarin or Cordova).

[0387] Additionally, the target system can be integrated with a mobile device through the use of a webview for accessing functionality and displaying data. This can access the target page in its entirety within the application, or can be used to display smaller segments of data or access specific functionality.

[0388] The target system can execute fully interactive modules and other code snippets (code snippets can be scripts or compiled) that allow all target system elements to be included on other pages. For example, the target system can support HTML iframe elements to display any page type on a third-party hosting website.

[0389] The target system may also operate in this mode where file uploads are accepted from a cloud-based file storage application, which may or may not be running on the same mobile device. This transfer may be accomplished through direct file access or through an internet-accessible API.

[0390] Although the descriptions in this document refer to web pages and browsers, they are essentially unchanged in the case of mobile endpoints, such as the mobile endpoint above which is a substitute for a browser. The illustrative changes can be summarized as first installing an application on the mobile device, which is designed and built to operate in a manner very similar to a web browser, whether it is implemented primarily as a browser for pages or multiple requests for multiple fragments, which are put together with some user interface elements that make it consistent with the mobile platform. The second change is that the target system server can in some cases generate a different output format using one of the multiple formats and methods common in the software development industry, instead of generating CSS and HTML for the resulting target page. Finally, in contrast to HTML menus, mobile applications can have some hard-coded user interface elements, such as page menus, which can be specific and consistent for mobile platforms. Such hard-coded user interface elements can be a combination of web service API calls for obtaining data lists and hard-coded user interface code within the mobile application for processing these data lists.

[0391] Automatically convert to flow layout

[0392] Many devices (e.g. mobile phones) have small screens with various resolutions. In such cases, modern flow-based layouts can improve the user experience. Whether the visitor is using a browser, a native mobile app or something else, Webifier can use a variety of methods to fully or partially convert a template table into a flow layout to optimize the visitor experience and / or the use of screen space.

[0393] When generating a page based on a template, webifier may analyze template formatting, template content, template usage, content of the template data source, data source usage, and even other templates associated with the data source to determine how to convert to a flow layout. Converting to a flow layout may involve identifying the best modifications to the layout, such as cell wrapping to specific rows, modifications to spacing, addition of formatting to aid visual grouping (such as borders or background colors), and / or modifications to cell widths. webifier may analyze many factors, such as those described below, and use them in combination in a variety of ways to determine modifications to the layout. For example, webifier may weight each factor by a static value of the factor's importance, the most important factors will determine the most important modifications, calculate the number of factors that support each modification, and perform a weighted sum of the factors that support each modification, or many other methods that will be apparent to those skilled in the art.

[0394] The webifier may prefer to wrap on an empty cell rather than between two cells that do not have an empty column between them. In this case, much like word wrapping normally skips rendering whitespace at the end of a line when wrapping to a new line of text, an empty cell may not be rendered. The webifier may prefer to wrap on completely empty columns for all or most rows. When a row of a template wraps, the webifier may increase the vertical spacing before or after that row so that a visitor can more easily identify a group of rendered rows as a single template row, or in the case where the template row represents a single record, as a single record. The webifier may then also increase the vertical space between all other template rows to provide consistency. Optionally, the webifier may identify a vertical section, such as by positioning the first lower or upper template row around a completely empty wrapped template row, and only increase the vertical space between template rows within that section; striking a balance between maintaining clarity and using space efficiently, especially in templates with multiple sections. The webifier may analyze the template content as well as the formatting and determine that many rows are very similar and may identify this as a vertical section, and may further prefer the same column wrapping for all rows within that section. The webifier may analyze borders to identify vertical or horizontal sections; for example, if a border is around 3 horizontally adjacent cells, the webifier may prefer not to wrap between them, and may increase the vertical space before and / or after those cells. The webifier may determine that a template row has one or more empty cells to the left of the first column, and may skip rendering those empty cells so that the first cell with content on the row will be rendered closer to the left edge of the screen. The webifier may determine that several rows are within a vertical section and have different numbers of empty cells to the left of the content, and may skip an equal number of empty cells on the left for all rows in the section.

[0395] Webifier can analyze metadata of data sources (e.g., record tables) to help automatically convert to flow layouts. For example, if a template has two cells, one cell references record table column A, the second cell references record table column B, has a formula such as "=A*2", and the third cell references record table column C, has no formula references to A or B, then Webifier may prefer a break between the second and third cells, making the first and second cells more like a group visually.

[0396] The webifier may determine that a second template with additional information uses the template's data source, which additional information may be analyzed to determine the best way to render the flow layout. For example, a ReportInvoice template may have an "InvoiceRecords" data source that is also used by an "InsertInvoice" insert template. The webifier may determine that InsertInvoice has many empty rows and borders around cells that reference column D and column L data of the InvoiceRecord. Even though there is no indication on the rendered ReportInvoice that the cells of columns D and L that reference the same InvoiceRecords are closely related (such as borders or empty rows), the webifier may still prefer not to wrap lines between them or visually group them. The webifier may treat cell areas that have a different background or text color than the surrounding cells in the same way as borders.

[0397] Defining relationships

[0398] In some demanding usage scenarios, according to an illustrative embodiment, a designer may wish to have a primary record set in a record table and one or more "secondary records" for each primary record in another table for each primary record, where the designer intends the secondary records to be identified as belonging to the primary record by the "primary key" of the primary record set, as in the traditional database software sense. In short, the designer may intend to express relationships between record sets so that the template table is populated not only with primary record data, but also with secondary record data. For example, one record table may have columns "company ID" and "company name", and a row with values ​​"1" and "Pleasant Solutions". A second record table may have columns "company ID" and "specialization", and a row with "1" and "Software Development Consultants". According to the illustrative embodiment, if the designer wishes to display a detail page based on a template form that mentions "PleasantSolutions" and "Software Development Consultants", the webifier system supports such use.

[0399] For example, the target system may request in advanced mode or in a section that the designer select a column or row on one table and another column or row on another table to indicate a primary key relationship between them as primary and secondary record sets. Alternatively, the specification of this relationship may be done in the configuration form. The target system may then allow the designer to specify not only one range or form in the source input control, but also a list of them; or the target system may automatically imply other inputs based on the specified first range or form and the primary key relationship that binds it to the secondary table. The methods described elsewhere for referencing record data from within a template table may be applicable at that time. The designer may use a cell formula reference to either record table, and by parsing the formula reference, it will be clear whether it is the primary or secondary record set from which the designer intends to obtain data. The target system populated with referenced data from a secondary record set will be completed in the same manner as populated from the primary record set.

[0400] A library of "application templates"

[0401] According to the illustrative embodiments, an entire application template including a pre-designed and / or pre-configured spreadsheet definition may be provided from a designer community to other designer users, with or without additional information from additional systems (such as a list of pages and their sharing permissions), to copy and use the template as a starting point for their own web applications. Fig. 22 ). These can be listed separately and searchable by purpose, category, industry, or smart search, based on the page type in the application, the column names on the record table, keywords from any cell content, cell formatting (such as the presence of a date field), and associations with which designers are using the application template web application or the industry of the designer's employer.

[0402] The smart search may also check if any other application templates have been used from the same other designer to associate two designers as being more similar. Alternatively, the target system may compare a group of multiple spreadsheet definitions from one designer to a group of multiple spreadsheet definitions from another designer based on the presence of keywords from the cell content or any of the search parameters described above to determine the level of similarity between the two designers. The search may then rank the search results based on one or more of the factors described above, the frequency of occurrence of the factors, and the importance of the factors. The system may then utilize common market known techniques and commercially available libraries to optimize the user's search experience.

[0403] Add-on integration and communication

[0404] The plug-in or additional component in the electronic form tool can communicate with the target system server, and display its user interface for client-server application model in a variety of ways typical for the software development industry. For example, some diagrams illustrate that the additional component using the web service API calls the target system to obtain the main HTML interface, traditional web page, and illustratively displays the result of the web page in the framework, and the framework is similar to the embedded browser window and operates. This allows the reuse of many additional component user interface elements and makes it roughly consistent with its respective corresponding parts on the management page of the target system by the designer who does not install the additional component. Some functions in the additional component (such as, using the mouse to select the function of the cell range) can be limited, deleted or changed to limit the designer to type in the range, or these functions can be performed on the embedded electronic form tool with the target system management page. For the sake of brevity, even if not attached, the accompanying drawings representing the additional component user interface or the target system management page should be assumed to have their respective corresponding parts.

[0405] The integration of add-ons to spreadsheet tools can vary depending on the spreadsheet tool into which the add-in is integrated. For example, integration with Excel can be accomplished using Excel's COM Interop library (development and use documentation publicly available at https: / / msdn.microsoft.com / en-us / library / microsoft.office.interop.excel(v=office.15).aspx) or Office's JavaScript API (development and use documentation publicly available at https: / / dev.office.com / docs / add-ins / develop / understanding-the-javascript-api-for-office). Cloud-based spreadsheet tools (such as Google Sheets) can be integrated using a web service, such as the Google Sheets API (development and use documentation publicly available at https: / / developers.google.com / sheets / ), which has an easily accessible and easily understood web service API function, such as using "spreadsheets.get(ranges, includeGridData)" to retrieve a table range.

[0406] If the spreadsheet tool's integration program allows, the system can be integrated into the right-click menu for areas such as cells or table tabs, allowing the user to indicate that they intend to perform an action related to an item such as adding a page. For example, the user can right-click a group of cells and select "add page", rather than selecting add page and then specifying the source, so that the add page screen already knows what the source is without prompting the user for that information. Fig.14 ).

[0407] Additional Designer Navigation

[0408] Webifier can handle a large number of forms and can act on multiple types of forms simultaneously, so additional organization and navigation of forms may be beneficial to the designer. Webifier may have a target system with an embedded spreadsheet tool or it may have an Add-On that integrates with an external spreadsheet tool. The following aspects may apply similarly to either one.

[0409] Spreadsheet tools often have a single row of tabs, where each tab represents a single table in the workbook. Webifier can automatically group tabs based on their type; for example, autogen tables, virtual tables, and / or designer-built tables are each grouped together, and can be grouped based on subtype, such as virtual template tables and virtual record tables, or user-unmodified autogen record tables and user-modified autogen record tables. Webifier can record metadata for each table indicating how the table was first created to aid in grouping. Changing a table may cause a tab to move from one group to another, such as a user-modified table in the "user-unmodified autogen record tables" group. Tab grouping can be done in a familiar menu style, where only the group name appears, but by clicking on the group name, a list of subgroups or a list of tables appears. Webifier can automatically colorize the code and / or change the appearance of the tabs based on the type of table or group they represent.

[0410] When the designer is entering a formula into a cell, a spreadsheet tool typically allows the user to select a range of cells by clicking on the cells, rather than typing in the range definition, and the tool may allow the user to click on the tab of another table, select one or more cells from that table, and still focus on the cell formula text being entered, so that the selected range is automatically typed into the formula. Similarly, a webifier may allow clicking on tabs as well as groups and subgroups without losing focus. When a specific table in a group or subgroup is clicked and that table becomes visible, the webifier may also temporarily move or copy the table's tab so that it is at the top level of the tabs next to the group. After the user selects a different table, the webifier may move back or delete the table's top-level tab, or the tab may remain as the most recently used tab until a limit on the number of recent tabs displayed as the top level is reached, at which time the least frequently used top-level tab is deleted.

[0411] Often there are many possible autogen and / or virtual tables available to the designer, but which may not have been accessed yet. For example, a different virtual row record table may be created for each different region of cells selected as a basis. In this case, one of the tabs under the group may be dynamic and cause a dialog box to appear, requesting additional information from the designer for the required table, such as the region of cells on which the table is based. Once selected, that table will be displayed directly as the most recently accessed virtual table without providing input to the dialog box again, and if the designer is typing a formula, focus will be retained and the designer can continue to select regions from the table without indicating to the tool that formula editing is continuing.

[0412] Tight integration with spreadsheet tools

[0413] The webifier need not be a separate application from the spreadsheet tool, and need not be limited to communicating with the spreadsheet tool via means accessible to the external application. The webifier may instead have a close relationship whereby it may have deeper and / or more backend-based integration and communication channels with the spreadsheet tool. This may also be referred to herein as "tight integration".

[0414] According to some embodiments, the spreadsheet tool can be a web-based spreadsheet system with a spreadsheet control that is embedded within a page of a target system server and allows the spreadsheet definition to be edited in any familiar manner of a traditional spreadsheet tool when the designer accesses the administrative page of the target system. Alternatively, the reverse is possible, whereby the webifier can be a control embedded within the web interface of the cloud spreadsheet tool. In any case, the user interface presented to the designer can be more tightly integrated and simplified for the designer than the user interface of the spreadsheet tool as a separate application that allows limited integration points. For example, using a browser to navigate will have a unified history of both the spreadsheet and the target system URLs, and menus can be organized so that both the spreadsheet tool menu links and the target system menu links are grouped in logical groups rather than being partially separated by application boundaries.

[0415] As described above, data transfer, synchronization, and other backend processing between the spreadsheet tool and the webifier may or may not be tightly integrated, regardless of whether the user interface is implemented as more tightly integrated. For example, the webifier may use function calls to the spreadsheet tool's DLL library, in part or as a complete replacement for web service API calls, or conversely, the spreadsheet tool may use public function calls to the webifier's DLL library. This may provide performance advantages to the webifier and allow the integration to utilize additional programming techniques known to those skilled in the art, such as shared memory access, low-level coordination objects (such as mutexes and semaphores), access to shared files, shared access to the underlying database, and shared use of DLL libraries. For example, such flexibility may be advantageous for real-time change notifications between two components, where the two components are the webifier and the spreadsheet tool.

[0416] Alternatively, tight integration can allow the webifier to more directly access the underlying database data of the web-based spreadsheet tool, or conversely, allow the web-based spreadsheet tool to more directly access the underlying database data of the webifier, or have a fully shared database with a specially designed design pattern to allow the data of each component to directly reference the data of another component, such as through a database external key that references from a database table of one component to a database table of another component, or have the data of one component in a column of the same database table as a column containing data from another component. Direct access can provide significant performance advantages and can reduce implementation complexity. For example, tight integration can allow a single SQL query to confirm whether the record data of a target page has changed, and the location of the query to be efficiently evaluated in the database. An example of such a query can involve connecting a database table related to the definition of the source data of the target page to a database table that has a change log for specific cells that have been changed within the spreadsheet. For example, without tight integration at the database level, a webifier system might fetch a large amount of cell data from a spreadsheet tool's web service API, which in turn might cause the web-based spreadsheet tool to fetch data from its database; compare the cell data previously fetched by fetching the cell data from the webifier database, and map the list of changed cells back to all target pages that use cell references to that page.

[0417] Additionally, tight integration can use completely different schemas for database data, where the schema is more efficient for one or both components to process, than for the webifier and the spreadsheet tool to communicate in a common schema for spreadsheet definitions. For example, a typical spreadsheet definition might have XML data organized by tables, where all cells of the table are nested in the definition of each table, and where various cell attributes are nested in the XML definition of each cell, such as format and formula and value. With tight integration at the database level, examples can be non-XML, and can have normalized, rather than nested, database records, such as a database table having columns "sheetId", "rowId", "columnId", and "cellContents", where the IDs refer to other database tables. Furthermore, the database schema need not even group cell contents together. Cell contents can instead be divided so that one database table has all cell formats, a different database table has cell formulas, and a third database table has cached values ​​from cells where formulas are evaluated. Furthermore, cells may not need to be organized in rows and columns, as a database schema may have one or more database tables with a list of "units" that are defined simply by a unique ID and a formula, and the schema may also have a separate table or tables that allow cells to be mapped back to rows and columns in select situations, such as when the data is presented to the designer as a familiar spreadsheet or the designer interacts with the data in spreadsheet form on an ad hoc basis.

[0418] Tight integration can also allow the target system and the spreadsheet tool to not only reside on the same single server or group of servers, but also coordinate the most efficient use of the distributed cloud computing process. For example, if an additional distributed server needs to be spun up to support an increased load, all designer and visitor requests related to a particular web application can be delegated to the additional server, and instances of both components and all data for that particular web application can be provided to the additional server. Such delegation across web application boundaries will allow additional servers to maintain effective units regarding web applications, with minimal transmission required from other servers. In contrast, without tight integration, it would be more complicated to ensure that the specific server hosting the web application and the associated webifier program instance is the same server or even a server geographically close to the spreadsheet tool hosting the same web application; therefore, communication between components is either more complex or less efficient.

[0419] Overall, with tight integration, the spreadsheet tool and the webifier do not need to be separate applications, the communication between the two does not need to be in the form of a spreadsheet definition or through an API, the storage of data does not need to be in the spreadsheet definition form, but the designer is still able to manipulate records and / or UI templates when they are presented in the user interface in a spreadsheet form, and the visitor is still able to interact with the data in the target page not in a spreadsheet form.

[0420] Figure 3B An illustrative software architecture using the tight integration described above is shown. Figure 3B In FIG. 4 , the combined tight system 450 includes a spreadsheet tool logic 452 and a webifier logic 453 , both of which share a common memory 451 , and both of which are integrated with a communication mechanism 410 and shared libraries, etc.

[0421] like Figure 3B As further shown, a designer may create or make record form and template form changes to one or more worksheets using a browser 454, and the data changes are transmitted 401a to the close system's spreadsheet logic 452 as http post requests with a portion of the spreadsheet definition or as change events. The spreadsheet logic 452 may convert the data to an alternative "Efficient Format" before storing 402 the designer's changes, or may store the data in a native spreadsheet format. The designer may create or make changes to the target page configuration using the same or a different browser 455, which are transmitted 401b to the webifier logic controller 453 as http post requests, and then stored 402 in memory 451.

[0422] In response to an http get request 403b from a visitor's browser 456 to the webifier logic 453 to provide a target page, the webifier logic 453 retrieves 404 the required data from the memory 451. The webifier logic can convert the data into an html target page by evaluating and referencing the values ​​and formats in the template table and evaluating and referencing the values ​​in the record table based on the template table identification. The target page can be sent 405b to the visitor's browser 456. Figure 3B As shown, the visitor sees a page with: text labels found only in the template table, not in the record table; text labels originating from RecordSheet!A1; a value of "100" evaluating to RecordSheet!C2; and html input controls defined by the template table with values ​​from the RecordSheet, RecordSheet!A2 for the checkbox, and RecordSheet!B2 for the textbox. Figure 3B Also shown is the ability for the visitor to check a checkbox and submit in the browser 456, causing the browser to send an http post request 406b to the webifier logic controller 454. The webifier logic 453 processes the post request and updates the memory 451 with the new data, optionally storing a valid format or whatever format the system uses. If the designer's browser 454 still has an active session, the spreadsheet logic controller 452 can obtain the updated data 408 from the memory 451, or optionally through an integrated communication mechanism 410, and convert the data into one or more spreadsheet definitions or change events sufficient to update the spreadsheet definition presented in the designer browser 454, and send them 409 to the designer browser 409, causing the designer's worksheet to then display "true" in cell A2.

[0423] like Figure 3B As further shown, a web service API call 403m from a visitor's mobile device 457 running a mobile application (e.g., as opposed to a browser) may be an alternative to an http get request 403b. In this case, the webifier logic 453 may send 405m to the mobile application a response to the web service API call, which may be in XML format, which is processed by the mobile application along with device or operating system specific design information to generate an interface for the visitor. As in this example, the mobile device 457 renders the target page differently than the visitor's browser 456 renders the target page in terms of having a different layout, different control types, and / or lacking certain record data. Figure 3B The visitor is also shown to have the ability to change the text box value to true and click submit on the mobile device 457, causing the mobile application to send a web service API call 406m to the webifier logic controller 453. The webifier logic 453 processes the data received from the web service and updates the memory 451 with the new data. The remaining steps may be similar to those generated by the visitor's browser. Figure 3B As shown, the target page can be sent 405m to a visitor's mobile device 457 running a mobile application (e.g., as opposed to a browser), or it can be served to a browser executing on a mobile device, but with the appearance of the target page altered based on one or more characteristics of the mobile device (e.g., screen size, hardware capabilities, etc.).

[0424] Other features

[0425] The system can use the same approach as template tables for "list item templates". For example, a "list" page type can be created where the input and output will be the same as for a "details" page type, except that instead of requiring the user to select a row to see its details, all row records will be displayed one after another in list form, where each record in the list is displayed according to the item template. This allows, for example, to display a list of row records to a visitor where each record requires several lines of information but not enough to warrant a full detail page, or because repeated navigation to the detail page is undesirable.

[0426] Throughout the interface, designers may be presented with thumbnails that depict what the target page will look like to a visitor. Thumbnails may be inline in the user interface (e.g., as Fig.28 ) and / or are depicted as pop-ups when hovering over certain parts of the screen or specific controls (e.g., Fig.11 As shown in many of the drawings included in this document (including at least Fig.28 and Fig.11 ), the thumbnail can be defined from the spreadsheet depicted in the figure to depict the target page results.

[0427] According to some aspects, a page type may have an auto-refresh option specified for a period of time that will automatically cause the client application or browser to periodically refresh the target page for the visitor. Fig.48 ). Alternatively, the same notification and logging methods can be used to push notifications to client sessions that have new information for a page or portion of a page open, and the notification will provide the client with the necessary information to update the page or the client will send a new request to refresh the page. Using either method can benefit use cases for unmanned wall-style dashboards, such as those provided by browsers that can access web pages and satisfy refresh requests, so that new information is always visible without affecting viewer interactivity.

[0428] According to some aspects, the target system's permission system may also have rules that restrict designer permissions beyond just visitor or page permissions. Designer permissions may include access types such as read-only, read and share, or read and share and update, ( Fig.21 ) which has granularity at a per-application, per-page, or per-page level. For example, read-only permission on the application ExampleApp would prohibit the designer from modifying the spreadsheet definition of any table used in ExampleApp, prohibit changing the page configuration of pages in ExampleApp, but allow the designer to view the spreadsheet definition and its underlying formulas and settings.

[0429] According to some aspects, the target system can support cell formatting that makes one or more cells a clickable hyperlink to a specified URL, like many electronic spreadsheet tools. This can be done using properties on the cell text that specifies the URL, where the presence of the URL can imply other format changes, such as underline and blue.

[0430] Webifier can allow the designer to provide a list of data tables and / or regions and specify security permissions on them, similar to security permissions on a page. Webifier can then use these rules as a second layer of security in addition to per-page security. Webifier may then block access to the entire page or part of a page if a request for those pages results in access to the underlying data not being allowed. Webifier can log access violations and / or send notifications to administrators or designers.

[0431] The webifier may detect that nested templates are nested to a second level. The webifier may display any such second level nested templates in a pop-up window such that clicking on an area that specifies that the deepest nested template is to be embedded will bring up a pop-up window for that deepest nested template. For example, if an invoice template has a nested template for invoice items, and the invoice item template has an even deeper nested template for the past purchase history of that invoice item (which may not be feasible to display in the space allocated to the history template invoice item template), the webifier may automatically display an invoice page with the invoice items on which clicking on a portion of the invoice items will pull up a pop-up history. A similar example of deeply nested input formats may apply, where the deepest nested template may utilize a pop-up window to provide secondary input details.

[0432] As mentioned previously, webifier can allow designers to specify permissions of various granularities for other designers to modify the application. webifier can also allow designers to specify permissions of various granularities for visitors so that they can provide formulas during input and editing, and specify restrictions on whether the formulas are mathematical only, they can use formulas to refer to other cells that are available for editing on the same page, or they can refer to cells that are not editable or visible on the same page. In this way, webifier can allow visitors to become constrained sub-designers.

[0433] Illustrative use case scenarios.

[0434] According to an illustrative use case, a designer may open a Windows desktop version of a spreadsheet application, such as MICROSOFT EXCEL, in which the webifier add-on has been installed. The add-on may include a "Sidebar" as the main add-on user interface embedded in the spreadsheet application. The designer may be required to log in ( Fig.13 ). Successful login takes the designer to Fig.12 , which has a list of previously made web applications. The design can create a new spreadsheet file in the usual way available to spreadsheet applications, and then can click the upload arrow (upload to the server), such as Fig.12 The designer can set a name for the application ( Fig.23 ), and sends the nearly empty spreadsheet (in this example) to the server, making it a nearly empty web application. The designer then Fig.18 , and click the web application action menu to view Fig.69 , or click the Actions menu on the Web Application tab to view Fig.55 The user clicks Add Page in the menu and is taken to Fig.51 The form in the , which can display properties common to all page types, such as the page "Title", and can also display a drop-down box to select the desired target page type. Next, the designer will see a mostly blank form that requires the designer to define the page type settings and configurations according to the page type selection, which may be similar to Fig.26 (API), 27 (Calendar), 29 (Containers), 33 (CSV Import), 35 (Detailed Reports), 38 (Edit Cell), 41 (Edit Record), 43 (Insert), 45 (Link), 46 (PDF), 47 (Reports), 49 (Tab Container). Designers can repeat this process to create as many pages as needed, each containing one or more of the features described in this article.

[0435] Any of the features described above may be used with any specific aspect or embodiment of the present invention. Many combinations, modifications or changes to the features of the above aspects and embodiments will be apparent to those skilled in the art and are intended to form a part of the present invention. Any feature specifically described in connection with one embodiment or example may be used in any other embodiment or example. In addition, although the subject matter has been described in language specific to structural features and / or method actions, it should be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or actions described above. For example, the use of specific software applications, formulas, and function names is not intended to limit alternative applications, formulas, and / or function names that may be used unless the claims recite such specificity. Instead, the specific features and actions described above are disclosed as illustrative forms of implementing the claims.< / date>

Claims

1. A computer-implemented method for generating an interactive web application, the interactive web application comprising at least one web page, the method comprising: determining at least one primary data source within the electronic form, wherein the at least one primary data source corresponds to a first worksheet of the electronic form; determining at least one auxiliary data source within the electronic form, wherein the at least one auxiliary data source corresponds to a second, different worksheet of the electronic form; Determining relationships between records of the primary data source and records of the secondary data source; Automatically generating a third worksheet based on the determined relationship, the third worksheet including at least a portion of the records of the primary data source and at least a portion of the records of the secondary data source, wherein content of the third worksheet is synchronized with content of the first worksheet and content of the second worksheet, wherein a first row of the third worksheet includes at least one first cell selected from the primary data source based on the determined relationship and at least one different second cell selected from the secondary data source; generating a specific web page of the interactive web application based on at least one user interface template corresponding to the specific web page and stored in the electronic form, wherein the specific web page references a record of a third worksheet identified based on the at least one user interface template corresponding to the specific web page, wherein generating the specific web page of the interactive web application includes extracting content corresponding to the record of the third worksheet from the electronic form, and wherein the content corresponding to the record of the third worksheet is identified by the at least one user interface template corresponding to the specific web page; receiving user input corresponding to a record of a third worksheet through an input control associated with a particular web page of the interactive web application; and At least one record of the secondary data source corresponding to the record of the third worksheet is updated based on the received user input and based on the determined relationship.

2. The method according to claim 1, wherein: The third worksheet includes records of the primary data source and an indirect view of records of the secondary data source.

3. The method according to claim 1, further comprising: determining a first user interface template within the electronic form, wherein the first user interface template identifies a primary data source and a secondary data source, In response to determining the first user interface template, generating a third worksheet is performed, and the third worksheet includes at least a portion of the records of the primary data source and at least a portion of the records of the secondary data source.

4. The method according to claim 2, further comprising: determining a first user interface template within the electronic form, wherein the first user interface template identifies a primary data source and a secondary data source, In response to determining the first user interface template, generating a third worksheet is performed, and the third worksheet includes at least a portion of the records of the primary data source and at least a portion of the records of the secondary data source.

5. The method according to any one of claims 1 to 4, further comprising: In response to determining that the content of the primary data source or the secondary data source has changed, the corresponding content of the third worksheet is updated.

6. The method according to any one of claims 1 to 4, further comprising: In response to determining that the content of the third worksheet has been changed, the corresponding content of the first worksheet or the second worksheet is updated.

7. The method according to any one of claims 1 to 4, wherein: Determining the relationship between records of the primary data source and records of the secondary data source includes: A second user input is received, the second user input comprising an indication of a relationship between records of the primary data source and records of the secondary data source.

8. The method according to any one of claims 1 to 4, wherein: Determining the relationship between records of the primary data source and records of the secondary data source includes: Based on one or more characteristics of the first worksheet and the second worksheet, a relationship between records of the primary data source and records of the secondary data source is automatically detected.

9. The method according to any one of claims 1 to 4, wherein: Determining the relationship between records of the primary data source and records of the secondary data source includes: Based on one or more characteristics of at least one user interface template, relationships between records of the primary data source and records of the secondary data source are automatically detected.

10. The method according to any one of claims 1 to 4, wherein: Determining the relationship between records of the primary data source and records of the secondary data source includes: Automatically determine potential relationships between records of a primary data source and records of a secondary data source based on one or more clues; determining a level of reliability based on the identified potential relationships; and The identified potential relationships are accepted or rejected based on the determined reliability level.

11. The method according to any one of claims 1 to 4, wherein: Determine the relationship based on the record structure of the records of the primary data source and the records of the secondary data source.

12. The method according to any one of claims 1 to 4, wherein: Determining a relationship is based on common content of a first column of records of the primary data source and a second column of records of the secondary data source.

13. The method according to any one of claims 1 to 4, further comprising: In response to determining that the formatting of at least one first record of the third worksheet has been changed, updating the formatting of a corresponding second record of the first worksheet or the second worksheet; or In response to determining that the formatting of at least one first record of the first worksheet or the second worksheet has been changed, the formatting of the corresponding second record of the third worksheet is updated.

14. The method according to any one of claims 1 to 4, further comprising: determining at least one third data source within the electronic form, wherein the at least one third data source corresponds to a different fourth worksheet of the electronic form; determining a second relationship between the records of the third worksheet and the records of the third data source; and A fifth worksheet is automatically generated based on the determined second relationship, the fifth worksheet including at least a portion of the records of the third worksheet and at least a portion of the records of the third data source, wherein content of the fifth worksheet is synchronized with content of the first worksheet, content of the second worksheet, and content of the fourth worksheet.

15. A computing device configured to execute the method according to any one of claims 1-14.

16. A computer readable medium storing instructions which, when executed by one or more processors, cause the method of any one of claims 1 to 14 to be performed.

Images