[Beta] Importing Excel Workbooks

๐Ÿšง

Template groups are currently in beta. Please contact your OneSchema representative for more info.

Overview

Template groups allow for multiple sheets to be imported and validated concurrently by uploading an Excel workbook. They can be used to validate relational data (i.e. existence checks between two different sheets) between sheets.

โ—๏ธ

Template groups do not provide a way to perform error resolution in the OneSchema UI. An error pane will be displayed including a prompt to download an annotated Excel file. Once the errors are resolved offline, the file can be re-uploaded.

What is a Template Group?

ATemplate Group consists of two main components:

  1. An ordered list of OneSchema Templates
  2. A collection of Linked Validations

These define the ruleset for an uploaded Excel Workbook. Each sheet will be mapped (in order) to the corresponding OneSchema template. In addition to the standard data validation that comes with each individual template, linked validations can be used to validate relational data between sheets.

What is a Linked Validation?

Linked validations verify if a given value in one column exists in a specific column of another sheet. Every linked validation will consist of a referenced column (the source of truth) and a validated column (the column which needs checking). One example would be to ensure that all user_id values in sheet 2 also exist in the user_id column for sheet 1.

How to set up a Template Group

Create a Template Group

To create a template group, navigate to the Template Group tab in the top page header. A template group needs a name, key (to be used for initializing an embed), and an optional description.

Once at least one template group is created, all template groups will be shown in a table. Clicking on a specific template group row will open that group. The templates and linked validations associated with the template group can be updated on that page.

Attaching Templates

Templates can be attached to a template group by clicking the Add templates button. Templates can then be dragged into the correct order, ensuring the order of the templates within OneSchema matches the order in the Excel Workbook.

๐Ÿ“˜

Template Groups do not support having multiple of the same template in a group (all templates must be unique). As such, template groups do not support workflows which require multiple sheets going through the same OneSchema Template.

Attaching Linked Validations

Linked validations can be created with the Create a linked validation button. A linked validation can only be created if at least two templates are attached to the template group. When creating a linked validation, a reference column (the source of truth) and a validated column must be specified.

How to use an Embedded Template Group

Configuring the importer

Follow steps 1 and 3 (skip step 2) from the importer quickstart to generate a sample code snippet. Rather than providing a templateKey, pass in the templateGroupKey of the template group you would like to use. From there, launch a session as you would for a regular OneSchema Importer.

Using an embedded template group

Upon launching an embedded template group session, the user will be prompted to upload an Excel file.

  • If the user fails to provide all necessary sheets and/or all necessary columns, they will be shown an error screen where they can download a sample Excel file with all the specified sheets and columns
  • If the user uploads a sheet with all necessary sheets and columns, OneSchema will then proceed to the data validation step. If there are any errors (from standard validation or linked validations), the user will be prompted to download an annotated Excel file with error highlighting.
  • If the upload succeeds, the importer will init a success event.

On success, the importer will provide the workbook_id, which should be used to retrieve the uploaded file via API.