Using Code Hooks

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 Transforms and Advanced Validations tabs of the Developer Dashboard.

Code Hooks can be written in JavaScript or TypeScript. 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. Read below to learn more about each type of code hook.

Code Hooks have the ability to fetch data from the internet and use npm packages.

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 restructured in order for it to look like a typical spreadsheet.

Post-upload code hooks require implementing a function with the following type signature:

function(rows: string[][]): string[][]

This function takes in the rows in the uploaded spreadsheet and returns a new set of rows.

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:

export default function(rows: string[][]): string[][] {
     const outRows = []

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

     rows.forEach((row) => {
         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)
         }
     })
     
     return outRows
 }

Post-mapping Code Hooks

Post-mapping code hooks perform transformations to the spreadsheet after it's been mapped. Each post-mapping code hook runs after the spreadsheet has been mapped.

The oneschema/postmapping package exposes several functions which allow you to perform operations on the spreadsheet, such as creating columns, transforming column values, and deleting rows.

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 using the hasColumn function. If this template column has been mapped and the first_name and last_name template columns have not been mapped, it uses createColumns to 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 using deleteColumn, so it won't appear in the "Review & finalize" step.

import { createColumns, deleteColumn, hasColumn } from "oneschema/postmapping"

export default function() {
  if (hasColumn("full_name") && !hasColumn("first_name") && !hasColumn("last_name")) {
    createColumns(["first_name", "last_name"], (row) => row["full_name"].split(" ", 2))
    deleteColumn("full_name")
  }
}

Example: Column Transforms

The transformColumn and transformColumns functions are useful for modifying column values. The following post-mapping hook checks if the phone1 column is empty and, if it is, updates it to be the value of the phone2 column.

import { transformColumn } from "oneschema/postmapping"

export default function() {
  transformColumn("phone1", (row) => {
    if (row["phone1"].length > 0) {
      // Do nothing.
      return null
    } else {
      return row["phone2"]
    }
  })
}

📘

Best Practice

If your code accesses a column, (e.g. last_name), you should either mark this column as "Column must be mapped" in the template, or call has_column("last_name") in your code before accessing values in this column.


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.

When creating a validation code hook, you must first select which template columns your code hook references. This lets us know when the validation should be run (and re-run if data changes).

Next, you must choose whether this is a row validation or a column validation. Row validations are the most common type of validation. These validations can be expressed using the values in a single row. Column validations, on the other hand, can only be expressed by referencing all values in a column. A typical example of a column validation is a column uniqueness check, where you need to look at all values in a column to determine if a given value is unique. See below for examples of each type of validation.

The last step is writing the code. You must export a function with the following signature: function(rows: Row[]): void. The function can use createError to create errors on the rows. Be sure to follow error message best practices. See below for example of row and column validations.

Example: Row Validation

This row validation expresses the constraint that state must be non-empty if city is non-empty:

import { Row } from "oneschema/validation"

export default function(rows: Row[]) {
    rows.forEach((row) => {
        if (row.values["city"] !== "" && row.values["state"] === "") {
            row.createError("state", {
              title: "Invalid State",
              message: "State must be non-empty when city is provided",
            })
        }
    })
}

Example: Column Validation

This column validation creates errors on cells in the "id" column if the an ID has already been seen on a previous row:

import { Row } from "oneschema/validation"

export default function(rows: Row[]) {
    const valuesSeen = new Set()
    rows.forEach((row) => {
      if (valuesSeen.has(row.values["id"])) {
        row.createError("id", {
          title: "Duplicate Value",
          message: "This value was seen on a previous row.",
        })
      } else {
        valuesSeen.add(row.values["id"])
      }
    })
}

Supported NPM packages

The following NPM packages are available to use in Code Hooks. If you would like to use a package not listed here please reach out to the OneSchema support team.

NPM packageVersion
dayjs1.11
dayjs/plugin/customParseFormat1.11
dataframe-js1.4
fuse.js6.6.2
libphonenumber-js1.11.3
uuid9.0
validator13.11.0

To use a package add an import statement to your code hook. For example import dayjs from "dayjs"


API Reference

Package: "oneschema"

Package: "oneschema/postmapping"

Package: "oneschema/validation"


Package: "oneschema"

function getUserJwtPayload(): Record<string, JsonValue>

Returns the decoded user JWT payload.


function getUserJwtData(key: string): JsonValue | undefined

Returns the user JWT's value for the given key.


type JsonPrimitive = string | number | boolean | null

type JsonObject = Partial<{ [key: string]: JsonValue }>

type JsonArray = Array<JsonValue>

