Code Hooks

Suggest Edits

Overview

Code Hooks allow you to write custom functions for validating uploaded data and modifying uploaded data at different points in the import process. They are created and attached to templates on the Code Hooks tab of the Developer Dashboard.

Code Hooks are written in AssemblyScript, a language very similar to TypeScript (JavaScript with types). There are currently three types of code hooks: post-upload code hooks, post-mapping code hooks, and validation code hooks. Each type of code hook requires a function to be implemented with a specific type signature.


Post-upload Code Hooks

Post-upload code hooks allow you to modify data directly after it's been uploaded. This is useful if the data that's uploaded needs to be massaged in order for it to look like a typical spreadsheet.

A post-upload code hook requires a function called process to be implemented that takes a RawSheet object and transforms it in some way.

Example: Nested Data

The following spreadsheet cannot be imported as-is:

Company: Acme  
Name Email
Sharlene Breach [email protected]
Mariya Son [email protected]
   
Company: Batman, Inc.  
Name Email
Francklyn Soles [email protected]
Rhea Lumsdon [email protected]

A post-upload code hook can transform this data into something more usable. In this case, we may want the data to look like this:

Company Name Email
Acme Sharlene Breach [email protected]
Acme Mariya Son [email protected]
Batman, Inc. Francklyn Soles [email protected]
Batman, Inc. Rhea Lumsdon [email protected]

The following post-upload code hook performs this transformation:

 import { RawSheet } from "./oneschema"

 export function process(sheet: RawSheet): void {
     let outRows: Array<Array<string>> = new Array()

     let rows = sheet.rows

     let company = ""
     let headerRowNext = false
     let headersSet = false

     for (let i = 0, numRows = rows.length; i < numRows; i++) {
         const row = rows[i]  
         if (row[0].startsWith("Company: ")) {
             company = row[0].replace("Company: ", "")
             headerRowNext = true
         } else if (headerRowNext) {
            if (!headersSet) {
              row.unshift("Company")
              outRows.push(row)
              headersSet = true
            }
            headerRowNext = false
         } else if (row[0] != "") {
             row.unshift(company)
             outRows.push(row)
         }
     }
     
    sheet.rows = outRows
 }

Post-mapping Code Hooks

Post-mapping code hooks perform transformations to the spreadsheet after it's been mapped.

A post-mapping code hooks requires a function called onMapping to be implemented that takes a Sheet object. The function can make changes to the sheet, such as creating columns and updating values.

Examples of things post-mapping code hooks are useful for include splitting and merging columns.

Example: Column Splitting

The following post-mapping hook looks for a column mapped to the full_name template column. If this template column has been mapped and the first_name and last_name template columns have not been mapped, it will create first_name and last_name columns and populate them using the data in the full_name column. The full_name column is then deleted, so it won't appear in the "Review & finalize" step.

import { Sheet } from "./oneschema"

export function onMapping(sheet: Sheet): void {
  if (
    !sheet.hasColumn("full_name")
      || sheet.hasColumn("first_name")
      || sheet.hasColumn("last_name")
  ) {
    return
  }

  sheet.createEmptyColumn("first_name")
  sheet.createEmptyColumn("last_name")

  sheet.getRows().forEach((row) => {
    const parts = row.get("full_name").split(" ", 2)
    if (parts.length == 2) {
      row.set("first_name", parts[0])
      row.set("last_name", parts[1])
    }
  })

  sheet.deleteColumn("full_name")
}

Validation Code Hooks

Validation code hooks perform custom validations on the data in a spreadsheet. They can add errors and warnings to cells in the spreadsheet. These are useful if the validations you'd like to perform cannot be done using the existing data type validations in OneSchema. These validations are run directly after mapping and after any change has been made to the values in the spreadsheet.

A validation code hook requires a function called validate to be implemented which takes a Sheet and returns a list of ValidationErrors. Each ValidationError includes the column and row number that the error should be applied to as well as a custom error message. The error can also include the title on the error popover which is displayed on the cell in the UI, a suggestion which is recommended to the user, and the severity of the error.

Example: Multi-column Uniqueness

A common use case for validation code hooks is multi-column uniqueness validations. The following hook creates errors on the first_name and last_name columns if the (first_name, last_name) pair exists on multiple rows. It works by first making a pass through the spreadsheet to get the number of occurrences of each (first_name, last_name) pair. It makes another pass through the sheet to create errors on duplicated names.

import { Sheet, ValidationError } from "./oneschema"

const ERROR_MESSAGE = "This name exists on multiple rows."
const ERROR_TITLE = "Duplicate name"

export function validate(sheet: Sheet): ValidationError[] {
  const errors: Array<ValidationError> = []
  const keyCounts = new Map<string, i32>()
  
  const rows = sheet.getRows();
  for (let i = 0; i < rows.length; i++) {
    const row = rows[i]
    const key = row.get("first_name") + row.get("last_name")
    if (keyCounts.has(key)) {
      const count = keyCounts.get(key)
      keyCounts.set(key, count + 1)
    } else {
      keyCounts.set(key, 1)
    }
  }

  for (let i = 0; i < rows.length; i++) {
    const row = rows[i]
    const key = row.get("first_name") + row.get("last_name")
    if (keyCounts.get(key) > 1) {
      errors.push(
        new ValidationError("first_name", row.id, ERROR_MESSAGE)
          .setPopoverTitle(ERROR_TITLE)
      )
      errors.push(
        new ValidationError("last_name", row.id, ERROR_MESSAGE)
          .setPopoverTitle(ERROR_TITLE)
      )
    }
  }

  return errors
}

