### Get Column Names and Types in SeaTable

Source: https://developer.seatable.com/scripts/javascript/objects

Fetches all column names and their corresponding types for a specified table in SeaTable. It iterates through the columns of a given table, outputting the name and type for each.

```javascript
const table = base.getTableByName('Table1'); 
const columns = base.getColumns(table);

for (var i=0; i<columns.length; i++) {  
    output.text(columns[i].name + " (" + columns[i].type + ")")
}
```

--------------------------------

### Get Number of Tables in SeaTable Base

Source: https://developer.seatable.com/scripts/javascript/objects

Retrieves and outputs the total number of tables within the current SeaTable base. This requires access to the `base` object, which is predefined in the SeaTable scripting environment.

```javascript
const tables = base.getTables(); 
output.text(tables.length);
```

--------------------------------

### Filter Number Columns with Various Operators (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Provides examples for filtering Number columns using greater-less comparisons, equal-unequal comparisons, and arithmetic operators. Supports numeric values and empty string checks.

```javascript
base.filter('Table1', 'Default View', "column_name>18")
base.filter('Table1', 'Default View', "column_name>-10 and column_name<=0")
```

```javascript
base.filter('Table1', 'Default View',"column_name<>20")
base.filter('Table1', 'Default View', "column_name=0")
base.filter('Table1', 'Default View',"column_name=''")
```

```javascript
base.filter('Table1', 'Default View', "column_name+3>18")
base.filter('Table1', 'Default View', "column_name*2=18")
base.filter('Table1', 'Default View', "column_name-2=18")
base.filter('Table1', 'Default View', "column_name/2=18")
base.filter('Table1', 'Default View', "column_name^2=18")
base.filter('Table1', 'Default View', "column_name%2=1")
```

--------------------------------

### Filter List-based Columns (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Shows how to filter rows based on List-based columns (Multiple Select). This example demonstrates an 'and' condition to filter for rows containing specific selections.

```javascript
// Equal-unequal query
base.filter('Table1','Default View', "column_name='A' and column_name='B'")
```

--------------------------------

### Get All Table Objects - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/tables

Fetches an array containing all table objects within the current SeaTable base. This function is essential for iterating through or accessing multiple tables. The output is an array of table objects.

```javascript
base.getTables();
```

```javascript
const tables = base.getTables();
output.text(tables);
```

--------------------------------

### Get Row Content from SeaTable View

Source: https://developer.seatable.com/scripts/javascript/objects

Retrieves all rows from a specific view within a SeaTable table and outputs their content. The script iterates through each row and prints its data, including the content of the 'Name' column.

```javascript
const table = base.getTableByName('Table1'); 
const view = base.getViewByName(table, 'Default View'); 
const rows = base.getRows(table, view);

for (var i=0; i<rows.length; i++) { 
    const row = rows[i];
    output.text('>>> new row <<<')
    output.text(row);
    output.text(row['Name']);
}
```

--------------------------------

### Get Linked Records (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/links

Fetches records linked to one or more rows in a source table. It takes the source table ID, the link column key, and an array of row objects (including row ID, limit, and offset) as input. The output is an object mapping source row IDs to arrays of their corresponding linked records. This function is asynchronous.

```javascript
await base.getLinkedRecords(tableId: String, linkColumnKey: String, linkedRows: Object) ;

// Example:
await base.getLinkedRecords('0000', '89o4', [
{'row_id': 'FzNqJxVUT8KrRjewBkPp8Q', 'limit': 2, 'offset': 0},
{'row_id': 'Jmnrkn6TQdyRg1KmOM4zZg', 'limit': 20}
]);
```

```javascript
// Example: Get linked records from current row
const table = base.getTableByName('Table1');
const linkColumn = base.getColumnByName(table, 'Table2 link');
const currentRowLinks = await base.getLinkedRecords(table._id, linkColumn.key, [{'row_id': base.context.currentRow._id, 'limit':100  }]);currentRowLinks[base.context.currentRow._id].forEach((link) => {output.text(link)});
```

--------------------------------

### Get All Columns (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/columns

Fetches an array containing all column objects for a specified table. The table can be provided as an object or a string (table name). This function is useful for iterating through all columns or performing bulk operations. It throws an error if the table does not exist.

```javascript
base.getColumns(table: Object/String);

// Example:
const table  = base.getTableByName('Table1');
const columns = base.getColumns(table);

columns.forEach((column) => {
    output.text(column.name);
})
```

```javascript
const columns = base.getColumns('Table1');

columns.forEach((column) => {
    output.text(column.name);
})
```

--------------------------------

### Get Table by Name - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/tables

Retrieves a specific table object from the SeaTable base using its name. If a table with the provided name does not exist, it returns undefined. This is useful for targeted operations on a known table.

```javascript
base.getTableByName(tableName: String);
```

```javascript
const table = base.getTableByName('Table1');
// Display only table _id
output.text(`The id of the table is: ${table._id}`);
// Display whole table structure
output.text(table);
```

--------------------------------

