# SeaTable Developer Manual Developer documentation for SeaTable — client libraries for Python, JavaScript, and PHP, SQL query reference, and plugin development guide. Base URL: https://developer.seatable.com ## Introduction Source: https://developer.seatable.com/ # SeaTable Developer Manual This manual covers the programmatic interfaces to SeaTable: client libraries, the scripting API reference, and plugin development. ## Who is this manual for? This manual is for **developers** who want to: - Build external applications that communicate with SeaTable (Python, JavaScript, PHP) - Develop custom plugins for SeaTable - Look up the function reference of the SeaTable client libraries (Python, JavaScript, PHP) If you want to create and run scripts directly within a SeaTable base, head to the [SeaTable User Manual](https://seatable.com/help/scripts/) for step-by-step examples and getting-started guides. The function reference for the scripting libraries is documented in this developer manual. ## Languages | Language | Use case | |---|---| | **[Python](python/index.md)** | External apps, data pipelines, automations, scripts in SeaTable | | **[JavaScript](javascript/index.md)** | Scripts in SeaTable, Node.js apps, frontend integrations | | **[PHP](php/index.md)** | Web applications, server-side integrations | | **[Ruby](ruby/index.md)** | Community client | For other languages, use the [REST API](https://api.seatable.com) directly. It provides interactive examples and code snippets. ## Data Model SeaTable organizes data in bases, tables, columns, rows, and views. The complete schema definition is available at [api.seatable.com/reference/models](https://api.seatable.com/reference/models). ## Plugin Development Custom plugins can visualize and interact with base data inside SeaTable. Plugin development requires JavaScript and React. See the [Plugin Development](plugins/index.md) section for details. --- Source: https://developer.seatable.com/introduction/get_support/ # Get Support Beyond this developer manual, SeaTable provides several other resources: - [SeaTable User Manual](https://seatable.com/help/) covers how to use SeaTable, including a section about [scripts](https://seatable.com/help/scripts/) - [SeaTable Admin Manual](https://admin.seatable.com) covers installation, configuration, upgrades, and maintenance - [SeaTable API Reference](https://api.seatable.com/reference/introduction) documents all available API endpoints with interactive examples The [Community Forum](https://forum.seatable.com) is a good place to ask questions, share experiences, or report bugs. You can also find tutorials and guides on the [SeaTable YouTube channel](https://www.youtube.com/seatable) and the [SeaTable blog](https://seatable.com/blog/). If your organization needs help with digitalizing processes, developing custom solutions, or improving efficiency, [get in touch](mailto:sales@seatable.com) to discuss our enterprise support offerings. --- ## Python Source: https://developer.seatable.com/python/ # Python Python scripts connect to SeaTable bases with the library [seatable-api](https://pypi.org/project/seatable-api/). The source code is available on [GitHub](https://github.com/seatable/seatable-api-python). The same library is used both **inside SeaTable scripts** and in **external Python programs**. The only difference is how you authenticate. All objects and methods work identically in both contexts. Every `seatable-api` method is a wrapper around the [SeaTable REST API](https://api.seatable.com). The library covers the most common base operations but not every API endpoint (e.g. admin, webhooks, or team management). For anything not covered by this library, you can call the [API](https://api.seatable.com) directly. For column type formats and data models, see the [API model reference](https://api.seatable.com/reference/models). ## Installation ```bash pip install seatable-api ``` When running scripts directly in SeaTable (via the built-in Python editor), `seatable-api` is already available. No installation required. ## Script vs. External Client | | Script in SeaTable | External client | |---|---|---| | Authentication | `context.api_token` (automatic) | API token or account credentials (manual) | | `context` object | Available | Not available | | `current_row` (button execution) | Available | Not available | | Python version | 3.12 | Your choice | | Available libraries | [Predefined set](https://seatable.com/help/supported-python-libraries/) | Unlimited | | Execution | SeaTable server (Python Pipeline) | Your own machine/server | ## Authentication ### In a SeaTable script Within SeaTable's integrated Python editor, the `context` object provides the authentication credentials automatically: ```python from seatable_api import Base, context base = Base(context.api_token, context.server_url) base.auth() ``` ### In an external program When running Python on your own machine or server, you provide the API token and server URL directly. API tokens can be [generated in the SeaTable web interface](https://seatable.com/help/create-api-tokens/). ```python from seatable_api import Base API_TOKEN = 'your-api-token' # (1)! SERVER_URL = 'https://cloud.seatable.io' base = Base(API_TOKEN, SERVER_URL) base.auth() ``` 1. Avoid exposing credentials directly in the code. Use environment variables or `.env` files instead. ??? question "Authentication with account credentials" Instead of using an API token (which is specific to a base), you can authenticate with your SeaTable account credentials. This gives you access to all your bases. ```python from seatable_api import Account account = Account(username, password, server_url) account.auth() base = account.get_base(workspace_id, base_name) ``` To find the `workspace_id`, open the base in your browser. The URL looks like `https://cloud.seatable.io/workspace/84254/dtable/MyBase`. ??? question "Handling authorization expiration" For long-running programs, authorization may expire. Catch `AuthExpiredError` to re-authenticate: ```python from seatable_api import Base, context from seatable_api.exception import AuthExpiredError base = Base(context.api_token, context.server_url) base.auth() while True: try: base.append_row('Table1', {"xxx": "xxx"}) ... except AuthExpiredError: base.auth() ``` ## Rate and Size Limits Since every method call is an API request, scripts are subject to [rate](https://api.seatable.com/reference/limits#general-rate-limits) and [size](https://api.seatable.com/reference/limits#size-limits) limits. Tips to stay within limits: - Be careful with operations in `for` or `while` loops - Use **batch operations** whenever possible: - `base.batch_append_rows` - `base.batch_update_rows` - `base.batch_delete_rows` - `base.batch_update_links` - Learn more about [optimizing your API calls](https://seatable.com/api-optimization/) ## Quick Start A minimal example assuming a table **Contacts** with columns **Name**, **Email** and **Status**: ```python from seatable_api import Base base = Base('your-api-token', 'https://cloud.seatable.io') base.auth() # Read all rows from a table rows = base.list_rows('Contacts') print(f"{len(rows)} rows found") # Update the first row base.update_row('Contacts', rows[0]['_id'], {'Status': 'Done'}) # Add a new row base.append_row('Contacts', {'Name': 'Alice', 'Email': 'alice@example.com'}) ``` --- Source: https://developer.seatable.com/python/objects/metadata/ # Metadata Metadata delivers the complete structure of a base with tables, views and columns. All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. Get the complete metadata of a table. The metadata will not contain the concrete rows of the table. === "Function call" ```python base.get_metadata() ``` === "Output structure" ```json { 'tables': [{ '_id': '4krH', 'name': 'Contact', 'is_header_locked': False, 'columns': [{ 'key': '0000', 'type': 'text', 'name': 'Name', 'editable': True, 'width': 200, 'resizable': True, 'draggable': True, 'data': None, 'permission_type': '', 'permitted_users': [] }, { 'key': 'M31F', 'type': 'text', 'name': 'Email', 'editable': True, 'width': 200, 'resizable': True, 'draggable': True, 'data': None, 'permission_type': '', 'permitted_users': [] }], 'views': [{ '_id': '0000', 'name': 'Default view', 'type': 'table', 'is_locked': False, 'filter_conjunction': 'And', 'filters': [], 'sorts': [], 'groupbys': [], 'group_rows': [], 'groups': [], 'colorbys': {}, 'hidden_columns': [], 'rows': [], 'formula_rows': {}, 'link_rows': {}, 'summaries': {}, 'colors': {} }] }] } ``` __Example__ ```python print(base.get_metadata()) ``` If you have a hard time reading the output of a complex object, use `json.dumps(result, indent=2)` for pretty printing. --- Source: https://developer.seatable.com/python/objects/context/ # Context When the script is running in the cloud, the context object provides a context environment. Here's how to use it. To use these functions, the context module must be imported. ```python from seatable_api import context ``` ## server_url Server URL, used to initialize Base. ```python context.server_url ``` __Example__ ```python from seatable_api import context print(context.server_url) ``` ## api_token API token to access a base. ```python context.api_token ``` __Example__ ```python from seatable_api import context print(context.api_token) ``` ## current_table The name of the table that the current user is viewing when the script is run. ```python context.current_table ``` __Example__ ```python from seatable_api import context print(context.current_table) ``` ## current_view The name of the view that the current user is viewing when the script is run. **Note:** This was added in version 6.2. ```python context.current_view ``` __Example__ ```python from seatable_api import context print(context.current_view) ``` ## current_row The line which triggered the script run: - the line where the cursor is currently located (if the script is run manually) - the line from which the button to launch the script was clicked (if the script is run from a button-type column click) - each line triggering the automation (if the script is run by an automation rule) ```python context.current_row ``` __Example__ ```python from seatable_api import context print(context.current_row) ``` ## current_username The system ID of the user who runs the script manually (it was previously called `current_user_id`). It is a unique identifier ending by `@auth.local`. ```python context.current_username ``` __Example__ ```python from seatable_api import context print(context.current_username) ``` ## current_id_in_org The id of the user in the team, it can be set by the team admin via the web interface. ```python context.current_id_in_org ``` __Example__ ```python from seatable_api import context print(context.current_id_in_org) ``` Get a context setting by its key. This provides access to additional context data beyond the predefined properties above. ```python context.get_setting_by_key(key) ``` __Output__ The value of the setting, or `None` if not found __Example__ ```python from seatable_api import context value = context.get_setting_by_key('server_url') print(value) ``` --- Source: https://developer.seatable.com/python/objects/tables/ # Tables You'll find below all the available methods to interact with the tables of a SeaTable base. All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. For the structure of objects returned by these methods, see the [API model reference](https://api.seatable.com/reference/models). ## Retrieve table(s) There is no specific method to get the current (selected) table as it is a property from the [context object](./context.md), so simply use `context.current_table`. Get all tables of the current base. ```python base.list_tables() ``` __Output__ List of table dicts __Example__ ```python tables = base.list_tables() print(tables) ``` Get a table object by its name. ```python base.get_table_by_name(table_name) ``` __Output__ Single table dict (`None` if there is no table named `table_name`) __Example__ ```python table = base.get_table_by_name('Table1') print(table) ``` ## Add table Add a table named `table_name` into a base. The `columns` argument is an optional list of [column objects](https://api.seatable.com/reference/models). ```python base.add_table(table_name, lang='en', columns=[]) # (1)! ``` 1. `lang` (optional): can be `en` (default) for English or `zh-cn` for Chinese and will determine the name of the first `Name` column (if no `columns` where specified) `columns` (optional): list of [column objects](https://api.seatable.com/reference/models) describing the columns of the new table. __Output__ Single table dict (throws an error if a table named `table_name` already exists) __Example__ ```python new_table = base.add_table('Investigation', lang='zh-cn') print(new_table) ``` ```python columns=[ { "column_type" : "text", "column_name": "name" }, { "column_type": "number", "column_name": "age" } ] base.add_table("ScriptTest", lang='en', columns=columns) ``` ## Rename table Rename an existing table named `table_name` to `new_table_name`. ```python base.rename_table(table_name, new_table_name) ``` __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no table named `table_name` exists) __Example__ === "Function call" ```python print(base.rename_table('Table1', 'Table11')) ``` === "Output" ```json {'success': True} ``` ## Delete table Delete a table named `tableName` from the base. By the way, the table can be [restored from the logs](https://seatable.com/help/eine-geloeschte-tabelle-wiederherstellen/). Deleting the last table is not possible. ```python base.delete_table(table_name) ``` __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no table named `table_name` exists or if you try to delete the last table) __Example__ ```python delete_table_success = base.delete_table('Table1') print(delete_table_success) ``` --- Source: https://developer.seatable.com/python/objects/views/ # Views You'll find below all the available methods to interact with the views of a SeaTable table. All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. For the structure of objects returned by these methods, see the [API model reference](https://api.seatable.com/reference/models). ## Get view(s) Get a view of the table `table_name`, specified by its name `view_name`. ```python base.get_view_by_name(table_name, view_name) ``` __Output__ Single view dict (throws an error if no view called `view_name` exists or if no table named `table_name` exists) __Example__ ```python view = base.get_view_by_name('Table1', 'Default View') print(view) ``` Get all the views of the table named `table_name`. ```python base.list_views(table_name) ``` __Output__ Dict with a single `views` key containing a list of the table's views (throws an error if no table named `table_name` exists) __Example__ ```python views = base.list_views('Table1') print(views) ``` ## Add view Add a view named `view_name` to the table `table_name`. ```python base.add_view(table_name, view_name) ``` __Output__ Single view dict (throws an error if a view called `view_name` already exists or if no table named `table_name` exists) __Example__ ```python view = base.add_view('Table1', 'New view') print(view) ``` ## Rename view Rename a view in the table `table_name` specified by its current name `view_name` and its new name `new_view_name`. Please ensure that no view named `new_view_name` already exists in the table `table_name`. ```python base.rename_view(table_name, view_name, new_view_name) ``` __Output__ Single view dict (throws an error if no view called `view_name` exists or if no table named `table_name` exists) __Example__ ```python view = base.rename_view('Table1', 'MyView', 'NewView') print(view) ``` ## Delete view Delete a view in the table `table_name`, specified by its name `view_name`. **DO NOT** try to delete the last view or you might no longer be able to access your table! ```python base.delete_view(table_name, view_name) ``` __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no table named `table_name` exists). Be careful, `{'success':True}` will be returned even if no view named `view_name` exists! __Example__ ```python print(base.delete_view('Table1', 'MyView')) ``` --- Source: https://developer.seatable.com/python/objects/columns/ # Columns You'll find below all the available methods to interact with the columns of a SeaTable table. All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. For column type formats and data structures, see the [API model reference](https://api.seatable.com/reference/models). ## ColumnTypes constants When you want to insert/add a column or change a column type, you will need to use these `ColumnTypes`. ```python from seatable_api.constants import ColumnTypes # (1)! ColumnTypes.NUMBER # number ColumnTypes.TEXT # text ColumnTypes.LONG_TEXT # long text ColumnTypes.CHECKBOX # checkbox ColumnTypes.DATE # date & time ColumnTypes.SINGLE_SELECT # single select ColumnTypes.MULTIPLE_SELECT # multiple select ColumnTypes.IMAGE # image ColumnTypes.FILE # file ColumnTypes.COLLABORATOR # collaborator ColumnTypes.LINK # link to other records ColumnTypes.FORMULA # formula ColumnTypes.CREATOR # creator ColumnTypes.CTIME # create time ColumnTypes.LAST_MODIFIER # last modifier ColumnTypes.MTIME # modify time ColumnTypes.GEOLOCATION # geolocation ColumnTypes.AUTO_NUMBER # auto number ColumnTypes.URL # URL ``` 1. Don't forget this particular import to use `ColumnTypes`! ## Get Column(s) Get the column of the table `table_name`, given the column name `column_name`. ```python base.get_column_by_name(table_name, column_name) ``` __Output__ Single column dict (`None` if no column named `column_name` exists, throws an error if no table named `table_name` exists) __Example__ ```python column = base.get_column_by_name('Table1', 'Name') print(column) ``` Get the columns of a table (specified by its name `table_name`), optionally from a specific view (specified by its name `view_name`). ```python base.list_columns(table_name, view_name=None) ``` __Output__ List of column dicts (throws an error if no table named `table_name` exists or if no view named `view_name` exists) __Example__ ```python columns = base.list_columns('Table1', 'Default View') print(columns) ``` Get all the columns of a specific `column_type` in the table `table_name`. See the [ColumnTypes constants](#columntypes-constants) above or the [API Reference](https://api.seatable.com/reference/models#supported-column-types) for more information about supported column types. ```python base.get_columns_by_type(table_name, column_type) ``` __Output__ List of column dicts (eventually empty; throws an error if no table named `table_name` exists or if `column_type` is not a valid `ColumnTypes` member) __Example__ ```python from seatable_api.constants import ColumnTypes columns = base.get_columns_by_type('Table1', ColumnTypes.TEXT) print(columns) ``` ## Insert column Insert (inside the table) or append (at the end of the table) a column named `column_name` to the table `table_name`. ```python base.insert_column(table_name, column_name, column_type, column_key=None, column_data=None) # (1)! ``` 1. `column_type`: See the [ColumnTypes constants](#columntypes-constants) above or the [API Reference](https://api.seatable.com/reference/models#supported-column-types) for more information about supported column types `column_key` (optional): argument specifying the key of the *anchor* column for the insertion (the newly created column will appear just to the right of the *anchor* column) `column_data` (optional): For some particular `ColumnTypes`, specific column data may be provided in the `column_data` dict. See the [API model reference](https://api.seatable.com/reference/models) for column data details. __Output__ Single column dict (throws an error if no table named `table_name` exists, if a column named `column_name` already exists or if `column_type` is not a valid `ColumnTypes` member) __Example__ ```python from seatable_api.constants import ColumnTypes base.insert_column('Table1', 'New long text column', ColumnTypes.LONG_TEXT) ``` ```python from seatable_api.constants import ColumnTypes base.insert_column('Table1', 'Link', ColumnTypes.LINK, column_data={ 'table':'Table1', 'other_table':'Test_User' }) ``` ## Rename column Rename the column in the table `table_name` whose key is `column_key` with the new name `new_column_name`. Please ensure that you choose a `new_column_name` that doesn't already exist in your table `table_name`. ```python base.rename_column(table_name, column_key, new_column_name) ``` __Output__ Single column dict (throws an error if no table named `table_name` exists or if no column with the key `column_key` exists) __Example__ ```python base.rename_column('Table1', '0000', 'new column name') # (1)! ``` 1. `0000` is always the key of the first column in each table ```python column_to_rename = base.get_column_by_name('Table1', 'My Column') base.rename_column('Table1', column_to_rename['key'], 'new column name') # (1)! ``` 1. Accessing the `key` value of a column you just retrieved (for example with `base.get_column_by_name`), you don't have to explicitly know its `column_key` ## (Un)freeze column Freeze ([fix](https://seatable.com/help/adjust-frozen-columns-seatable/)) or unfreeze the column of table `table_name` whose key is `column_key`. !!! warning "(Un)freezing a group of columns" Please note that this method acts on a single column: to freeze the n-first left columns, please run it **for each column!** ```python base.freeze_column(table_name, column_key, frozen) # (1)! ``` 1. `column_key`: the key of the column you want to (un)freeze `frozen`: `True` to freeze, `False` to unfreeze __Output__ Single column dict (throws an error if no table named `table_name` exists or if no column with the key `column_key` exists) __Example__ ```python base.freeze_column('Table1', '0000', True) ``` ## Move column Move the column of table `table_name` whose key is `column_key`. ```python base.move_column(table_name, column_key, target_column_key) # (1)! ``` 1. `column_key`: the key of the column you want to move `target_column_key`: the key of the *anchor* column for the move (the column whose key is `column_key` will be moved just to the right of the *anchor* column) __Output__ Single column dict (throws an error if no table named `table_name` exists or if no column with the key `column_key` or `target_column_key` exists) __Example__ ```python base.move_column('Table1', 'loPx', '0000') # (1)! ``` 1. In this example, the column with the key `loPx` will be moved to the right of the column `0000` ## Modify column type Change the column type of an existing column of table `table_name` whose key is `column_key`. !!! warning "Don't change column type to ColumnTypes.LINK" This method doesn't allow to pass column data for the moment. Trying to change the column type to `ColumnTypes.LINK` will then lead to a "broken" column (you won't be able to edit the column's settings) as column data is mandatory for link-type columns. ```python base.modify_column_type(table_name, column_key, new_column_type) # (1)! ``` 1. `column_key` (optional): the key of the column you want to modify the type `new_column_type`: See the [ColumnTypes constants](#columntypes-constants) above or the [API Reference](https://api.seatable.com/reference/models#supported-column-types) for more information about supported column types __Output__ Single column dict (throws an error if no table named `table_name` exists, if no column with the key `column_key` exists or if `new_column_type` is not a valid `ColumnTypes` member) __Example__ ```python from seatable_api.constants import ColumnTypes base.modify_column_type('Table1', 'nePI', ColumnTypes.CHECKBOX) ``` ## Delete column Delete the column whose key is `column_key` in the table `table_name`. You cannot delete the first column as explained [here](https://seatable.com/help/warum-kann-ich-die-erste-spalte-meiner-tabelle-nicht-loeschen/). ```python base.delete_column(table_name, column_key) ``` __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no table named `table_name` exists, if no column with the key `column_key` exists or if you try to delete the first column) __Example__ ```python base.delete_column('Table1', 'bsKL') ``` ## Single- and/or multiple-select columns specific methods ### Add column options Used by both "single select" or "multiple select"-type columns to add new options to the column `column_name` of the table `table_name`. ```python base.add_column_options(table_name, column_name, options) # (1)! ``` 1. `options`: list of option dict containing the following keys: - `name`: displayed text of the option - `color`: background color of the option (hex code) - `textColor`: text color of the option (hex code) __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no table named `table_name` exists, if no column named `column_name` exists or if `options` is invalid) __Example__ ```python base.add_column_options('Table1', 'My choices', [ {"name": "ddd", "color": "#aaa", "textColor": "#000000"}, {"name": "eee", "color": "#aaa", "textColor": "#000000"}, {"name": "fff", "color": "#aaa", "textColor": "#000000"}, ]) ``` ### Add column cascade settings Used by "single select"-type column, to condition the available options (see cascading in the [user manual](https://seatable.com/help/die-einfachauswahl-spalte/#cascading-a-single-select-column-search) or in the [API Reference](https://api.seatable.com/reference/updatecolumncascade-1)) of a child column `child_column` based on the options of a parent column `parent_column`. ```python base.add_column_cascade_settings(table_name, child_column, parent_column, cascade_settings) # (1)! ``` 1. `child_column`: name of the column you want to condition the available options for `parent_column`: name of the parent column whose options will be used to condition the available options of the child column `cascade_settings`: cascade dict using the following structure: - each key is the `name` of an option from the parent column - each corresponding value is a list of the names of every allowed options from the child column __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no table named `table_name` exists, if no column named `child_column` or `parent_column` exists or if `cascade_settings` is invalid) __Example__ ```python base.add_column_cascade_settings("Table1", "Child", "Parent", { "aaa": ["aaa-1", "aaa-2"], # (1)! "bbb": ["bbb-1", "bbb-2"], "ccc": ["ccc-1", "ccc-2"] }) ``` 1. If `aaa` is selected in the parent column, the available options for the child column will be `aaa-1` and `aaa-2` --- Source: https://developer.seatable.com/python/objects/rows/ # Rows You'll find below all the available methods to interact with the rows of a SeaTable table. In this section, you'll have to deal with the **id** of the rows. You can find few tips on how to get it in [the user manual](https://seatable.com/help/what-is-row-id/). All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. For the structure of objects returned by these methods, see the [API model reference](https://api.seatable.com/reference/models). ## Get row(s) Get a row from table `table_name` via its `row_id`. ```python base.get_row(table_name, row_id) ``` __Output__ Single row dict (throws an error if no table named `table_name` exists or if no row with the id `row_id` exists) __Example__ ```python row = base.get_row('Table1', 'U_eTV7mDSmSd-K2P535Wzw') ``` Lists multiple rows of the table `table_name`. If `view_name` is provided, only the rows displayed in this specific view will be returned. The default `limit` is 1000 which is also the maximum number of rows this method returns. The query method (see below) offers more filter options and can return more rows. ```python base.list_rows(table_name, view_name=None, start=None, limit=None) # (1)! ``` 1. `view_name` (optional): the name of the view you want to get the rows from. If there is no view named `view_name`, all the rows from table `table_name` will be eventually returned (depending on `start` and `limit`) `start` (optional): the index of the first rows you want to get (default is `0`) `limit` (optional): the maximum number of rows that should be returned (default is 1000, couldn't be higher) !!! warning "Mind the indexes!" In the SeaTable web interface, the row numbers, on the left, start at 1, whereas the `start` argument for the `base.list_rows` method starts at 0! This means that to get as first row the row numbered 10 in the web interface, you'll have to enter `start=9`. __Output__ List of row dicts (eventually empty if `start` is higher than the number of rows or if the view `view_name` is empty, throws an error if no table named `table_name` exists or if no view named `view_name` exists) __Example__ ```python rows = base.list_rows('Table1') rows = base.list_rows('Table1', view_name='Default View', start=5, limit=20) ``` Use SQL to query a base. SQL queries are the most powerful way access data stored in a base. If you're not familiar with SQL syntax, we recommend using first the [SQL query plugin](https://seatable.com/help/anleitung-zum-sql-abfrage-plugin/). Most SQL syntax is supported, you can check the [SQL Reference](../../sql/index.md) section of this manual for more information. ```python base.query(sql_statement) ``` Unless the SQL statement specifies a higher limit, the method returns a maximum of 100 rows. The maximum number of rows returned is 10000 no matter the limit specified in the SQL statement. !!! info "Backticks for table or column names containing or special characters or using reserved words" For SQL queries, you can use numbers, special characters or spaces in the names of your tables and columns. However, you'll **have to** escape these names with backticks in order for your query to be correctly interpreted, for example `` SELECT * FROM `My Table` ``. Similarly, if some of your table or column names are the same as [SQL function](../../sql/functions.md) names (for example a date-type column named `date`), you'll also **have to** escape them in order for the query interpreter to understand that it's not a function call missing parameters, but rather a table or column name. __Output__ List of row dicts (eventually empty if no row match the request's conditions) All the examples below are related to a table **Bill** with the following structure/data: | name | price | year | | ----- | ----- | ----- | | Bob | 300 | 2021 | | Bob | 300 | 2019 | | Tom | 100 | 2019 | | Tom | 100 | 2020 | | Tom | 200 | 2021 | | Jane | 200 | 2020 | | Jane | 200 | 2021 | __Example with a wildcard__ === "Function call" ```python import json json_data = base.query('select * from Bill') # (1)! print(json.dumps(json_data, indent=' ')) ``` 1. `*` means that you want to get the whole rows data (columns's values and specific row data such as id, etc.) === "Output" ```json [ { "name": "Bob", "price": 300, "year": 2021, "_locked": null, "_locked_by": null, "_archived": false, "_creator": "bd26d2b...82ca3fe1178073@auth.local", "_ctime": "2025-09-15T10:57:19.106+02:00", "_last_modifier": "bd26d2b...82ca3fe1178073@auth.local", "_mtime": "2025-09-18T09:52:00+02:00", "_id": "W77uzH1cSXu2v2UtqA3xSw" }, { "name": "Bob", "price": 300, "year": 2019, "_locked": null, "_locked_by": null, "_archived": false, "_creator": "bd26d2b...82ca3fe1178073@auth.local", "_ctime": "2025-09-15T10:57:22.112+02:00", "_last_modifier": "bd26d2b...82ca3fe1178073@auth.local", "_mtime": "2025-09-18T09:52:00+02:00", "_id": "IxONgyDFQxmcDKpZWlQ9XA" }, { "name": "Tom", "price": 100, "year": 2019, "_locked": null, "_locked_by": null, "_archived": false, "_creator": "bd26d2b...82ca3fe1178073@auth.local", "_ctime": "2025-09-15T10:57:23.4+02:00", "_last_modifier": "bd26d2b...82ca3fe1178073@auth.local", "_mtime": "2025-09-18T09:52:00+02:00", "_id": "K4LBuQ7aSjK9JwN14ITqvA" }, { "name": "Tom", "price": 100, "year": 2020, "_locked": null, "_locked_by": null, "_archived": false, "_creator": "bd26d2b...82ca3fe1178073@auth.local", "_ctime": "2025-09-18T09:52:00+02:00", "_last_modifier": "bd26d2b...82ca3fe1178073@auth.local", "_mtime": "2025-09-18T09:52:00+02:00", "_id": "EHcQEaxiRzm3Zvq8B33bwQ" }, { "name": "Tom", "price": 200, "year": 2021, "_locked": null, "_locked_by": null, "_archived": false, "_creator": "bd26d2b...82ca3fe1178073@auth.local", "_ctime": "2025-09-18T09:52:00+02:00", "_last_modifier": "bd26d2b...82ca3fe1178073@auth.local", "_mtime": "2025-09-18T09:52:00+02:00", "_id": "CjaCdBlNRXKkYkm231shqg" }, { "name": "Jane", "price": 200, "year": 2020, "_locked": null, "_locked_by": null, "_archived": false, "_creator": "bd26d2b...82ca3fe1178073@auth.local", "_ctime": "2025-09-18T09:52:00+02:00", "_last_modifier": "bd26d2b...82ca3fe1178073@auth.local", "_mtime": "2025-09-18T09:52:00+02:00", "_id": "YzmUexIAR7iDWmhKGHgpMw" }, { "name": "Jane", "price": 200, "year": 2021, "_locked": null, "_locked_by": null, "_archived": false, "_creator": "bd26d2b...82ca3fe1178073@auth.local", "_ctime": "2025-09-18T09:52:00+02:00", "_last_modifier": "bd26d2b...82ca3fe1178073@auth.local", "_mtime": "2025-09-18T09:52:00+02:00", "_id": "HJi7wbUMQIOuIlPaoO9Fbg" } ] ``` __Example with WHERE__ === "Function call 1 (filter by year)" ```python import json json_data = base.query('select name, price from Bill where year = 2021') print(json.dumps(json_data, indent=' ')) ``` === "Output #1" ```json [ {"name":"Bob","price":"300"}, {"name":"Tom","price":"200"}, {"name":"Jane","price":"200"} ] ``` === "Function call 2 (filter by name)" ```python import json json_data = base.query('select name, price, year from Bill where name = "Bob"') print(json.dumps(json_data, indent=' ')) ``` === "Output #2" ```json [ {"name":"Bob","price":"300","year":"2021"}, {"name":"Bob","price":"300","year":"2019"} ] ``` __Example with GROUP BY__ === "Function call" ```python import json json_data = base.query('select name, sum(price) from Bill group by name') print(json.dumps(json_data, indent=' ')) ``` === "Output" ```json [ {'name': 'Bob', 'SUM(price)': 600}, {'name': 'Tom', 'SUM(price)': 400}, {'name': 'Jane', 'SUM(price)': 400} ] ``` __Example with DISTINCT__ === "Function call" ```python import json json_data = base.query('select distinct name from Bill') print(json.dumps(json_data, indent=' ')) ``` === "Output" ```json [ {'name': 'Bob'}, {'name': 'Tom'}, {'name': 'Jane'} ] ``` ## Add row(s) By default, the default values specified for the table columns in the web interface do **not** apply when adding/appending rows via Python scripts. In order to apply the default values, add `apply_default=True`as a function parameter. If set to `True`, the default values can be overwritten by specifying alternative values in `row_data`. Add a row to the table `table_name`. This row contains the data specified in the dict `row_data`. No row will be added if `row_data` is an empty dict (`{}`) or if it contains only keys that don't exist in the table. ```python base.append_row(table_name, row_data, apply_default=False) # (1)! ``` 1. `row_data`: dict (pairs of `key`:`value`, each `key` being the name of a column), for example: ```json { 'First Name': 'John', 'Last Name': 'Doe', 'Invoice amount': 100, 'Products': ['Office Supplies', 'Computer'] } ``` `apply_default` (optional): whether to use default values or not (default is `False`) !!! info "Creating an empty row" To create an empty row, specify a `row_data` dict containing at least one existing column of the table with an empty string as value, for example: `{'Name': ''}` __Output__ Single row dict (`None` if no row were added, throws an error if no table named `table_name` exists) __Example__ ```python row_data = { "Name": "Ron" } row = base.append_row('Table1', row_data, apply_default=True) print(row) ``` Append multiple rows to the table `table_name` at once. This function can't operate more than 1000 rows at once. To handle more than 1000 rows, use a loop with offset or an [SQL query](../../sql/index.md) which supports up to 10,000 rows. ```python base.batch_append_rows(table_name, rows_data, apply_default=False) # (1)! ``` 1. `rows_data`: list of `row_data` dict (see `base.append_row` above) `apply_default` (optional): whether to use default values or not (default is `False`) __Output__ Single dict object containing the number of new rows, the list of the ids of the created rows and the first row (see example output below); throws an error if no table named `table_name` exists __Example__ === "Function call" ```python rows_data = [{ 'Name': 'Ron', 'Birthday': '1975-01-01' }, { 'Name': 'Richard', 'Birthday': '1978-10-08' }] rows = base.batch_append_rows('Table1', rows_data) print(rows) ``` === "Output" ```json {   "inserted_row_count": 2, /* (1)! */   "row_ids": [ /* (2)! */   {     "_id": "bglW5pKfQxG9D70hc693Wg"   },   {     "_id": "Q3E3IJWrTQCjOOxjipM8jA"   } ],   "first_row": { /* (3)! */   "0000": "Ron",   "1JGG": "1975-01-01",   "_creator": "cc7a1d0fcec84bf9b36df5dcf5b65b99@auth.local",   "_last_modifier": "cc7a1d0fcec84bf9b36df5dcf5b65b99@auth.local",   "_id": "bglW5pKfQxG9D70hc693Wg",   "_ctime": "2025-09-24T14:52:55.651+00:00",   "_mtime": "2025-09-24T14:52:55.651+00:00" } } ``` 1. `inserted_row_count`: number of new rows 2. `row_ids`: list of dicts, each containing a single `_id` key and the id of the corresponding created row as value 3. `first_row`: the row dict of the first created row Insert one row to the table `table_name` under an *anchor* row whose id is `anchor_row_id`. If no row with id `anchor_row_id` exists, the row is added to the end of the table (similar to `base.append_row` in this case). ```python base.insert_row(table_name, row_data, anchor_row_id, apply_default=False) ``` __Output__ Single row dict (`None` if no row were added, throws an error if no table named `table_name` exists) __Example__ ```python row_data = { "Name": "Ron" } row = base.insert_row('Table1', row_data, 'U_eTV7mDSmSd-K2P535Wzw') print(row) ``` ## Update row(s) Update the row whose id is `row_id` in the table `table_name`. The `row_data` dict (pairs of `key`:`value`, each `key` being the name of a column) need to contain only the data you want to update. To reset a value, specify the `key`:`value` pair with an empty string `''`. ```python base.update_row(table_name, row_id, row_data) ``` __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no table named `table_name` exists or if no row with the id `row_id` exists) __Example__ ```python row_data = { "Name": "Ron" } row_update = base.update_row('Table1', 'U_eTV7mDSmSd-K2P535Wzw', row_data) print(row_update) ``` Updates multiple rows in the table `table_name` at once. This function can't operate more than 1000 rows at once. To handle more than 1000 rows, use a loop with offset or an [SQL query](../../sql/index.md) which supports up to 10,000 rows. ```python base.batch_update_rows(table_name, rows_data) # (1)! ``` 1. `rows_data`: list of dicts containing two `key`:`value` pairs: - `row_id`: the id of the row to update - `row`: the dict containing the row data to update (see `base.append_row` above) __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no table named `table_name` exists, if no row with the id `row_id` exists or if `rows_data` is wrong, for example with non-existing `row_id` value) __Example__ ```python rows_data = [{ "row_id" : "fMmCFyoxT4GN5Y2Powbl0Q", "row" : { "Name" : "Ron", "Height" : "183" } }, { "row_id" : "cF5JTE99Tae-VVx0BGT-3A", "row" : { "Name" : "Richard", "Height" : "184" } }, { "row_id" : "WP-8rb5PSUaM-tZRmTOCPA", "row" : { "Name" : "Regina", "Height" : "173" } }] row_update = base.batch_update_rows('Table1', rows_data) print(row_update) ``` ## Delete row(s) Delete a single row (whose id is `row_id`) from the table `table_name`. ```python base.delete_row(table_name, row_id) ``` __Output__ Dict containing a single `deleted_rows` key with the number of deleted rows (`0` if no row with id `row_id` exists, throws an error if no table named `table_name` exists) __Example__ ```python base.delete_row('Table1', 'U_eTV7mDSmSd-K2P535Wzw') ``` Delete multiple rows from the table `table_name` at once. This function can't operate more than 1000 rows at once. To handle more than 1000 rows, use a loop with offset or an [SQL query](../../sql/index.md) which supports up to 10,000 rows. ```python base.batch_delete_rows(table_name, row_ids) # (1)! ``` 1. `row_ids`: list of the ids of the rows to delete __Output__ Dict containing a single `deleted_rows` key with the number of deleted rows (`0` if `row_ids` is an empty list, throws an error if no table named `table_name` exists) __Example__ ```python # Retrieving the rows of table 'Table1' rows = base.list_rows('Table1') #Getting only the three first rows del_rows = rows[:3] # Creating a list of the ids from these three rows row_ids = [row['_id'] for row in del_rows] deletion_result = base.batch_delete_rows('Table1', row_ids) print(deletion_result) ``` ## Big Data Storage Batch insert rows into big data storage. ```python base.big_data_insert_rows(table_name, rows_data) ``` __Output__ Dict containing a single `inserted_row_count` key with the number of rows actually inserted. __Example__ ```python rows = [ {'Name': "A"}, {'Name': "B"} ] base.big_data_insert_rows('Table1', rows_data=rows) ``` ## Filter rows Filter rows using a condition string. Returns a QuerySet object with chainable methods: `.all()`, `.count()`, `.first()`, `.last()`, `.filter()`, `.get()`, `.delete()`, `.update()`. ```python base.filter(table_name, conditions='', view_name=None) ``` __Output__ QuerySet object __Example__ ```python # Get all rows where Status is "Done" queryset = base.filter('Table1', "Status = 'Done'") rows = queryset.all() count = queryset.count() first = queryset.first() # Chain filters queryset = base.filter('Table1', "Year = 2024").filter("Price > 100") # Update all matching rows base.filter('Table1', "Status = 'Open'").update({'Status': 'Archived'}) ``` Filter rows using structured filter objects. Supports combining multiple filters with `And` or `Or` conjunction. ```python base.filter_rows(table_name, filters, view_name=None, filter_conjunction='And') ``` - `filters`: list of filter dicts, each containing `column_name`, `filter_predicate`, and `filter_term` - `filter_conjunction`: `'And'` or `'Or'` __Output__ List of row dicts __Example__ ```python filters = [ { "column_name": "Status", "filter_predicate": "is", "filter_term": "Done" }, { "column_name": "Price", "filter_predicate": "greater", "filter_term": 100 } ] rows = base.filter_rows('Table1', filters, filter_conjunction='And') ``` --- Source: https://developer.seatable.com/python/objects/links/ # Links All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. `link_id` should not be mistaken with the column `key`! The `key` value is unique (like an id) whereas the link id will be shared between the two linked columns. Please note that `link_id` is used as argument to add/update/remove links, whereas you'll have to provide `link_column_key` (the link column `key`) to get linked records. Both information are available in the column object: ```json {  "key": "Cp51", /* (1)! */  "type": "link",  "name": "Link column",  "editable": true,  "width": 200,  "resizable": true,  "draggable": true,  "data": {   "display_column_key": "0000",   "is_internal": true,   "link_id": "UAmR", /* (2)! */   "table_id": "FJkA", /* (3)! */   "other_table_id": "nw8k", /* (4)! */   "is_multiple": true,   "is_row_form_view": false,   "view_id": "",   "array_type": "text",   "array_data": null,   "result_type": "array"  },  "permission_type": "",  "permitted_users": [],  "permitted_group": [],  "edit_metadata_permission_type": "",  "edit_metadata_permitted_users": [],  "edit_metadata_permitted_group": [],  "description": null,  "colorbys": {} } ``` 1. The column `key` (referred as `link_column_key` in `base.get_linked_records` arguments) 2. The link id of the column (referred as `link_id` in the add/update/remove links operations) 3. The table whose id is `table_id` is referred later in this section as the *source* table (the table containing this column) 4. The table whose id is `other_table_id` is referred later in this section as the *target* table ## Get link id Get the link id of the column `column_name` from the table `table_name`. ```python base.get_column_link_id(table_name, column_name) ``` __Output__ String (throws an error if no table named `table_name` exists or if no column named `column_name` exists) __Example__ ```python link_id = base.get_column_link_id('Table1', 'Link column') print(link_id) ``` ## Get linked records Rows and records are basically the same things. However, to make the following description easier to understand, we will differentiate them: - Rows are from the *source* table (the table whose id is `table_id`) - Records are the rows from the *target* table (the table linked to the *source* table in the column whose `key` is `link_column_key` or whose link id is `link_id`) List the records linked (in the column whose `key` is `link_column_key`) to one or more rows of the *source* table. The row(s) you want to get the linked records from are defined in the `rows` objects (see below). ```python base.get_linked_records(table_id, link_column_key, rows) # (1)! ``` 1. `table_id`: the id of *source* table `link_column_key`: the column **key** of the link-type column of *source* table (**not** the link id from `base.get_column_link_id`) `rows`: a list of dicts, each of them containing: - `row_id`: the id of the row we want to get the linked records from - `limit`: the maximum number of linked records to get (default is 10) - `offset`: the number of first linked records not to retrieve (default is 0) __Output__ Single dict where each `key` is the id of a row of the *source* table and the corresponding value is a list of link dicts (see Output structure example below) __Example__ === "Function run" ```python import json linked_records = base.get_linked_records('0000', '89o4', rows=[ {'row_id': 'FzNqJxVUT8KrRjewBkPp8Q', 'limit': 2, 'offset': 0}, {'row_id': 'Jmnrkn6TQdyRg1KmOM4zZg', 'limit': 20} ]) print(json.dumps(linked_records, indent=' ')) ``` === "Output structure example" ```json { "FzNqJxVUT8KrRjewBkPp8Q" /* (1)! */: [ {"row_id": "LocPgVvsRm6bmnzjFDP9bA", "display_value": "1"} /* (2)! */, {"row_id": "OA6x7CYoRuyc2pT52Znfmw", "display_value": "3"}, ... ], "Jmnrkn6TQdyRg1KmOM4zZg": [ {"row_id": "LocPgVvsRm6bmnzjFDP9bA", "display_value": "1"}, {"row_id": "OA6x7CYoRuyc2pT52Znfmw", "display_value": "3"}, ... ] } ``` 1. id of a row of the *source* table 2. link object: - `row_id` is the id of the linked record (row from the *target* table) - `display_value` is the value displayed in the column whose `key` is `link_column_key` (from a column of the *target* table) ## Add link Add link in a link-type column. You'll need the *source* table's name `table_name`, the *target* table's name `other_table_name`, the link id from the link-type column and both the ids of the rows you want to link: `row_id` for the row from the *source* table and `other_row_id` for the record from the *target* table. ```python base.add_link(link_id, table_name, other_table_name, row_id, other_row_id) ``` __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no column with link id `link_id` exists in the *source* table, if no table named `table_name` or `other_table_name` exists or if no row with id `row_id` or `other_row_id` exists in their respective tables) __Example__ ```python base.add_link('5WeC', 'Team Members', 'Contacts', 'CGtoJB1oQM60RiKT-c5J-g', 'PALm2wPKTCy-jdJNv_UWaQ') ``` __Example: Add link to current row__ ```python from seatable_api import context # Do not hesitate to store the tables' and columns' names at the beginning of your script, # it will make it really easier to update if names change TABLE1_NAME = "Table1" TABLE1_LINK_COLUMN_NAME = "Table2 link" TABLE2_NAME = "Table2" lin_id = base.get_column_link_id(TABLE1_NAME, TABLE1_LINK_COLUMN_NAME) # (1)! current_row_id = context.current_row['_id'] base.add_link(lin_id, TABLE1_NAME, TABLE2_NAME, current_row_id, 'J5St2clyTMu_OFf9WD8PbA') ``` 1. Remember you can use `base.get_column_link_id` to get the link id of a specific link-type column. Add links for multiple rows at once. The `other_rows_ids_map` maps source row IDs to lists of target row IDs. ```python base.batch_add_links(link_id, table_name, other_table_name, other_rows_ids_map) ``` __Output__ Dict containing the result of the operation __Example__ ```python base.batch_add_links( 'r4IJ', 'Table1', 'Table2', { 'fRLglslWQYSGmkU7o6KyHw': ['OcCE8aX8T7a4dvJr-qNh3g', 'JckTyhN0TeS8yvH8D3EN7g'], 'eSQe9OpPQxih8A9zPXdMVA': ['cWHbzQiTR8uHHzH_gVSKIg'] } ) ``` ## Update link(s) Update the content of the link-type column whose link id is `link_id` for the row with id `row_id` in the table `table_name`. It will remove all existing row links and add new links to records of table `other_table_name` with ids in the `other_rows_ids` list. ```python base.update_link(link_id, table_name, other_table_name, row_id, other_rows_ids) ``` __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no column with link id `link_id` exists in the *source* table, if no table named `table_name` or `other_table_name` exists or if no row with id `row_id` exists in the *source* table) __Example__ ```python base.update_link( link_id='r4IJ', table_name='Table1', other_table_name='Table2', row_id='BXhEm9ucTNu3FjupIk7Xug', other_rows_ids=[ 'exkb56fAT66j8R0w6wD9Qg', 'DjHjwmlRRB6WgU9uPnrWeA' ] ) ``` Same than above, except that it allows you to batch update infos of link-type columns for several rows at once. Learn more about `other_rows_ids_map` in the [SeaTable API Reference](https://api.seatable.com/reference/createrowlink). This function can't operate more than 1000 rows at once. To handle more than 1000 rows, use a loop with offset. ```python base.batch_update_links(link_id, table_name, other_table_name, row_id_list, other_rows_ids_map) # (1)! ``` 1. `row_id_list` is a list containing the ids of all the rows of the source table (whose id is `table_id`) you want to update `other_rows_ids_map` is an object with the following syntax, the keys `id_1`,`id_2`,...,`id_n` being **all** the ids of `row_id_list`: ```python { 'id_1': [record1['_id'], record2['_id']], 'id_2': [record5['_id']], ... 'id_n': [record1['_id'], recordn['_id']] } ``` __Output__ Dict containing a single `success` key with the result of the operation (throws an error if no column with link id `link_id` exists in the *source* table, if no table named `table_name` or `other_table_name` exists or if no row with one of the id `row_id_list` exists in the *source* table) __Example__ ```python link_id = "WaW5" table_name = "Table1" other_table_name ="Table2" row_id_list = ["fRLglslWQYSGmkU7o6KyHw","FseN8ygVTzq1CHDqI4NjjQ"] other_rows_ids_map = { "FseN8ygVTzq1CHDqI4NjjQ":["OcCE8aX8T7a4dvJr-qNh3g","JckTyhN0TeS8yvH8D3EN7g"], "fRLglslWQYSGmkU7o6KyHw":["MdfUQiWcTL--uMlrGtqqgw","E7Sh3FboSPmfBlDsrj_Fhg","UcZ7w9wDT-uVq4Ohtwgy9w"] } base.batch_update_links(link_id, table_name, other_table_name, row_id_list, other_rows_ids_map) ``` ## Remove link Delete the link to the record from table `other_table_name` whose id is `other_row_id` in the row from table `table_name` whose id is `row_id`. ```python base.remove_link(link_id, table_name, other_table_name, row_id, other_row_id) ``` __Output__ Dict containing a `success` key with the result of the operation and a `deleted_links_count` with the number of actually deleted links (throws an error if no column with link id `link_id` exists in the *source* table, if no table named `table_name` or `other_table_name` exists or if no row with id `row_id` or `other_row_id` exists in their respective tables) __Example__ ```python base.remove_link('5WeC', 'Table1', 'Table2', 'CGtoJB1oQM60RiKT-c5J-g', 'PALm2wPKTCy-jdJNv_UWaQ') ``` Remove links for multiple rows at once. The `other_rows_ids_map` maps source row IDs to lists of target row IDs to unlink. ```python base.batch_remove_links(link_id, table_name, other_table_name, other_rows_ids_map) ``` __Output__ Dict containing the result of the operation __Example__ ```python base.batch_remove_links( 'r4IJ', 'Table1', 'Table2', { 'fRLglslWQYSGmkU7o6KyHw': ['OcCE8aX8T7a4dvJr-qNh3g'], 'eSQe9OpPQxih8A9zPXdMVA': ['cWHbzQiTR8uHHzH_gVSKIg', 'X56gE7BrRF-i61YlE4oTcw'] } ) ``` --- Source: https://developer.seatable.com/python/objects/files/ # Files All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. ## Download For the following methods, you'll have to provide the URL of the file you want to download. The file URL structure is as follows: ```js {server_url}/workspace/{workspace_id}/asset-preview/{base_uuid}/{file location} ``` - `{server_url}` is the URL of your server, for example `https://cloud.seatable.io` - You can find the `workspace_id` by looking at any of your database URL which will look like `{server_url}/workspace/{workspace_id}/dtable/{base_name}`, or by checking the [user manual](https://seatable.com/help/find-workspace-id-group/) - You can find the base uuid in your Team administration (see the [User manual](https://seatable.com/help/bases-in-team-administration/), it's displayed as `ID` in the base right panel) or by looking for `dtableUuid` in the source code of the web page while consulting any of your bases - The file location is what you can find in the [file manager](https://seatable.com/help/file-management-in-a-base/) of your base and will always have the same structure: - if uploaded automatically, the file will be in the system folder `files` (if uploaded in a **file-type column**, even if it's an image) and in the subdirectory `YYYY-MM` (year and month of the upload), for example `https://cloud.seatable.io/dtable-web/workspace/74/asset-preview/41cd05da-b29a-4428-bc31-bd66f4600817/files/2020-10/invoice.pdf` is the URL of a file called invoice.pdf and downloaded in October 2020 (`2020-10`) in the base whose uuid is `41cd05da-b29a-4428-bc31-bd66f4600817` in the workspace whose id is `74` on `https://cloud.seatable.io`. It will be the same for an image, but in the `images` system folder instead of the `files` folder (if uploaded in an **image-type column only**) - if uploaded by yourself in a custom folder, the file will be in directory `custom` and in the eventual directory you created, for example: `https://cloud.seatable.io/dtable-web/workspace/74/asset-preview/41cd05da-b29a-4428-bc31-bd66f4600817/custom/My Personal Folder/quote.pdf` is the URL of a file called quote.pdf that you stored in the folder `My Personal Folder` of the custom folders in the same database. For files that need to open an external window to preview (almost all files except images), the URL of this new window will actually be the URL your looking for! Download a file to a local path. The save path naturally has to end with the same extension as the original file. ```python base.download_file(file_url, save_path) ``` __Output__ Nothing (throws an error if the URL is invalid or if the save path is wrong) __Example__ ```python file_url = "https://cloud.seatable.io/workspace/74/asset-preview/41cd05da-b29a-4428-bc31-bd66f4600817/files/2020-10/invoice.pdf" save_path = "/tmp/invoice.pdf" base.download_file(file_url, save_path) ``` Download every file from the *File* column of *Table1* table, providing the id of the row. ```python from seatable_api import Base server_url = 'https://cloud.seatable.io' api_token = '5e165f8b7af...98950b20b022083' base = Base(api_token, server_url) base.auth() target_row = base.get_row('Table1','Pd_pHLM8SgiEcnFW5I7HLA') save_directory = './tmp/' # (1)! files = target_row['File'] print(f"{len(files)} files to download") for file in files : print(f"Downloading {file['url']}") base.download_file(file['url'], save_directory + file['name']) # (2)! ``` 1. Ensure that your target directory exists! Directory beginning with `.` are relative to the current working directory 2. The download URL is found in the `url` key of each element of the file-type column. The `name` key is used so every files will keep their original names (and you don't have to bother with extensions) This detailed method is for handling complex situations, for example when the file is extremely large or the internet connection is slow. In this example, we assume that a file with URL `https://cloud.seatable.io/dtable-web/workspace/74/asset-preview/41cd05da-b29a-4428-bc31-bd66f4600817/files/2020-10/invoice.pdf` exists (a file called invoice.pdf and downloaded in October 2020 (`2020-10`) in the base whose uuid is `41cd05da-b29a-4428-bc31-bd66f4600817`, located in the workspace whose id is `74`). This method actually relies on two different steps: first getting the file public download link and then downloading it using a `GET` request. Compared to the file URL from the `base.download_file` method, the file path needed here is just the "file location" part of the URL corresponding to the location of the file in the base file system (starting **with** `/files/`, `/images/` or `/custom/`). !!! info "Download link expires" The download link is only valid for some hours. After that the download link must be created again. That's why it's not possible to use permanent download links of files hosted on SeaTable in web pages. For such purpose, we recommend to store the files on public hosting services and to save only the links in SeaTable, which will allow direct use. ```python base.get_file_download_link(file_path) ``` __Output__ The public download link (looking like `{server_url}/seafhttp/files/{access_token}/{file_name}`). Keep in mind that it's not permanent as the token expires! (throws an error if the file path is wrong) __Example__ ```python from seatable_api import Base, context import requests base = Base(context.api_token, context.server_url) base.auth() download_link = base.get_file_download_link('files/2020-10/invoice.pdf') response = requests.get(download_link) if response.status_code in range(200,300) : # (1)! with open("invoice.pdf", "wb") as f: # (2)! f.write(response.content) ``` 1. `2xx` response status codes correspond to a successful request 2. Open the file with write permission and write the response content into it This method is specific for files stored in a custom folder. Compared to the file URL from the `base.download_file` method, the custom path needed here is just the part of the URL corresponding to the location of the file in the custom folders file system (part of the URL starting **after** `/custom/`). In the following example, we consider the file quote.pdf described in the `base.download_file` section uploaded in the custom folder `My Personal Folder` whose URL is `https://cloud.seatable.io/dtable-web/workspace/74/asset-preview/41cd05da-b29a-4428-bc31-bd66f4600817/custom/My Personal Folder/quote.pdf`. ```python base.download_custom_file(custom_path, save_path) ``` __Output__ Nothing (throws an error if the URL is invalid or if the save path is wrong) __Example__ ```python custom_file_path = "My Personal Folder/quote.pdf" # (1) ! local_path = "/Users/Desktop/quote.pdf" base.download_custom_file(custom_file_path, local_path) ``` 1. Unlike the `get_file_download_link` method, `custom_file_part` **doesn't** include `/custom/` ## Upload Please note that uploading a file *to a cell* will require two or three steps, depending on the method you use: you'll first need to upload the file to the base, and then you'll be able to update the row with the newly uploaded file details in the cell. You can learn more about this process in the [API Reference](https://api.seatable.com/reference/uploadfile). As for download, there are one simple (one-step) and one detailed (two-steps) process to upload a file: Upload a file from your local drive, memory or a website. ```python base.upload_local_file(file_path, name=None, file_type='file', replace=False) # (1)! # or base.upload_bytes_file(name, content, file_type='file', replace=False) # (2)! ``` 1. - `name`: the name of the file once uploaded. If `name` is not provided, the uploaded file will keep the same name than the original - `file_type`: can be either `file` or `image` (default is `file`) - `replace`: if set to `True`, uploading a new file with the same name as an existing one will overwrite it (default is `False`) 2. When using `base.upload_bytes_file`, `name` is mandatory as there is no name attached to the `content` __Output__ File dict containing the same keys as every element in a file-type column: `type` (`file` or `image`), `size`, `name` and `url` __Example: Upload a file from local hard drive__ ```python local_file = '/Users/Markus/Downloads/seatable-logo.png' info_dict = base.upload_local_file(local_file, name='seatable-logo.png', file_type='image', replace=True) ``` __Example: Upload a file from memory__ ```python local_file = '/Users/Markus/Downloads/seatable-logo.png' with open (local_file, 'rb') as f: content = f.read() info_dict = base.upload_bytes_file('seatable-logo.png', content, file_type='image') ``` __Example: Upload a file from a website__ ```python import requests file_url = 'https://seatable.io/wp-content/uploads/2021/09/seatable-logo.png' response = requests.get(file_url) if response.status_code in range(200,300) : info_dict = base.upload_bytes_file('seatable-logo.png', response.content) ``` As for the download detailed method, this method actually relies on two different steps: first getting a file upload link and then uploading it using a `POST` request. ```python base.get_file_upload_link() ``` __Output__ - `base.get_file_upload_link` outputs a dict containing `upload_link`, `parent_path`, `img_relative_path` and `file_relative_path` keys - the `POST` request will return a `400` error `Parent dir doesn't exist.` if `parent_dir` is wrong or a `403` error `Access token not found.` if `upload_link` is wrong __Example__ ```python import requests from seatable_api import Base, context base = Base(context.api_token, context.server_url) base.auth() # Get the upload link and file path allocated by server upload_link_dict = base.get_file_upload_link() upload_link = upload_link_dict['upload_link'] # (1)! parent_dir = upload_link_dict['parent_path'] # (2)! file_relative_path = upload_link_dict['file_relative_path'] img_relative_path = upload_link_dict['img_relative_path'] # Upload the file upload_file_name = "file_uploaded.txt" replace = True response = requests.post(upload_link, data={ 'parent_dir': parent_dir, 'replace': 1 if replace else 0 # (3)! }, files={ 'file': (upload_file_name, open('/User/Desktop/file.txt', 'rb')), 'relative_path': file_relative_path # (4)! }) ``` 1. `upload_link` will look like `{server_url}/seafhttp/upload-api/{temporary_upload_token}` 2. `parent_path` will look like `/asset/{base_uuid}`. Please note that the name of the corresponding parameter for the upload `POST` request is `parent_dir`! 3. `replace` requires `0` or `1`. You can use this syntax if you prefer to specify `True` or `False` 4. Choose either the **file** relative path or the **image** relative path depending on the type of column you want to upload your file to This method is specific for files to store in a custom folder. It is the counterpart of the `base.download_custom_file` method. Please note that using this method, existing files will not be replaced (a new `My file(2)` will be created if `My file` already exists). ```python base.upload_local_file_to_custom_folder(local_path, custom_folder_path=None, name=None) # (1)! ``` 1. - `custom_folder_path`: the path in the custom folders of the base where you want to upload the file - `name`: the name of the file once uploaded. If `name` is not provided, the uploaded file will keep the same name than the original __Output__ Single file dict containing `type`, `size`, `name` and `url` keys. This dict can be used to "assign" a file to a row. __Example__ ```python #Step 1: Uploading a file to the base local_path = "/Users/Desktop/sky.png" custom_path = "/Main/" info_dict = base.upload_local_file_to_custom_folder(local_path, custom_path) #Step 2: Update a row with the uploaded file row_id = "xxxx" FILE_COL_NAME = "File" # (1)! base.update_row('Table1', row_id, {FILE_COL_NAME: [info_dict]}) ``` 1. Get in the habit of storing column and/or table names in variables, this will make your scripts much easier to update if names change ## List files List files in any folder of the custom folders file system (use `/` as path if you want to see the content of Custom folders). If you need to list the files present in a system (non-custom) folder, please refer to the [API Reference](https://api.seatable.com/reference/listbaseassets). ```python base.list_custom_assets(path) # (1)! ``` 1. `path`: **Absolute** path of the directory you want to list the assets for (for example `/My Personal Folder/Photos` for a subdirectory `Photos` located in the directory `My Personal Folder`) __Output__ A dict containing a `dir` and a `file` key, each containing a list of respectively directories and files present in the `path` you specified (throws an error if the path is not valid) __Example__ ```python folder_dir = "/Main/photos" main_photos_content = base.list_custom_assets(folder_dir) print(main_photos_content) ``` __Example: display the whole Custom folders file structure__ ```python def list_assets(path): global indent if path == "/" : print(f"📁 {path}") else : print(f"{indent}∟ 📁 {path.split('/')[-1]}") assets = base.list_custom_assets(path) if assets: indent += ' ' for f in assets['file']: print(f"{indent}∟ 📄 {f['name']}") for d in assets['dir']: # (1)! if path == '/' : list_assets(path+d['name']) else : list_assets(path+'/'+d['name']) indent = indent[:-1] indent = '' list_assets('/') # (2)! ``` 1. Recursive function: for each directory of the current directory, the functions calls itself 2. The `list_assets` function we created starts at the root level (`/`) ## Get file info This methods allows you to get the file dict of any `name` file in any folder (`path`) of the custom folders file system. ```python base.get_custom_file_info(path, name) # (1)! ``` 1. `path`: **Absolute** path of the directory you want to list the assets for (for example `/My Personal Folder/Photos` for a subdirectory `Photos` located in the directory `My Personal Folder`) __Output__ Single file dict containing `type`, `size`, `name` and `url` keys (throws an error if `path` or `name` is not valid). This dict can be used to "assign" a file to a row. __Example__ === "Replace existing content" ```python #Step 1: Get file info folder_dir = "/Main/" file_name = "sky.png" file_dict = base.get_custom_file_info(folder_dir, file_name) print(file_dict) #Step 2: Update row content with file info (overwriting current content) row_id = "fDMHEdraSRuUMNPGEj-4kQ" FILE_COL_NAME = "File" base.update_row("Table1", row_id, {FILE_COL_NAME: [file_dict]}) ``` === "Append to content (detailed version)" ```python #Step 1: Get file info folder_dir = "/Main/" file_name = "sky.png" file_dict = base.get_custom_file_info(folder_dir, file_name) print(file_dict) #Step 2: Update row content with file info (appending to current content) row_id = "fDMHEdraSRuUMNPGEj-4kQ" FILE_COL_NAME = "File" row = base.get_row("Table1", row_id) current_files = row[FILE_COL_NAME] current_files.append(file_dict) print(base.update_row("Table1", row_id, {FILE_COL_NAME: current_files})) ``` === "Append to content (short version)" ```python #Step 1: Get file info folder_dir = "/Main/" file_name = "sky.png" file_dict = base.get_custom_file_info(folder_dir, file_name) print(file_dict) #Step 2: Update row content with file info (appending to current content) row_id = "fDMHEdraSRuUMNPGEj-4kQ" FILE_COL_NAME = "File" print(base.update_row("Table1", row_id, {FILE_COL_NAME: base.get_row("Table1",row_id)[FILE_COL_NAME] + [file_dict]})) ``` --- Source: https://developer.seatable.com/python/objects/accounts/ # Accounts The account object provides an interface to list workspaces, add/copy/delete bases, and obtain access rights to a base. Accessing the account object requires a specific authentication. ```python from seatable_api import Account # (1)! username = 'xxx@email.com' # (2)! password = 'xxxxxxx' server_url = 'https://cloud.seatable.io/' account = Account(username, password, server_url) account.auth() ``` 1. Don't forget to import `Account` from `seatable_api` 2. Always be vigilant when exposing your credentials in a script! Prefer as often as possible more secure solutions such as environment variables or \.env\ files ## Manage workspaces Get all your workspaces and their bases. ```python account.list_workspaces() ``` __Output__ Dict with a single `workspace_list` key containing a list of every workspaces and for each a list of tables or shared tables of views __Example__ === "Function call" ```python import json from seatable_api import Account username = 'xxx@email.com' password = 'xxxxxxx' server_url = 'https://cloud.seatable.io/' account = Account(username, password, server_url) account.auth() workspaces = account.list_workspaces() print(json.dumps(workspaces, indent=' ')) ``` === "Output example" ```json { "workspace_list": [ { "id": "", "name": "starred", /* (1)! */ "type": "starred", "table_list": [] }, { "id": "", "name": "shared", /* (2)! */ "type": "shared", "shared_table_list": [], "shared_view_list": [ { "id": 1416, "dtable_name": "MBase", "from_user": "b4980649.....b1311ab4ba2@auth.local", "to_user": "cc7a1d0fcec......df5dcf5b65b99@auth.local", "permission": "rw", "table_id": "ji9k", "view_id": "0000", "shared_name": "Shared MBase", "from_user_name": "Tony Stark", "to_user_name": "Hulk", "from_user_avatar": "", "workspace_id": 34996, "color": null, "text_color": null, "icon": null, "share_id": 1416, "share_type": "view-share" } ], "share_folders": [] }, { "id": 84254, "name": "personal", /* (3)! */ "type": "personal", "table_list": [ { "id": 198299, "workspace_id": 84254, "uuid": "0959ee9c-6b....8c-a751-c798431ab3ad", "name": "AllColumnsBase", "created_at": "2025-09-04T12:39:08+02:00", "updated_at": "2025-09-25T11:31:48+02:00", "color": null, "text_color": null, "icon": null, "is_encrypted": false, "in_storage": true, "starred": false }, { "id": 200036, "workspace_id": 84254, "uuid": "30fd2a69-07.....e-85ee-be3230a87ea2", "name": "Big Data", "created_at": "2025-09-11T12:11:58+02:00", "updated_at": "2025-09-23T11:09:09+02:00", "color": null, "text_color": null, "icon": null, "is_encrypted": false, "in_storage": true, "starred": false }, { "id": 202730, "workspace_id": 84254, "uuid": "98e53b22-80....d5-92ca-c44d783d9561", "name": "Ledger", "created_at": "2025-09-23T15:19:30+02:00", "updated_at": "2025-09-23T17:06:48+02:00", "color": "#E91E63", "text_color": null, "icon": "icon-dollar", "is_encrypted": false, "in_storage": true, "starred": false }, { "id": 197691, "workspace_id": 84254, "uuid": "4b5ef925-c178-4000-89e2-941aa65cc747", "name": "Test", "created_at": "2025-09-03T09:03:57+02:00", "updated_at": "2025-09-25T10:37:13+02:00", "color": "#656463", "text_color": null, "icon": "icon-research", "is_encrypted": false, "in_storage": true, "starred": false } ], "folders": [] }, { "id": 86760, "name": "My group", "type": "group", "group_id": 10339, "group_owner": "cc7a1d0fcec......df5dcf5b65b99@auth.local", "is_admin": true, "table_list": [ { "id": 197108, "workspace_id": 86760, "uuid": "eec7ff7b-638......4cb-315489bca05e", "name": "My grouped table", "created_at": "2025-09-01T11:44:49+02:00", "updated_at": "2025-09-01T11:44:49+02:00", "color": null, "text_color": null, "icon": null, "is_encrypted": false, "in_storage": true, "starred": false } ], "group_shared_dtables": [], "group_shared_views": [], "folders": [] } ] } ``` 1. "Favorites" section 2. "Shared with me" section 3. "My bases" section ## Manage bases Get the base named `base_name` in the workspace whose id is `workspace_id`. You'll be able to interact with this base using all the `base` methods presented in this manual. Please note that the base is authorized. ```python account.get_base(workspace_id, base_name) ``` __Output__ base object (throws an error if no workspace with id `workspace_id` or no base `base_name` exists, or if you encounter permission issue) __Example__ ```python from seatable_api import Account username = 'xxx@email.com' password = 'xxxxxxx' server_url = 'https://cloud.seatable.io/' account = Account(username, password, server_url) account.auth() base = account.get_base(35, 'new-base') print(base.get_metadata()) ``` Add a base named `base_name` to a Workspace. If no `workspace_id` is provided, the base will be created in the "My bases" section (workspace named "personal"). ```python account.add_base(base_name, workspace_id=None) ``` __Output__ Dict containing the same base metadata as members of the `table_list` of the workspace metadata (throws an error if no workspace with id `workspace_id` exists or if a base named `base_name` already exists in the workspace) __Example__ ```python from seatable_api import Account username = 'xxx@email.com' password = 'xxxxxxx' server_url = 'https://cloud.seatable.io/' account = Account(username, password, server_url) account.auth() base_metadata = account.add_base('My New Base', 35) print(base_metadata) ``` Copy the base base_name from the workspace whose id is `src_workspace_id` to the workspace whose id is `dst_workspace_id`. ```python account.copy_base(src_workspace_id, base_name, dst_workspace_id) ``` __Output__ Dict containing the same base metadata as members of the `table_list` of the workspace metadata (throws an error if no workspace with id `workspace_id` exists or if a base named `base_name` already exists in the workspace) for the newly created base __Example__ ```python from seatable_api import Account username = 'xxx@email.com' password = 'xxxxxxx' server_url = 'https://cloud.seatable.io/' account = Account(username, password, server_url) account.auth() base_metadata = account.copy_base(35, 'My Base', 74) print(base_metadata) ``` --- Source: https://developer.seatable.com/python/objects/users/ # Users All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. ## Get user info Returns the name of the user and their ID. The username you have to provide is a unique identifier ending with `@auth.local`. This is **neither** the email address of the user **nor** their name. ```python base.get_user_info(username) ``` __Output__ Dict containing `id_in_org` and `name` keys __Example__ ```python user_info = base.get_user_info("aea9e807bcfd4f3481d60294df74f6ee@auth.local") print(user_info) ``` ## Get related users Get a list of users related to the current base (collaborators who have access). ```python base.get_related_users() ``` __Output__ List of user dicts __Example__ ```python users = base.get_related_users() for user in users: print(user) ``` --- Source: https://developer.seatable.com/python/objects/date-utils/ # Date utility functions We provide a set of functions for the datetime (date and time) operations based on the datetime python library. These functions have the same behavior as the functions provided by the formula column of SeaTable. To use these functions, the dateutils module must be imported. ```python from seatable_api.date_utils import dateutils ``` ## Introduction ### Date and time formatting The ISO format is used in date methods, both for input and output, which means: - `YYYY-MM-DD` (or `%Y-%m-%d` referring to the [python datetime library format codes](https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes)) for dates - `YYYY-MM-DD HH:mm:ss` (or `%Y-%m-%d %H:%M:%S`) for datetimes. Please note that the hour is (24-hour based) - Datetimes format with timezone info requires a specific format: `YYYY-MM-DDTHH:mm:ss±hh:mm` with the letter `T` separating the date from the time and `±hh:mm` representing the offset to UTC (here `+08:00` for UTC+8) Of course, methods outputs with this format can be reused as input for other `dateutils` methods requiring the same format. You'll find below an overview example. Every methods are detailed in the following of this section. If the input time string has a timezone info, it will be automatically converted to local time. ### Overview example In this example as in the following of this section, the comment at the end of each line shows the expected result (what you should update if you `print` the result of the current line) ```python from seatable_api.date_utils import dateutils dt_now = dateutils.now() # 2025-09-30 15:47:00 # 1. date 10 days after dt_now dt_10_days = dateutils.dateadd(dt_now, 10) # 2025-10-10 15:47:00 # 2. month 10 days after dt_now dt_month_10_days = dateutils.month(dt_10_days) # 10 # 3. difference between 2 days dt_10_days_before = dateutils.dateadd(dt_now, -10) date_df = dateutils.datediff(dt_10_days_before, dt_10_days, unit="D") # 20 # 4. handle the time string with time-zone info with local timezone of "Asia/Shanghai" (UTC+8) time_str = "2025-07-17T21:57:41+08:00" time_day = dateutils.day(time_str) # 17 time_month = dateutils.month(time_str) # 7 time_year = dateutils.year(time_str) # 2025 time_hour = dateutils.hour(time_str) # 15 (! if local timezone is UTC+2 !) time_minute = dateutils.minute(time_str) # 57 time_date = dateutils.date(time_year, time_month, time_day) # 2025-07-17 ``` ## Dealing with date and time ### date Return the ISO formatted date string. ```python dateutils.date(year, month, day) ``` __Example__ ```python from seatable_api.date_utils import dateutils custom_date = dateutils.date(2025, 9, 16) print(custom_date) # 2025-09-16 ``` ### dateadd Add a `number` of a specified `interval` to a datetime `datetime_str`. `interval` can represent the following units: `years`, `months`, `weeks`, `days`, `hours`, `minutes` and `seconds` (default is `days`). Negative values ​​can be used to subtract from `datetime_str`. ```python dateutils.dateadd(datetime_str, number, interval) ``` __Example__ ```python from seatable_api.date_utils import dateutils date_str = "2025-9-15" datetime_str = "2025-9-15 15:23:21" dateutils.dateadd(date_str, -2, 'years') # 2023-09-15 dateutils.dateadd(date_str, 3, 'months') # 2025-12-15 dateutils.dateadd(datetime_str, 44, 'minutes') # 2025-09-15 16:07:21 dateutils.dateadd(datetime_str, 1000, 'days') # 2028-06-11 15:23:21 dateutils.dateadd(datetime_str, 3, 'weeks') # 2025-10-06 15:23:21 dateutils.dateadd(datetime_str, -3, 'hours') # 2025-09-15 12:23:21 dateutils.dateadd(datetime_str, 3, 'seconds') # 2025-09-15 15:23:24 ``` ### datediff Compute the time between two datetimes in one of the following units:`S`, `Y`, `D`, `H`, `M`, `YM`, `MD`, `YD`. The result can be negative if `end_date` is before `start_date`. For date units (`Y`,`M` and `D`), unit might include two characters: - `YM`: The difference between the months in `start_date` and `end_date`. The days and years of the dates are ignored. - `MD`: The difference between the days in `start_date` and `end_date`. The months and years of the dates are ignored. - `YD`: The difference between the days of `start_date` and `end_date`. The years of the dates are ignored. ```python dateutils.datediff(start=start_date, end=end_date, unit=datetime_unit) dateutils.datediff(start_date, end_date, datetime_unit) # (1)! ``` 1. Depending on your preferences, you can either specify the name of each parameter (longer but easier to reread) or not __Example__ ```python from seatable_api.date_utils import dateutils as dt # (1)! start_date = "2025-5-16" end_date = "2026-5-15" dt.datediff(start=start_date, end=end_date, unit='S') # 31449600 (seconds) dt.datediff(start=start_date, end=end_date, unit='Y') # 0 (years) dt.datediff(start=start_date, end=end_date, unit='D') # 364 (days) dt.datediff(start=start_date, end=end_date, unit='H') # 8736 (hours) dt.datediff(start=start_date, end=end_date, unit='M') # 12 (months) (from 2025-5 to 2026-5) dt.datediff(start=start_date, end=end_date, unit='YM') # 0 (months) (from May to May) dt.datediff(start=start_date, end=end_date, unit='MD') # -1 (days) (from 16 to 15) dt.datediff("2025-1-28","2026-2-1", unit='YD') # 4 (days) (from January 28 to February 1) ``` 1. To make calls shorter or more explicit, feel free to use an alternative name using `as` keyword. Here, we use `dt` instead of the default `dateutils` ### day Return the day of a given `date`. ```python dateutils.day(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.day('2025-6-15 14:23:21') # 15 ``` ### days Return the days difference between two given dates. The result can be negative if `end` is before `start`. ```python dateutils.days(start, end) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.days('2024-6-1', '2025-5-15') # 348 ``` ### eomonth Return the ISO formatted last day of the `n`th month before or after given date (depending on the sign of `n`). ```python dateutils.eomonth(date, months=n) ``` __Example__ ```python from seatable_api.date_utils import dateutils date = "2025-7-4" dateutils.eomonth(date, months=0) # 2025-07-31 (months=0 => current month) dateutils.eomonth(date, months=2) # 2025-09-30 (2 months after July => September) dateutils.eomonth(date, months=-5) # 2025-02-28 (5 months before July => February) ``` ### hour Return the hour of a given `datetime`. ```python dateutils.hour(datetime) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.hour("2025-1-1 12:13:14") # 12 ``` ### hours Return the hours difference between two given datetime. The result can be negative if `end` is before `start`. ```python dateutils.hours(start, end) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.hours("2019-6-3 20:01:12", "2020-5-3 13:13:13") # 8009 ``` ### minute Return the minutes of a given `datetime`. ```python dateutils.minute(datetime) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.minute("2025-5-3 13:14:15") # 14 ``` ### month Return the month of a given `date`. The month number starts at 1, like when writing a date. ```python dateutils.month(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.month("2025-5-4") # 5 ``` ### isomonth Return the ISO formatted (`YYYY-MM`) month of a given `date`. ```python dateutils.isomonth(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.isomonth("2025-1-2") # 2025-01 ``` ### months Return the months difference between two given dates. The result can be negative if `end` is before `start`. ```python dateutils.months(start, end) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.months("2024-5-1","2025-5-4") # 12 ``` ### now Return the ISO formatted current date and time, accurate to seconds. ```python dateutils.now() ``` __Example__ ```python from seatable_api.date_utils import dateutils now = dateutils.now() print(now) # 2025-09-30 12:56:41 ``` ### second Return the seconds of given datetime. ```python dateutils.second(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.second("2025-5-3 13:13:33") # 33 ``` ### today Return the ISO formatted current date as a string ```python dateutils.today() ``` __Example__ ```python from seatable_api.date_utils import dateutils today = dateutils.today() print(today) # 2025-09-30 ``` ### weekday Return the weekday of a `date`. The result (from 0 to 6) consider a week starting on Monday (returns 0) and ending on Sunday (returns 6). ```python dateutils.weekday(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.weekday("2025-6-2") # 0 (June 2, 2025 was a Monday) ``` ### isoweekday Return the weekday of a `date` from 1 to 7 and considering a week starting on Monday (based on ISO standard). ```python dateutils.isoweekday(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.isoweekday("2025-6-2") # 1 ``` ### weeknum Return the week number of a given `date`, considering the week including January 1st as the first week. ```python dateutils.weeknum(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.weeknum('2027-1-2') # 1 ``` ### isoweeknum Return the week number of a given `date` based on ISO standard. ```python dateutils.isoweeknum(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.isoweeknum('2027-1-2') # 53 ``` ### year Return the year of a given `date`. ```python dateutils.year(date) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.year("2030-1-1") # 2030 ``` ## Dealing with quarters A specific DateQuarter object exists to deal with quarters. The operations/properties/methods available for this object are presented below. ### quarter_from_yq Return a DateQuarter object, from a given `year` and `quarter` (1 to 4 for current year). if `quarter` is n less than 1 (or n greater than 4), the returned DateQuarter object will correspond to the year and quarter shifted by n quarters before the first quarter (or n quarters after the fourth quarter) of the `year`. ```python dateutils.quarter_from_yq(year, quarter) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.quarter_from_yq(2025, 3) # DateQuarter obj: dateutils.quarter_from_yq(2025, 0) # DateQuarter obj: dateutils.quarter_from_yq(2025, 6) # DateQuarter obj: ``` ### quarter_from_ym Return a DateQuarter object, for specified `year` and `month`. ```python dateutils.quarter_from_ym(year, month) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.quarter_from_ym(2025, 3) # DateQuarter obj: ``` ### to_quarter Return a DateQuarter object from an ISO formatted date or datetime string `datetime_str`. ```python dateutils.to_quarter(datetime_str) ``` __Example__ ```python from seatable_api.date_utils import dateutils dateutils.to_quarter("2025-07-17") # DateQuarter obj: ``` ### quarters_within Return a generator which will generate the DateQuater objects between a `start` date and an `end` date. The last (not necessarily full) quarter isn't included by default. You can get it in the generator if you set param `include_last` to `True` (`False` by default). ```python dateutils.quarters_within(start, end, include_last=False) ``` __Example__ ```python from seatable_api.date_utils import dateutils qs1 = dateutils.quarters_within("2024-03-28", "2025-07-17") print(list(qs1)) # [, ,...., ] qs2 = dateutils.quarters_within("2024-03-28", "2025-07-17", include_last=True) print(list(qs2)) # [, ,...., , ] ``` ### DateQuarter properties and methods Some operations/properties/methods are available for DateQuarter objects. - `year`: The year of the considered DateQuarter - `quarter`: The quarter of the considered DateQuarter (1 to 4) - `start_date`: The ISO formatted first day of the considered DateQuarter - `end_date`: The ISO formatted last day of the considered DateQuarter - `days()`: A generator, which will generate every dates (`datetime.date` objects) in the considered DateQuarter __Example__ ```python from seatable_api.date_utils import dateutils q = dateutils.quarter_from_yq(2025, 3) q.year # 2025 q.quarter # 3 q.start_date # 2025-07-01 q.end_date # 2025-09-30 q.days() list(q.days()) # [datetime.date(2025, 7, 1), datetime.date(2025, 7, 2),....., datetime.date(2025, 9, 30)] ``` ### DateQuarter operations Classical operators are available for DateQuarter objects: - **Arithmetic operators**: `+` (adds a number of quarters to a DateQuarter object), `-` (returns the number of quarters between two quarters, or the quarter shifted by the number of quarters if used with a number) - **Comparison operators**: `<`, `<=`, `==`, `>=`, `>`, `!=` - **Membership operators**: `in`, `not in` __Example__ ```python from seatable_api.date_utils import dateutils q = dateutils.quarter_from_yq(2026, 3) q + 10 # q1 = dateutils.quarter_from_yq(2025, 1) # q - q1 # 6 q - 7 # q < q1 # False "2026-28" in q # False "2026-8-28" in q # True ``` --- Source: https://developer.seatable.com/python/objects/communication-utils/ # Communication utility functions Several outgoing communications features are available within SeaTable. Whether you want to communicate with a user in the web interface or be alerted of database changes from another process, here are the methods you can use while scripting. All examples on this page assume that `base` has been initialized and authenticated as described on the [introduction](../index.md#authentication) page. Keep in mind that communication methods will probably require other coding skills as they mostly make sense outside of SeaTable. The [API Reference](https://api.seatable.com/reference/getbaseactivitylog-1) also details other methods such as getting base or row activities logs, which might also help you stay informed about what's happening in the base (but without the automatic firing on the SeaTable side of the methods presented here). ## Send email Send an email using a pre-configured email account in SeaTable. The email account must be set up in the system administration. ```python base.send_email(account_name, msg, **kwargs) ``` - `account_name`: name of the configured email account - `msg`: email message content __Example__ ```python base.send_email('my-email-account', 'Hello from SeaTable!') ``` ## Toast notifications Show a toast notification in SeaTable's web interface to a user. The username you have to provide is a unique identifier ending by `@auth.local`. This is **neither** the email address of the user **nor** its name. The content of `msg` is plain text. ```python base.send_toast_notification(username, msg, toast_type='success') # (1)! ``` 1. `toast_type` can be one of `success`, `warning` or `danger` __Example__ ```python base.send_toast_notification( "aea9e807bcfd4f3481d60294df74f6ee@auth.local", "error request", "danger" ) ``` ```python from seatable_api import context # Time to cheer up yourself! my_username = context.current_username base.send_toast_notification( my_username, "You're doing great!", "success" ) ``` ## Websockets By using websocket, you can get __realtime data update notifications__ of a base. !!! info "websocket-client library recommended" You might encounter the warning `websocket-client package not installed, only polling transport is available` when you run the script below. The library is not required as you'll get the update infos anyway (using polling transport), but installing the websocket-client library will allow you to benefit from a real websocket transport. ```python from seatable_api import Base server_url = 'https://cloud.seatable.io' api_token = 'c3c75dca2c369849455a39f4436147639cf02b2d' base = Base(api_token, server_url) base.auth(with_socket_io=True) # (1)! base.socketIO.wait() ``` 1. Note that using websocket needs to specify the argument `with_socket_io=True` as compared to usual authentication When the base data is updated, the following will be output in the terminal. ```log 2022-07-19 11:48:37.803956 [ SeaTable SocketIO connection established ] 2022-07-19 11:48:39.953150 [ SeaTable SocketIO on UPDATE_DTABLE ] {"op_type":"insert_row","table_id":"0000","row_id":"YFK9bD1XReSuQ7WP1YYjMA","row_insert_position":"insert_below","row_data":{"_id":"RngJuRa0SMGXyiA-SHDiAw","_participants":[],"_creator":"seatable@seatable.com","_ctime":"","_last_modifier":"seatable@seatable.com","_mtime":""},"links_data":{}} ``` After getting data update notifications, perform self-defined actions by listen to a specific event. Available events are `UPDATE_DTABLE` (database update) or `NEW_NOTIFICATION` (new notification received). Please note that we are here talking about SeaTable system's notifications (see the [User manual](https://seatable.com/help/homepage/notifications/) and not about the toast notifications fired by the `base.send_toast_notification` method). ```python import json from seatable_api import Base from seatable_api.constants import UPDATE_DTABLE # (1)! server_url = 'https://cloud.seatable.io' api_token = 'c3c75dca2c369849455a39f4436147639cf02b2d' def on_update(data, index, *args): try: operation = json.loads(data) print(operation) op_type = operation['op_type'] table_id = operation['table_id'] row_id = operation['row_id'] # ... do something except Exception as e: print(e) base = Base(api_token, server_url) base.auth(with_socket_io=True) base.socketIO.on(UPDATE_DTABLE, on_update) # (2)! base.socketIO.wait() ``` 1. Note that you'll have to import the corresponding event (`UPDATE_DTABLE` or `NEW_NOTIFICATION`) 2. First argument is the event triggering the system, second argument is the event handler (the name of the function that will be run when a new event happens) ## Webhooks Another communication feature offered by SeaTable is Webhooks. Webhooks are covered in the [User manual](https://seatable.com/help/integrations/webhooks/) for global understanding and in the [API Reference](https://api.seatable.com/reference/listwebhooks) for webhook handlings functions. As SeaTable usually sends a webhook for every change, this might not be fully adapted if you need to track only a few changes. If you want to track only few operations (to trigger a workflow automation process for example), you can create [automation rules](https://seatable.com/help/automations-overview-seatable/) to send, via a Python script, a `POST` request to an incoming webhook, passing, for example, a string to identify the action and the id of the triggering row. Automations are available only with an [Enterprise subscription](https://seatable.com/help/subscription-plans/#seatable-cloud-enterprise-search). __Example__ In this example, we can imagine setting up a simple automation rule triggered by a record update in a specific column that should trigger an automation process through a webhook. This automation will have a single "Run Python script" action launching the following script. ```python import requests from seatable_api import context url = 'https://mywebhookurl.com' data = {'action': 'transfer', '_id': context.current_row['_id']} # (1)! try: response = requests.post(url, json = data) if response.status_code != 200: print('Failed request, status code: ', response.status_code) exit(1) except Exception as e: print(e) exit(1) print(response.text) # (2)! ``` 1. You can actually pass whatever data you want in the `data` object. Here, there are two keys: - `action`: a string parameter allowing to switch processes depending on this parameter to allow one single entry point for several scenarios - `_id`: we pass the id of the triggering row to be able to use it on the webhook receiver side 2. Allows you to check if your request was successful or not --- ## JavaScript Source: https://developer.seatable.com/javascript/ # JavaScript SeaTable provides a JavaScript API that works in two contexts: inside SeaTable as a script, or externally via Node.js or a frontend application. Many methods exist in both contexts, but the two are **not** identical -- most notably, columns can only be created or modified from an external client. Every method that is limited to one context carries a marker on the respective page. ## Script vs. External Client | | Script in SeaTable | External client | |---|---|---| | Installation | None | `npm install seatable-api` | | Authentication | Not needed (user is already logged in) | API token required | | Execution | In the browser | Node.js or frontend app | | `await` required | Only for `query()` and `getLinkedRecords()` | For all calls | | Exclusive features | [Context, Output, Utilities, Filter/QuerySet](scripting-features.md) | [Constants](constants.md) | | Column management | Read only | Full (create, modify, delete) | ### Context markers Methods that are not available in both contexts are marked in the reference pages: | Marker | Meaning | |---|---| | :material-tag-outline:{ title='Scripting only' } | Available **only** in scripts inside SeaTable. Calling it from an external client returns `undefined`. | | :material-package-variant-closed:{ title='External client only' } | Available **only** in the external `seatable-api` client. Calling it in a SeaTable script returns `undefined`. | Methods without a marker work in both contexts. ## Installation ```shell npm install seatable-api ``` Not needed for scripts inside SeaTable. ## Authentication External programs need an API token for authentication. API tokens can be [generated in the SeaTable web interface](https://seatable.com/help/create-api-tokens/). Scripts inside SeaTable require no authentication. ```js import { Base } from "seatable-api"; const base = new Base({ server: "https://cloud.seatable.io", APIToken: "your-api-token", }); await base.auth(); ``` ## Async Operations External API calls are asynchronous and return promises. Use `await` to wait for the result. In scripting context, most methods are synchronous. The exceptions are `query()` and `getLinkedRecords()`, which also require `await`. ## API Limits JavaScript calls are subject to [rate](https://api.seatable.com/reference/limits#general-rate-limits) and [size](https://api.seatable.com/reference/limits#size-limits) limits. Use batch operations (`batchAppendRows`, `batchUpdateRows`, `batchDeleteRows`) whenever possible to reduce the number of API calls. Step-by-step JavaScript script examples are available in the [SeaTable User Manual](https://seatable.com/help/scripts/). --- Source: https://developer.seatable.com/javascript/tables/ # Tables ## Get Table(s) Get the currently selected table. Only available in SeaTable scripts. ```js base.getActiveTable(); ``` __Output__ Single table object __Example__ ```js const table = base.getActiveTable(); output.text(`The name of the active table is: ${table.name}`); ``` Get all tables of the current base. ```js base.getTables(); ``` __Output__ Array of table objects __Example__ ```js const tables = base.getTables(); ``` Get a table object by its name. ```js base.getTableByName(tableName); ``` __Output__ Single table object (`undefined` if table doesn't exist) __Example__ ```js const table = base.getTableByName('Table1'); ``` ## Add Table Add a new table to this base. Ensure the name doesn't already exist. ```js base.addTable(tableName, lang='en', columns=[]); ``` The `lang` and `columns` parameters are optional. __Example__ ```js base.addTable('New table'); ``` ## Rename Table Rename an existing table. ```js base.renameTable(oldName, newName); ``` __Example__ ```js base.renameTable('Table1', 'Projects 2023'); ``` ## Delete Table Delete a table from the base. The table can be [restored from the logs](https://seatable.com/help/eine-geloeschte-tabelle-wiederherstellen/). Deleting the last table is not possible. ```js base.deleteTable(tableName); ``` __Example__ ```js base.deleteTable('Old table'); ``` --- Source: https://developer.seatable.com/javascript/views/ # Views ## Get View(s) Get the current view of the active table. Only available in SeaTable scripts. ```js base.getActiveView(); ``` __Output__ Single view object __Example__ ```js const view = base.getActiveView(); output.text(view.name); ``` Get a view of a table, specified by its name. ```js base.getViewByName(table, viewName); ``` __Output__ Single view object (`undefined` if no view with that name exists) __Example__ ```js const view = base.getViewByName('Table1', 'Default View'); ``` Get all the views of a table. ```js base.listViews(table); ``` __Output__ Array of view objects __Example__ ```js const views = base.listViews('Table1'); ``` ## Add View Add a new view to a table. ```js base.addView(table, viewName); ``` __Example__ ```js base.addView('Table1', 'My View'); ``` ## Rename View Rename an existing view. ```js base.renameView(table, currentViewName, newViewName); ``` __Example__ ```js base.renameView('Table1', 'Default View', 'Main View'); ``` ## Delete View Delete a view. Deleting the last view is not possible. ```js base.deleteView(table, viewName); ``` __Example__ ```js base.deleteView('Table1', 'Old View'); ``` --- Source: https://developer.seatable.com/javascript/columns/ # Columns Reading columns works in both contexts, but every write method on this page -- *Add Column*, *Rename Column*, *Column Settings* and *Delete Column* -- is only available in the external `seatable-api` client. In a JavaScript script inside a base, `base.insertColumn`, `base.deleteColumn` and the others are `undefined`. To create or modify columns from within SeaTable, use a [Python script](../python/objects/columns.md) instead: the Python library supports the full range of column operations in both contexts, including link columns. Alternatively, call the [REST API](https://api.seatable.com/reference/insertcolumn-1) directly. ## Get Column(s) Get the column object of a table, specified by the column name. ```js base.getColumnByName(table, columnName); ``` __Output__ Single column object (`undefined` if column doesn't exist) __Example__ ```js const column = base.getColumnByName('Table1', 'Name'); ``` Get all columns of a table. ```js base.getColumns(table); ``` __Output__ Array of column objects __Example__ ```js const columns = base.getColumns('Table1'); columns.forEach((column) => { console.log(column.name); }); ``` Get the columns of a table, optionally filtered by view. If no view is specified, all columns are returned. ```js base.listColumns(tableName, viewName); ``` __Output__ Array of column objects __Example__ ```js const columns = base.listColumns('Table1', 'Default View'); ``` Get all visible columns of a table in a specific view (hidden columns are excluded). Only available in SeaTable scripts. ```js base.getShownColumns(table, view); ``` __Output__ Array of column objects __Example__ ```js const columns = base.getShownColumns('Table1', 'Default View'); ``` Get all columns of a specific type in a table. See the [API Reference](https://api.seatable.com/reference/models#supported-column-types) for supported column types. ```js base.getColumnsByType(table, type); ``` __Output__ Array of column objects (empty array if no match) __Example__ ```js const textColumns = base.getColumnsByType('Table1', 'text'); ``` ## Add Column Add a new column to a table. ```js base.insertColumn(tableName, columnName, columnType, columnKey='', columnData=''); ``` __Example__ ```js import { ColumnTypes } from 'seatable-api'; await base.insertColumn('Table1', 'Notes', ColumnTypes.TEXT); // Insert after a specific column await base.insertColumn('Table1', 'Notes', ColumnTypes.TEXT, '0000'); // Create a link column await base.insertColumn('Table1', 'Link1', ColumnTypes.LINK, '', { 'table': 'Table1', 'other_table': 'Table2' }); ``` ## Rename Column Rename a column, identified by its column key. ```js base.renameColumn(tableName, columnKey, newColumnName); ``` __Example__ ```js await base.renameColumn('Table1', 'kSiR', 'New Name'); ``` ## Column Settings ```js base.resizeColumn(tableName, columnKey, newColumnWidth); ``` __Example__ ```js await base.resizeColumn('Table1', 'asFV', 500); ``` ```js base.freezeColumn(tableName, columnKey, frozen); ``` __Example__ ```js await base.freezeColumn('Table1', '0000', true); ``` Move a column to the right of the target column. ```js base.moveColumn(tableName, columnKey, targetColumnKey); ``` __Example__ ```js await base.moveColumn('Table1', 'loPx', '0000'); ``` Change the type of an existing column. ```js base.modifyColumnType(tableName, columnKey, newColumnType); ``` __Example__ ```js import { ColumnTypes } from 'seatable-api'; await base.modifyColumnType('Table1', 'nePI', ColumnTypes.NUMBER); ``` Add options to a single-select or multiple-select column. ```js base.addColumnOptions(tableName, columnName, options); ``` __Example__ ```js await base.addColumnOptions('Table1', 'Status', [ {"name": "Done", "color": "#73d56e", "textColor": "#000000"}, {"name": "Open", "color": "#ff8000", "textColor": "#ffffff"}, ]); ``` Add cascade settings to a single-select column, limiting child options based on the parent column's selection. ```js base.addColumnCascadeSettings(tableName, childColumn, parentColumn, cascadeSettings); ``` __Example__ ```js await base.addColumnCascadeSettings('Table1', 'Sub-Category', 'Category', { "Electronics": ["Phones", "Laptops"], "Furniture": ["Chairs", "Tables"] }); ``` ## Delete Column Delete a column, identified by its column key. ```js base.deleteColumn(tableName, columnKey); ``` __Example__ ```js await base.deleteColumn('Table1', 'bsKL'); ``` --- Source: https://developer.seatable.com/javascript/rows/ # Rows ## Get Row(s) Get a single row by its ID. ```js base.getRow(table, rowId); ``` __Output__ Single row object __Example__ ```js const row = base.getRow('Table1', 'M_lSEOYYTeuKTaHCEOL7nw'); ``` Get all rows displayed in a view. ```js base.getRows(table, view); ``` __Output__ Array of row objects __Example__ ```js const rows = base.getRows('Table1', 'Default View'); ``` Get rows with optional sorting and pagination. Particularly useful for large tables. ```js base.listRows(tableName, viewName='', orderBy='', desc='', start='', limit=''); ``` __Output__ Array of row objects __Example__ ```js // Simple const rows = await base.listRows('Table1'); // With pagination and sorting const rows = await base.listRows('Table1', 'Default View', 'Name', true, 0, 100); ``` Get rows grouped according to the view's grouping settings. Only available in SeaTable scripts. ```js base.getGroupedRows(table, view); ``` __Output__ Array of group objects, each containing a `rows` array __Example__ ```js const table = base.getTableByName('Table1'); const view = base.getViewByName(table, 'Grouped View'); const groups = base.getGroupedRows(table, view); ``` Use SQL to query a base. Most SQL syntax is supported -- see the [SQL Reference](../sql/index.md) for details. ```js await base.query(sql); ``` !!! info "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__ ```js const data = await base.query('SELECT name, price FROM Bill WHERE year = 2021'); ``` ```js // Aggregation const data = await base.query('SELECT name, SUM(price) FROM Bill GROUP BY name'); ``` Filter rows using a filter expression. Returns a QuerySet with chainable methods. Only available in SeaTable scripts. ```js base.filter(tableName, viewName, filterExpression); ``` __Output__ QuerySet object __Example__ ```js // 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) Append a new row to the end of a table. ```js base.appendRow(tableName, rowData, applyDefault=false); ``` Set `applyDefault` to `true` to use default column values for unspecified columns. __Example__ ```js base.appendRow('Table1', { 'Name': 'New entry', 'Status': 'Open' }); ``` Insert a row after a specific anchor row. ```js base.insertRow(tableName, rowData, anchorRowId, applyDefault=false); ``` __Example__ ```js await base.insertRow('Table1', {'Name': 'Inserted row'}, 'U_eTV7mDSmSd-K2P535Wzw'); ``` Append multiple rows at once. More efficient than calling `appendRow` in a loop. ```js base.batchAppendRows(tableName, rowsData, applyDefault=false); ``` __Example__ ```js await base.batchAppendRows('Table1', [ {'Name': 'Row 1', 'Status': 'Open'}, {'Name': 'Row 2', 'Status': 'Done'}, {'Name': 'Row 3', 'Status': 'Open'} ]); ``` ## Update Row(s) Update a single row identified by its row ID. ```js base.updateRow(tableName, rowId, rowData); ``` In scripting context, you can also pass a row object instead of a row ID. __Example__ ```js base.updateRow('Table1', 'U_eTV7mDSmSd-K2P535Wzw', { 'Status': 'Done' }); ``` Update multiple rows at once in scripting context. Pass two arrays: the rows to update and the corresponding update data. ```js base.modifyRows(table, rows, updatedRows); ``` __Example__ ```js 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); ``` Update multiple rows at once. Each entry specifies a row ID and the data to update. ```js base.batchUpdateRows(tableName, rowsData); ``` __Example__ ```js await base.batchUpdateRows('Table1', [ {"row_id": "fMmCFyoxT4GN5Y2Powbl0Q", "row": {"Name": "Updated 1"}}, {"row_id": "cF5JTE99Tae-VVx0BGT-3A", "row": {"Name": "Updated 2"}} ]); ``` ## Delete Row(s) Delete a single row by its ID. ```js base.deleteRow(tableName, rowId); ``` __Example__ ```js base.deleteRow('Table1', 'U_eTV7mDSmSd-K2P535Wzw'); ``` Delete multiple rows at once. ```js base.batchDeleteRows(tableName, rowIds); ``` __Example__ ```js await base.batchDeleteRows('Table1', [ 'fMmCFyoxT4GN5Y2Powbl0Q', 'cF5JTE99Tae-VVx0BGT-3A' ]); ``` --- Source: https://developer.seatable.com/javascript/links/ # Links Link columns connect rows between tables. Most link operations require the `link_id`, which you can retrieve with `getColumnLinkId`. ## Get Link ID Get the link ID of a link column. You need this ID for all other link operations. ```js base.getColumnLinkId(tableName, columnName); ``` __Output__ Link ID string (e.g. `'aHL2'`) __Example__ ```js const linkId = base.getColumnLinkId('Table1', 'Related Records'); ``` ## Get Linked Records Get the linked records of one or more rows. Supports pagination per row. ```js await base.getLinkedRecords(tableId, linkColumnKey, rows); ``` __Output__ Object with row IDs as keys and arrays of linked record info as values. __Example__ ```js const linked = await base.getLinkedRecords('0000', '89o4', [ {'row_id': 'FzNqJxVUT8KrRjewBkPp8Q', 'limit': 10, 'offset': 0}, {'row_id': 'Jmnrkn6TQdyRg1KmOM4zZg', 'limit': 20} ]); // Result: // { // 'FzNqJxVUT8KrRjewBkPp8Q': [ // {'row_id': 'LocPgVvsRm6bmnzjFDP9bA', 'display_value': '1'}, // ... // ], // 'Jmnrkn6TQdyRg1KmOM4zZg': [...] // } ``` ## Add Link Create a link between two rows in different tables. ```js base.addLink(linkId, tableName, otherTableName, rowId, otherRowId); ``` __Example__ ```js base.addLink('5WeC', 'Projects', 'Contacts', 'CGtoJB1oQM60RiKT-c5J-g', 'PALm2wPKTCy-jdJNv_UWaQ'); ``` ## Update Link(s) Replace all linked records of a row with a new set. ```js base.updateLink(linkId, tableName, otherTableName, rowId, otherRowIds); ``` !!! warning "Different name in scripts" In a SeaTable script, this method is called `updateLinks` (plural) -- see below. `base.updateLink` is `undefined` in the script context. __Example__ ```js base.updateLink('r4IJ', 'Table1', 'Table2', 'BXhEm9ucTNu3FjupIk7Xug', [ 'exkb56fAT66j8R0w6wD9Qg', 'DjHjwmlRRB6WgU9uPnrWeA' ]); ``` Replace all linked records of a row with a new set. This is the script equivalent of `updateLink`; the parameters are identical. ```js base.updateLinks(linkId, tableName, otherTableName, rowId, otherRowIds); ``` __Example__ ```js base.updateLinks('r4IJ', 'Table1', 'Table2', 'BXhEm9ucTNu3FjupIk7Xug', [ 'exkb56fAT66j8R0w6wD9Qg', 'DjHjwmlRRB6WgU9uPnrWeA' ]); ``` Update links for multiple rows at once. ```js base.batchUpdateLinks(linkId, tableName, otherTableName, rowIdList, otherRowsIdsMap); ``` __Example__ ```js await base.batchUpdateLinks('WaW5', 'Table1', 'Table2', ['fRLglslWQYSGmkU7o6KyHw', 'eSQe9OpPQxih8A9zPXdMVA'], { 'fRLglslWQYSGmkU7o6KyHw': ['MdfUQiWcTL--uMlrGtqqgw', 'E7Sh3FboSPmfBlDsrj_Fhg'], 'eSQe9OpPQxih8A9zPXdMVA': ['cWHbzQiTR8uHHzH_gVSKIg', 'X56gE7BrRF-i61YlE4oTcw'] } ); ``` ## Remove Link Remove a link between two rows. ```js base.removeLink(linkId, tableName, otherTableName, rowId, otherRowId); ``` __Example__ ```js base.removeLink('5WeC', 'Projects', 'Contacts', 'CGtoJB1oQM60RiKT-c5J-g', 'PALm2wPKTCy-jdJNv_UWaQ'); ``` --- Source: https://developer.seatable.com/javascript/sql/ # SQL Queries Use SQL to query a base. This is the most powerful way to access data. For the full SQL syntax reference, see the [SQL Reference](../sql/index.md). ```js await base.query(sql); ``` !!! info "Backticks for special names" Escape table or column names that contain spaces, special characters, or are [SQL function names](../sql/functions.md) with backticks: `` SELECT * FROM `My Table` `` __Output__ Array of row objects __Example: SELECT all__ ```js const data = await base.query('SELECT * FROM Bill'); ``` __Example: WHERE__ ```js const data = await base.query('SELECT name, price FROM Bill WHERE year = 2021'); ``` __Example: ORDER BY__ ```js const data = await base.query('SELECT name, price, year FROM Bill ORDER BY year'); ``` __Example: GROUP BY__ ```js const data = await base.query('SELECT name, SUM(price) FROM Bill GROUP BY name'); // Returns: [{'SUM(price)': 600, 'name': 'Bob'}, ...] ``` __Example: DISTINCT__ ```js const data = await base.query('SELECT DISTINCT name FROM Bill'); ``` --- Source: https://developer.seatable.com/javascript/metadata/ # Metadata Get the complete structure of a base -- tables, views, and columns. Does not include row data. ```js base.getMetadata(); ``` __Example output__ ```json { "tables": [{ "_id": "4krH", "name": "Contact", "is_header_locked": false, "columns": [{ "key": "0000", "type": "text", "name": "Name", "editable": true, "width": 200 }], "views": [{ "_id": "0000", "name": "Default view", "type": "table", "is_locked": false, "filters": [], "sorts": [], "groupbys": [], "hidden_columns": [] }] }] } ``` --- Source: https://developer.seatable.com/javascript/files/ # Files The `seatable-api` npm package does not currently support file or image uploads. To upload files, you need to use the SeaTable REST API directly via `fetch()`. The example on this page runs in Node.js: it reads from the local filesystem and authenticates with an API token. Neither is available in the SeaTable script editor. ## Upload workflow Uploading a file to SeaTable requires three steps: 1. **Get an upload link** from SeaTable 2. **Upload the file** to that link 3. **Attach the file** to a row by updating the file/image column ## Complete example: Upload an image This Node.js script uploads an image from the local filesystem and attaches it to an image column in a new row. No external dependencies required. ### Prerequisites - A valid API token ([how to generate one](https://seatable.com/help/create-api-tokens/)) - Node.js installed on your machine ### Code ```js import { readFileSync } from 'fs'; import { basename } from 'path'; const SERVER_URL = 'https://cloud.seatable.io'; const API_TOKEN = ''; const TABLE_NAME = 'Table1'; const IMAGE_COLUMN_NAME = 'Images'; // Absolute path to the file on the local filesystem const FILE_PATH = 'Test.svg'; const FILE_NAME = basename(FILE_PATH); /** * Step 1: Get upload link * Docs: https://api.seatable.com/reference/getuploadlink */ let url = `${SERVER_URL}/api/v2.1/dtable/app-upload-link/`; let response = await fetch(url, { method: "GET", headers: { Authorization: `Token ${API_TOKEN}` }, }); const uploadLink = await response.json(); /** * Step 2: Upload file * Docs: https://api.seatable.com/reference/uploadfile */ const file = readFileSync(FILE_PATH); const formData = new FormData(); formData.append("parent_dir", uploadLink.parent_path); formData.append("file", new Blob([file.buffer]), FILE_NAME); formData.append('relative_path', uploadLink.img_relative_path); response = await fetch(uploadLink.upload_link + "?ret-json=1", { method: "POST", body: formData, }); const files = await response.json(); /** * Step 3: Attach file to a row * Docs: https://api.seatable.com/reference/appendrows */ url = `${SERVER_URL}/api/v2.1/dtable/app-access-token/`; response = await fetch(url, { headers: { Authorization: `Token ${API_TOKEN}` } }); const baseToken = await response.json(); const workspaceId = baseToken.workspace_id; const baseUuid = baseToken.dtable_uuid; const relativeImageURL = `/workspace/${workspaceId}${uploadLink.parent_path}/${uploadLink.img_relative_path}/${files[0].name}`; const body = { table_name: TABLE_NAME, rows: [ { [IMAGE_COLUMN_NAME]: [relativeImageURL], }, ], }; url = `${SERVER_URL}/api-gateway/api/v2/dtables/${baseUuid}/rows/`; response = await fetch(url, { method: 'POST', headers: { accept: 'application/json', authorization: `Bearer ${baseToken.access_token}`, 'Content-Type': 'application/json', }, body: JSON.stringify(body), }); console.log(await response.json()); ``` ### Run ```bash node upload-file.js ``` ## Further reading The complete file and image API is documented at [api.seatable.com](https://api.seatable.com/reference/uploadfile). --- Source: https://developer.seatable.com/javascript/constants/ # Constants When creating or modifying columns, use the `ColumnTypes` constants for type-safe column type references. ```js import { ColumnTypes } from 'seatable-api'; ``` `ColumnTypes` comes from the `seatable-api` npm package. There is no `import` in the SeaTable script editor, so these constants are not available there. Since columns cannot be created or modified from a script anyway, this is not a limitation in practice -- see [Columns](columns.md). ## ColumnTypes | Constant | Column type | |---|---| | `ColumnTypes.TEXT` | Text | | `ColumnTypes.LONG_TEXT` | Long text | | `ColumnTypes.NUMBER` | Number | | `ColumnTypes.CHECKBOX` | Checkbox | | `ColumnTypes.DATE` | Date & time | | `ColumnTypes.SINGLE_SELECT` | Single select | | `ColumnTypes.MULTIPLE_SELECT` | Multiple select | | `ColumnTypes.IMAGE` | Image | | `ColumnTypes.FILE` | File | | `ColumnTypes.COLLABORATOR` | Collaborator | | `ColumnTypes.LINK` | Link to other records | | `ColumnTypes.FORMULA` | Formula | | `ColumnTypes.CREATOR` | Creator | | `ColumnTypes.CTIME` | Created time | | `ColumnTypes.LAST_MODIFIER` | Last modifier | | `ColumnTypes.MTIME` | Modified time | | `ColumnTypes.GEOLOCATION` | Geolocation | | `ColumnTypes.AUTO_NUMBER` | Auto number | | `ColumnTypes.URL` | URL | --- Source: https://developer.seatable.com/javascript/scripting-features/ # Scripting Features These features are only available when running JavaScript scripts inside SeaTable. They provide access to the browser context, output functions, and utility helpers. ## Context The `base.context` object provides information about the current user interaction. The currently selected table name. ```js const tableName = base.context.currentTable; ``` The current row when the script is triggered via a button. Contains the full row data. ```js const row = base.context.currentRow; output.text(row['Name']); ``` !!! warning `currentRow` is only available when the script is executed via a [button column](https://seatable.com/help/skript-manuell-per-schaltflaeche-oder-automation-ausfuehren/). When running manually, `currentRow` is `undefined`. ## Output The `output` object displays results in the script output panel. Display text or any variable in the output panel. Accepts strings, numbers, objects, and arrays. ```js output.text(anything); ``` __Example__ ```js output.text('Hello World'); output.text(42); output.text(row); ``` Display markdown-formatted content in the output panel. ```js output.markdown(markdownString); ``` __Example__ ```js output.markdown('# Title\n\nSome **bold** text.'); ``` ## Utilities The `base.utils` object provides helper functions. Format a date object to `YYYY-MM-DD`. ```js base.utils.formatDate(date); ``` __Example__ ```js const today = base.utils.formatDate(new Date()); // Returns: "2026-03-18" ``` Format a date object to `YYYY-MM-DD HH:mm`. ```js base.utils.formatDateWithMinutes(date); ``` __Example__ ```js const now = base.utils.formatDateWithMinutes(new Date()); // Returns: "2026-03-18 14:30" ``` Look up a value in another table and copy it. Similar to VLOOKUP in spreadsheets. ```js base.utils.lookupAndCopy(targetTable, targetColumn, targetColumnToSearch, sourceTable, sourceColumn, sourceColumnToSearch); ``` __Example__ ```js // Copy "Email" from Contacts table where the Name matches base.utils.lookupAndCopy( 'Orders', 'Customer Email', 'Customer Name', 'Contacts', 'Email', 'Name' ); ``` --- ## PHP Source: https://developer.seatable.com/php/ # PHP The SeaTable PHP Client encapsulates the SeaTable REST API. It is auto-generated from the public [OpenAPI specification](https://api.seatable.com), which ensures all API endpoints are covered automatically. - Source code on [GitHub](https://github.com/seatable/seatable-api-php) - Package on [Packagist](https://packagist.org/packages/seatable/seatable-api-php) - [Interactive API Reference](https://api.seatable.com/reference/introduction) with code examples ## Installation ```bash composer require seatable/seatable-api-php ``` ## Authentication Most operations on base data require a **Base Token**. You obtain it by exchanging an **API Token**, which can be [generated in the SeaTable web interface](https://seatable.com/help/create-api-tokens/): ```php setAccessToken('YOUR_API_TOKEN'); $apiInstance = new SeaTable\Client\Auth\BaseTokenApi( new GuzzleHttp\Client(), $config ); $result = $apiInstance->getBaseTokenWithApiToken(); $baseToken = $result['access_token']; $baseUuid = $result['dtable_uuid']; ``` ??? question "Account-level operations" For operations like listing bases or getting user info, authenticate with an **Account Token** instead of an API Token. An Account Token identifies a user and gives access to all their bases. See the [API docs](https://api.seatable.com/reference/getaccounttokenfromusername) for how to obtain one. ??? question "Connecting to a self-hosted server" By default, the client connects to SeaTable Cloud. For self-hosted installations, set the host: ```php $config = SeaTable\Client\Configuration::getDefaultConfiguration(); $config->setAccessToken('YOUR_TOKEN'); $config->setHost('https://seatable.example.com'); ``` --- Source: https://developer.seatable.com/php/examples/ # Examples ## Get account information Connect to SeaTable Cloud and retrieve your account details. ```php setAccessToken('YOUR_ACCOUNT_TOKEN'); $apiInstance = new SeaTable\Client\User\UserApi( new GuzzleHttp\Client(), $config ); try { $result = $apiInstance->getAccountInfo(); print_r($result); } catch (Exception $e) { echo 'Exception: ', $e->getMessage(), PHP_EOL; } ``` ## List your bases ```php setAccessToken('YOUR_ACCOUNT_TOKEN'); $apiInstance = new SeaTable\Client\User\BasesApi( new GuzzleHttp\Client(), $config ); try { $result = $apiInstance->listBases(); print_r($result); } catch (Exception $e) { echo 'Exception: ', $e->getMessage(), PHP_EOL; } ``` ## Get base metadata First obtain a Base Token from your API Token, then retrieve the metadata. ```php setAccessToken('YOUR_API_TOKEN'); $apiInstance = new SeaTable\Client\Auth\BaseTokenApi( new GuzzleHttp\Client(), $config ); $result = $apiInstance->getBaseTokenWithApiToken(); // Step 2: Get Metadata $config = SeaTable\Client\Configuration::getDefaultConfiguration() ->setAccessToken($result['access_token']); $apiInstance = new SeaTable\Client\Base\BaseInfoApi( new GuzzleHttp\Client(), $config ); try { $result = $apiInstance->getMetadata($result['dtable_uuid']); print_r($result); } catch (Exception $e) { echo 'Exception: ', $e->getMessage(), PHP_EOL; } ``` ## Execute an SQL query ```php setAccessToken('YOUR_API_TOKEN'); $authApi = new SeaTable\Client\Auth\BaseTokenApi( new GuzzleHttp\Client(), $config ); $auth = $authApi->getBaseTokenWithApiToken(); // Step 2: Query $config = SeaTable\Client\Configuration::getDefaultConfiguration() ->setAccessToken($auth['access_token']); $apiInstance = new SeaTable\Client\Base\RowsApi( new GuzzleHttp\Client(), $config ); $sqlQuery = new SeaTable\Client\Base\SqlQuery([ "sql" => "SELECT * FROM Table1", "convert_keys" => false ]); try { $result = $apiInstance->querySQL($auth['dtable_uuid'], $sqlQuery); print_r($result); } catch (Exception $e) { echo 'Exception: ', $e->getMessage(), PHP_EOL; } ``` ## Add a row ```php setAccessToken('YOUR_API_TOKEN'); $authApi = new SeaTable\Client\Auth\BaseTokenApi( new GuzzleHttp\Client(), $config ); $auth = $authApi->getBaseTokenWithApiToken(); // Step 2: Append row $config = SeaTable\Client\Configuration::getDefaultConfiguration() ->setAccessToken($auth['access_token']); $apiInstance = new SeaTable\Client\Base\RowsApi( new GuzzleHttp\Client(), $config ); $request = new SeaTable\Client\Base\AppendRows([ 'table_name' => 'Table1', 'rows' => [ ['Name' => 'Inserted via API'], ], 'apply_default' => false, ]); try { $result = $apiInstance->appendRows($auth['dtable_uuid'], $request); print_r($result); } catch (Exception $e) { echo 'Exception: ', $e->getMessage(), PHP_EOL; } ``` --- Source: https://developer.seatable.com/php/api-reference/ # API Reference The PHP client is auto-generated from the SeaTable OpenAPI specification. Detailed documentation for all endpoints, including parameters and response types, is available on GitHub: ## Endpoint categories | Category | Description | Documentation | |---|---|---| | **Auth** | Obtain base tokens and account tokens | [Auth API](https://github.com/seatable/seatable-api-php/blob/main/README_Auth.md) | | **Base** | Rows, columns, views, links, metadata, SQL queries | [Base API](https://github.com/seatable/seatable-api-php/blob/main/README_Base.md) | | **File** | Upload and download files and images | [File API](https://github.com/seatable/seatable-api-php/blob/main/README_File.md) | | **User** | Account info, bases, shared views, API tokens | [User API](https://github.com/seatable/seatable-api-php/blob/main/README_User.md) | | **Team Admin** | Team management, members, sharing | [TeamAdmin API](https://github.com/seatable/seatable-api-php/blob/main/README_TeamAdmin.md) | | **Sys Admin** | Server administration (self-hosted only) | [SysAdmin API](https://github.com/seatable/seatable-api-php/blob/main/README_SysAdmin.md) | --- ## Ruby Source: https://developer.seatable.com/ruby/ # Ruby client One of our community members [made a first version](https://forum.seatable.com/t/seatable-ruby-ruby-gem-for-seatable/2366) of a SeaTable Ruby client. The source code of the Ruby client API and additional explanations are available at [GitHub](https://github.com/viktorMarkevich/seatable_ruby). --- ## SQL Source: https://developer.seatable.com/sql/ # SQL SeaTable provides an SQL interface for querying and modifying data. It supports `SELECT`, `INSERT`, `UPDATE`, and `DELETE` statements. SQL can be used from any programming language — through the [Python](../python/index.md) and [JavaScript](../javascript/index.md) client libraries via `base.query()`, or directly through the [REST API](https://api.seatable.com/reference/querysql). === "Python" ```python results = base.query("SELECT * FROM Table1 LIMIT 100") ``` === "JavaScript" ```js const results = await base.query("SELECT * FROM Table1 LIMIT 100"); ``` === "API" ```bash curl -X POST \ 'https://cloud.seatable.io/api-gateway/api/v2/dtables/{base_uuid}/sql/' \ -H 'Authorization: Bearer {base_token}' \ -H 'Content-Type: application/json' \ -d '{"sql": "SELECT * FROM Table1 LIMIT 100", "convert_keys": true}' ``` All three methods use the same SQL engine and return identical results. SQL syntax is case insensitive — keywords, function names, and identifiers can be written in any case. We use upper-case for SQL keywords (`SELECT`, `WHERE`, ...) and lower-case for function names (`now()`, `round()`, ...) for readability. Try the [SQL query plugin](https://seatable.com/help/anleitung-zum-sql-abfrage-plugin/) in SeaTable to experiment with queries interactively. ## Quick reference ### Supported | Feature | Notes | |:---|:---| | `SELECT`, `UPDATE`, `DELETE` | | | `INSERT` | [Requires Big Data storage](insert.md) | | `WHERE` with `=`, `!=`, `<>`, `>`, `<`, `>=`, `<=` | | | `LIKE`, `ILIKE`, `IN`, `NOT IN`, `BETWEEN`, `IS [NOT] NULL` | | | `AND`, `OR`, `NOT` | | | `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT`, `OFFSET` | | | `DISTINCT`, `AS` aliases | | | `COUNT`, `SUM`, `MIN`, `MAX`, `AVG` | Standard SQL aggregates | | Implicit joins: `FROM T1, T2 WHERE T1.col = T2.col` | Inner join only | | Arithmetic operators `+`, `-`, `*`, `/` in `SELECT` | | | [SeaTable functions](functions.md) in `SELECT`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY` | | | [Extended list operators](select.md#extended-list-operators): `HAS ANY OF`, `HAS ALL OF`, etc. | For multi-select and collaborator columns | ### Not supported | Feature | Alternative | |:---|:---| | `JOIN` keyword (`INNER JOIN`, `LEFT JOIN`, etc.) | Use implicit joins | | Subqueries | Split into multiple queries | | `UNION` / `UNION ALL` | Split into multiple queries | | `CASE WHEN ... THEN ... END` | Use SeaTable `if()` or `ifs()` function | | MySQL functions (`SUBSTR`, `CONCAT`, `LENGTH`, etc.) | Use [SeaTable equivalents](functions.md): `mid()`, `concatenate()`, `len()` | | Functions or expressions in `UPDATE SET` | Read with `SELECT`, compute, write with API | | Functions in `INSERT VALUES` | Use API `appendRow` instead | ## Formulas in SQL queries You can use SeaTable formula syntax directly in SQL queries. A few differences from SeaTable's built-in formulas: - Link formulas (e.g. `{link.age}`) are **not** supported - Column references are **not** enclosed in curly brackets: use `abs(column)`, not `abs({column})` - Use backticks for column names with spaces or hyphens: `` abs(`column-a`) `` - Column aliases cannot be used in formulas: `abs(t.column)` is invalid For the complete list of available functions, see the [function reference](./functions.md). --- Source: https://developer.seatable.com/sql/select/ # SELECT The `SELECT` statement retrieves an optionally filtered, sorted, and grouped list of rows from a table. Each returned row is a JSON object. `SELECT` works the same way regardless of whether the table uses normal or Big Data storage. ## Syntax ```sql SELECT [Column List] FROM tableName [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT ... OFFSET ...] ``` `[Column List]` is a comma-separated list of columns. Use `*` to retrieve all columns. ## Limits Unless you specify a higher limit, the method returns a maximum of **100 rows**. The absolute maximum is **10,000 rows**. __Example__ ```sql SELECT * FROM Table1 LIMIT 10000 ``` Returns the first 10,000 rows. ```sql SELECT * FROM Table1 LIMIT 10000 OFFSET 10000 ``` Returns the next 10,000 rows. ## Column keys vs. column names By default, returned rows use column **names** as keys (when using `base.query` in Python or JavaScript). The raw API returns column **keys**. This can be controlled with the `convert_keys` parameter. ## DISTINCT Use `DISTINCT` to return only unique values: ```sql SELECT DISTINCT city FROM Contacts ``` ## Field aliases Field aliases with `AS` are supported: ```sql SELECT table.amount AS a, COUNT(*) FROM Invoices AS i GROUP BY a HAVING a > 100 ``` - Aliases **can** be used in `GROUP BY`, `HAVING`, and `ORDER BY` - Aliases **cannot** be used in `WHERE` ## WHERE Escape table or column names that contain spaces, special characters, or match [SQL function](./functions.md) names with backticks: `` SELECT * FROM `My Table` ``. Most SQL syntax can be used in the `WHERE` clause: arithmetic expressions, comparison operators, `[NOT] LIKE`, `IN`, `BETWEEN ... AND ...`, `AND`, `OR`, `NOT`, `IS [NOT] TRUE`, `IS [NOT] NULL`. - Arithmetic expressions only support numbers - Time constants must be strings in ISO format (e.g. `"2020-09-08 00:11:23"`). Since version 2.8, RFC 3339 format is also supported (e.g. `"2020-12-31T23:59:60Z"`) ### LIKE `LIKE` only supports strings. Use `ILIKE` for case-insensitive matching. The percent sign `%` represents zero, one, or multiple characters. __Example__ ```sql SELECT `Full Name` FROM Contacts WHERE `Full Name` LIKE "% M%" ``` Returns every record with a last name starting with M. ### BETWEEN `BETWEEN lowerLimit AND upperLimit` supports numbers and time. Both limits are included. They must be in the correct order. __Example__ ```sql SELECT * FROM Contacts WHERE Age BETWEEN 18 AND 25 ``` ### Extended list operators SeaTable supports special operators for list-type columns (multiple select, collaborator, etc.): | Operator | Description | |---|---| | `HAS ANY OF` | Row contains at least one of the values | | `HAS ALL OF` | Row contains all of the values | | `HAS NONE OF` | Row contains none of the values | | `IS EXACTLY` | Row contains exactly these values (order-independent) | Values are enclosed in parentheses, like the `IN` operator. __Example__ ```sql SELECT * FROM table WHERE city HAS ANY OF ("New York", "Paris") ``` ## GROUP BY `GROUP BY` uses strict syntax. Selected fields must appear in the GROUP BY list, except for aggregation functions (`COUNT`, `SUM`, `MAX`, `MIN`, `AVG`) and formulas. ## HAVING `HAVING` filters rows resulting from `GROUP BY`. Only fields in the GROUP BY list or aggregation functions can be used. Other syntax is the same as WHERE. ## ORDER BY Fields in the `ORDER BY` list can be columns, expressions, or functions. __Example__ ```sql SELECT name, age FROM table ORDER BY age DESC SELECT name, abs(score) FROM table ORDER BY abs(score) ``` ## Aggregation functions When using `GROUP BY`, these aggregation functions are available: | Function | Description | Example | |---|---|---| | `COUNT(*)` | Number of rows | `COUNT(*)` | | `SUM(col)` | Sum of values | `SUM(Amount)` | | `MAX(col)` | Maximum value | `MAX(Amount)` | | `MIN(col)` | Minimum value | `MIN(Amount)` | | `AVG(col)` | Average of non-empty values | `AVG(Amount)` | __Example__ ```sql SELECT Customer, SUM(Amount) FROM Invoices GROUP BY Customer ``` ## JOIN Since version 4.3, basic implicit join queries are supported: ```sql SELECT ... FROM Table1, Table2 WHERE Table1.column1 = Table2.column2 AND ... ``` Restrictions: - Do **not** use the `JOIN` keyword explicitly - Only inner join is supported (no left, right, or full join) - Tables in the `FROM` clause must be unique - Each table must have at least one join condition - Join conditions use equality only: `Table1.column1 = Table2.column2` - Join conditions must be placed in the `WHERE` clause, connected with `AND` - Columns in join conditions must be indexed (unless the table is not archived) --- Source: https://developer.seatable.com/sql/insert/ # INSERT Appends a new row to a table via SQL. `INSERT` **only** works with bases that have [Big Data storage](https://seatable.com/help/big-data-capabilities/) enabled. Rows are inserted into big data storage. This feature requires an [Enterprise subscription](https://seatable.com/help/subscription-plans/#seatable-cloud-enterprise-search). For non-archived bases, use the API functions instead (e.g. [Python `append_row`](../python/objects/rows.md#add-rows) or [JavaScript `appendRow`](../javascript/rows.md)). ## Syntax ```sql INSERT INTO tableName (column1, column2, ...) VALUES (value1, value2, ...) ``` - Values must be constants (strings, numbers, booleans) — functions and expressions are **not** supported in `VALUES` - Multi-value columns (e.g. multiple select): use nested parentheses: `("foo", "bar")` - Single/multiple select values must be option **names**, not keys - Not all column types can be written via SQL — see [Limitations](limitations.md#column-writability) for details __Example__ ```sql INSERT INTO Table1 (Name, Age) VALUES ('Erika', 38) ``` --- Source: https://developer.seatable.com/sql/update/ # UPDATE Updates one or more rows in a table. Works with both normal and Big Data storage. ## Syntax ```sql UPDATE tableName SET column1 = value [, column2 = value, ...] [WHERE ...] ``` If you omit the WHERE clause, **all rows** in the table will be updated. The `value` in SET must be a **constant** (string, number, or boolean). Expressions and [functions](functions.md) are not supported in SET — see [not supported features](index.md#not-supported) for alternatives. The same column restrictions and multi-value rules as [INSERT](insert.md) apply. Not all column types can be written via SQL — see [column writability](limitations.md#column-writability) for details. `LIMIT` is not supported in `UPDATE` statements. ## Setting values to NULL To clear a cell, set the column to `NULL`. Setting a text column to an empty string `""` also results in NULL — see [NULL values](limitations.md#null-values). ```sql UPDATE Contacts SET City=NULL WHERE Name="Alice" ``` ## Response `UPDATE` returns `success: true` on success. The response does not include the number of affected rows. If no rows match the WHERE condition, the response is still `success: true`. ```json { "success": true, "metadata": null, "results": null } ``` ## Examples Update a single column: ```sql UPDATE Contacts SET Adult=true WHERE Age>=18 ``` Update multiple columns at once: ```sql UPDATE Contacts SET Adult=true, `Age group`="18+" WHERE Age>=18 ``` --- Source: https://developer.seatable.com/sql/delete/ # DELETE Deletes one or more rows from a table. Works with both normal and Big Data storage. ## Syntax ```sql DELETE FROM tableName [WHERE ...] ``` If you omit the WHERE clause, **all rows** in the table will be deleted. `LIMIT` is not supported in `DELETE` statements. ## Response `DELETE` returns `success: true` on success. The response does not include the number of deleted rows. If no rows match the WHERE condition, the response is still `success: true`. ```json { "success": true, "metadata": null, "results": null } ``` ## Examples Delete rows matching a condition: ```sql DELETE FROM Contacts WHERE Age<18 ``` Delete rows with empty values: ```sql DELETE FROM Contacts WHERE City IS NULL ``` --- Source: https://developer.seatable.com/sql/limitations/ # Limitations This page documents SQL-specific behavior and restrictions. For general column type definitions, see the [API column model reference](https://api.seatable.com/reference/models). ## Column writability Not all column types can be written via SQL (`INSERT`, `UPDATE`). The following columns are **read-only** in SQL: | Column type | Readable | Writable | Notes | |:---|:---:|:---:|:---| | image | Yes | **No** | Use API to upload/update | | file | Limited | **No** | WHERE and ORDER BY not supported | | link | Yes | **No** | Use API to manage links | | link-formula | Yes | **No** | Computed from links | | formula | Yes | **No** | Computed value | | geolocation | Yes | **No** | WHERE and ORDER BY not supported; use `country()` function to query | | auto-number | Yes | **No** | System-managed sequence | | digital-sign | Limited | **No** | WHERE and ORDER BY not supported | | button | **No** | **No** | Not queryable | | \_creator, \_ctime | Yes | **No** | System-managed | | \_last\_modifier, \_mtime | Yes | **No** | System-managed | All other column types (text, long-text, number, single-select, multiple-select, checkbox, date, duration, rate, url, email, collaborator) are both readable and writable. ## List types Several column types contain multiple values: multiple-select, image, file, collaborator, link, and link formulas using `lookup`, `findmin`, or `findmax`. ### WHERE rules for list types | Element type | Operator | Rule | |:---|:---|:---| | string | `IN`, `HAS ANY OF`, etc. | Follow operator rules | | string | `LIKE`, `ILIKE` | Uses first element; empty string if no element | | string | `IS NULL` | True when list is empty | | string | `=`, `!=` | Uses first element | | float | `=`, `!=`, `<`, `<=`, `>`, `>=`, `BETWEEN` | Uses single element; only `!=` returns true for multiple | | float | `IS NULL` | True when list is empty | | float | `+`, `-`, `*`, `/` | Uses first element | | datetime | Same rules as float | | | bool | `IS TRUE` | Uses first element; false if empty | | linked record | | Follows rules for the display column type | ### Sorting list types In `GROUP BY` / `ORDER BY`, elements are first sorted ascending within each list, then lists are compared element by element. Shorter lists sort before longer lists when all compared elements are equal. ### Aggregation on list types For `MIN`, `MAX`, `SUM`, `AVG`: if the list has exactly one element, that element is used. Otherwise the row is not aggregated. ## NULL values NULL represents a missing value (distinct from 0). These are treated as NULL: - Empty cells - Values that cannot be converted to the column type - Empty strings (`""`) - Empty lists (see list types rules) - Formulas that return an error ### NULL in WHERE - Arithmetic on NULL returns NULL - `!=`, `NOT LIKE`, `NOT IN`, `NOT BETWEEN`, `HAS NONE OF`, `IS NOT TRUE`, `IS NULL` return `true` for NULL - `AND`, `OR`, `NOT` treat NULL as `false` - Aggregate functions ignore NULL values In formulas, NULL is converted to 0 or empty string. ## Big Data storage indexes SeaTable automatically creates indexes for rows in big data storage to improve query performance. Indexed column types: text, number, date, single select, multiple select, collaborators, creator, create date, modifier, modification date. Indexes are updated when: 1. The table is archived the next time 2. A user triggers index management from the "Big data management" menu in the base --- Source: https://developer.seatable.com/sql/functions/ # SQL function reference The functions supported in SQL are roughly the same as the set of functions supported by formulas in SeaTable. The function parameters can be numbers, strings, constants, column names or other functions. Column name cannot be an alias. Functions can be used in `SELECT`, `WHERE`, `GROUP BY`, `HAVING`, and `ORDER BY` clauses. They are **not supported** in the `SET` clause of `UPDATE` statements or in the `VALUES` list of `INSERT` statements. Only constant values can be used there. ## Differences from MySQL/MariaDB Where SeaTable and MySQL/MariaDB share the same function name (e.g. `now()`, `trim()`, `round()`), the syntax is identical. The table below lists only functions where the name or syntax differs. For the complete list of supported functions, see the [function reference](#operators) below. | MySQL/MariaDB | SeaTable equivalent | Notes | |:---|:---|:---| | `SUBSTR()` / `SUBSTRING()` | `mid(str, pos, len)` | | | `CONCAT()` / `CONCAT_WS()` | `concatenate(str1, str2, ...)` | | | `LENGTH()` / `CHAR_LENGTH()` | `len(str)` | | | `UCASE()` | `upper(str)` | | | `LCASE()` | `lower(str)` | | | `LOCATE()` / `INSTR()` | `find(substr, str)` or `search(substr, str)` | `find` is case-sensitive, `search` is not | | `REPLACE(str, from, to)` | `substitute(str, old, new)` | SeaTable also has `replace(str, pos, count, new)` which is position-based | | `REPEAT()` | `rept(str, n)` | | | `FORMAT()` | `text(num, format)` | Formats: `'number'`, `'euro'`, `'percent'` etc. | | `CURDATE()` / `CURRENT_DATE()` | `today()` | | | `DATE_ADD()` | `dateAdd(date, n, 'unit')` | | | `DATE_SUB()` | `dateAdd(date, -n, 'unit')` | Use negative count | | `DATEDIFF()` | `dateDif(d1, d2, 'D')` | Unit parameter is optional (defaults to days) | | `DATE_FORMAT()` | `isodate(date)` or `isomonth(date)` | Limited formatting options | | `EXTRACT(YEAR FROM date)` | `year(date)`, `month(date)`, `day(date)` | Separate functions per field | | `DAYOFWEEK()` | `weekday(date, weekStart)` | Optional `weekStart`: `'Monday'` or `'Sunday'` (default) | | `WEEK()` | `weeknum(date, return_type)` | Use `return_type` 21 for ISO week numbers | | `LAST_DAY()` | `eomonth(date, n)` | `n=0` for current month's last day | | `DAYOFMONTH()` | `day(date)` | | | `DATE()` | `isodate(date)` | Extracts date part as string | | `TIMESTAMPDIFF()` | `dateDif(d1, d2, unit)` | Different syntax, unit as string: `'D'`, `'M'`, `'Y'`, `'S'` | | `TIMESTAMPADD()` | `dateAdd(date, n, 'unit')` | Different syntax | | `CEIL()` | `ceiling(n)` | | | `POW()` | `power(a, b)` | | | `LOG10()` | `lg(n)` | | | `LOG2()` | — | Use `log(n, 2)` as workaround | | `TRUNCATE()` | `rounddown(n, d)` | | | `CASE WHEN ... THEN ... END` | `if(cond, trueVal, falseVal)` or `ifs(...)` | | | `CAST()` / `CONVERT()` | — | Not supported | | `GREATEST()` / `LEAST()` | — | Not supported | | `DAYNAME()` / `MONTHNAME()` | — | Not supported | | `STR_TO_DATE()` | — | Not supported | | `LPAD` / `RPAD` / `REVERSE` | — | Not supported | | `RAND()` | — | Not supported | | `IFNULL()` / `COALESCE()` / `NULLIF()` | — | Not supported | | `GROUP_CONCAT()` | — | Not supported | ## Constants You can use the following constants as arguments inside functions (e.g., `multiply(pi, 2)`). Note: in SQL, constants cannot be used as standalone expressions in `SELECT` — `SELECT pi FROM table` will fail because `pi` is interpreted as a column name. | VARIABLE | DESCRIPTION | | :------- | :-------------------------------------- | | `e` | Returns the Euler number e=2.71828... | | `pi` | Returns the circle number π=3.14159... | | `true` | Returns the logical value `true`. | | `false` | Returns the logical value `false`. | ## Operators Parameters must be strings or numbers. If a number is passed to a parameter that expects a string, it'll be converted to string, and vice versa. ### Arithmetic operators Adds two numeric values (`num1` and `num2`) and returns the result. ```sql add(num1,num2) ``` __Example__ `add(1,2)` returns `3` Subtracts one numeric value (`num2`) from another (`num1`). ```sql subtract(num1,num2) ``` __Example__ `subtract(5,4)` returns `1` Multiplies two numeric values. ```sql multiply(num1,num2) ``` __Example__ `multiply(3,4)` returns `12` Divides one numeric value (`num1`) by another (`num2`). ```sql divide(num1,num2) ``` __Example__ `divide(3,2)` returns `1.5` Calculates the remainder of a division. ```sql mod(num1,num2) ``` __Example__ `mod(15,7)` returns `1` Calculates the power (`num2`) of a number (`num1`). ```sql power(num1,num2) ``` __Example__ `power(3,2)` returns `9` ### Greater-Less comparisons Checks if a numeric value (`num1`) is greater than another (`num2`) and returns the logical value `true` or `false`. ```sql greater(num1,num2) ``` __Example__ `greater(2,3)` returns `false` Checks if a numeric value (`num1`) is less than another (`num2`) and returns the logical value `true` or `false`. ```sql lessthan(num1,num2) ``` __Example__ `lessthan(2,3)` returns `true` Checks if a numeric value (`num1`) is greater than or equal to another (`num2`) and returns the logical value `true` or `false`. ```sql greatereq(num1,num2) ``` __Example__ `greatereq(2,2)` returns `true` Checks if a numeric value (`num1`) is less than or equal to another (`num2`) and returns the logical value `true` or `false`. ```sql lessthaneq(num1,num2) ``` __Example__ `lessthaneq(2,2)` returns `true` ### Equal-Not equal comparisons The functions work for both numbers and strings. Checks if two values (`num1`, `num2`) are equal and returns the logical value `true` or `false`. ```sql equal(num1,num2) ``` __Example__ ```equal(`Old price`,`New price`)``` compares the content of the `Old price` and the `New price` columns and returns `true` or `false` accordingly Checks if two values (`num1`, `num2`) are not equal and returns the logical value `true` or `false`. ```sql unequal(num1,num2) ``` __Example__ ```unequal(`Single select`,"Option 1")``` compares the content of the `Single select` column to the string "Option 1" and returns `true` or `false` accordingly ## Mathematical functions Parameters must be numbers. If a string is passed as parameter, it will be converted to number. Returns the absolute value of a `number`. ```sql abs(number) ``` __Example__ `abs(-2)` returns `2` Rounds a `number` to the nearest greater integer or to the nearest greater multiple of the specified `significance`. If either argument is non-numeric, the formula returns an empty value. ```sql ceiling(number, significance) ``` __Example__ `ceiling(2.14)` returns `3` If the `number` is an exact multiple of the `significance`, then no rounding occurs. If the `number` and the `significance` are negative, then the rounding is away from 0. If the `number` is negative and the `significance` is positive, then the rounding is towards 0. __Example__ `ceiling(-2.14, 4)` returns `0` Returns the nearest greater even `number`. ```sql even(number) ``` __Example__ `even(2.14)` returns `4` Exponential function for Euler's `number` e. Returns the value of e to the power of `number`. ```sql exp(number) ``` __Example__ `exp(1)` returns `2.71828...` Rounds a `number` to the nearest smaller integer or to the nearest smaller multiple of the specified `significance`. If either argument is non-numeric, the formula returns an empty value. ```sql floor(number, significance) ``` __Example__ `floor(2.86)` returns `2` If the `number` is an exact multiple of the `significance`, then no rounding takes place. If the sign of the `number` is positive, then the rounding is towards 0. If the sign of the `number` is negative, then the rounding is away from 0. __Example__ `floor(-3.14, 5)` returns `-5` Assigns the nearest smaller integer to a real `number`. ```sql int(number) ``` __Example__ `int(-3.14)` returns `-4` Logarithm function with 10 as base. ```sql lg(number) ``` __Example__ `lg(100)` returns `2` Logarithm function with a definable `base`. ```sql log(number, base) ``` __Example__ `log(81, 3)` returns `4` But if no `base` is given, this function works exactly like lg(), with 10 as `base`. __Example__ `log(1000)` returns `3` Returns the nearest greater odd `number`. ```sql odd(number) ``` __Example__ `odd(-2.14)` returns `-1` Rounds a `number` to the nearest integer. If no decimal place (`digits`) is specified, the `number` is rounded to an integer. ```sql round(number, digits) ``` __Example__ `round(3.14)` returns `3` If a positive decimal place (`digits`) is given, the result will have `digits` decimals. __Example__ `round(3.14, 1)` returns `3.1` If a negative decimal place (`digits`) is given, the result is rounded to the left of the decimal point. __Example__ `round(3.14, -3)` returns `0` Rounds a `number` towards zero. If no decimal place (`digits`) is given, the `number` is rounded to an integer. ```sql rounddown(number, digits) ``` __Example__ `rounddown(3.12, 1)` returns `3.1` Rounds a `number` away from zero. If no decimal place (`digits`) is given, the `number` is rounded to an integer. ```sql roundup(number, digits) ``` __Example__ `roundup(-3.15)` returns `-4` Checks whether a `number` is greater, equal or less than 0. Returns the values 1, 0 and -1 respectively. In other words: it returns the sign of a `number`, for '+', 'zero' and '-' with 1, 0, and -1 respectively. ```sql sign(number) ``` __Example__ `sign(-2)` returns `-1` Returns the square root of a `number`. ```sql sqrt(number) ``` __Example__ `sqrt(81)` returns `9` ## Text functions Combines several strings (`string1`, `string 2`, ...) into one single string. ```sql concatenate(string1, string2, ...) ``` __Example__ `concatenate(`Supplier`, " has the product ", `Product`)` returns for example `Microsoft has the product GitHub` if `Supplier` column contains "Microsoft" and `Product` column contains "GitHub" Checks whether two strings (`string1`, `string2`) are exactly identical. Returns the values `true` or `false` respectively. ```sql exact(string1, string2) ``` __Example__ `exact('SeaTable', 'Seatable')` returns `false` Returns the start position of a string (`findString`) within another string (`sourceString`). The numbering starts at 1. If not found, 0 is returned. If the start position (`startPosition`) is given as decimal, it is rounded down. If the cell in the column for the keyword (`findString`) is empty, 1 is returned. If the cell in the column for the target string (`sourceString`) is empty, an empty value ('') is returned. ```sql find(findString, sourceString, startPosition) ``` __Example__ `find('Sea', 'seaTable', 1)` returns `0` The search will start from the given `startPosition`. This `startPosition` has no influence on the result: it always returns the absolute start position. If the '`startPosition`' of the string to be searched for (`findString`) is given after the actual start position of the string (`sourceString`), 0 is returned, since nothing was found from this position. __Example__ `find('table', 'big table', 4)` returns `5` Returns the specified number (`count`) of characters at the beginning of a `string`. ```sql left(string, count) ``` __Example__ `left('SeaTable', 3)` returns `Sea` Returns the number of characters in a `string`. ```sql len(string) ``` __Example__ `len('SeaTable')` returns `8` Converts a character `string` to lower case letters. ```sql lower(string) ``` __Example__ `lower('German')` returns `german` Returns the specified number (`count`) of characters from the specified start position (`startPosition`) of a `string`. ```sql mid(string, startPosition, count) ``` __Example__ `mid('SeaTable is the best', 1, 8)` returns `SeaTable` Start position (`startPosition`) and `count` must not be empty, negative or zero. However, if start position (`startPosition`) and number (`count`) are given as decimal, they are rounded down. Too much `count` is ignored. __Example__ `mid('SeaTable is the best.', 10.9, 27.3)` returns `is the best.` Replaces a part (`count`) of a character string (`sourceString`) from a certain start position (`startPosition`) with another character string (`newString`). The number (`count`) of characters is only taken into account for the old string (`sourceString`), but not for the new string (`newString`). ```sql replace(sourceString, startPosition, count, newString) ``` __Example__ `replace('SeaTable is the best.', 1, 8, 'Seafile')` returns `Seafile is the best.` If number (`count`) is given as zero, the new string (`newString`) is simply added to the old string (`sourceString`) from the start position (`startPosition`). __Example__ `replace('SeaTable is the best.', 1, 0, 'Seafile')` returns `SeafileSeaTable is the best.` Repeats a `string` as often (`number`) as specified. ```sql rept(string, number) ``` __Example__ `rept('Sea', 3)` returns `SeaSeaSea` Returns the specified number (`count`) of characters at the end of a `string`. ```sql right(string, count) ``` __Example__ `right('SeaTable', 5)` returns `Table` Returns the start position of a string (`findString`) within another string (`sourceString`). The numbering starts at 1. If not found, 0 is returned. If the start position (`startPosition`) is given as decimal, it is rounded down. If the cell in the column for the keyword (`findString`) is empty, 1 is returned. If the cell in the column for the target string (`sourceString`) is empty, an empty value ('') is returned. ```sql search(findString, sourceString, startPosition) ``` __Example__ `search('Sea', 'seaTable', 1)` returns `1` The search will start from the given `startPosition`. This `startPosition` has no influence on the result: it always returns the absolute start position. If the `startPosition` of the character string to be searched for (`findString`) is given after the actual start position of the character string (`sourceString`), 0 is returned, since nothing was found from this position. __Example__ `search('table', 'big table', 6)` returns `0` Replaces existing text (`oldString`) with new text (`newString`) in a string (`sourceString`). If there is more than one text (`oldString`) in the string (`sourceString`), only the `index`-th text is replaced. ```sql substitute(sourceString, oldString, newString, index) ``` __Example__ `substitute('SeaTableTable', 'Table', 'file', 1)` returns `SeafileTable` If the `index` is given as 0 or not, all found text (`oldString`) will be replaced by the new text (`newString`). __Example__ `substitute('SeaTableTable', 'Table', 'file')` returns `Seafilefile` Checks whether a `value` is text. If so, the text is returned. If no, the return `value` is empty. ```sql T(value) ``` __Example__ `T('123')` returns `123` Converts a `number` into text and `format`s it in the specified `format`. The `format` can be percent, number, dollar, euro or yuan. ```sql text(number, format) ``` __Example__ `text(150, 'euro')` returns `€150` When a `number` is converted directly to percent, its absolute value is retained. In other words, 50 is converted into 5000%. But if you want 50%, you have to divide the `number` by 100 before the conversion. __Example__ `text(50, 'percent')` returns `5000%` Removes spaces at both the beginning and the end of a `string`. ```sql trim(string) ``` __Example__ `trim(' SeaTable ')` returns `SeaTable` Converts a `string` to uppercase letters. ```sql upper(string) ``` __Example__ `upper('German')` returns `GERMAN` Converts a text (`string`) representing a number into a number. ```sql value(string) ``` __Example__ `value('123')` returns `123` ## Date functions When passing a parameter with time or date type, you can specify a constant in "2025-09-01 12:00:01" or "2025-09-01" format. When you query the result of a date function in SQL, the result will be converted to a string in RFC3339 format, e.g. "2025-09-03T00:00:00+02:00". Please note that if a date function returns a date, it cannot be used as parameter for text or maths functions. Returns a date in international format (ISO) from entered `year`, `month` and `day`. If the `year` is entered with two digits, it is automatically understood as a year in the 1900s. If the number of the `month` or `day` is too large (greater than 12 or 31 respectively), these months or days are automatically converted to the next year or month. ```sql date(year, month, day) ``` __Example__ `date(2025, 1, 3)` returns `2025-01-03T00:00:00+02:00` Adds the specified number (`count`) of years ('years'), months ('months'), weeks ('weeks'), days ('days'), hours ('hours'), minutes ('minutes') or seconds ('seconds') to a datetime (`date`). ```sql dateAdd(date, count, unit) ``` __Example__ `dateAdd('2024-02-03', 2, 'days')` returns `2024-02-05T00:00:00+02:00` Tip: if you want to add a complex duration (`count`) such as 1 day 12 hours, you can convert it to e.g. 24+12=36 hours ('hours') and enter it into the formula as a uniform duration (`count`). The duration is converted to the smallest `unit`: in this case, hours. __Example__ `dateAdd('2024-09-04 13:05:18', 36, 'hours') OR dateAdd(`form submission`, 36, 'hours')` returns `2024-09-06T01:05:18+02:00` Calculates the seconds, days, months, or years between two date values. The optional `unit` argument can be one of the following: S (seconds), D (full days), M (full months), Y (full years), YD (full days, ignoring years), YM (full months, ignoring days and years), MD (full days, ignoring months and years). If the `startDate` is empty, a default value of "1900-01-01" will be set. If both date values are empty, it will return 0. ```sql dateDif(startDate, endDate, unit) ``` __Example__ `dateDif('2023-01-01', '2025-01-01','Y')` returns `2` __Example__ `dateDif('2024-10-11', '2025-12-12', 'M')` returns `14` Returns the day of a `date` as a number. The returned number is between 1 and 31. ```sql day(date) ``` __Example__ `day('2025-01-03')` returns `3` Determines the date of the last day of `n`th month before or after (depending on the sign of `n`) the specified date (`startDate`). If `n` is 0, the last day of the month is simply determined. ```sql eomonth(startDate, n) ``` __Example__ `eomonth('2025-01-01', 1)` returns `2025-02-28T00:00:00+02:00` __Example__ `eomonth('2025-01-01', -1)` returns `2024-12-31T00:00:00+02:00` Returns the hour of a `date` as a number. The number returned is between 0 and 23. ```sql hour(date) ``` __Example__ `hour('2025-02-14 13:14:52')` returns `13` If no hour is contained in the time specification (`date`), 0 is returned. __Example__ `hour('2025-02-14')` returns `0` Returns the number of hours between two date values (`startDate` and `endDate`). The minutes in the date values are not taken into account. ```sql hours(startDate, endDate) ``` __Example__ `hours('2025-02-14 13:14', '2025-02-14 15:14')` returns `2` If no hours are included in the time specification (`startDate` or `endDate`), 0 o'clock on this day is automatically assumed. __Example__ `hours('2020-02-14', '2020-02-14 15:14')` returns `15` !!! info "SQL support" Support for `hours()` in SQL queries was added in v6.2. In earlier versions, use `dateDif(startDate, endDate, 'S')` and divide by 3600 as a workaround. Returns the minutes of a time specification (`date`) as a number. The number returned is between 0 and 59. ```sql minute(date) ``` __Example__ `minute('2025-02-14 13:14:52')` returns `14` If no minutes are included in the time (`date`), 0 is returned. __Example__ `minute('2025-02-14')` returns `0` Returns the month of a `date` as a number. The returned number is between 1 (January) and 12 (December). ```sql month(date) ``` __Example__ `month('2025-02-14 13:14:52')` returns `2` Returns the number of months between two date values (`startDate` and `endDate`). The days and time in the date values are not taken into account. ```sql months(startDate, endDate) ``` __Example__ `months('2025-02-01 13:14', '2025-03-31 15:54')` returns `1` Returns the number of full working days between two dates (`startDate` and `endDate`). You can also define holidays other than Saturday and Sunday (`holiday1`, `holiday2`, etc.), which are also deducted. If you do not want to include public holidays, you can simply omit these parameters. ```sql networkdays(startDate, endDate, holiday1, holiday2, ...) ``` __Example__ `networkdays('2025-01-01', '2025-01-07','2025-01-01')` returns `4` Please note that the specified last day (`endDate`) is also included in the formula. Thus, for the following example, three working days are counted: the 7th, 8th and 9th of September, 2025. __Example__ `networkdays('2025-09-08', '2025-09-10')` returns `3` Returns the current date and time. ```sql now() ``` __Example__ `now()` returns `2025-09-07T12:59+02:00` Returns the seconds of a time (`date`) as a number. The number returned is between 0 and 59. ```sql second(date) ``` __Example__ `second('2025-02-14 13:14:52')` returns `52` Returns the current date. ```sql today() ``` __Example__ `today()` returns `2020-09-07T00:00:00+02:00` This function is handy for calculating time between a certain datetime and now. On each reload of the Base or recalculation, the calculation is updated. __Example__ `networkdays('2025-10-01', today())` returns `4` Returns the weekday of a `date` as a number. The returned number between 1 and 7, where you can define the first day of the week (`weekStart`). `weekStart` is Sunday by default, it can also be set to Monday ('Monday' or 'monday', not case sensitive). ```sql weekday(date, weekStart) ``` __Example__ `weekday('2025-01-01', 'Monday')` returns `3` If no `weekStart` is given or if a `weekStart` other than 'Monday' or 'Sunday' is given, the default value ('Sunday') is used. So if it should be 'Monday', enter 'Monday'; if it should be 'Sunday', you can omit this parameter. __Example__ `weekday('2025-01-01', 'Thursday') OR weekday('2025-01-01')` returns `4` Returns the absolute week number of a `date` as a number. The returned number is between 1 and 53, where you can define the first day of the week (`return_type`). Enter the number 1 or 2, or 11 to 17, and 21 as `return_type` to define the start of a week: 1/Sunday, 2/Monday, 11/Monday, 12/Tuesday, 13/Wednesday, 14/Thursday, 15/Friday, 16/Saturday, 17/Sunday. If you want the week number to be returned according to ISO standard, specify the number of 21 as `return_type`. Note: the standalone function `isoweeknum` is only available in formulas, not in SQL queries — use `weeknum(date, 21)` instead. ```sql weeknum(date, return_type) ``` __Example__ `weeknum('2025-01-12', 11)` returns `2` If no '`return_type`' is given, it is always assumed to be 'Sunday'. __Example__ `weeknum('2025-01-12')` returns `3` Returns the year of a `date` as a number. ```sql year(date) ``` __Example__ `year('2025-01-01')` returns `2025` Returns the first day of the week in which the `date` is located. `weekStart` is Sunday by default, it can also be set to Monday ('Monday' or 'monday', not case sensitive). ```sql startofweek(date, weekStart) ``` __Example__ `startofweek('2025-04-28')` returns `2025-4-27T00:00:00+02:00` Returns the quarter of the `date`, the return value is 1, 2, 3, 4. ```sql quarter(date) ``` __Example__ `quarter('2025-01-01')` returns `1` Returns the ISO string representation of the `date`. ```sql isodate(date) ``` __Example__ `isodate('2025-01-01 11:00:00')` returns `2025-01-01` Returns the ISO string representation (of the year and month) of the month of a specified `date`. ```sql isomonth(date) ``` __Example__ `isomonth('2025-01-01 11:00:00')` returns `2025-01` ## Geo functions Returns the country or region of a geolocation-type column. (Since version 5.1.0) ```sql country(geolocation) ``` __Example__ ```country(`Country of residence`)``` returns `Germany` ## Logical functions Checks if all arguments (`logical1`, `logical2`, ...) are true (valid, not empty and not equal to zero). If yes, `true` is returned, otherwise `false`. ```sql and(logical1, logical2, ...) ``` __Example__ `and(1, '', 2)` returns `false` Checks if an argument (`logical`) is true and returns `trueValue` or `falseValue` accordingly. ```sql if(logical, trueValue, falseValue) ``` __Example__ `if(1>2, 3, 4)` returns `4` For the condition (`logical`) only a comparison is allowed. If `falseValue` is omitted`: it will return the first value (`trueValue`) if the condition (`logical`) is true; and it will return an empty value ('') if the condition (`logical`) is false. __Example__ `if(`Budget`>`Price`, 'Yes')` returns `Yes` or '' Checks if one or more conditions (`logical1`, `logical2`, ...) are true and returns a value (`value1`, `value2`, ...) that matches the **first** true condition. ```sql ifs(logical1, value1, logical2, value2, ...) ``` __Example__ `ifs( 1>2, 3, 5>4, 9)` returns `9` Inverts the logical value (`boolean`). In other words: converts true to false and false to true. ```sql not(boolean) ``` __Example__ `not(and(1, '', 2))` returns `true` Checks if at least 1 of the arguments (`logical1`, `logical2`, ...) is true (valid, not empty and not equal to zero), and returns `true` in this case. If all arguments are false, then returns `false`. ```sql or(logical1, logical2, ...) ``` __Example__ `or(1,'',2)` returns `true` Evaluates an expression (`logical`) against a list of values (matcher) and returns the result (value) corresponding to the **first** matching value. If there is no match, an optional `default` value is returned. At least 3 parameters (`logical`, matcher, value) must be specified. ```sql switch(logical, matcher1, value1, matcher2, value2, ..., default) ``` __Example__ `switch(`grades`, 1, 'very good', 2, 'good', 3, 'satisfactory', 4, 'passed', 'failed')` returns `very good` If there are several identical values in the value list (matcher), only the first hit is taken into account. __Example__ `switch(int(68/10), 6, 'OK', 6, 'KO')` returns `OK` Returns the logical inequality of all arguments. In other words, returns `true` if the number of true arguments is odd. ```sql xor(logical1, logical2, ...) ``` __Example__ `xor(1, 0, 2<1)` returns `true` ## Statistical functions These are formula-style functions that operate on literal values or within a single row. They are **not** SQL aggregate functions — use the standard SQL aggregates `COUNT(*)`, `SUM()`, `MIN()`, `MAX()`, and `AVG()` with `GROUP BY` instead (see [aggregation functions](select.md#aggregation-functions)). Returns the average of the numbers (`number1`, `number2`, ...). ```sql average(number1, number2, ...) ``` __Example__ `average(1, 2, 3, 4, 5)` returns `3` !!! warning "Not the same as AVG()" `average()` is a formula function for literal values. For aggregation across rows (e.g. with `GROUP BY`), use the SQL aggregate `AVG(column)` instead. Counts the number of non-empty cells (`textORnumber1`, `textORnumber2`, ...). These cells can be text or numbers. In this example, 1 and 2 are numbers, '3' is text, and '' is an empty value. ```sql counta(textORnumber1, textORnumber2, ...) ``` __Example__ `counta(1, '', 2, '3')` returns `3` Counts the number of elements (`textORnumber1`, `textORnumber2`, ...) including numbers (1, 2), text ('3') and empty cells (''). ```sql countall(textORnumber1, textORnumber2, ...) ``` __Example__ `countall(1, '', 2, '3')` returns `4` Counts the number of empty cells. ```sql countblank(textORnumber1, textORnumber2, ...) ``` __Example__ `countblank(1, '', 2, '3')` returns `1` Counts the number of items in a `column`. The supported `column` types are multiple select, collaborator, file, image (available since version 2.7.0). ```sql countItems(column) ``` __Example__ `countItems(column_name)` returns `2` --- ## Plugin Development Source: https://developer.seatable.com/plugins/ # SeaTable plugin development process In this guide, we will demonstrate step by step how to write a plugin to SeaTable. This plugin can display the basic information of the base, including - Number of tables - Number of records - Number of collaborators The code of the plugin development example is very simple. You can click this [github](https://github.com/seatable/seatable-plugin-table-info) link to get the source code. The plugin development process is as follows. ## The basic process of plugin development ### 1. Install development tool Using npm: ```bash $ npm install -g create-dtable-plugin ``` ### 2. Create plugin ```bash $ create-dtable-plugin init seatable-plugin-table-info ``` Install dependencies ```bash $ cd seatable-plugin-table-info $ npm install ``` ### 3. Modify the plugin configuration Modify the info.json configuration file in the plugin-config folder. ```js "name": '', // The name can only contain letters, numbers and underscores "version": '', // Plugin version number "display_name": '', // The name displayed by the plugin "description": '', // Description of plugin ``` There is no need to add other configuration parameters, other parameters are automatically generated by the packaging tool. Optional operation - Add a custom icon.png to the plugin-config folder as the icon of the plugin (it may not be provided, the default icon is used. The icon.png requires 128x128 pixels) - Add a custom card_image.png to the plugin-config folder as the background image of the plugin icon (it may not be provided, the default background is displayed. The card_image.png requires 560x240 pixels) ### 4. Modify the plugin registration function in the entry.js file Modify ```js window.app.registerPluginItemCallback("test", TaskList.execute); ``` to ```js window.app.registerPluginItemCallback(name, TaskList.execute); ``` The name value here is the "name" value in plugin-config/info.json. ### 5. Add plugin development configuration file There is a file setting.local.dist.js in the project src folder, copy it and name it setting.local.js The content of the file is as follows, and you can modify it according to the comments ```js const config = { APIToken: "**", // The apiToken of the dtable server: "**", // The deployment URL of the dtable workspaceID: "**", // The workspaceID of the dtable dtableName: "**", // The name of the dtable to which the plugin lang: "**", // Plugin default language type, 'en' or 'zh-cn' }; ``` ### 6. Start development Run local development environment ```bash $ npm start ``` Open localhost:3000 on the browser, you can see that the plugin dialog has been opened, and the interface function provided by the dtable-sdk library is displayed by default in the dialog 1. getTables: obtained table information of the dtable base 2. getRelatedUsers: get detailed information of dtable collaborators Main code and purpose - /src/index.js: entry file for local development plugin - /src/entry.js: follow the entry file when SeaTable is run as a plugin - /src/app.js: the main code of the plugin ### 7. Display basic information of the table Write a TableInfo component, this component needs to pass in two props, tables and collaborators ```jsx class TableInfo extends React.Component {} const propTypes = { tables: PropTypes.array.isRequired, collaborators: PropTypes.array.isRequired, }; TableInfo.propTypes = propTypes; export default TableInfo; ``` Get the number of tables ```js getTablesNumber = (tables) => { return tables && Array.isArray(tables) ? tables.length : 0; }; ``` Get the number of records ```js getRecords = (tables) => { let recordsNumber = 0; if (!tables) return recordsNumber; for (let i = 0; i < tables.length; i++) { const table = tables[i]; const rows = table.rows; if (rows && Array.isArray(rows)) { recordsNumber += rows.length; } } return recordsNumber; }; ``` Get the number of collaborators ```jsx renderCollaborators = (collaborators) => { if (!collaborators || !Array.isArray(collaborators)) { return null; } return collaborators.map((collaborator, index) => { return (
{collaborator.name}
); }); }; ``` Interface rendering: the number of tables, the number of records and the number of collaborators ```jsx render() { const { tables, collaborators } = this.props; return (
{'Number of tables: '}{this.getTablesNumber(tables)}

{'Total number of records: '}{this.getRecords(tables)}

{'Number of collaborators: '}{collaborators ? collaborators.length : 0}

{this.renderCollaborators(collaborators)}
); } ``` In the parent component app.js, use the `TableInfo` component , modify the render function in app.js, and pass in tables and collaborators. ```jsx import TableInfo from './table-info'; class App extends React.Component{ let tables = this.dtable.getTables(); let collaborators = this.dtable.getRelatedUsers(); render() { return ( {'Plugin'} ); } } ``` Add the css/table-info.css file and modify the style of the plugin. Run `npm start` again, you can see the following information on the browser localhost: 3000. ```md Number of tables: X Total number of records: XXX Number of collaborators: X ``` ### 8. Package upload plugin 1. Execute `npm run build-plugin` to package the plugin, and the path of the packaged plugin is /plugin/task.zip 2. Upload the plugin task.zip to dtable --- Source: https://developer.seatable.com/plugins/environments/ # dtable ## Init The plugin development environment is divided into two types, the development environment and the production environment. Because of the different environments, the initialization methods are also different: - In the development environment, you need to provide the configuration file required by the plugin, which is used to initialize the plugin and obtain the data required by the plugin. - In the production environment, you need to install the plugin, then the plugin can directly read the data of the base in the current browser to initialize the plugin. ### Initialize the plugin #### Development environment Initialize the plugin in the development environment ```javascript import DTable from "dtable-sdk"; const dtable = new DTable(); const settings = { server: "https://cloud.seatable.cn", APIToken: "50c17897ae8b1c7c428d459fc2c379a9bc3806cc", }; await dtable.init(config); ``` #### Production environment Initialize the plugin in the production environment ```javascript import DTable from "dtable-sdk"; const dtable = new Dtable(); const dtableStore = window.app.dtableStore; // Get initialization data from the production environment await dtable.initBrowser(dtableStore); ``` ### Monitoring event changes #### Subscribe events | Event type | description | use | | --------------------- | ------------------------------------------------------------------------------------------------------- | --------------------------- | | dtable-connect | Indicates that a link has been established with the server, and the data loading is complete | Update state and UI display | | local-dtable-changed | Indicates that some operations have been performed locally, and the data has changed | Update state and UI display | | remote-dtable-changed | Indicates that some operations sent by the server have been performed locally, and the data has changed | Update state and UI display | ```javascript import DTable from 'dtable-sdk'; const dtable = new Dtable(); dtable.subscribe('dtable-connect', () => {...}); dtable.subscribe('local-dtable-changed', () => {...}); dtable.subscribe('remote-dtable-changed', () => {...}); ``` ## Example This is an initialization example in the development environment. Since two environments need to be compatible, the initialization operations for general plugin development are as follows: ```javascript import Dtable from 'dtable-sdk'; import PropTypes from 'prop-types'; const propsTypes = { isDevelopment: PropTypes.bool }; const settings = { "server": "https://cloud.seatable.cn", "APIToken": "50c17897ae8b1c7c428d459fc2c379a9bc3806cc", }; class App extends React.Component { constructor(props) { super(props); this.state = { isLoading: true }; this.dtable = new Dtable(); } async componentDidMount() { const { isDevelopment } = this.props; if (isDevelopment) { await dtable.init(settings); await this.dtable.syncWithServer(); this.dtable.subscribe('dtable-connect', this.resetData); } else { const dtableStore = window.app.dtableStore; dtable.initBrowser(dtableStore); } this.dtable.subscribe('local-dtable-changed', this.resetData); this.dtable.subscribe('remote-dtable-changed', this.resetData); } resetData = () => { // ... this.setState({isLoading: false}); } render() { return ( ... ); } } ``` --- Source: https://developer.seatable.com/plugins/methods/ # Methods This is a list of all available objects and methods in SeaTable you can use in the plugin development. For a more detailed description of the used parameters, refer to the data model at the [SeaTable API Reference](https://api.seatable.com/reference/models). ## Common Base represents a table in SeaTable. The `base` object provide a way to read, manipulate and output data in/from your base. The following methods are available. ### Users ??? question "getRelatedUsers" Get other users associated with the current base (collaborators of the table, the shared person of the table, etc.) ```js dtable.getRelatedUsers() ``` __Example__ ```js const collaborators = dtable.getRelatedUsers(); ``` ??? question "getCollaboratorsName" Get a list of names of collaborators ```js dtable.getCollaboratorsName(collaborators, value) ``` Arguments * collaborators: collaborator list in this base * value: email list of collaborators __Example__ ```js const collaborators = dtable.getRelatedUsers(); const value = ['abc@seafile.com', 'shun@seafile.com']; const name = dtable.getCollaboratorsName(collaborators, value); // 'abc, shun' ``` ### Views ??? question "getViewRowsColor" Get the color attributes of the row data in the view ```js dtable.getViewRowsColor(rows, view, table) ``` __Arguments__ * rows: the rows of the color attribute * view: view object * table: table object __Example__ ```js const tableName = 'tableName'; const viewName = 'viewName'; const table = dtable.getTableByName(tableName); const view = dtable.getViewByName(table, viewName); const rows = dtable.getViewRows(view, table); const rowsColor = dtable.getViewRowsColor(rows, view, table); ``` ### Output ??? question "getTableFormulaResults" Get the data in the calculation formula column of the table ```js dtable.getTableFormulaResults(table, rows) ``` __Arguments__ * table: table object * rows: row data of the relevant data of the calculation formula column __Example__ ```js const tableName = 'tableName'; const viewName = 'viewName'; const table = dtable.getTableByName(tableName); const view = dtable.getViewByName(table, viewName); const rows = dtable.getViewRows(view, table); const formulaResult = dtable.getTableFormulaResults(table, rows); ``` ??? question "getLinkCellValue" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getLinkDisplayString" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getLinkDisplayString" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getNumberDisplayString" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getGeolocationDisplayString" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getDurationDisplayString" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getDateDisplayString" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ## Tables ??? question "addTable" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "deleteTable" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "renameTable" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getTables" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getActiveTable" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getTableByName" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getTableById" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "importDataIntoNewTable" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ## Views ??? question "addView" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "deleteView" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "renameView" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getViews" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getNonArchiveViews" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getActiveView" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getViewByName" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getViewById" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "isDefaultView" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "isGroupView" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "isFilterView" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ## Columns ??? question "getColumns" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getShownColumns" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getColumnsByType" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getColumnByName" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getColumnByKey" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "modifyColumnData" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ## Rows ??? question "sqlQuery" Use sql statement to query a dtable ```js dtable.sqlQuery(sql) ``` __Arguments__ * sql: SQL statement to be executed Note: By default, up to 100 results are returned. If you need more results, please add the limit parameter in the sql statement Possible errors include * ValueError: sql can not be empty * ConnectionError: network error * Exception: no such table * Exception: no such column * Exception: columns in group by should match columns in select __Example__ ```js dtable.sqlQuery('select name, price, year from Bill') ``` ```json [ {'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob', 'price': 300, 'year': 2019}, {'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300, 'year': 2021}, {'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom', 'price': 100, 'year': 2019}, {'_id': 'NxeaB5pDRFKOItUs_Ugxug', 'name': 'Tom', 'price': 100, 'year': 2020}, {'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200, 'year': 2021}, {'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane', 'price': 200, 'year': 2020}, {'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200, 'year': 2021} ] ``` __WHERE__ ```js dtable.sqlQuery('select name, price from Bill where year = 2021 ') ``` ```json [ {'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300}, {'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200}, {'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200} ] ``` __ORDER BY__ ```js dtable.sqlQuery('select name, price, year from Bill order by year') ``` ```json [ {'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob', 'price': 300, 'year': 2019}, {'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom', 'price': 100, 'year': 2019}, {'_id': 'NxeaB5pDRFKOItUs_Ugxug', 'name': 'Tom', 'price': 100, 'year': 2020}, {'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane', 'price': 200, 'year': 2020}, {'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300, 'year': 2021}, {'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200, 'year': 2021}, {'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200, 'year': 2021} ] ``` __GROUP BY__ ```js dtable.sqlQuery('select name, sum(price) from Bill group by name') ``` ```json [ {'SUM(price)': 600, 'name': 'Bob'}, {'SUM(price)': 400, 'name': 'Tom'}, {'SUM(price)': 400, 'name': 'Jane'} ] ``` __DISTINCT__ ```js dtable.sqlQuery('select distinct name from Bill') ``` ```json [ {'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob'}, {'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom'}, {'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane'} ] ``` ??? question "appendRow" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "deleteRowById" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "deleteRowsByIds" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "modifyRow" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "forEachRow" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getTableLinkRows" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getViewRows" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getGroupRows" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getInsertedRowInitData" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getRowsByID" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "getRowById" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "moveGroupRows" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ## Plugins ??? question "getPluginSettings" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "updatePluginSettings" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "deletePluginSettings" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ## Constants ??? question "ColumnTypes" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "Column icon configs" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "Column options" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "Formula result type" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "Select option colors" Get a list of names of collaborators ```js ``` __Example__ ```js ``` ??? question "Table permission type" Get a list of names of collaborators ```js ``` __Example__ ```js ``` --- ## HTML pages Source: https://developer.seatable.com/html-pages/ # HTML pages Starting with SeaTable 6.2, a Universal App can contain a new page type: the **HTML page**. You upload a packaged HTML/JavaScript/CSS bundle, and SeaTable renders it as a full page inside the app. A static bundle on its own can only display fixed content. To turn an HTML page into a real application — a custom form, a dashboard, a calculator — it needs to read from and write to the base. That data exchange runs through the [`seatable-html-page-sdk`](https://www.npmjs.com/package/seatable-html-page-sdk). This section is written for developers. It covers how to set up a project, how to develop and package a page, and the full SDK reference. ## Architecture An HTML page is rendered in a sandboxed context inside the Universal App. It never talks to the base directly. Instead, the SDK provides a messaging bridge to the app, which performs the actual data operations. ```mermaid flowchart LR A[HTML page] -->|seatable-html-page-sdk| B[Universal App] B -->|API| C[(Base)] C --> B B --> A ``` The SDK offers: - **Data APIs** — list, add, update and delete rows; upload files and images. - **Event propagation** — bidirectional events (mouse, keyboard, drag-and-drop) between the page and the app. ## Prerequisites - SeaTable 6.2 or later. - A Universal App with an HTML page added to it. The developer setup additionally needs Node.js and npm, and an API token generated in the base for local development. The low-code approach needs neither. ## Two ways to build The same kind of page can be built two ways. Pick the one that matches how you like to work — both produce the same uploadable ZIP and use the same SDK.
- :material-cursor-default-click:{ .lg .middle } __Low-code quickstart__ --- Design the page in any visual (WYSIWYG) HTML editor, connect it to your base with ready-made copy-paste snippets, and upload. No Node.js, no npm, no dev server. Best for forms, dashboards and small tools. [:octicons-arrow-right-24: Low-code quickstart](low-code-quickstart.md) - :material-code-tags:{ .lg .middle } __Developer setup__ --- Clone the official template and use a full toolchain: modular files, a live-reload dev server, npm scripts and a real build. Best for larger or more complex pages. [:octicons-arrow-right-24: Developer setup](getting-started.md)
## Where to start - [Low-code quickstart](low-code-quickstart.md) — build a page with a visual editor and copy-paste snippets, no toolchain. - [Developer setup](getting-started.md) — set up the project, develop locally, build and upload a page. We follow the official [simple form template](https://github.com/seatable/seatable-html-page-template-simple-form) end to end. - [SDK Reference](sdk/initialization.md) — installation, initialization, and the full API for rows, files and images. The [`seatable-html-page-template-simple-form`](https://github.com/seatable/seatable-html-page-template-simple-form) repository contains a complete, buildable form page. It is the running example used throughout the developer setup. --- Source: https://developer.seatable.com/html-pages/low-code-quickstart/ # Low-code quickstart You do not necessarily need Node.js, npm or a development server to build an HTML page. If you can edit an HTML file and change a few names, you can publish a **custom-looking page that reads from and writes to your base** — a styled form, a landing page, a simple read-and-submit tool. The fastest way in is not to build one from scratch. It is to **copy a page that already works and rename it** to match your base. Custom-looking **forms, lists and info pages** that read and write the base in simple ways — the kind of page where the value is the *design* and *simple read/write*, not complex logic. If parts of your page need to **react to each other** (a running total, a list that reloads when a dropdown changes, conditional fields), that is a real application — jump to the [developer setup](getting-started.md) instead. See [When this approach stops](#when-this-approach-stops) below. ## What this page does The example below is a small **event sign-up page**. It: 1. reads a list of upcoming **sessions** from your base and shows them as styled cards; 2. lets a visitor pick a session, type their name and email, and click **Sign up**; 3. writes that as a new row in a **Signups** table, linked back to the chosen session. ![The rendered sign-up page: session cards showing date and duration, and a labelled sign-up form.](../media/html-page-quickstart.png) That is the whole loop: **read a table → show it → write a row back**. Everything else on this page is styling. ## Required tables The page expects two tables. Create them as below, or rename the code (in [step 3](#3-change-the-names-to-match-your-base)) to match tables you already have. **Sessions** — the events people can sign up for. | Name | Type | Description | | -------- | -------- | ---------------------------------------- | | Title | text | the session name shown on the card | | Time | date | when the session takes place | | Duration | duration | how long it runs (comes back in seconds) | **Signups** — one row is written here per sign-up. | Name | Type | Description | | ------- | ----- | ------------------------ | | Name | text | the visitor's name | | Email | email | the visitor's email | | Session | link | link to **Sessions** | ## What you need - A **text or visual HTML editor** — anything from Notepad/TextEdit to a WYSIWYG editor. You only need to change some text. - A way to make a **ZIP** file (built into Windows, macOS and Linux). - A **Universal App** where you can add an HTML page ([step 5](#5-add-an-html-page-to-your-app)). You do not need an API token for this approach — the page gets its connection from the app once it is uploaded. ## The recipe at a glance Building the page is eight short steps. Each one is detailed below. 1. **Copy** the code into a text editor. 2. **Save** it as `index.html`. 3. **Change** the table and column names to match your base. 4. **Zip** it — with `index.html` at the top level. 5. **Add** an HTML page to your Universal App. 6. **Upload** the ZIP. 7. **Grant** the page access to its tables. 8. **Open** the page and use it. The two steps people miss are **3** (some names are changed differently from others) and **7** (skip it and the page loads blank). Both are spelled out below. ## 1. Copy the code into a text editor This is the complete, working page. Copy all of it into a plain text editor (or paste it into your visual editor's code view). ```html Event sign-up

