csvbox.io
  • Help Center
  • Getting Started
    • 1. Add Template
    • 2. Install Code
    • 3. Receive Data
  • Dashboard Settings
    • Column Options
    • Bulk Add Columns
    • Validations
    • Sheet Options
    • Styling
    • AI Bulk Transforms
  • Advanced Installation
    • Dynamic Columns
    • Virtual Columns
    • Unmapped Columns
    • Ignored Columns
    • Import Links
    • Validation Functions
    • Server Side Validation
    • Data Transforms
    • REST File API
    • Environment Variables
  • Data Destinations
    • Bubble.io
    • Zapier
    • Notion
    • Webflow
    • Private Mode
  • Account
    • Teams
  • Contact Us
  • App
  • Website
  • Login
  • Change Log
  • Suggestions
  • Legal
    • Privacy Policy
    • Terms of Use
    • Cookie Policy
    • User Data Policy
    • GDPR
      • Data Processing Addendum
    • SOC 2 Type 2
Powered by GitBook
On this page
  • Row Functions
  • Adding Row Functions
  • Example Row Functions
  • Variables in the Row Function
  • Error Response JSON Format for Row Functions
  • Column Functions
  • Adding Column Functions
  • Example Column Functions
  • Dependencies
  • Variables in the Column Function
  • Error Response JSON Format for Column Functions

Was this helpful?

  1. Advanced Installation

Validation Functions

Validate data with custom Javascript functions.

PreviousImport LinksNextServer Side Validation

Last updated 24 days ago

Was this helpful?

If the validations you require are not covered with the in-built in CSVbox then you can code your own custom validation functions in Javascript.

There are two types of Validation Functions: 1. Row Functions 2. Column Functions.

Row Functions run validation on each row of data and return an error message (if any) for the user. These functions run at the beginning of the "validate" step and then also when a row data is updated during the "validate" step. An example use case for Row Function is if you want to mark a cell as "mandatory" based on a specific value in another cell in the same row.

Column Functions run validation on a set of selected columns when the user clicks the Submit button on the "validate" step. These are best used in cases where entire column data is required for validation. For example, say you want to find duplicate entries in a column. You could grab all the values in the column, find duplicate values, and display the message to the user.

Row Functions

Adding Row Functions

  • Go to the edit sheet page > Columns tab > Click Add Functions button.

  • Add Function Name.

  • Select Row under Function Type.

  • Provide Javascript code.

  • Click Save.

Example Row Functions

Column 5 is mandatory only if the column 4 is not null.

//replace "col4" and "col5" with actual column names

if(csvbox.row["col4"] != "" && csvbox.row["col5"] == "") {
  let err = [
  {   
    "column": "col5",
    "message": "Column 5 is mandatory if Column 4 is not empty"
  }];    
  return err;  
}

Either col 2 or col 3 needs to have data.

//replace "col2" and "col3" with actual column names

if(csvbox.row["col2"] == "" && csvbox.row["col3"] == ""){
  let err = [
    {   
      "column": "col2",
      "message": "Columns 2 OR 3 needs to have data"
    },
    {
      "column": "col3",
      "message": "Columns 2 OR 3 needs to have data"
    }
  ];    
  return err;
}

Variables in the Row Function

You have access to data variables included in the csvbox object. The following data is available:

csvbox.row

It contains row data. Each cell in the row can be accessed by providing the column name. Examples:

csvbox.row["first_name"]
csvbox.row["order_id"]
csvbox.row["price"]

The column name must exist on the sheet or an error will be thrown.

csvbox.user

csvbox.user["user_id"]
csvbox.user["team_id"]
csvbox.user["isAuthenticated"]

csvbox.import

This refers to the current import-specific data. The following data is available:

csvbox.import["sheet_id"]
csvbox.import["sheet_name"]
csvbox.import["original_filename"]
csvbox.import["import_start_time"]
csvbox.import["destination_type"]
csvbox.import["total_rows"]
csvbox.import["row_number"] //current row number starting with 1

csvbox.columns

This object contains the column metadata (name, type).

//Example usage
let column = csvbox.columns['birthdate'];

if(column.type == 'date') {

   // code  
   
}

if(column.isDynamic) {
   
   // this is a dynamic column
   // code  
   
}

if(column.isUnmapped) {
   
   // this is a unmapped column
   // code  
   
}

csvbox.environment

// Example Usage
if(csvbox.environment["user_id"] && csvbox.environment["user_id"] == "abc123") {
 
 // code
 
}

console.log(csvbox);

With this statement, you can print all the available variables in the debugging console,

Error Response JSON Format for Row Functions