### Get First Matching Row with Filter (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Retrieves the first row from a QuerySet that matches a specified `filterExpression`. This is a convenient shortcut for `querySet.filter(filterExpression).first()`. Returns undefined if no row matches or '#ERROR!' if the expression is invalid.

```javascript
// Get the first data of Tom in the Name column of the querySet
const querySet = base.filter('Table1', 'Default View', 'number = 5');
const row = querySet.get("Name = 'Tom'");
```

--------------------------------

### List All Views in SeaTable JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/views

Retrieves an array containing all views associated with a specified table. The table can be provided as an object or a string (table name). This function is used to get a collection of view objects.

```javascript
base.listViews(table: Object/String);
```

```javascript
const table  = base.getTableByName('Table1');
const views = base.listViews(table);
output.text(views.length);
```

--------------------------------

### Get All Rows from QuerySet (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Retrieves all filtered rows from a QuerySet as an array of row objects. This method is useful for processing all matching records after an initial filter operation.

```javascript
const querySet = base.filter('Table1', 'Default View', 'number = 5');
const list = querySet.all();
output.text(list);
```

--------------------------------

### Get First Row from QuerySet (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Retrieves the first row from a QuerySet that matches the filter. Returns undefined if the QuerySet is empty. Useful for accessing a specific record without fetching all.

```javascript
const querySet = base.filter('Table1', 'Default View', 'number = 5');
const row = querySet.first();
```

--------------------------------

### Get Grouped Rows Javascript

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Retrieves grouped rows from a specified table and view. This function is used when dealing with tables that have multiple grouping rules or subgroups.

```javascript
const groupViewRows = base.getGroupedRows('Table1', 'GroupedView');
```

--------------------------------

### Get Shown Columns in View (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/columns

Fetches an array of column objects that are currently displayed in a specific view of a table. Hidden columns are excluded from the result. Both the table and the view can be provided as objects or their respective names (strings). Throws an error if either the table or view does not exist.

```javascript
base.getShownColumns(table: Object/String, view: Object/String);

// Example:
const table  = base.getTableByName('Table1');
const view = base.getViewByName(table, 'Default View');
const columns = base.getShownColumns(table, view);
columns.forEach((column) => {
    output.text(column.name);
})
```

```javascript
const columns = base.getShownColumns('Table1', 'Default View');
columns.forEach((column) => {
    output.text(column.name);
})
```

--------------------------------

### Get Grouped Rows from a Seatable View

Source: https://developer.seatable.com/scripts/javascript/objects/rows

This snippet demonstrates how to fetch rows from a Seatable table that are organized into groups within a specific view. It requires specifying the table and the view. The function returns an array of objects, where each object represents a group and contains details about the grouping column and the rows within that group.

```javascript
const table = base.getTableByName('Table1');
const view = base.getViewByName(table, 'GroupedView');
const groupViewRows = base.getGroupedRows(table, view);
```

--------------------------------

### Get Active View in SeaTable JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/views

Retrieves the currently active view of the active table. This function returns a single view object representing the current view's state and properties.

```javascript
base.getActiveView();
```

```javascript
const view  = base.getActiveView();
output.text(view._id);
output.text(view);
```

--------------------------------

### Get View by Name in SeaTable JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/views

Retrieves a specific view from a given table by its name. It accepts either a table object or table name as the first argument and the view name as the second. Returns the view object or undefined if not found.

```javascript
base.getViewByName(table: Object/String, viewName: String);
```

```javascript
const table  = base.getTableByName('Table1');
const view = base.getViewByName(table, 'Default View');
output.text(view.name);
```

```javascript
const view = base.getViewByName('Table1', 'Default View');
output.text(view.name);
```

--------------------------------

### Get Active Table Object - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/tables

Retrieves the currently selected table object from the SeaTable base. This is useful for performing operations on the table the user is currently viewing or interacting with. The output is a single table object.

```javascript
base.getActiveTable();
```

```javascript
const table = base.getActiveTable();
output.text(`The name of the active table is: ${table.name}`);
```

--------------------------------

### Get Last Row from QuerySet (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Retrieves the last row from a QuerySet that matches the filter. Returns undefined if the QuerySet is empty. This complements `first()` for accessing records at the end of the filtered set.

```javascript
const querySet = base.filter('Table1', 'Default View', 'number = 5');
const row = querySet.last();
```

--------------------------------

### Get All Rows in View - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Fetches all rows currently displayed within a specific view of a table. This function takes the table object or name and the view object or name as parameters. It returns an array of row objects or throws an error if the table or view does not exist.

```javascript
const table = base.getTableByName('Table1');
const view = base.getViewByName(table, 'Default View');
const rows = base.getRows(table, view);

rows.forEach((row) => {
    output.text(row._id);
})
```

```javascript
const rows = base.getRows('Table1', 'Default View');
```

--------------------------------

### Get Columns by Type (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/columns

Retrieves all columns of a specific data type within a given table. The table can be specified as an object or a string (name), and the type is provided as a string (e.g., 'text', 'number'). Returns an empty array if no columns match the type or if an incorrect type string is provided. Useful for filtering columns based on their data format.

