> ## Documentation Index
> Fetch the complete documentation index at: https://sdk.grid.is/llms.txt
> Use this file to discover all available pages before exploring further.

# Advanced data queries

> Greater control over workbook calculations and data retrieval.

This endpoint offers more control and flexibility compared to the [`calc`](/api-reference/workbooks/calc) and [`values`](/api-reference/workbooks/values) endpoints. While all three endpoints allow you to read and write cell values from a workbook, the `query` endpoint provides several advanced capabilities that give developers greater control over data manipulation and retrieval:

* Goal seeking
* Organise data by rows or columns
* Use structured request parameters

For most use-cases, the `calc` or `values` endpoints provide a simpler, more streamlined interface that's easier to work with. However, when you need greater control, the `query` endpoint becomes essential.


## OpenAPI

````yaml post /v1/workbooks/{id}/query
openapi: 3.1.0
info:
  title: GRID API
  summary: Spreadsheets run the world. Now you can run spreadsheets.
  description: >-
    GRID's API transforms spreadsheets into powerful, programmable APIs. Our
    platform supports seamless integration of spreadsheet data into your
    applications, supporting dynamic interactions with spreadsheets, including
    real-time data querying with ephemeral updates.
  version: 1.0.0
servers:
  - url: https://api.grid.is
security: []
tags:
  - name: workbooks
    description: Interact with workbooks and their data
paths:
  /v1/workbooks/{id}/query:
    post:
      tags:
        - workbooks
      summary: Query workbook data
      description: >-
        Read cell data or apply temporary changes.


        Send a JSON object with a `read` key to read values from cells and
        formulas. Optionally, use

        the `apply` key to update cells before reading.
      operationId: query_workbook
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
            title: Unique workbook id
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/QueryRequest'
      responses:
        '200':
          description: Successful Response
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/QueryResponse'
        '400':
          description: Error returned when you make a bad request
          content:
            application/json:
              example:
                error_code: invalid_json
                error_message: Bad request
                error_details: RequestJsonParsingFailed
        '403':
          description: Error returned when you don't have permission to query a workbook
          content:
            application/json:
              example:
                error_message: Forbidden
                error_details: Only API keys can access the workbook engine API
        '429':
          description: Error returned when you exceed your API rate limit
          content:
            application/json:
              example:
                error_code: rate_limit_exceeded
                error_message: Rate limit exceeded
                error_details: 'Retry after: 60'
      security:
        - apiKey: []
      x-codeSamples:
        - lang: JavaScript
          source: >-
            import Grid from '@grid-is/api';


            const client = new Grid({
              apiKey: 'My API Key',
            });


            const response = await client.workbooks.query('id', { read: ['A1',
            'Sheet2!B3', '=SUM(A1:A4)'] });


            console.log(response.apply);
        - lang: Python
          source: |-
            from grid_api import Grid

            client = Grid(
                api_key="My API Key",
            )
            response = client.workbooks.query(
                id="id",
                read=["A1", "Sheet2!B3", "=SUM(A1:A4)"],
            )
            print(response.apply)
        - lang: Java
          source: |-
            package is.grid.api.example;

            import is.grid.api.client.GridClient;
            import is.grid.api.client.okhttp.GridOkHttpClient;
            import is.grid.api.models.workbooks.WorkbookQueryParams;
            import is.grid.api.models.workbooks.WorkbookQueryResponse;

            public final class Main {
                private Main() {}

                public static void main(String[] args) {
                    GridClient client = GridOkHttpClient.fromEnv();

                    WorkbookQueryParams params = WorkbookQueryParams.builder()
                        .id("id")
                        .addRead("A1:A4")
                        .build();
                    WorkbookQueryResponse response = client.workbooks().query(params);
                }
            }