CSVbox will expect the Row Function to return an array of errors. Each error should specify the column the error appeared in, and a message to be displayed in the UI.

JSON Response Schema

Parameter
Type
Description

column

string

message

string

The message that is to be displayed to the user on the validation screen of the importer.

Example Row Function JSON Response

[
  {
    "column": "employee_id",
    "message": "Invalid Emp ID"
  },
  {   
    "column": "dept",
    "message": "Department does not exist"
  },
  {   
    "column": "employee_name",
    "message": "Employee's name has changed"
  }
]

Column Functions

Adding Column Functions

  • Go to the edit sheet page > Columns tab > Click Add Functions button.

  • Add Function Name.

  • Select Column under Function Type.

  • Add the Columns you need in the function.

  • Provide Javascript code.

  • Attach Dependent libraries (optional).

  • Click Save.

Example Column Functions

Check if a column has duplicate entries.

//replace "col4" with actual column name

function findRepeatingIndices(arr) {
  const repeatingIndices = {};
  
  for (let i = 0; i < arr.length; i++) {
    const element = arr[i];
    if (repeatingIndices[element] === undefined) {
      repeatingIndices[element] = [i];
    } else {
      repeatingIndices[element].push(i);
    }
  }
  
  const result = [];
  
  for (const key in repeatingIndices) {
    if (repeatingIndices[key].length > 1) {
      result.push(...repeatingIndices[key]);
    }
  }
  
  return result;
}

const arr = csvbox.column["col4"];
const repeatingIndices = findRepeatingIndices(arr);

let errs = [];

repeatingIndices.forEach(index => {
  errs.push({
    "row_id": (index + 1),
    "column": "col4",
    "message": "Duplicate entry."
  });
});

return errs;

Dependencies

In the Column Function Javascript snippet, you can utilize an external library that is hosted via a CDN. You can also call any external API endpoint to fetch real-time data. Simply add the library script tag and/or any custom scripts to the 'Dependencies' section and start using it in the main Javascript snippet.

Variables in the Column Function

You have access to data variables included in the csvbox object. The following data is available:

csvbox.column

It contains the entire column data. Each cell in the column can be accessed by providing the column name and the row number. The row number starts with 1. Examples:

csvbox.column["first_name"][1]
csvbox.column["order_id"][12]
csvbox.column["price"][10001]

Only the selected columns will be available in the Column Function.

csvbox.user

csvbox.user["user_id"]
csvbox.user["team_id"]
csvbox.user["isAuthenticated"]

csvbox.import

This refers to the current import-specific data. The following data is available:

csvbox.import["sheet_id"]
csvbox.import["sheet_name"]
csvbox.import["original_filename"]
csvbox.import["import_start_time"]
csvbox.import["destination_type"]
csvbox.import["total_rows"]
csvbox.import["row_number"] //current row number starting with 1

csvbox.environment

// Example Usage
if(csvbox.environment["user_id"] && csvbox.environment["user_id"] == "abc123") {
 
 // code
 
}

csvbox.columns

This object contains the column metadata (name, type).

//Example usage
let column = csvbox.columns['birthdate'];

if(column.type == 'date') {

   // code  
   
}

if(column.isDynamic) {
   
   // this is a dynamic column
   // code  
   
}

if(column.isUnmapped) {
   
   // this is a unmapped column
   // code  
   
}

console.log(csvbox);

With this statement, you can print all the available variables in the debugging console.

Error Response JSON Format for Column Functions

CSVbox will expect the Column Function to return an array of errors. Each error should specify the row_id, the column the error appeared in, and a message to be displayed in the UI.

JSON Response Schema for Column Functions

Parameter
Type
Description

row_id

integer

The row number of the error. Starts with 1.

column

string

message

string

The message to be displayed to the user on the validation screen of the importer.

Example Column Function JSON Response

[
  {
    "row_id": 1,
    "column": "employee_id",
    "message": "Invalid Emp ID"
  },
  {
    "row_id": 2,
    "column": "dept",
    "message": "Department does not exist"
  },
  {
    "row_id": 3,
    "column": "employee_name",
    "message": "Employee's name has changed"
  }
]

It contains the defined while initializing the importer. Examples:

It contains the that are passed during importer initialization.

The of the error. It is case sensitive.

You can also enter the names to access them in the Validation Functions.

It contains the defined while initializing the importer. Examples:

It contains the that are passed during importer initialization.

The of the error. It is case sensitive.

environment variables
Dynamic Column
environment variables
column name
column name
data type validations
Validation Functions
Adding Row Functions
Adding Column Functions
custom user attributes
custom user attributes