> For the complete documentation index, see [llms.txt](https://guardian.hedera.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://guardian.hedera.com/docs/develop/guardian/standard-registry/import-export-in-excel/import-and-export-excel-file-user-guide.md).
# 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. [Validation Rules](#id-2.-validation-rules)
3. [Demo Video](#id-3.-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.
Import section pops up where we need to upload .xlsx file:
#### 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:
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:\\
#### 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.
#### 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:
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 – unique name of the enumeration as listed in the shared **Enums** tab - 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
* ***Description*** – 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)
* ***Test Value*** – example of the valid data
{% hint style="info" %}
**Note:** Currently only expression containing simple arithmetic operations are supported for **Auto-Calculate** in the ***Test Value.*** When specified it would result an the generation of the function template as shown on the example below.
{% endhint %}
### 2. Export
Use the corresponding menu option : **Export schemas to Excel** in the Manage Policies or Policy Schemas pages.
### 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”**
2. The expression specified in the corresponding cell in the “Test Value” column
Expressions can contain references to cells in the ‘Test Value’ 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 ‘Description’ column.’
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.
**Structure of the ‘customLogicBlock’ code**
1. 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 ()
formulajs – reference to the formulajs library object allowing the use its functions in the script ()
done – special function to finish the execution of the script
2. 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.
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.
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.
4. Main calculations
The main body of the script is encapsulated 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:
5. ‘Entry’ function starting the execution of the calculation
## 2. Validation Rules
The following rules are enforced by the import engine. When a rule is violated the error message shown in the import dialog will describe what went wrong and how to fix it.
### Schema sheet
| Rule | What triggers it | How to fix |
| -------------------------------------- | --------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------- |
| Schema name must not be empty | Cell A1 of the schema sheet is blank | Enter the schema name in cell A1 |
| Column headers must match the template | A required column header (e.g. `Field Type`, `Required Field`) is missing or misspelled in the header row | Compare the header row against the downloaded template and correct any mismatches |
### Field rows
| Rule | What triggers it | How to fix |
| ---------------------------------------- | ------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Field Type must not be empty | The `Field Type` cell for a row is blank | Set a valid type: `Number`, `Integer`, `String`, `Boolean`, `Date`, `Time`, `DateTime`, `Duration`, `URL`, `URI`, `Email`, `Image`, `File`, `Pattern`, `Help Text`, `GeoJSON`, `HederaAccount`, `Prefix`, `Postfix`, `Auto-Calculate`, `Enum`, `Sub-Schema` |
| `Help Text` cannot be required | A `Help Text` field has `Required Field` = Yes | Set `Required Field` to No for all Help Text fields |
| `Auto-Calculate` must have an expression | An `Auto-Calculate` field has an empty `Parameter` cell | Enter a math expression referencing other field cells (e.g. `G6 + G7`) in the `Parameter` column |
| Field key cannot contain dots | The `Key` column value contains a `.` character | Remove dots from the key — dots are reserved as path separators |
| Field keys must be unique per schema | Two fields on the same sheet share the same key value | Rename one of the fields in the `Key` column so every key is unique within the sheet |
### Visibility column
| Rule | What triggers it | How to fix |
| ------------------------------------------ | ------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Visibility formula must be valid | The `Visibility` cell contains an unsupported formula | Use one of the supported formats: blank (always visible), `TRUE`, `FALSE`, `Hidden`, `EXACT(Gn,"value")`, `NOT(EXACT(Gn,"value"))`, `OR(EXACT(...), EXACT(...))`, `AND(EXACT(...), EXACT(...))` — where `Gn` is a cell reference in the `Test Value` column |
| Visibility references a non-existent field | The cell reference inside `EXACT(...)` does not correspond to any field defined above | Make sure the cell reference points to the `Test Value` cell of a field that exists in the same sheet |
### Enum fields
| Rule | What triggers it | How to fix |
| --------------------------------- | -------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Enum must exist in the Enums tab | An `Enum` field has no matching entry in the `Enums` tab | Add an entry to the `Enums` tab where `Schema name` exactly matches the owner schema name — the top-level schema name (cell A1) for root fields, or the sub-schema name (the `Parameter` value of the parent `Sub-Schema` field, or its `Description` if Parameter is empty) for nested fields — and `Field name` exactly matches the `Description` value of the enum field |
| Enum must have at least one value | An enum entry in the `Enums` tab has no values | Add at least one value in the `Value` column for the enum group |
| Enum values must be unique | The same value appears more than once in an enum list | Remove or rename the duplicate entry in the `Enums` tab |
| Enum must upload successfully | `Loaded to IPFS` is `Yes` but the upload failed | Check your IPFS configuration, or set `Loaded to IPFS` to `No` to store the enum inline instead |
### Enums tab (shared enum sheet)
| Rule | What triggers it | How to fix |
| --------------------------------- | ----------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------- |
| Column headers must match exactly | Any of the four header cells in the `Enums` sheet does not match the expected value | The headers must be exactly: column A — `Schema name`, column B — `Field name`, column C — `Loaded to IPFS`, column D — `Value` |
### Sub-schemas (inline)
| Rule | What triggers it | How to fix |
| ------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Sub-schema defined multiple times with different fields | The same sub-schema name (the `Parameter` value of a `Sub-Schema` field, or its `Description` if Parameter is empty) appears more than once in the sheet with different child fields | Ensure all occurrences of the sub-schema have the same field structure, or set a different `Parameter` value on one of them to treat them as separate sub-schemas |
## 3. Demo Video
[Youtube](https://www.youtube.com/watch?v=o-4NHLREyBo\&list=PLnld0e1pwLhqdR0F9dusqILDww6uZywwR\&index=14\&t=1s)
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://guardian.hedera.com/docs/develop/guardian/standard-registry/import-export-in-excel/import-and-export-excel-file-user-guide.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.