=TABLEJSON

Overview

The =TABLEJSON function retrieves data from a specified process table and returns it as a JSON array of objects. Each object in the array represents one table row, with the specified number of columns included. This function is useful for converting tabular data into a structured JSON format for further processing or analysis.

Syntax

=TABLEJSON(NumberOfTableColumnsToRetrieve; ProcessTableID; OptionalTableQuery)

Parameters

NumberOfTableColumnsToRetrieve: The number of columns to include in the result

• Must be a positive integer

• Determines how many columns from the left of the table will be retrieved

• Cannot exceed the total number of columns in the table

ProcessTableID: The identifier of the table to retrieve data from

• Specifies which table contains the data to be extracted

• Must be a valid table identifier in the current account

OptionalTableQuery: An optional filter or query to apply to the table data

• Used to filter which rows are returned

• If omitted, all rows from the table are returned

Examples

Basic Usage

// Retrieve first 3 columns from the employee table (Process Table ID 123456789012345678)
// Table columns: [Name, Department, Salary, StartDate, Status]

=TABLEJSON(3; 123456789012345678; )
// Returns: [
//     {"Name": "Alice Johnson", "Department": "Sales", "Salary": 65000},
//     {"Name": "Bob Smith", "Department": "Marketing", "Salary": 58000},
//     {"Name": "Carol Davis", "Department": "IT", "Salary": 72000},
//     {"Name": "David Wilson", "Department": "HR", "Salary": 55000}
// ]

Retrieving All Columns

// Product inventory table (Process Table ID 234567890123456789)
// Table columns: [ProductID, Name, Category, Price, Stock]

=TABLEJSON(5; 234567890123456789; )
// Returns: [
//     {"ProductID": "P001", "Name": "Wireless Mouse", "Category": "Electronics", "Price": 25.99, "Stock": 150},
//     {"ProductID": "P002", "Name": "USB Cable", "Category": "Electronics", "Price": 12.50, "Stock": 200},
//     {"ProductID": "P003", "Name": "Notebook", "Category": "Office", "Price": 8.99, "Stock": 75},
//     {"ProductID": "P004", "Name": "Pen Set", "Category": "Office", "Price": 15.99, "Stock": 120}
// ]

Using Column Limits

// Customer data table (Process Table ID 345678901234567890)
// Table columns: [CustomerID, Name, Email, Phone, Address, City, State, ZipCode]

// Retrieve only first 4 columns
=TABLEJSON(4; 345678901234567890; )
// Returns: [
//     {"CustomerID": "C001", "Name": "John Doe", "Email": "[email protected]", "Phone": "555-0123"},
//     {"CustomerID": "C002", "Name": "Jane Smith", "Email": "[email protected]", "Phone": "555-0124"},
//     {"CustomerID": "C003", "Name": "Mike Johnson", "Email": "[email protected]", "Phone": "555-0125"}
// ]

// Retrieve only first 2 columns
=TABLEJSON(2; 345678901234567890; )
// Returns: [
//     {"CustomerID": "C001", "Name": "John Doe"},
//     {"CustomerID": "C002", "Name": "Jane Smith"},
//     {"CustomerID": "C003", "Name": "Mike Johnson"}
// ]

Important Notes

• Column ordering: Columns are retrieved from left to right based on their position in the source table

• Empty results: If no rows match the optional query, an empty array [] is returned

• Column limits: If NumberOfTableColumnsToRetrieve exceeds available columns, all available columns are returned

• Performance: Large tables with many columns may impact performance; use column limits appropriately

Note: Formula functions are case sensitive and must be in all caps.