type JsonValue = JsonPrimitive | JsonObject | JsonArray


const EMBED_USER_JWT: string

The encoded embed user JWT for the embed session.


FetchOptions

Fields

  • method?: string
    The HTTP method ("GET", "POST", etc.). If not specified, it is assumed to be a GET request.
  • body?: string
    The body of the request.
  • headers?: { [key: string]: string }
    Headers to be included with the request.

async function fetch(url: string, options?: FetchOptions): Promise<Response>

This function allows you to fetch data over the internet.

Example

The following postmapping code hook fills the email column with emails fetched from a URL:


import { createColumn, hasColumn, transformColumn } from "oneschema/postmapping"
import { fetch } from "oneschema"

const EMAIL_COLUMN_KEY = "email"

const response = await fetch("https://jsonplaceholder.typicode.com/users")
const users = await response.json()
const userEmails = users.map((user: { email: string }) => user.email)

export default function () {
    if (!hasColumn(EMAIL_COLUMN_KEY)) {
        createColumn(EMAIL_COLUMN_KEY)
    }

    let i = 0
    transformColumn(EMAIL_COLUMN_KEY, (_) => userEmails[i++ % userEmails.length])
}


Package: "oneschema/postmapping"

Column

Information about the column in a spreadsheet.

Fields

  • key: string
  • index: number
  • validation_options: { [name: string]: any } | undefined
    The validation options configured for this column.

CreateColumnOptions

Fields

  • index?: number
    A number representing the position of the column relative to other columns.

function createColumn(
key: string,
func?: (row: Record<string, string>) => string | null,
options?: CreateColumnOptions,
)

Creates a new column key. If func is defined it is used to populate the rows for the new column. func will be called for each row and the result of it will be set for the new column in this row. This function will raise an error if a column with key already exists.

Example

Create a full_name column by merging first_name and last_name:

createColumn("full_name", (row) => row["first_name"] + " " + row["last_name"])

Create an empty last_name column next to the first_name column:

createColumn("last_name", null, { index: findColumn("first_name").index })

function createColumns(
keys: string[],
func?: (row: Record<string, string>) => (string | null)[],
options?: CreateColumnOptions,
)

Creates multiple new columns with the given keys. If func is defined it is used to populate the rows for the new columns. func is called for each row and the result values will be used to populate the new columns for this row. If func returns more values than columns created it will ignore the additional values. If less values than columns are returned empty values will be inserted in the additional columns. This function will raise an error if a column for any of the provided keys already exists.

Example

Create first_name and last_name columns by splitting full_name:

createColumns(["first_name", "last_name"], (row) => row["full_name"].split(" "))

function deleteColumn(key: string)

Deletes the column with the given key. This function will raise an error if no column with the given key exists.


function deleteRows(
func: (row: Record<string, string>) => boolean,
)

Delete all rows for which func returns true.

Example

Delete all rows in which the id column is empty:

deleteRows((row) => row["id"] === "")

function findColumn(key: string): Column | undefined

Find a column with key. Returns the Column with key or undefined if no column with key exists.


function hasColumn(key: string): boolean

Check if a column with key exists. Returns true is a column with key exists, false otherwise.


function transformColumn(
key: string,
func: (row: Record<string, string>) => string | null,
)

Allows updating the values in column key. func will be called for each row. If it returns a value the cell will be updated with this value. This function will raise an error if column with key does not exists.

Example

Upper-case the full_name column:

transformColumn("full_name", (row) => row["full_name"].toUpperCase())

function transformColumns(
func: (row: Record<string, string>) => Record<string, string | null>
)

Allows updating multiple columns. func is called for each row and the result is used to update the cells. This function will raise an error if a column for any of the provided keys does not exists.

Example

Upper-case the first_name and last_name columns:

transformColumns((row) => {
    return {
        "first_name": row["first_name"].toUpperCase(),
        "last_name": row["last_name"].toUpperCase(),
    }
})

Package: "oneschema/validation"

Severity

An enum representing the severity of an error

Values

  • ERROR
  • WARNING

CreateErrorOptions

Fields

  • title: string
    The title for the error. This is displayed as the title on the error popover.
  • message: string
    A message indicating why the value is invalid. This message is displayed as the contents of the error popover.
  • severity?: Severity
    If this isn't specified, the severity is Severity.ERROR.
  • suggestion?: string | string[]
    If specified, one or more valid values to suggest as replacements for the invalid value.

Row

A row in the spreadsheet

Methods

  • createError(column: string, options: CreateErrorOptions)

    Create an error on the specified column with the given options. The options must include a title and a message.