components:
  schemas:
    QueryRequest:
      properties:
        options:
          anyOf:
            - $ref: '#/components/schemas/QueryRequestOptions'
            - type: 'null'
          description: Options to choose the structure of the returned data
        apply:
          anyOf:
            - items:
                $ref: '#/components/schemas/DataTransformation'
              type: array
            - type: 'null'
          title: Apply
          description: >-
            Cells to update before reading. Note that the API has no state and
            any changes made are cleared after each request
          examples:
            - - target: A2
                value: 1234
        goalSeek:
          anyOf:
            - $ref: '#/components/schemas/QueryRequestGoalSeek'
            - type: 'null'
          description: >-
            Goal seek. Use this to calculate the required input value for a
            formula to achieve a specified target result. Useful when the
            desired outcome is known, but the corresponding input is not
        read:
          items:
            anyOf:
              - type: string
                description: A1-style string reference with optional sheet prefix
                examples:
                  - Sheet1!A1:B2
                  - '''Loan Calculator''!C32'
              - $ref: '#/components/schemas/ReferenceObject'
            description: A reference to a range of spreadsheet cells
          type: array
          title: Read
          description: Cell references to read from the workbook and return to the client
          examples:
            - - A1
              - Sheet2!B3
              - '=SUM(A1:A4)'
      type: object
      required:
        - read
      title: QueryRequest
      description: >-
        Defines a request to read workbook data and, optionally, apply temporary
        changes. It includes

        `read` (required cell references or formulas), `apply` (optional
        transient updates to cells),

        and `options` (optional settings for the structure of returned data).
      examples:
        - apply:
            - target: A2
              value: 1234
          read:
            - A1:A4
        - read:
            - A1
            - Sheet1!B2
            - '=A2+D2'
    QueryResponse:
      properties:
        apply:
          anyOf:
            - items:
                $ref: '#/components/schemas/QueryResponseApply'
              type: array
            - type: 'null'
          title: Apply
          description: >-
            Confirmation of the changes that were applied to the spreadsheet.
            Note that the API has no state and any changes made are cleared
            after each request
        goalSeek:
          anyOf:
            - $ref: '#/components/schemas/QueryResponseGoalSeek'
            - type: 'null'
          description: Results of the goal seek operation
        read:
          items:
            $ref: '#/components/schemas/DataTable'
          type: array
          title: Read
          description: Details on the values that were read from the workbook cells
      type: object
      required:
        - apply
        - read
      title: QueryResponse
      description: >-
        Contains the results of a workbook query, including `read` (queried cell
        data) and `apply`

        (details of temporary changes applied). Note that the API has no state
        and any changes made are

        cleared after each request.
    QueryRequestOptions:
      properties:
        axis:
          anyOf:
            - type: string
              enum:
                - rows
                - columns
            - type: 'null'
          title: Axis
          description: Determines if data is outputted as rows or columns
          default: rows
      type: object
      title: QueryRequestOptions
      description: Defines settings for configuring query results.
    DataTransformation:
      properties:
        target:
          anyOf:
            - type: string
              description: A1-style string reference with optional sheet prefix
              examples:
                - Sheet1!A1:B2
                - '''Loan Calculator''!C32'
            - $ref: '#/components/schemas/ReferenceObject'
          title: Target
          description: Reference for the cell to write to
        value:
          anyOf:
            - type: integer
            - type: number
            - type: string
            - type: boolean
            - type: 'null'
          title: Value
          description: Value to write to the target cell
      type: object
      required:
        - target
        - value
      title: DataTransformation
      description: >-
        Specifies a temporary change to a workbook cell, including the `target`
        cell reference and the

        `value` to apply. The API has no state, and so any changes made are
        cleared after each request.
    QueryRequestGoalSeek:
      properties:
        targetCell:
          anyOf:
            - type: string
              description: A1-style string reference with optional sheet prefix
              examples:
                - Sheet1!A1:B2
                - '''Loan Calculator''!C32'
            - $ref: '#/components/schemas/ReferenceObject'
          title: Target cell
          description: Reference for the cell that contains the formula you want to resolve
        targetValue:
          anyOf:
            - type: integer
            - type: number
          title: Target value
          description: The value you want the formula to return
        controlCell:
          anyOf:
            - type: string
              description: A1-style string reference with optional sheet prefix
              examples:
                - Sheet1!A1:B2
                - '''Loan Calculator''!C32'
            - $ref: '#/components/schemas/ReferenceObject'
          title: Control cell
          description: Reference for the cell that will contain the solution
      type: object
      required:
        - targetCell
        - targetValue
        - controlCell
      title: QueryRequestGoalSeek
      description: >-
        Goal seek. Use this to calculate the required input value for a formula
        to achieve a specified

        target result. This is particularly useful when the desired outcome is
        known, but the

        corresponding input is not.
    ReferenceObject:
      properties:
        cells:
          type: string
          title: Cells
          description: Unprefixed A1-style range, id, or name
          examples:
            - A1:B2
            - C32
            - name
        sheet:
          anyOf:
            - type: string
            - type: 'null'
          title: Sheet
          description: Name of the sheet to reference
          examples:
            - Sheet1
            - Loan Calculator
      type: object
      required:
        - cells
        - sheet
      title: ReferenceObject
      description: A reference to a range of spreadsheet cells.
    QueryResponseApply:
      properties:
        target:
          type: string
          title: Target
          description: A1-style reference for the cell that was updated
          examples:
            - Sheet1!A1:B2
            - '''Loan Calculator''!C32'
        value:
          anyOf:
            - type: integer
            - type: number
            - type: string
            - type: boolean
            - type: 'null'
          title: Value
          description: New value of the cell
        originalValue:
          anyOf:
            - type: integer
            - type: number
            - type: string
            - type: boolean
            - type: 'null'
          title: Original value
          description: Original value of the cell before applying the new value
      type: object
      required:
        - target
        - value
        - originalValue
      title: QueryResponseApply
      description: >-
        Details temporary changes made during a query, including the `target`
        cell, the new `value`, and

        the `originalValue` before the change. Note that the API has no state
        and any changes made are

        cleared after each request.
    QueryResponseGoalSeek:
      properties:
        targetCell:
          type: string
          title: Target cell
          description: >-
            Reference for the cell that contains the formula you wanted to
            resolve
          examples:
            - Sheet1!A1:B2
            - '''Loan Calculator''!C32'
        targetValue:
          anyOf:
            - type: integer
            - type: number
          title: Target value
          description: The value you wanted the formula to return
        controlCell:
          type: string
          title: Control cell
          description: Reference for the cell that contains the solution
          examples:
            - Sheet1!A1:B2
            - '''Loan Calculator''!C32'
        solution:
          anyOf:
            - type: integer
            - type: number
          title: Formula result
          description: The result of the formula
      type: object
      required:
        - targetCell
        - targetValue
        - controlCell
      title: QueryResponseGoalSeek
      description: Results of a goal seek operation.
    DataTable:
      properties:
        source:
          type: string
          title: Source
          description: A1-style reference for the cell or cells that were updated
          examples:
            - Sheet1!A1:B2
            - '''Loan Calculator''!C32'
        type:
          type: string
          const: dataTable
          title: Type
        data:
          items:
            items:
              anyOf:
                - $ref: '#/components/schemas/ValueCell'
                - $ref: '#/components/schemas/ErrorCell'
                - $ref: '#/components/schemas/EmptyCell'
            type: array
          type: array
          title: Data
      type: object
      required:
        - source
        - type
        - data
      title: DataTable
      description: A two-dimensional table of cells retrieved from a spreadsheet.
    ValueCell:
      properties:
        r:
          anyOf:
            - type: string
            - type: 'null'
          title: Cell reference
          description: >-
            Relative A1-based cell reference. This property only appears when
            there's a real cell behind the value
        t:
          $ref: '#/components/schemas/app__model__query__CellType'
          title: Cell type
          description: Data type of the cell value (e.g. boolean, number, text)
        v:
          anyOf:
            - type: integer
            - type: number
            - type: string
            - type: boolean
            - type: 'null'
          title: Cell value
          description: Underlying cell value
        z:
          anyOf:
            - type: string
            - type: 'null'
          title: Number format
          description: Number format associated with the cell
        w:
          anyOf:
            - type: string
            - type: 'null'
          title: Formatted text
          description: Formatted cell value
      type: object
      required:
        - t
        - v
      title: ValueCell
      description: >-
        Represents a single workbook cell, including its value (`v`), cell
        reference (`r`), type (`t`),

        number format (`z`), and formatted text (`w`).
    ErrorCell:
      properties:
        r:
          anyOf:
            - type: string
            - type: 'null'
          title: Cell reference
          description: >-
            Relative A1-based cell reference. This property only appears when
            there's a real cell behind the value
        t:
          type: string
          const: e
          title: Cell type
          description: Data type of the cell value (always 'e' for 'error')
        v:
          type: string
          title: Cell value
          description: Underlying cell value
        e:
          anyOf:
            - type: string
            - type: 'null'
          title: Error code
          description: Description of the error
      type: object
      required:
        - t
        - v
      title: ErrorCell
      description: >-
        Represents a workbook cell with an error. It includes the cell reference
        (`r`), type (`t`,

        always `e`), value (`v`), and an optional error code (`e`). It provides
        details for

        identifying and understanding errors in workbook data.
    EmptyCell:
      properties:
        r:
          anyOf:
            - type: string
            - type: 'null'
          title: Cell reference
          description: >-
            Relative A1-based cell reference. This property only appears when
            there's a real cell behind the value
        t:
          type: string
          const: z
          title: Cell type
          description: Data type of the cell value (always 'z' for 'empty cell')
      type: object
      required:
        - t
      title: EmptyCell
      description: Cells that have no content but hold metadata like comments.
    app__model__query__CellType:
      type: string
      enum:
        - b
        - 'n'
        - d
        - s
      title: CellType
      description: >-
        Specifies the type of a workbook cell. Possible values include `b`
        (boolean), `n` (number),

        `d` (date), and `s` (string).
  securitySchemes:
    apiKey:
      type: http
      scheme: bearer
      bearerFormat: JWT

````