```javascript
base.getColumnsByType(table: Object/String, type: String );

// Example:
const table  = base.getTableByName('Table1');
const columns = base.getColumnsByType(table, 'text');
output.text(columns.length);
```

```javascript
const columns = base.getColumnsByType('Table1', 'text');
output.text(columns.length);
```

--------------------------------

### Get Column by Name (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/columns

Retrieves a specific column object from a table by its name. It can accept either a table object or a table name as the first argument. Returns undefined if the column name does not exist. Useful for accessing and manipulating individual column properties.

```javascript
base.getColumnByName(table: Object/String, name: String);

// Example:
const table  = base.getTableByName('Table1');
const column = base.getColumnByName(table, 'Column name');
output.text(column.name);
```

```javascript
const column = base.getColumnByName('Table1', 'Column name');
output.text(column.name);
```

--------------------------------

### Get Single Row by ID - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Retrieves a single row from a specified table using its unique row ID. This function requires the table object or name and the row ID as input. It returns a row object or throws an error if the table or row is not found.

```javascript
const table = base.getTableByName('Table1');
const row = base.getRow(table, "M_lSEOYYTeuKTaHCEOL7nw");
```

```javascript
const row = base.getRow('Table1', "M_lSEOYYTeuKTaHCEOL7nw");
```

--------------------------------

### Get Column Link ID (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/links

Retrieves the unique link ID for a specified column in a given table. This ID is essential for operations involving linked records. It requires the table name and column name as input and returns the link ID as a string. An error is thrown if the table or column does not exist.

```javascript
base.getColumnLinkId(tableName: String, columnName: String);

// Example:
base.getColumnLinkId('Table1', 'Table2 link');
```

--------------------------------

### Format Seatable Rows with JSON Pretty Print

Source: https://developer.seatable.com/scripts/javascript/common_questions

Illustrates how to retrieve rows from a Seatable base using `base.getRows` and then format the retrieved data for display using the `jsonPrettyFormat` function, enhancing the readability of table data.

```javascript
let rows = base.getRows('Daily expenses', 'Default View');
jsonPrettyFormat(rows);
```

--------------------------------

### Output Variable Content in JavaScript

Source: https://developer.seatable.com/scripts/javascript/common_questions

Demonstrates how to output the content of a JavaScript variable using `output.text()`. It shows direct variable output, formatted output using template literals, and contrasts this with incorrect attempts using string encapsulation.

```javascript
const myVariable = 462;
// variable-only output
output.text(myVariable);

// prettier output formatting
output.text(`the content of my variable is ${myVariable}`);

// Simple/Double quotes won't work as they are used to encapsulate strings
output.text("myVariable");
output.text('myVariable');
```

--------------------------------

### Pretty Print JSON Data in JavaScript

Source: https://developer.seatable.com/scripts/javascript/common_questions

Provides a JavaScript function `jsonPrettyFormat` to recursively format and output JSON data (objects and arrays) with indentation for improved readability. This function is useful for debugging complex data structures.

```javascript
function jsonPrettyFormat(json, indent=0) {
    const indenterChar = " "; 
    if (json instanceof Array) {
        output.text(indenterChar.repeat(indent) + "[");
        indent += 1;
        json.forEach((elem)=>jsonPrettyFormat(elem, indent));
        indent -= 1;
        output.text(indenterChar.repeat(indent) + "]");
    }
    else {
        if (!(typeof(json)=="object")) {
            output.text(indenterChar.repeat(indent) + json);
        } else {
            output.text(indenterChar.repeat(indent) + "{");
            indent += 1;
            for (const [key, value] of Object.entries(json)) {
                if (!(typeof(value)=="object")) {
                    output.text(indenterChar.repeat(indent) + key + ": " + value)
                } else {
                    output.text(indenterChar.repeat(indent) + key + ": ");
                    indent += 1;
                    jsonPrettyFormat(value, indent);
                }
            }
            indent -= 1;
            output.text(indenterChar.repeat(indent) + "}");
        }
    }
}
```

--------------------------------

### Compute Daily Attendance Statistics using JavaScript

Source: https://developer.seatable.com/scripts/javascript/examples/compute-attendance-statistics

This JavaScript code calculates daily clock-in and clock-out times from a list of clocking records. It groups records by date and then by staff member to find the earliest and latest times. Dependencies include access to SeaTable base tables and their respective columns. The input is expected to be in a 'Clocking table', and the output is written to an 'Attendance statistics' table.

