Rows¶

Global structure¶

Here is the global structure of a row object:

{
  "_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"
            }
          ]
}

Please note the specific format for link-type columns (structure of the array objects for key link):

display_value: Value displayed in the cell
row_id: id of the linked row in the other table

Get Row(s)¶

getRow

Get a single row by its ID.

base.getRow(table, rowId);

Output Single row object

Example

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

getRows

Get all rows displayed in a view.

base.getRows(table, view);

Output Array of row objects

Example

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

listRows

Get rows with optional sorting and pagination. Particularly useful for large tables.

base.listRows(tableName, viewName='', orderBy='', desc='', start='', limit='');

Output Array of row objects

Example

// Simple
const rows = await base.listRows('Table1');

// With pagination and sorting
const rows = await base.listRows('Table1', 'Default View', 'Name', true, 0, 100);

getGroupedRows

Get rows grouped according to the view's grouping settings. Only available in SeaTable scripts.

base.getGroupedRows(table, view);

Output Array of group objects, each containing a rows array

Example

const table = base.getTableByName('Table1');
const view = base.getViewByName(table, 'Grouped View');
const groups = base.getGroupedRows(table, view);

query

Use SQL to query a base. Most SQL syntax is supported -- see the SQL Reference for details.

await base.query(sql);

Backticks for special names

Escape table or column names that contain spaces or special characters with backticks: SELECT * FROM `My Table`

Output Array of row objects

Example

const data = await base.query('SELECT name, price FROM Bill WHERE year = 2021');

// Aggregation
const data = await base.query('SELECT name, SUM(price) FROM Bill GROUP BY name');

filter

Filter rows using a filter expression. Returns a QuerySet with chainable methods. Only available in SeaTable scripts.

base.filter(tableName, viewName, filterExpression);

Output QuerySet object

Example

// Get all rows where status is "Done"
const querySet = base.filter('Table1', 'Default View', 'Status = "Done"');
const rows = querySet.all();
const count = querySet.count();
const first = querySet.first();

QuerySet methods: .all(), .count(), .first(), .last(), .get(filter), .filter(filter), .delete(), .update(rowData)

Add Row(s)¶

appendRow

Append a new row to the end of a table.

base.appendRow(tableName, rowData, applyDefault=false);

Set applyDefault to true to use default column values for unspecified columns.

Example

base.appendRow('Table1', {
    'Name': 'New entry',
    'Status': 'Open'
});

insertRow

Insert a row after a specific anchor row.

base.insertRow(tableName, rowData, anchorRowId, applyDefault=false);

Example

await base.insertRow('Table1', {'Name': 'Inserted row'}, 'U_eTV7mDSmSd-K2P535Wzw');

batchAppendRows

Append multiple rows at once. More efficient than calling appendRow in a loop.

base.batchAppendRows(tableName, rowsData, applyDefault=false);

Example

await base.batchAppendRows('Table1', [
    {'Name': 'Row 1', 'Status': 'Open'},
    {'Name': 'Row 2', 'Status': 'Done'},
    {'Name': 'Row 3', 'Status': 'Open'}
]);

Update Row(s)¶

updateRow

Update a single row identified by its row ID.

base.updateRow(tableName, rowId, rowData);

In scripting context, you can also pass a row object instead of a row ID.

Example

base.updateRow('Table1', 'U_eTV7mDSmSd-K2P535Wzw', {
    'Status': 'Done'
});

modifyRows

Update multiple rows at once in scripting context. Pass two arrays: the rows to update and the corresponding update data.

base.modifyRows(table, rows, updatedRows);

Example

const table = base.getTableByName('Table1');
const rows = base.getRows(table, base.getViewByName(table, 'Default View'));
const selectedRows = rows.filter(row => row['Status'] === 'Open');
const updates = selectedRows.map(() => ({'Status': 'Archived'}));
base.modifyRows(table, selectedRows, updates);

batchUpdateRows

Update multiple rows at once. Each entry specifies a row ID and the data to update.

base.batchUpdateRows(tableName, rowsData);

Example

await base.batchUpdateRows('Table1', [
    {"row_id": "fMmCFyoxT4GN5Y2Powbl0Q", "row": {"Name": "Updated 1"}},
    {"row_id": "cF5JTE99Tae-VVx0BGT-3A", "row": {"Name": "Updated 2"}}
]);

Delete Row(s)¶

deleteRow

Delete a single row by its ID.

base.deleteRow(tableName, rowId);

Example

base.deleteRow('Table1', 'U_eTV7mDSmSd-K2P535Wzw');

batchDeleteRows

Delete multiple rows at once.

base.batchDeleteRows(tableName, rowIds);

Example

await base.batchDeleteRows('Table1', [
    'fMmCFyoxT4GN5Y2Powbl0Q',
    'cF5JTE99Tae-VVx0BGT-3A'
]);