Working with Tables
Beta APIs
The Document APIs are beta. Send the EARLY-ACCESS: true header on every request shown here; without it these endpoints are unavailable. See Introduction to Documents and the Stability Guidelines.
Current limitations
The API reads and writes data in unstructured tables and Results tables. Support for more structured table types is in active development.
Table rows are where most document lab data lives. Because row data can be large, it is fetched separately from the list of document parts.
Documents contain two kinds of tables:
- Unstructured tables have free-form columns, and every cell holds text.
- Structured tables have columns defined by a schema, and each cell holds a value of its column’s type. The supported structured table type is the Results table.
Reading table data
When you list a document’s content, tables appear as summary objects. The __typename identifies the kind of table, such as DocumentPartUnstructuredTable or DocumentPartResultsTable.
To get the table’s columns and other details, request the table by ID.
On structured tables, each column definition has a cellValueType, which sets the type of value the column accepts.
To get the rows, follow the rows URL. Rows are paginated with pageSize and nextToken.
Cell values
Two properties hold a cell’s value:
inputValueis the value the user or API edits. In unstructured tables it’s always aTextValue, which can hold plain text, cell references, and formulas. In structured tables its type matches the column, such asObjectLinkValueorDecimalValue.evaluationValueis what the spreadsheet evaluator outputs, and it usually matches what Benchling displays. Prefer it when reading a table, but it isn’t always available; see Evaluation.
Evaluation
Tables take part in spreadsheet evaluation. Evaluation takes each inputValue and coerces it into the standard value Benchling displays. It’s also what interprets spreadsheet references and formulas, such as =SUM(A1:A10)/6.
The table’s isEvaluated property says whether its cells have evaluationValues.
Only the browser client evaluates spreadsheets. New evaluationValues are generated only when someone edits the document in the Benchling web app. Any API edit clears the evaluated values until the document is next edited there. When you read a table right after an API write, fall back to inputValue if evaluationValue is null.
Writing table data
Tables are edited with a partsMutation on the document that contains them. The request body is the same for unstructured and structured tables.
Two operations edit an existing table:
insert_table_rowsadds rows to the end of a table.update_table_cellschanges the value or formatting of specific cells.
Every partsMutation needs the document’s current version. It’s available as versionMetadata.versionId on the document or on any of its parts. If it doesn’t match the current version on the server, the request returns HTTP 409. Get the latest version and retry.
Inserting rows
Each new row lists the cells to fill in, and each cell targets a column by columnIndex. Columns without a cell get the column’s default value, and default formulas shift down to the new row.
Two optional properties control where the rows go:
positionsets where the rows are inserted. The only supported value isend_of_table, which is the default.replaceEmptyTrailingRows, whentrue, replaces any empty rows at the end of the table with the new rows, instead of inserting after them. A row is empty when it’s unsubmitted and every cell matches its column’s default value. This helps with templates that start with blank rows. Defaults tofalse.
To find a column’s columnIndex, look the column up by name in the table’s columnDefinitions. Matching by name keeps an integration working if the columns are reordered.
Updating cells
Each cell update targets one cell by rowIndex and columnIndex, both counted from 0. In each update:
inputValue: include it to change the value, set it tonullto clear the value, or leave it out to keep the existing value.format: include it to change the cell’s formatting.
This example changes one value, and highlights another cell without changing its value.
Cell value types
Unstructured table cells always take a text_value. Structured table cells take the type that matches the column’s cellValueType, or a text_value holding a formula.