Simple Example

This page shows how to setup a simple activity in Excel with inputs and outputs, and how to publish it for consumption by end users in a web browser interface. A complete SheetKraft installation is assumed.

Create a new activity in a new schema

Activities are organized by schema at the top level. Typically a schema corresponds to a business process. All activities that make up the business process are organized under the schema for the business process. Any database tables necessary to hold data for the process are typically created under the schema.

Open Microsoft Excel and go to the SheetKraft ribbon.

Click on Define Activity > New Activity

NewActivity

Click on the + button next to the schema drop-down.

NewSchema

Provide a short name and a display name for the schema. The short name should be between 2 to 5 characters without spaces and preferable in lower case. This will be the name of the database schema. The display name will be shown to the end user on the website and should be sufficiently descriptive. It should not be more than a few words however as it is a name and not a description. Click Create.

Select the newly created schema. Provide a name for the activity. This name will be shown to the user on the website and should be in the business terminology of the user. Overly generic names like Import File or Generate Report are not good. Names like Import Trades or Portfolio Dashboard are good. Click Create.

A folder is created for the activity along with a Excel workbook. This workbook is opened in Excel. The logic for the activity will be setup in this workbook.

Copy sample input files into the Inputs folder for the activity

Open the activity folder using the Define Activity > Open Folder from the SheetKraft ribbon. This opens the folder for the currently open activity workbook. By default sub-folders Inputs and Outputs are created under the activity folder. Any sample input files used for setting up the activity should be placed under the Inputs folder. Further organization of files and folders under the Inputs folder can be done if desired.

A sample input text file for the purpose of this example can be downloaded here. Place this file in the Inputs folder.

Import the data from the text file

Click on Import From > Text/CSV on the SheetKraft ribbon DataFromText

Click the browse (...) button and select the input file from the Inputs folder. Note that the File's location is relative to workbook's location checkbox gets automatically checked when a file inside the activity folder is selected. This means that the automation refers to the path of the input file relative to the activity folder instead of an absolute path. When another user downloads the activity on another machine, the automation still works because the paths are relative.

Click on Delimited under File Type. The delimiter is inferred from the file contents and the preview of the imported data is shown. If the inferred delimiter is not correct, the correct delimiter can be chosen manually. Several more advanced options are available for the import but they will not be covered here. Click Next to accept the default values of these options.

A blank range needs to be chosen where the data will be imported. ResultSelection SheetKraft inserts the main formula for the import along with any auxiliary values and formulas in the worksheet. The user can choose the location where the insertion will occur. The location chosen should be entirely blank. Additionally, there should not be any used cells under the chosen location. This ensures that there is space for the import formula to expand vertically when the number of rows in the input file is greater than the number of rows in the sample file.

For this example select a range starting from the top-most cell in the sheet. The formula is evaluated and the data from the file is imported into the sheet. Formula

Note that Cell A2 contains a formula that returns the path of the input file. Cell C8 contains an array formula =DataFromText.SK($A$2,"PIPE",1,$C$2:$I$5,,,65001,"") All SheetKraft functions that return more than one cell of data are inserted as array formulas. If the inputs to the formula change so that the size of the result changes, the array formula is auto-adjusted to the correct size. The correct size may be smaller or larger than the size at the time of the initial setup. This adjustment happens every time the workbook is run using the Run Book button on the SheetKraft ribbon.

Filter the imported data

Type the value Threshold in cell L1 and the value 8% in cell M1 Select the range C7:I7 and press Ctrl + Shift + Down. This expands the selection to the entire imported data. Click on the Filter / Sort > Filter button in the SheetKraft ribbon and confirm the range selection. Usually SheetKraft is able to detect if the selection contains headers as in this case. However this detection may fail if all cells have textual data. If the My data has headers checkbox is not checked correctly, check it manually to indicate whether the selection has headers.

Change the filter condition to the one shown below Filter1

The result of the filter operation can be customized as necessary using the additional options in the UI not covered in this example. Click Next and choose the range starting from cell K7 to place the results. An array formula is inserted in cell K8. The filter operation runs and the results are shown on the sheet.

Try changing the value of the threshold in cell M1 and then click the Run Book button. The two steps in the automation setup so far run in the correct sequence with the new threshold. Observe how the array formula in cell K8 adjusts its size based on the number of rows that meet the filter condition.

