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.

📘

Improvements over AssemblyScript Code Hooks

Users of AssemblyScript Code Hooks will notice several improvements with JavaScript Code Hooks:

• Performance: code hooks are noticeably faster for large spreadsheets
• Language: code hooks were previously written in AssemblyScript, which has some limitations when compared to TypeScript/JavaScript
• Packages: code hooks have access to npm packages
• Data Fetching: code hooks can fetch data from the internet

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:

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:

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.

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

API Reference

import { fetch } from "oneschema"
import { Row } from "oneschema/validation"

const response = await fetch("https://webhook.site/cff17c25-3a51-49ce-9751-d880981ffba7")
const NAMES_DATA = await response.json()

export default function(rows: Row[]) {
    rows.forEach((row) => {
        if (!NAMES_DATA["names"].includes(row.values["first_name"])) {
            row.createError("first_name", {
              title: "Invalid Name",
              message: "This name is invalid.",
            })
        }
    })
}

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

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

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

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

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

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