Workbook Scripting Overview

The Dodeca Spreadsheet Management System features a robust low-code development environment known as Workbook Scripting. Workbook Scripts enables customers to automate tailoring of the data contained on a view worksheet, the formatting of the worksheet, and/or the behavior of a view generated in Dodeca. Workbook Scripts are composed of five components:

Four of these components are displayed in the Workbook Script Metadata Editor. Workbook Script Functions are primarily used in conjunction with Methods and, as such, will be discussed with Methods:

The top of the Workbook Script Metadata Editor contains a context-sensitive toolbar that will change based on the section selected. It contains tools relevant to the given section. The top strip of the toolbar contains buttons which filter the sections displayed to the user and can be used to maximize screen space.

The Workbook Script Definitions section provides a section for storing descriptive information about the given script.

The ViewID property is used to provide the workbook script arguments access to the Excel file used by the view to enable range name lists to be used for various Workbook Script method arguments. The ScriptSet property allows a developer to automatically integrate the logic of other workbook scripts into the given workbook script providing the ability to create reusable utility workbook scripts. The Comments property gives a developer the ability to document the workbook script. The DebugMode property provides the ability for a developer to specify how they can step through the logic of the Workbook Script.

Workbook Script Properties are similar in concept to variables in any other programming language.

Workbook Script Properties store values for use within the script. The value types include:

Properties may be shared between scripts. Properties may be set by the developer at design time or programmatically at runtime using the workbook script @PVal() Workbook Script Function.

Workbook Script EventLinks capture actions that occur within the environment and provide a hook onto which a developer may tailor the application.

There are currently over 110 events in the Dodeca Spreadsheet Management System for which EventLinks are available. Frequently used events include and event which occurs after a workbook opens and which allows a developer to modify a spreadsheet template dynamically and programmatically when the view is generated. Another frequently used event, the AfterBuild event, occurs after most of the view processing is complete and just before it is displayed. This event is often used to perform finalization tasks, such as locking cells and protecting the worksheet contents, just before the workbook is displayed to the user.

The default naming convention for Procedures wired to a given EventLink is the word “On” joined together with the event name. Though this naming convention is not mandatory, it makes the code easier to maintain in the future if the naming convention is used.

Workbook Script Procedures are the code actions that are taken in response to events, or in certain cases, in response to tool button clicks. Procedures, in turn, are composed of one or more Workbook Script Methods.

Methods are pre-written code modules that accept arguments to determine their actions. Methods dynamically change the Dodeca view instance by changing behaviors of the worksheet, cell formatting, data, or virtually any other aspect of the view worksheet or workbook.

Method arguments are the variables that control how the methods work. Each workbook script method has seven standard workbook script arguments common to all workbook script methods and may contain additional method arguments to provide instructions for how the method may be performed. The seven standard arguments are in the following table.

Most methods have additional arguments to control how the actions of the method are executed. In addition, methods may have multiple overloads where both the behavior of the method actions and the method arguments differ between the different overloads. For example, the SetEntry method, which is used to place data into a cell or a range of cells, has separate overloads for placing text into a cell, placing a number into a cell, placing a formula into a cell, and clearing the cell, among others.

In addition to Properties, EventLinks, Procedures, and Methods, another common workbook script component is the workbook script function. A workbook script function is a workbook script component that returns information not available via workbook script functions. There are workbook script functions that return information about workbook cells, the desktop environment, the file system, Essbase data points, and Essbase members, among other things. For example, there is a workbook script function that returns whether a given range is contained within a second range. There is another workbook book function that returns an Essbase member name based on the Essbase member alias. Here is the argument value editor showing how to select a workbook script function in the editor.

One of the most powerful features of workbook script methods is that most method arguments may contain calculated values. Depending on the argument, argument values may be hard-coded, selected from a picklist, calculated using Excel functions within the context of the view workbook, specified using a workbook script function, or by a combination of these ways.

In summary, Dodeca workbook scripts enable powerful tailored applications to be built in a low-code environment.