```javascript
// Computes, from a list of clocking times, daily clock in (earliest clocking) 
// and clock out (latest clocking) times for each day and staff member
const originTableName = 'Clocking table';
const originViewName = 'Default View';
const originNameColumnName = 'Name';
const originDepartmentColumnName = 'Department';
const originDateColumnName = 'Date';
const originTimeColumnName = 'Clocking time';

const targetTableName = 'Attendance statistics';
const targetNameColumnName = 'Name';
const targetDepartmentColumnName = 'Department';
const targetDateColumnName = 'Date';
const targetStartTimeColumnName = 'Clock-in';
const targetEndTimeColumnName = 'Clock-out';
const targetTable = base.getTableByName(targetTableName);

const table = base.getTableByName(originTableName);
const view = base.getViewByName(table, originViewName);
const rows = base.getRows(table, view);

// Sort the rows in the table according to the date column;
rows.sort((row1, row2) => {
    if (row1[originDateColumnName] < row2[originDateColumnName]) {
      return -1;
    } else if (row1[originDateColumnName] > row2[originDateColumnName]) {
      return 1; 
    } else {
      return 0;
    }
});

/*
 Group all rows via date and save them to groupedRows, the format
 of the object is {'2020-09-01': [row, ...], '2020-09-02': [row, ...]}
*/
const groupedRows = {};
rows.forEach((row) => {
  const date = row[originDateColumnName]; 
  if (!groupedRows[date]) {
    groupedRows[date] = [];
  }
  groupedRows[date].push(row);
});

const dateKeys = Object.keys(groupedRows);

// Traverse all the groups in groupedRows
dateKeys.forEach((dateKey) => { 
  // Get all clocking data of all members for the current date
  const dateRows = groupedRows[dateKey];
  const staffDateStatItem = {};
  // Traverse these rows and group by the name of the employee, get the clock-in and clock-out time of each employee that day, and save it to staffDateStatItem
  // the format is { EmployeeName: {Name: 'EmployeeName', Date: '2020-09-01', Clock-in: '08:00', Clock-out: '18:00'},...
  dateRows.forEach((row)=> {
    const name = row[originNameColumnName];
    if (!staffDateStatItem[name]) {
      // Generate a new row based on the original row data, and add Clock-in and Clock-out columns in the newly generated row
      staffDateStatItem[name] = { [targetNameColumnName]: name, [targetDateColumnName]: row[originDateColumnName], [targetDepartmentColumnName]: row[originDepartmentColumnName], [targetEndTimeColumnName]: row[originTimeColumnName], [targetStartTimeColumnName]: row[originTimeColumnName]};
    } else {
      // When another record (same employee and same date) is found, compare the time, choose the latest one as the Clock-out time, and the earliest one as the Clock-in time
      const time = row[originTimeColumnName];
      const staffItem = staffDateStatItem[name];
      if (staffItem[targetStartTimeColumnName] > time) {
          staffItem[targetStartTimeColumnName] = time;
      } else if (staffItem[targetEndTimeColumnName] < time) {
          staffItem[targetEndTimeColumnName] = time;
      }
    }
  });
  // Write the attendance data of all employees on the current date into the table
  Object.keys(staffDateStatItem).forEach((name) => {
    base.appendRow(targetTable, staffDateStatItem[name]);
  });  
});

```

--------------------------------

### Query Base with SQL - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Executes SQL queries against the Seatable base to retrieve data. This method supports most SQL syntax and requires a SQL statement string. Special characters or reserved words in table/column names must be escaped with backticks. It returns an array of row objects.

```javascript
const data = await base.query('select *  from Bill');
output.text(data);
```

--------------------------------

### Output Markdown with Javascript

Source: https://developer.seatable.com/scripts/javascript/objects/output

Prints the content of a variable using Markdown formatting for styling. This function allows for rich text output, including headlines, bold text, and more. It accepts String, Object, or Array as input and returns a String.

```javascript
const table = base.getActiveTable();
output.markdown(`# This is a headline and prints the name of the table: ${table.name}`);
```

--------------------------------

### Query Table Data with SQL-like Statements - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/utilities

Filters and summarizes data from a specified SeaTable table and view using SQL-like query statements. It takes table name, view name, and the query string as input, returning an object with aggregated results.

```javascript
// Filter out the rows where the sum of the three columns 'number', 'number1',
// and 'number2' is greater than 5 then sum the number and number2 columns in these rows,
// return {number: 12, number2: 23}
base.utils.query('Table1', 'Default View', 'select sum(number), sum(number2) where number + number1 + number2 > 5');
```

--------------------------------

### List Columns by Table and View (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/columns

Retrieves an array of column objects for a given table name. Optionally, it can filter these columns to only include those visible in a specific view name. If no view name is provided, it returns all columns in the table, similar to `base.getColumns`. Throws an error if the table does not exist.

```javascript
base.listColumns(tableName: String, viewName: String);

// Example with view:
const tableName  = 'Table1'
const viewName = 'Default View'
const columns = base.listColumns(tableName, viewName);

columns.forEach((column) => {
    output.text(column.name);
})
```

```javascript
const columns = base.listColumns('Table1');

