# Import and Export Excel file User Guide

The ‘Import/Export schemas from/to Excel’ UI menu options trigger the process of seamless transformation of schemas written in Excel into valid Guardian JSON schemas, and vice versa. The content of such schema excel files must conform to the format presented in the template accessible via the corresponding button on the 'Policy Schemas’ page. The menu options for actioning Import and Export are accessible via the ‘Manage Policies’ and ‘Policy Schemas’ pages.

In Guardian schemas usually exist within a Policy, a Tool or embedded into another schema. To ease the process when a schema or a set of schemas are imported, they get imported into a context of a policy. Users can create a new empty policy for such purposes or use an existing policy. In the latter case all existing schemas in the policy are preserved (and can be manually deleted later), new schemas are added together with the basic policy blocks scaffolding which is inserted at the beginning of the policy flow.

When schemas are exported, they are packaged into a single Excel file formatted like the template mentioned above. It is recommended to experiment with exporting your existing policies and reviewing the resulting excel files so you can familiarize yourself with the format and the content.

1. [Step By Step Process](#id-1.-step-by-step-process)
2. [Demo Video](#id-2.-demo-video)

## 1. Step By Step Process

### 1. Import

Use the corresponding menu option : **Import schemas from Excel** in the Manage Policies or Policy Schemas pages.

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-dfcf5735eb3d10fffbbd951d9d6cb50117a2d993%2F0.png?alt=media" alt=""><figcaption></figcaption></figure>

Import section pops up where we need to upload .xlsx file:

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-5ca6c193632f8a8d4a4fa8eb0937244a2ba6b809%2F1.png?alt=media" alt=""><figcaption></figcaption></figure>

#### 1.1 Errors

In case, when the importing engine was unable to parse the content of the given excel file then Guardian indicates the existence of the problem to the user, and gives an option to skip the invalid part to continue importing by clicking on **Skip & Import** button:

![](https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-f1515f04cfceafcd9484b2e0115759808d5a75f9%2F2.png?alt=media)

In cases, where the importing engine was not able to parse the content of a field then Guardian would specifically highlight this field in the schema after the import in red color rows:\\

![](https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-56d57bfe6472329c3ad2eace22a2e59ca188bda1%2F3.png?alt=media)

![](https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-3671a24c14a1e1f9d8b98fd7f4334d1719a2384b%2F4.png?alt=media)

#### 1.2 Blocks

Importing schemas into a Guardian policy will result in a number of new autogenerated blocks appearing in the beginning of the policy flow which ‘hold’ each imported schema and its tool or form as shown in the screenshot below.

![](https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-5ac840968571fce91e57d63ed6f62a40227205b6%2F5.png?alt=media)

#### 1.3 Template

Guardian provides a basic downloadable Excel schema template which contains all possible types and structural elements supported by the system for importing. This template can be downloaded by clicking on the button highlighted in screenshot below:

![](https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-acbd3a39da1866cba39aecd3b47f68d66b3a7352%2F6.png?alt=media)

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-a38854a0b1aa308f36a33b91ab2e911afe04f670%2F7.png?alt=media" alt=""><figcaption></figcaption></figure>

All schemas created for importing into Guardian must follow the design of the template containing the following elements:

* ***Schema name*** – unique schema name (also used in the name of the page and is limited to 30 characters)
* ***Description** (optional)* – schema description
* ***Schema Type*** – the value in this field influences the type of the signature and additional system fields
  * **Verifiable Credentials (VC)** – linked to the policy and can be used in forms.
  * **Encrypted Verifiable Credential (EVC)** – linked to the policy and can be used in forms. Unlike a simple VC it gets encrypted when saved into IPFS.
  * **Sub-Schema (None)** – schemas without additional system fields which are suitable for embedded data.
* ***Tool*** *(optional) –* name of the Tool to which this schema belongs
* ***Tool Id*** – message id of the Tool to which this schema belongs (if relevant)

{% hint style="info" %}
**Note:** If the imported schema belongs to a Tool (not a Policy) - i.e. there is a Tool ID setting as per above - all extended information about the schema will not be processed during import. Since Tools are immutable and can only be referenced the only relevant information that would be used in the import is the Tool ID.
{% endhint %}

* Table listing each field which the schema contains.

{% hint style="info" %}
**Note:** The order of the columns in the spreadsheet is irrelevant.
{% endhint %}

* ***Required Field*** - this field must be filled our (Yes/No)
* ***Field Type*** – type of the data matching that of Schema concepts in Guardian
  * Enum
  * Number
  * Integer
  * String
  * Pattern – text field (String) which gets validated against the specified pattern
  * Boolean
  * Date
  * Time
  * DateTime
  * Duration
  * URL
  * URI
  * Email
  * Image
  * Help Text
  * GeoJSON
  * Prefix
  * Postfix
  * HederaAccount
  * Auto-Calculate
  * **Sub-schema name** – the name of the embedded schema (or the name of the tag in the spreadsheet)
* ***Parameter*** – additional field for information relevant for some data types
  * Enum – name of the enumeration (or the name of the tab in the spreadsheet) - list of possible options
  * Pattern – regular expression
  * Help Text – text style
  * Prefix – symbol
  * Postfix – symbol
* ***Visibility*** – determines the visibility of the field for user
  * No – always hidden
  * EXACT({FieldName},{Value}) – only shown when the condition is true
  * NOT(EXACT({FieldName},{Value})) – only shown when the condition is not true
* ***Question*** – Description of the field. This is the text which users would see when filling out the form in Guardian.
* ***Allow Multiple Answers*** – Determines if the data is an array or a single item (Yes/No)
* ***Answer*** – example of the valid data

{% hint style="info" %}
**Note:** Currently only expression containing simple arithmetic operations are supported for **Auto-Calculate** in the ***Answer.*** When specified it would result an the generation of the function template as shown on the example below.
{% endhint %}

![](https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-2dcedec85772b899cd19f3138f74c07979c3535d%2F8.png?alt=media)

### 2. Export

Use the corresponding menu option : **Export schemas to Excel** in the Manage Policies or Policy Schemas pages.

![](https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-c1fedceb2662853a30b956ba52357dec91e9d77d%2F9.png?alt=media)

![](https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-362792ce35a1aff51727b561a1e2ec2a7b1691f9%2F10.png?alt=media)

### 3. Calculations and math expressions

1. **How to specify them in Excel schemas**

For math expressions in Excel schema documents to be recognised by Guardian the following is required

1\. **‘Field Type’** value set to **“Auto-Calculate”**

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-57e7038202e0e3cd6aed44893e7d6030801587e4%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

2. The expression specified in the corresponding cell in the “Answer” column

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-bb7de453ce7ead2b6b58c9e8a1f1c0b551aa88da%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

Expressions can contain references to cells in the ‘Answer’ column from the current schema (as shown above) and all embedded schemas. To use fields from embedded schemas these fields need to be added to the parent (current) schema as shown below.

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-ba55da01ebaf4e3debe9060a1f174aa3e507fbe0%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

These field from embedded schemas must be grouped to ‘fold’ under the main field as on the example below.

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-d62be1919f903c83328db8d81d77a4bd54e7181e%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

Only the fields that are used in the expression need to be brought into the parent schema, all the others don’t need to be mentioned.

The fields from embedded schema definition tab (e.g. titled as ‘Production Device’ on the screenshot below) and their duplicates in the parent schema where they used for calculations expression must be identical at all times as best observed via values in the ‘Question' column.'

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-9687940db0293b3465cc32a83dea63e455cdd963%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

2. **How they are processed by Guardian on import**

On import for each VC schema imported Guardian will create basic scaffolding of Policy block, which includes “*requestVcDocumentBlock” and a “customLogicBlock”* if the imported schema contained’Auto-Calculate’ fields.

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-d5d6c6963c3b0d00dc49e325f0d7110d2ac75595%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

**Structure of the ‘customLogicBlock’ code**

1. Each customLogicBlock starts with a comment listing all pre-defined variable which contain data referenceable in the code.

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-55be1c4e6809a654305853447e031075b9ce7fba%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

documents – array containing VC documents passed to the customLogicBlock on entry

user – user which is executing the block

artifacts – array containing files linked with the block

mathjs – reference to the mathjs library object allowing the use its functions in the script (<https://mathjs.org/>)

formulajs – reference to the formulajs library object allowing the use its functions in the script (<https://formulajs.info/>)

done – special function to finish the execution of the script

2. Helper function

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-134973a5faae59b02ac3e2901787602b74e53f7e%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

Schema structure does not allow to leave empty fields in the resulting document. If he expression allows for an empty value (for example in the ‘if’ operator) then after the execution of the expression the field needs to be checked for empty value, and deleted if it’s empty. This is done by calling the clearUnsetField function as shown on the example below.

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-dd8d98151000cda5af9613c2d56047250477d115%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

3. Description of the functions used in the expressions in the customLogicBlock

For each function Guardian generates a template which helpfully lists in the comments the expressions in which the function is used.

If the function from the Excel file is supported by the formulajs library Guardian will automatically generated the corresponding method call as shown on the examples below.

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-8a41482e7557c79276c4a19c7fd337f11014acf4%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

If the Excel contains unsupported function Guardian would generate the comment as shown below. In these cases policy authors need to manually add the code replicating the original Excel functionality.

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-82e8ddc2eca13cc9fee9f95dc94f882b5c632b64%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

4. Main calculations

The main body of the script is incapsulated into the ‘main’ function and consist of the following main sections:\
\
\- Declaration of the used variables

\- Execution of the expressions

\- Retiring of the resulting document

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-bf04c780a377f7fe5017bca0cf0d1f116225fe13%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

In the cases where a variable used in the expression is absent in the VC document Guardian will generate a corresponding error comment in the code:

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-a009b56de0c535191683d32c6ef5a59237379424%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

If Guardian was unable to parse the expression for any reason the following error comment would be placed in the code:

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-77dadcf2cc91ab71a358ac1c9aa0eea77d213f0d%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

5. ‘Entry’ function starting the execution of the calculation

<figure><img src="https://501642358-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbKnJV8vV7zUxRwKIsJKg%2Fuploads%2Fgit-blob-cd5ac08d68b28d13f88c2c2307effcddfcea11e5%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

## 2. Demo Video

[Youtube](https://www.youtube.com/watch?v=o-4NHLREyBo\&list=PLnld0e1pwLhqdR0F9dusqILDww6uZywwR\&index=14\&t=1s)
