query-table-rows¶

Description¶

The QueryTableRows API allows you to use a filter formula to query for specific rows in a table.

Synopsis¶

  query-table-rows
--workbook-id <value>
--table-id <value>
--filter-formula <value>
[--cli-input-json | --cli-input-yaml]
[--starting-token <value>]
[--page-size <value>]
[--max-items <value>]
[--generate-cli-skeleton <value>]

Options¶

--workbook-id (string)

The ID of the workbook whose table rows are being queried.

If a workbook with the specified id could not be found, this API throws ResourceNotFoundException.

--table-id (string)

The ID of the table whose rows are being queried.

If a table with the specified id could not be found, this API throws ResourceNotFoundException.

--filter-formula (structure)

An object that represents a filter formula along with the id of the context row under which the filter function needs to evaluate.

formula -> (string)

A formula representing a filter function that returns zero or more matching rows from a table. Valid formulas in this field return a list of rows from a table. The most common ways of writing a formula to return a list of rows are to use the FindRow() or Filter() functions. Any other formula that returns zero or more rows is also acceptable. For example, you can use a formula that points to a cell that contains a filter function.

contextRowId -> (string)

The optional contextRowId attribute can be used to specify the row id of the context row if the filter formula contains unqualified references to table columns and needs a context row to evaluate them successfully.

Shorthand Syntax:

formula=string,contextRowId=string

JSON Syntax:

{
  "formula": "string",
  "contextRowId": "string"
}

--cli-input-json | --cli-input-yaml (string) Reads arguments from the JSON string provided. The JSON string follows the format provided by --generate-cli-skeleton. If other arguments are provided on the command line, those values will override the JSON-provided values. It is not possible to pass arbitrary binary values using a JSON-provided value as the string will be taken literally. This may not be specified along with --cli-input-yaml.

--starting-token (string)

A token to specify where to start paginating. This is the NextToken from a previously truncated response.

For usage examples, see Pagination in the AWS Command Line Interface User Guide .

--page-size (integer)

The size of each page to get in the AWS service call. This does not affect the number of items returned in the command’s output. Setting a smaller page size results in more calls to the AWS service, retrieving fewer items in each call. This can help prevent the AWS service calls from timing out.

For usage examples, see Pagination in the AWS Command Line Interface User Guide .

--max-items (integer)

The total number of items to return in the command’s output. If the total number of items available is more than the value specified, a NextToken is provided in the command’s output. To resume pagination, provide the NextToken value in the starting-token argument of a subsequent command. Do not use the NextToken response element directly outside of the AWS CLI.

For usage examples, see Pagination in the AWS Command Line Interface User Guide .

--generate-cli-skeleton (string) Prints a JSON skeleton to standard output without sending an API request. If provided with no value or the value input, prints a sample input JSON that can be used as an argument for --cli-input-json. Similarly, if provided yaml-input it will print a sample input YAML that can be used with --cli-input-yaml. If provided with the value output, it validates the command inputs and returns a sample output JSON for that command.

See ‘aws help’ for descriptions of global parameters.

Output¶

columnIds -> (list)

The list of columns in the table whose row data is returned in the result.

(string)

rows -> (list)

The list of rows in the table that match the query filter.

(structure)

An object that contains attributes about a single row in a table

rowId -> (string)

The id of the row in the table.

cells -> (list)

A list of cells in the table row. The cells appear in the same order as the columns of the table.

(structure)

An object that represents a single cell in a table.

formula -> (string)

The formula contained in the cell. This field is empty if a cell does not have a formula.

format -> (string)

The format of the cell. If this field is empty, then the format is either not specified in the workbook or the format is set to AUTO.

rawValue -> (string)

The raw value of the data contained in the cell. The raw value depends on the format of the data in the cell. However the attribute in the API return value is always a string containing the raw value.

Cells with format DATE, DATE_TIME or TIME have the raw value as a floating point number where the whole number represents the number of days since 1/1/1900 and the fractional part represents the fraction of the day since midnight. For example, a cell with date 11/3/2020 has the raw value “44138”. A cell with the time 9:00 AM has the raw value “0.375” and a cell with date/time value of 11/3/2020 9:00 AM has the raw value “44138.375”. Notice that even though the raw value is a number in all three cases, it is still represented as a string.

Cells with format NUMBER, CURRENCY, PERCENTAGE and ACCOUNTING have the raw value of the data as the number representing the data being displayed. For example, the number 1.325 with two decimal places in the format will have it’s raw value as “1.325” and formatted value as “1.33”. A currency value for $10 will have the raw value as “10” and formatted value as “$10.00”. A value representing 20% with two decimal places in the format will have its raw value as “0.2” and the formatted value as “20.00%”. An accounting value of -$25 will have “-25” as the raw value and “$ (25.00)” as the formatted value.

Cells with format TEXT will have the raw text as the raw value. For example, a cell with text “John Smith” will have “John Smith” as both the raw value and the formatted value.

Cells with format CONTACT will have the name of the contact as a formatted value and the email address of the contact as the raw value. For example, a contact for John Smith will have “John Smith” as the formatted value and “john.smith@example.com” as the raw value.

Cells with format ROWLINK (aka picklist) will have the first column of the linked row as the formatted value and the row id of the linked row as the raw value. For example, a cell containing a picklist to a table that displays task status might have “Completed” as the formatted value and “row:dfcefaee-5b37-4355-8f28-40c3e4ff5dd4/ca432b2f-b8eb-431d-9fb5-cbe0342f9f03” as the raw value.

Cells with format AUTO or cells without any format that are auto-detected as one of the formats above will contain the raw and formatted values as mentioned above, based on the auto-detected formats. If there is no auto-detected format, the raw and formatted values will be the same as the data in the cell.

formattedValue -> (string)

The formatted value of the cell. This is the value that you see displayed in the cell in the UI.

Note that the formatted value of a cell is always represented as a string irrespective of the data that is stored in the cell. For example, if a cell contains a date, the formatted value of the cell is the string representation of the formatted date being shown in the cell in the UI. See details in the rawValue field below for how cells of different formats will have different raw and formatted values.

nextToken -> (string)

Provides the pagination token to load the next page if there are more results matching the request. If a pagination token is not present in the response, it means that all data matching the request has been loaded.

workbookCursor -> (long)

Indicates the cursor of the workbook at which the data returned by this request is read. Workbook cursor keeps increasing with every update and the increments are not sequential.

Table of Contents

Feedback

User Guide

query-table-rows¶

Description¶

Synopsis¶

Options¶

Output¶