columns.forEach((column) => {
    output.text(column.name);
})
```

--------------------------------

### Lookup and Copy Data - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/utilities

Mimics the VLOOKUP function to find matching rows in a source table and copy data to a target table. All arguments are strings. It returns nothing but may throw an error if tables or columns don't exist.

```javascript
base.utils.lookupAndCopy('Target table', 'TargetEmail', 'Name', 'Source table', 'SourceEmail');
```

```javascript
// Match the rows with the same content in the Name column of Table1 and Table2,
// copy the contents of the Email column of the row in Table1 to the Email column
// of the corresponding row in Table2
base.utils.lookupAndCopy('Table2', 'Email', 'Name', 'Table1', 'Email');
```

```javascript
// Match the rows with the same content in the Name column in Table1 and the Name1 column
// in Table2, and copy the contents of the Email column of the row in Table1 to the
// Email1 column of the corresponding row in Table2
base.utils.lookupAndCopy('Table2', 'Email1', 'Name1', 'Table1', 'Email', 'Name');
```

--------------------------------

### SeaTable Link Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object details the specific 'data' properties for a SeaTable 'link' type column. It includes configurations for linking to other tables, display columns, and multi-selection options.

```json
{
    "display_column_key":"qqXZ",
    "table_id":"0000",
    "other_table_id":"XE5U",
    "is_internal_link":true,
    "is_multiple":true,
    "only_adding_new_record":false,
    "is_row_from_view":false,
    "other_view_id":"",
    "link_id":"OSD1",
    "array_type":"text",
    "array_data":null,
    "result_type":"array"
}

```

--------------------------------

### Output Text with Javascript

Source: https://developer.seatable.com/scripts/javascript/objects/output

Prints the content of a variable as plain text, ignoring any code syntax. This function is useful for displaying raw data or debugging. It accepts String, Object, or Array as input and returns a String.

```javascript
const table = base.getActiveTable();
output.text(table.name);
```

--------------------------------

### SeaTable Select Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object outlines the 'data' properties for SeaTable 'single select' and 'multiple select' columns. It includes the list of available options with their names, IDs, and colors, as well as cascade settings for dependent selections.

```json
{
    "options":
        [
            {
                "name":"Male",
                "id":"783482",
                "color":"#46A1FD",
                "textColor":"#FFFFFF",
                "borderColor":"#3C8FE4"
            },
            {
                "name":"Female",
                "id":"330935",
                "color":"#DC82D2",
                "textColor":"#FFFFFF",
                "borderColor":"#D166C5"
            },
            {
                "name":"Non-binary",
                "id":"147140",
                "color":"#ADDF84",
                "textColor":"#FFFFFF",
                "borderColor":"#9CCF72"
            }
        ],
        "cascade_column_key":"Qvkt",
        "cascade_settings":
            {
                "147140":["783482"],
                "330935":["330935"],
                "783482":["783482"]
            }
}

```

--------------------------------

### SeaTable Rate Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object specifies the 'data' properties for a SeaTable 'rate' column, configuring the maximum rating number, star color, and default value settings.

```json
{
    "rate_max_number":5,
    "rate_style_color":"#FF8000",
    "default_value":"",
    "enable_fill_default_default_value":false
}

```

--------------------------------

### SeaTable Formula Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object details the 'data' properties for a SeaTable 'formula' column, including the formula expression, operated columns, and result type. It also supports precision and thousands separator settings.

```json
{
    "formula":"left({Email},search(\"@\",{Email},1)-1)",
    "operated_columns":["JfP2"],
    "result_type":"string",
    "enable_precision":true,
    "precision":1,
    "thousands":"no"
}

```

--------------------------------

### Query Bill Table by Year with WHERE Clause

Source: https://developer.seatable.com/scripts/javascript/objects/rows

This snippet demonstrates how to query the 'Bill' table and filter results by the 'year' column using a WHERE clause. It selects the 'name' and 'price' fields. The input is a string representing the SQL-like query, and the output is a JSON array of matching records.

```javascript
const data = await base.query('select name, price from Bill where year = 2021');
output.text(data);
```

--------------------------------

### SeaTable Column Object Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON structure represents a generic SeaTable column object, outlining its general properties such as key, type, name, and editability. It also includes nested objects for data, editor, and formatter configurations.

```json
{
    "key":"bjcM",
    "type":"number",
    "name":"Val",
    "editable":true,
    "width":200,
    "resizable":true,
    "draggable":true,
    "data": 
        {
            "format":"number",
            "precision":2,
            "enable_precision":false,
            "enable_fill_default_value":false,
            "enable_check_format":false,
            "decimal":"comma",
            "thousands":"no",
            "format_min_value":0,
            "format_max_value":1000
        },
    "permission_type":"",
    "permitted_users":[],
    "permitted_group":[],
    "edit_metadata_permission_type":"",
    "edit_metadata_permitted_users":[],
    "edit_metadata_permitted_group":[],
    "description":null,
    "colorbys":{},
    "editor":
        {
            "key":null,
            "ref":null,
            "props":{},
            "_owner":null
        },
    "formatter":
        {
            "key":null,
            "ref":null,
            "props":{},
            "_owner":null
        }
}