The errors that are returned by this function will be surfaced on the "Review & finalize" step:



API Reference

The following types are defined in the module "oneschema" and can be imported in code hooks. For more information about AssemblyScript and the types defined in the AssemblyScript standard library, see here.

Column

A column in a mapped spreadsheet

Fields

  • index: i32

    A number representing the order of this column relative to other columns

  • name: string

    The name of the column in the uploaded file

  • templateColumnKey: string

    The key of the template column that this column is mapped to

RawSheet

The contents of an uploaded spreadsheet before the spreadsheet has been mapped

Fields

  • rows: Array<Array<string>>

    The contents of the spreadsheet

Regex

This is a utility class that we've included since AssemblyScript doesn't have native support for regular expressions. It wraps the Rust regex library. Navigate to that link to learn more about the regular expressions that are accepted. Note that you will need to escape backslash characters.

Example:

const r = new Regex("\\w:[0-9]+:\\w")
r.isMatch("abc:123:def") // true

Constructor

  • new Regex(regex: string)

    Compiles a regular expression.

Methods

  • isMatch(text: string): bool

    Returns true if there is a match for the regex in the given string.

  • captures(text: string): RegexCaptures | null

    Returns the capture groups corresponding to the first match in text. Capture group 0 always corresponds to the entire match. If no match is found, then null is returned.

RegexCaptures

Methods

  • get(i: i32): string

    Returns the match associated with the capture group at index i. If i does not correspond to a capture group or if the capture group did not participate in the match, then an error is thrown. To check if i is a valid index, use #has(i).

  • has(i: i32): bool

    Returns true if the capture group at index i exists and participated in the match.

  • getName(name: string): string

    Returns the match for the capture group named name. If name isn’t a valid capture group or if the capture group did not participate in the match, then an error is thrown. To check if name is a valid capture name, use #hasName(name).

  • hasName(name: string): bool

    Returns true if the capture group named name exists and participated in the match.

Row

A row in a mapped spreadsheet

Methods

  • get(columnKey: string): string

    Returns the value in the row with the given template column key.

    If columnKey does not exist in the template or is not mapped, a runtime error is throw. To check if the column exists in the sheet, use Sheet#hasColumn.

  • set(columnKey: string, value: string): void

    Sets the value in the row with the template column key columnKey to value.

    If the column key doesn't exist in the template or it isn't mapped, an error is thrown.

  • delete(): void

    Removes this row from the sheet.

Severity

An enum representing the severity of an error

Values

  • ERROR

  • WARNING


Sheet

A spreadsheet that's been uploaded and mapped

Fields

  • template: Template

    The template that's been applied to the sheet

Methods

  • createEmptyColumn(columnKey: string, index?: i32): void

    Create an empty column and map it to the template column with the given columnKey.

    If index is included, this is a number that represents the ordering of this column in the spreadsheet relative to other columns. If it isn't included, the column is added on the right.

    If columnKey does not match any of the template column keys or if there is already a column mapped to it, a runtime error is thrown.

  • deleteColumn(columnKey: string): void

    Delete the column with the given template columnKey.

    If a column with the given column key does not exist, a runtime error will be thrown.

  • getColumn(columnKey: string): Column

    Get the column with the given key.

    If a column with the given column key does not exist, a runtime error will be thrown. Use #hasColumn to check if the column exists.

  • getRows(): Array<Row>

    Returns Row objects, each Row corresponding to a row in the sheet.

  • hasColumn(columnKey: string): bool

    Returns true if columnKey exists in the template and a column is mapped to it.

Template

Fields

  • columns: Array<TemplateColumn>

    The template columns which comprise this template

TemplateColumn

Fields

  • key: string

    The template column key

  • name: string

    The template column name

ValidationError

An error or warning on a cell in a spreadsheet

Constructor

  • new ValidationError(columnKey: string, rowId: i32, message: string)

    Create an error on the column specified by the given template column key and row id. The message parameter should be a description of the error.

    By default, the error's severity is Severity.ERROR.

Methods

  • setPopoverTitle(title: string | null): ValidationError

    Set the title of the popover displayed on the "Review & finalize" pane. Returns this.

  • setSeverity(severity: Severity): ValidationError

    Set the severity of the error. This is useful for creating warnings. Returns this.

  • setSuggestion(suggestion: string | null): ValidationError

    Set a suggested value which will be displayed in the error popover. Returns this.


Frequently Asked Questions

How do code hooks work?

Code hooks are written in AssemblyScript and compiled to WebAssembly bytecode (wasm code) by the AssemblyScript compiler. At runtime, the bytecode is loaded into a sandboxed Wasmtime runtime which includes custom functions that allow the bytecode to perform operations on user-uploaded spreadsheets. The bytecode is then executed in the Wasmtime runtime.

Using WebAssembly and Wasmtime in this way allows us to run code hooks securely and quickly. Code hooks should be fast, even when run on spreadsheets with millions of rows.

Why AssemblyScript?

AssemblyScript can be compiled quickly into very efficient (small and fast) WebAssembly bytecode. It's also very similar to TypeScript, a language which many developers are already familiar with.

Why do I get this error: "ERROR AS100: Not implemented: Closures"?

Closures are commonly used in JavaScript/TypeScript but are not yet implemented in AssemblyScript. See here for some workarounds.


Updated about 1 month ago