Note the formula generated for the filter operation. =SelectRows.SK(RegionDown.SK($C$8:$I$8),FilterBy.SK($G$8>=$M$1),Headers.SK(0,1)) The RegionDown.SK($C$8:$I$8) part of the formula makes the automation dynamic. When the automation runs on a different file with a different number of rows, the size of the DataFromText array formula in cell C8 will adjust to return the correct number of rows. The filter operation will still work correctly because the formula considers all rows including and below C8:I8. This dynamism is at the heart of SheetKraft's ability to combine an arbitrary number of steps to produce a flow of calculations.

Export the filtered result into an Excel file

Select the range K7:Q7 and press Ctrl + Shift + Down to select the full region of the filtered result. Click Export > Export To Excel on the SheetKraft ribbon and confirm the range selection. The Export To Excel UI has a number of options to control how the data will be exported. This example will cover only the simplest case of exporting the selected range as a table of data with headers into a single Excel file with a single worksheet.

Export

Click the browse (...) button, navigate to the Outputs folder and specify the name of a file to be generated. Click Next and choose a range starting from cell S1 where the Export formula will be inserted. Note that cell T1 contains a formula specifying the path of the output file. This formula can be modified as desired to control the output file name. For example, it is common to generate a file name with a date in it using a formula like =RelativePath("Outputs\High Value Loans-"&TEXT(TODAY(),"yyyymmdd"))

Note that certain characters like \ are not allowed in file names and should not be used when using formulas like this.

Cell S9 contains the formula =ExportToExcel.SK($T$1, TRUE,,, RegionDown.SK($K$8:$Q$8),,,, IntoTable.SK(Report.SK($K$8,$L$8,$M$8,$N$8,$O$8,$P$8,$Q$8),0, $T$4:$Z$7,1,0,),,, Headers.SK(1)) This formula is responsible for exporting the filtered data into a file.

Click Run Book and check the generated file when the run completes.

The automation is complete at this point. Try changing the input file and the threshold and running the automation to verify that the exported file is generated correctly.

Define the Activity metadata

Although the automation logic is complete, some more work is required before the activity can be used by end users from the website. In particular, the inputs and outputs of the activity need to be defined so that a web interface can be generated for the activity. Also a Group, Function and Description should be provided to make it easy for end users to locate the activity and understand what it does.

Click Define Activity on the SheetKraft ribbon.

Click the + button on the bottom left on the inputs tab to add an input. Provide the name of the input as Loans file. Choose Single Cell in the Type drop-down and specify cell A2 for Range. The Value Type is auto detected as a FilePath.

Click the + button again to add another input. Provide the name of the input as Threshold, choose Single Cell in the Type drop-down and specify cell M1 for Range. The Value Type is auto detected as Number.

Inputs

Switch to the Outputs tab.

Click the + button on the bottom left to add an output. Provide the name of the output as Filtered Loans. Choose Single Cell in the Type drop-down and specify cell S9 for Range. The Value Type is auto detected as a FilePath.

Outputs

Switch to the Info tab and provide a Description, Group, and Function. Activities are organized in a three level hierarchy - schema, group, and function. Schema refers to an entire process. Group refers to a logically independent set of activities under the process. This might be useful for complex processes with a lot of activities. Function refers to the kind of work done by the activity. Unlike this example where data import, calculation, and export all occur in the same activity, typically activities do only one type of work.

Click Save to save the definitions and return to Excel.

Publishing the activity

To publish the activity, a login to the website is required. Click on the Login button on the SheetKraft ribbon.

Login

If this is the first login to the website from the SheetKraft addin, the Application Url will be blank. Enter the url used to login to SheetKraft from the browser. Note that the /login part of the url should not be included. Enter valid credentials and click Login. After the login is successful, the Activities window will be shown. Go back to Excel and click on the Define Activity button on the ribbon and click on Publish. A warning may be shown to review the files under the Files tab. This tab shows the files that will be included as part of the published activity. All files that may be needed by another user on another machine to test / edit the automation should be included. Output files and any other unnecessary files should be excluded. Large files should be avoided to keep the size of the activity package small. It is usually possible to work on small sample input files during the setup and use large files only for testing. Click on Publish again. A window will be shown to confirm the activities to be published. In general it is possible to publish more than one activity and this window serves to ensure that errors are not made.

Publish

Click on Publish again. The last column of the list will change to a tick mark once the publishing is complete.

Running the activity from the web interface.

Log in to the website from a web browser. The newly published activity will be listed on the activities page. Click the activity and a page will open where an input file can be uploaded and the threshold can be typed in. Click on Start and the activity will run on the server. Once the run is complete the filtered loans Excel file will be available for download.