```

--------------------------------

### Add New Table - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/tables

Creates a new table within the current SeaTable base, identified by a unique table name. It's important to ensure the chosen name does not already exist to avoid errors. This function does not return any data upon success.

```javascript
base.addTable(tableName: String);
```

```javascript
base.addTable('New table');
output.text("Wow, I just added a new table to this base.")
```

--------------------------------

### Write New Row to SeaTable

Source: https://developer.seatable.com/scripts/javascript/objects

Adds a new row with specified data to a SeaTable table. It defines the table and constructs a new row object with column names and values, then attempts to add it using `base.addRow()`.

```javascript
const table = base.getTableByName('Table1'); 

const newRow = { 
    'Name': 'Hugo',
    'Age': 3,
};

try {
    const row = base.addRow(table, newRow);
    output.text(`New row added with _id: ${row._id}`);
} catch (error) {
    output.text(`Error adding row: ${error}`);
}
```

--------------------------------

### SeaTable Link Formula Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object outlines the 'data' properties for a SeaTable 'link-formula' column. It allows specifying the formula, result type, operated columns, conditions, and linking details to related records.

```json
{
    "formula":"findmax",
    "result_type":"array",
    "operated_columns":["TaXD"],
    "conditions":[],
    "link_column_key":"TaXD",
    "include_condition":false,
    "condition_conjunction":"And",
    "column_key_in_linked_record":"0000",
    "column_key_for_comparison":"RSjx",
    "level2_linked_table_column_key":null,
    "array_type":"auto-number",
    "array_data":null
}

```

--------------------------------

### Filter Date Columns (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Illustrates how to filter Date, Created time, and Last modified time columns. Supports date pattern comparisons and exact matching.

```javascript
base.filter('Table1', 'Default View', "column_name>'2020-1-30'")
base.filter('Table1', 'Default View', "column_name>='2019-1-1 5:30' and column_name<='2019-5-1 6:00'")
```

```javascript
base.filter('Table1', 'Default View', "column_name='2020-1-1 10:59:59'")
base.filter('Table1', 'Default View', "column_name!=''")
```

--------------------------------

### Add Link (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/links

Establishes a link between a row in a source table and a record in a target table. This function requires the link ID of the column, the names of the source and target tables, the ID of the source row, and the ID of the linked record. It does not return any value upon successful execution.

```javascript
base.addLink(linkId: String, tableName: String, linkedTableName: String, rowId: String, 
             linkedRowId: String);

// Example:
base.addLink('5WeC', 'Team Members', 'Contacts', 'CGtoJB1oQM60RiKT-c5J-g', 'PALm2wPKTCy-jdJNv_UWaQ');
```

```javascript
// Example: Add link to current row
const table1Name = "Table1";
const table1LinkColumnName = "Table2 link";
const table2Name = "Table2";

const linId = base.getColumnLinkId(table1Name, table1LinkColumnName); 
const currentRowId = base.context.currentRow._id;
base.addLink(linId, table1Name, table2Name, currentRowId, 'J5St2clyTMu_OFf9WD8PbA');
```

--------------------------------

### Query Bill Table by Name with WHERE Clause

Source: https://developer.seatable.com/scripts/javascript/objects/rows

This snippet shows how to query the 'Bill' table and filter results by the 'name' column using a WHERE clause. It selects 'name', 'price', and 'year' fields. The input is a query string, and the output is a JSON array of records where the name matches 'Bob'.

```javascript
const data = await base.query('select name, price, year from Bill where name = "Bob"');
output.text(data);
```

--------------------------------

### SeaTable Date Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object specifies the 'data' properties for a SeaTable 'date' column, allowing customization of the date and time display format.

```json
{
    "format":"M/D/YYYY HH:mm"
}

```

--------------------------------

### SeaTable Number Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object defines the 'data' properties for a SeaTable 'number' column. It allows for custom formatting, precision settings, decimal and thousands separators, and currency symbol configurations.

```json
{
    "format":"custom_currency",
    "precision":2,
    "enable_precision":true,
    "enable_fill_default_value":false,
    "decimal":"comma",
    "thousands":"no",
    "currency_symbol_position":"after",
    "currency_symbol":"p"
}

```

--------------------------------

### SeaTable Duration Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object defines the 'data' properties for a SeaTable 'duration' column, enabling the configuration of the duration display format.

```json
{
    "format":"duration",
    "duration_format":"h:mm"
}

```

--------------------------------

### Aggregate Bill Prices by Name with GROUP BY

Source: https://developer.seatable.com/scripts/javascript/objects/rows

This snippet demonstrates how to use the GROUP BY clause to aggregate data in the 'Bill' table. It calculates the sum of 'price' for each unique 'name'. The input is a query string, and the output is a JSON array where each object contains a 'name' and the 'SUM(price)' for that name.

```javascript
const data = await base.query('select name, sum(price) from Bill group by name');
output.text(data);
```

--------------------------------

### SeaTable Button Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object specifies the 'data' properties for a SeaTable 'button' column, defining the button's type, name, color, and the target table ID for actions like copying rows.

```json
{
    "button_type":"copy_row_to_another_table",
    "button_name":"Copy to Table2",
    "button_color":"#FFFCB5",
    "table_id":"0000"
}