Upcoming sessions

Sign up

``` ## 2. Save it as `index.html` Save the file with the exact name **`index.html`**. The name matters — this is the file SeaTable opens first. You can now edit and preview it like any web page. ## 3. Change the names to match your base This is the one step where you touch the code — and the part where a first page usually goes wrong. **You only change names.** The rest — the whole `loadTable` helper, the structure, the `sdk` calls — stays exactly as it is. There are **three kinds of names**, and they are changed *differently*: | In the code | What it is | How to change it | | ------------------------------- | ------------------------------ | -------------------------------------------------- | | `"Sessions"`, `"Signups"` | **table names**, in quotes | replace the text **inside** the quotes | | `s.Title`, `s.Time` | **columns you read** | keep the `s.` — replace **only** `Title` / `Time` | | `Name:`, `Email:`, `Session:` | **columns you write** | replace the whole word **before the `:`** | `s` means "the current session row"; `.Title` is the column on it. So `s.Title` becomes `s.YourColumn` — **not** `YourColumn`. This one trips everybody up. `Session` is a **link** column (it points at the Sessions table). A link column is always written as a **list of row IDs**, even for a single link — that is what the `[ ]` around the value is. The value inside (`...select-select").value`) is the id of the chosen session. Two more value facts, for when you adapt the page: - **Single-select / multi-select** values come back as the option **text** you see in SeaTable, not a hidden code. - For every operation and value shape, see the [SDK Reference](sdk/rows.md). The look — all the HTML and CSS in `