💻Import and Export Excel file User Guide
Last updated
Last updated
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.
Use the corresponding menu option : Import schemas from Excel in the Manage Policies or Policy Schemas pages.
Import section pops up where we need to upload .xlsx file:
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:
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:
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.
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:
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)
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.
Table listing each field which the schema contains.
Note: The order of the columns in the spreadsheet is irrelevant.
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
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
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.
Use the corresponding menu option : Export schemas to Excel in the Manage Policies or Policy Schemas pages.
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”
The expression specified in the corresponding cell in the “Answer” column
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.
These field from embedded schemas must be grouped to ‘fold’ under the main field as on the example below.
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.'
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.
Structure of the ‘customLogicBlock’ code
Each customLogicBlock starts with a comment listing all pre-defined variable which contain data referenceable in the code.
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
Helper function
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.
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.
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.
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
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:
If Guardian was unable to parse the expression for any reason the following error comment would be placed in the code:
‘Entry’ function starting the execution of the calculation