```

--------------------------------

### SeaTable Row Object Structure

Source: https://developer.seatable.com/scripts/javascript/objects/rows

This JSON object represents the global structure of a row in a SeaTable table. It includes metadata like '_id' and '_mtime', along with column data represented by key-value pairs. Link-type columns have a specific array format for their values.

```json
{
  "_id": "Qtf7xPmoRaiFyQPO1aENTjb",
  "_mtime": "2021-03-10T16:19:31.761+00:00",
  "Name": "NewName",
  "Date": "2020-08-01",
  "Content": "111",
  "link": [
            {
              "display_value": "1",
              "row_id": "XzdZfL2oS-aILnhfagTWEg"
            }
          ]
}
```

--------------------------------

### SeaTable Auto-Number Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object details the 'data' properties for a SeaTable 'auto-number' column, including format, digits, prefix type, and the maximum used number.

```json
{
    "format":"YYYYMMDD-00",
    "max_used_auto_number":33,
    "digits":2,
    "prefix_type":"date",
    "prefix":"20250909"
}

```

--------------------------------

### Further Filter QuerySet (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Applies an additional filter expression to an existing QuerySet, returning a new QuerySet with the further refined results. This allows for chained filtering operations.

```javascript
// Filter out the rows with the value of Tom in the Name column of querySet1
const querySet1 = base.filter('Table1', 'Default View', 'number = 5');
const querySet2 = querySet1.filter("Name = 'Tom'");
```

--------------------------------

### Format Date with Minutes to YYYY-MM-DD HH:mm - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/utilities

Formats a given Date object into a 'YYYY-MM-DD HH:mm' string, including minutes, for use in SeaTable date-type columns. It accepts a date object and outputs a formatted string.

```javascript
let date = new Date();
let formatDate = base.utils.formatDateWithMinutes(date);
output.text(formatDate);
```

--------------------------------

### Filter Boolean Columns (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Demonstrates filtering Checkbox columns using boolean values (True/False) and checking for empty string values.

```javascript
base.filter('Table1', 'Default View','column_name=False')
base.filter('Table1', 'Default View', "column_name=True")
```

--------------------------------

### Add Monthly Expenses with Checks - JavaScript

Source: https://developer.seatable.com/scripts/javascript/examples/auto-add-rows

This script adds two recurring monthly expense records ('Amazon Cloud Service' and 'Clean') to a 'Daily expenses' table. It first checks if records for the current month already exist using filter conditions based on the 'Name' and formatted 'Date' columns. If a record does not exist, it prepares the data and appends it to the table. Requires the 'base' object for Seatable API interactions.

```javascript
// Record monthly repetitive expenses in a ledger

const table = base.getTableByName('Daily expenses');

// Get date objects on the 10th and 20th of the current month
var date = new Date();
var date10 = new Date(date.setDate(10));
var date20 = new Date(date.setDate(20));

// Check if the monthly expense items have already been created and eventually create them
const AWSCondition = "Name='Amazon Cloud Service' and Date='" + base.utils.formatDate(date10) + "'";
const feeAWSCurrentMonth = base.filter('Daily expenses', 'Default View', AWSCondition);
if (feeAWSCurrentMonth.count() == 0) {
  var feeAWS = { 'Name': 'Amazon Cloud Service', 
                 'Date': base.utils.formatDate(date10),
                 'Type': 'Cloud service',
                 'Type (single select)': 'Cloud service',
               };
}
const CleanCondition = "Name='Clean' and Date='" + base.utils.formatDate(date20) + "'";
const feeCleanCurrentMonth = base.filter('Daily expenses', 'Default View', CleanCondition);
if (feeCleanCurrentMonth.count() == 0) {
  var feeClean = { 'Name': 'Clean', 
                   'Date': base.utils.formatDate(date20),
                   'Type': 'Daily office',
                   'Type (single select)': 'Daily office',
                   'Fee': 260
                 };
}

// Auto add data (if needed)
if (feeAWS) {
  base.appendRow(table, feeAWS);
}
if (feeClean) {
  base.appendRow(table, feeClean);
}


```

--------------------------------

### Filter String-based Columns (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Demonstrates how to filter rows based on string-based columns like Text, Long Text, URL, Email, and Single Select. Supports equal-unequal comparisons.

```javascript
// Equal-unequal query
base.filter('Table1', 'Default View', "column_name='hello world'")
base.filter('Table1', 'Default View', "column_name!=''")
```

--------------------------------

### SeaTable Checkbox Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object defines the 'data' properties for a SeaTable 'checkbox' column, including its default value and whether to enable filling with a default value.

```json
{
    "default_value":false,
    "enable_fill_default_value":false
}

```

--------------------------------

### Rename Table - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/tables

Changes the name of an existing table from its current name (`oldName`) to a new name (`newName`). The new name must be unique within the base. An error is thrown if the `oldName` table does not exist. This function does not return any data.

```javascript
base.renameTable(oldName: String, newName: String);
```

```javascript
const old_name = "Table1";
const new_name = "Projects 2023";
base.renameTable(old_name, new_name);
output.text(`This base ${old_name} got a new name: ${new_name}`);
```

--------------------------------

### Add View in SeaTable JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/views

Adds a new view with a specified name to a given table. The table can be referenced by its object or name. This function creates a new view instance within the table.

```javascript
base.addView(table: Object/String, viewName: String);
```

```javascript
const table  = base.getTableByName('Table1');
base.addView(table, 'view 2');
```

```javascript
base.addView('Table1', 'view 2');
```

--------------------------------

### Access Current Table in Seatable JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/context

Retrieves the currently selected table from the Seatable interface. This is useful for scripts that need to operate on the table the user is currently viewing or interacting with. It returns a table object.

```javascript
base.context.currentTable;
```

```javascript
const name = base.context.currentTable;
output.text(`The name of the current table is: ${name}`);
```

--------------------------------

### Update Links in SeaTable using Javascript

Source: https://developer.seatable.com/scripts/javascript/objects/links

Updates the content of a link-type column in a specified table and row. It replaces existing links with new ones based on provided record IDs. This function requires the link ID, table names, row ID, and an array of linked row IDs.

```javascript
base.updateLinks(linkId, tableName, linkedTableName, rowId, updatedlinkedRowIds: Array of String);
```

```javascript
const records = base.getRows('Contacts', 'Default_view');
// Update links for row from "Team Members" with _id CGtoJB1oQM60RiKT-c5J-g to [records[0]._id, records[1]._id, records[2]._id, records[3]._id]
// Real-life tip: ensure that the array "records" actually contains at least 4 elements!
base.updateLinks('5WeC', 'Team Members', 'Contacts', 'CGtoJB1oQM60RiKT-c5J-g', [records[0]._id, records[1]._id, records[2]._id, records[3]._id]);
```

--------------------------------

### SeaTable Geolocation Column Data Structure

Source: https://developer.seatable.com/scripts/javascript/objects/columns

This JSON object defines the 'data' properties for a SeaTable 'geolocation' column, specifying the desired geographic format (e.g., longitude and latitude).

```json
{
    "geo_format":"lng_lat"
}

```

--------------------------------

### Select Distinct Names from Bill Table

Source: https://developer.seatable.com/scripts/javascript/objects/rows

This snippet shows how to retrieve unique values from the 'name' column of the 'Bill' table using the DISTINCT keyword. The input is a query string, and the output is a JSON array containing objects, each with a single 'name' field representing a distinct name found in the table.

```javascript
const data = await base.query('select distinct name from Bill');
output.text(data);
```

--------------------------------

### Format Date to YYYY-MM-DD - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/utilities

Formats a given Date object into a 'YYYY-MM-DD' string, suitable for date-type columns in SeaTable. It takes a Date object as input and returns a formatted string.

```javascript
let date = new Date();
let formatDate = base.utils.formatDate(date);
output.text(formatDate);
```

--------------------------------

### Delete Table - JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/tables

Removes a specified table from the SeaTable base by its name (`tableName`). The deleted table can be restored from the logs. Note that deleting the very last table in a base is not permitted. An error is thrown if the specified table does not exist.

```javascript
base.deleteTable(tableName: String);
```

```javascript
base.deleteTable('Old table');
```

--------------------------------

### Rename View in SeaTable JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/views

Renames an existing view within a specified table. It requires the table identifier, the current view name, and the desired new name. Ensure the new name does not already exist in the table to avoid conflicts.

```javascript
base.renameView(table: Object/String, currentViewName: String, nextViewName: String);
```

```javascript
const table  = base.getTableByName('Table1');
base.renameView(table, 'Default View', 'view2');
```

```javascript
base.renameView('Table1', 'Default View', 'view2');
```

--------------------------------

### Add Row Javascript

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Appends a new row to a specified table with the provided row data. The row will be empty if rowData is empty or contains keys not present in the table. This function can optionally target a specific view.

```javascript
const table = base.getTableByName('Table1');
base.appendRow(table, {'Name': 'Alex', 'Age': '18'});
base.appendRow(table, {'Name': 'Alex', 'Age': '18'}, 'Default View');
```

--------------------------------

### Count Filtered Rows in QuerySet (JavaScript)

Source: https://developer.seatable.com/scripts/javascript/objects/rows

Returns the total number of rows that match the filter criteria within a QuerySet. This is helpful for understanding the scope of the filtered data before further operations.

```javascript
const querySet = base.filter('Table1', 'Default View', 'number = 5');
const count = querySet.count();
output.text(`The querySet contains ${count} rows`);
```

--------------------------------

### Access Current Row in Seatable JavaScript

Source: https://developer.seatable.com/scripts/javascript/objects/context

Retrieves the currently selected row from the Seatable interface. This is particularly useful when a script is triggered from a button within a specific row, providing access to that row's data. It returns a row object or undefined if no row is selected.

```javascript
base.context.currentRow;
```

```javascript
const row = base.context.currentRow;
output.text(row);
```