Google Sheets API

openapi: 3.0.3
info:
  title: Google Sheets API
  description: >-
    The Google Sheets API v4 is a RESTful interface that lets developers read and modify
    Google Spreadsheet data programmatically. Supports creating spreadsheets, reading and
    writing cell values by range (A1 notation), batch operations for efficiency, managing
    sheet structure and formatting, pivot tables, charts, and developer metadata.
    Authentication uses Google OAuth 2.0 with the sheets.readonly or sheets scope.
  version: '4.0.0'
  termsOfService: https://developers.google.com/terms/
  contact:
    name: Google Workspace Developer Support
    url: https://developers.google.com/workspace/sheets/api
  license:
    name: Creative Commons Attribution 4.0
    url: https://creativecommons.org/licenses/by/4.0/
externalDocs:
  description: Google Sheets API Documentation
  url: https://developers.google.com/workspace/sheets/api
servers:
  - url: https://sheets.googleapis.com/v4
    description: Google Sheets API v4
tags:
  - name: Spreadsheets
    description: Spreadsheet-level operations
  - name: Values
    description: Read and write cell values
  - name: Sheets
    description: Sheet-level operations
  - name: Developer Metadata
    description: Manage developer metadata attached to spreadsheets
security:
  - OAuth2:
      - https://www.googleapis.com/auth/spreadsheets
      - https://www.googleapis.com/auth/spreadsheets.readonly
paths:
  /spreadsheets:
    post:
      operationId: create-spreadsheet
      tags:
        - Spreadsheets
      summary: Create Spreadsheet
      description: >-
        Creates a new spreadsheet, returning the newly created spreadsheet.
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/SpreadsheetRequest'
      responses:
        '200':
          description: Spreadsheet created successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Spreadsheet'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}:
    get:
      operationId: get-spreadsheet
      tags:
        - Spreadsheets
      summary: Get Spreadsheet
      description: >-
        Returns the spreadsheet at the given ID. The caller must specify the spreadsheet ID.
        By default, data within grids is not returned. Include the ranges request parameter
        to specify the data to retrieve.
      parameters:
        - name: spreadsheetId
          in: path
          description: The spreadsheet ID to retrieve
          required: true
          schema:
            type: string
            example: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms'
        - name: ranges
          in: query
          description: The ranges to retrieve from the spreadsheet
          required: false
          schema:
            type: array
            items:
              type: string
          style: form
          explode: true
        - name: includeGridData
          in: query
          description: True if grid data should be returned
          required: false
          schema:
            type: boolean
            default: false
      responses:
        '200':
          description: Spreadsheet retrieved successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Spreadsheet'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'
        '404':
          $ref: '#/components/responses/NotFound'

  /spreadsheets/{spreadsheetId}:batchUpdate:
    post:
      operationId: batch-update-spreadsheet
      tags:
        - Spreadsheets
      summary: Batch Update Spreadsheet
      description: >-
        Applies one or more updates to the spreadsheet. Each request is validated before
        being applied. If any request is not valid then the entire request will fail and
        nothing will be applied.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/BatchUpdateRequest'
      responses:
        '200':
          description: Batch update applied successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/BatchUpdateResponse'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}/values/{range}:
    get:
      operationId: get-values
      tags:
        - Values
      summary: Get Values
      description: >-
        Returns a range of values from a spreadsheet. The caller must specify the
        spreadsheet ID and a range.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
        - name: range
          in: path
          description: The A1 notation or R1C1 notation of the range to retrieve values from
          required: true
          schema:
            type: string
            example: 'Sheet1!A1:D10'
        - name: majorDimension
          in: query
          description: The major dimension that results should use
          required: false
          schema:
            type: string
            enum: [ROWS, COLUMNS]
            default: ROWS
        - name: valueRenderOption
          in: query
          description: How values should be represented in the output
          required: false
          schema:
            type: string
            enum: [FORMATTED_VALUE, UNFORMATTED_VALUE, FORMULA]
            default: FORMATTED_VALUE
        - name: dateTimeRenderOption
          in: query
          description: How dates, times, and durations should be represented
          required: false
          schema:
            type: string
            enum: [SERIAL_NUMBER, FORMATTED_STRING]
      responses:
        '200':
          description: Values retrieved successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ValueRange'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'
        '404':
          $ref: '#/components/responses/NotFound'
    put:
      operationId: update-values
      tags:
        - Values
      summary: Update Values
      description: >-
        Sets values in a range of a spreadsheet. The caller must specify the spreadsheet ID,
        range, and a valueInputOption.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
        - name: range
          in: path
          required: true
          schema:
            type: string
        - name: valueInputOption
          in: query
          description: How the input data should be interpreted
          required: true
          schema:
            type: string
            enum: [RAW, USER_ENTERED]
        - name: includeValuesInResponse
          in: query
          required: false
          schema:
            type: boolean
            default: false
        - name: responseValueRenderOption
          in: query
          required: false
          schema:
            type: string
            enum: [FORMATTED_VALUE, UNFORMATTED_VALUE, FORMULA]
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/ValueRange'
      responses:
        '200':
          description: Values updated successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/UpdateValuesResponse'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}/values/{range}:append:
    post:
      operationId: append-values
      tags:
        - Values
      summary: Append Values
      description: >-
        Appends values to a spreadsheet. The input range is used to search for
        existing data and find a "table" in that range. Values will be appended
        to the next row of the table.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
        - name: range
          in: path
          required: true
          schema:
            type: string
        - name: valueInputOption
          in: query
          required: true
          schema:
            type: string
            enum: [RAW, USER_ENTERED]
        - name: insertDataOption
          in: query
          required: false
          schema:
            type: string
            enum: [OVERWRITE, INSERT_ROWS]
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/ValueRange'
      responses:
        '200':
          description: Values appended successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/AppendValuesResponse'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}/values/{range}:clear:
    post:
      operationId: clear-values
      tags:
        - Values
      summary: Clear Values
      description: >-
        Clears values from a spreadsheet. The caller must specify the spreadsheet ID and range.
        Only values are cleared; all other properties of the cell (such as formatting, data
        validation, etc.) are kept.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
        - name: range
          in: path
          required: true
          schema:
            type: string
      requestBody:
        required: false
        content:
          application/json:
            schema:
              type: object
      responses:
        '200':
          description: Values cleared successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ClearValuesResponse'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}/values:batchGet:
    get:
      operationId: batch-get-values
      tags:
        - Values
      summary: Batch Get Values
      description: >-
        Returns one or more ranges of values from a spreadsheet. The caller must specify
        the spreadsheet ID and one or more ranges.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
        - name: ranges
          in: query
          required: true
          schema:
            type: array
            items:
              type: string
          style: form
          explode: true
        - name: majorDimension
          in: query
          required: false
          schema:
            type: string
            enum: [ROWS, COLUMNS]
        - name: valueRenderOption
          in: query
          required: false
          schema:
            type: string
            enum: [FORMATTED_VALUE, UNFORMATTED_VALUE, FORMULA]
      responses:
        '200':
          description: Values retrieved successfully
          content:
            application/json:
              schema:
                type: object
                properties:
                  spreadsheetId:
                    type: string
                  valueRanges:
                    type: array
                    items:
                      $ref: '#/components/schemas/ValueRange'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}/values:batchUpdate:
    post:
      operationId: batch-update-values
      tags:
        - Values
      summary: Batch Update Values
      description: >-
        Sets values in one or more ranges of a spreadsheet. The caller must specify
        the spreadsheet ID, a valueInputOption, and one or more ValueRanges.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                valueInputOption:
                  type: string
                  enum: [RAW, USER_ENTERED]
                data:
                  type: array
                  items:
                    $ref: '#/components/schemas/ValueRange'
              required: [valueInputOption, data]
      responses:
        '200':
          description: Values batch updated successfully
          content:
            application/json:
              schema:
                type: object
                properties:
                  spreadsheetId:
                    type: string
                  totalUpdatedRows:
                    type: integer
                  totalUpdatedColumns:
                    type: integer
                  totalUpdatedCells:
                    type: integer
                  totalUpdatedSheets:
                    type: integer
                  responses:
                    type: array
                    items:
                      $ref: '#/components/schemas/UpdateValuesResponse'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}/values:batchClear:
    post:
      operationId: batch-clear-values
      tags:
        - Values
      summary: Batch Clear Values
      description: Clears one or more ranges of values from a spreadsheet.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                ranges:
                  type: array
                  items:
                    type: string
      responses:
        '200':
          description: Values cleared successfully
          content:
            application/json:
              schema:
                type: object
                properties:
                  spreadsheetId:
                    type: string
                  clearedRanges:
                    type: array
                    items:
                      type: string
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}/sheets/{sheetId}:copyTo:
    post:
      operationId: copy-sheet-to
      tags:
        - Sheets
      summary: Copy Sheet To
      description: >-
        Copies a single sheet from a spreadsheet to another spreadsheet.
        Returns the properties of the newly created sheet.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
        - name: sheetId
          in: path
          description: The ID of the sheet to copy
          required: true
          schema:
            type: integer
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                destinationSpreadsheetId:
                  type: string
                  description: The ID of the spreadsheet to copy the sheet to
              required: [destinationSpreadsheetId]
      responses:
        '200':
          description: Sheet copied successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SheetProperties'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

  /spreadsheets/{spreadsheetId}/developerMetadata/{metadataId}:
    get:
      operationId: get-developer-metadata
      tags:
        - Developer Metadata
      summary: Get Developer Metadata
      description: Returns the developer metadata with the specified ID.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
        - name: metadataId
          in: path
          required: true
          schema:
            type: integer
      responses:
        '200':
          description: Developer metadata retrieved
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/DeveloperMetadata'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'
        '404':
          $ref: '#/components/responses/NotFound'

  /spreadsheets/{spreadsheetId}/developerMetadata:search:
    post:
      operationId: search-developer-metadata
      tags:
        - Developer Metadata
      summary: Search Developer Metadata
      description: Returns all developer metadata matching the specified DataFilter.
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                dataFilters:
                  type: array
                  items:
                    type: object
      responses:
        '200':
          description: Developer metadata search results
          content:
            application/json:
              schema:
                type: object
                properties:
                  matchedDeveloperMetadata:
                    type: array
                    items:
                      type: object
                      properties:
                        developerMetadata:
                          $ref: '#/components/schemas/DeveloperMetadata'
        '401':
          $ref: '#/components/responses/Unauthorized'
        '403':
          $ref: '#/components/responses/Forbidden'

components:
  securitySchemes:
    OAuth2:
      type: oauth2
      flows:
        authorizationCode:
          authorizationUrl: https://accounts.google.com/o/oauth2/v2/auth
          tokenUrl: https://oauth2.googleapis.com/token
          scopes:
            https://www.googleapis.com/auth/spreadsheets: Read and write access to Google Sheets
            https://www.googleapis.com/auth/spreadsheets.readonly: Read-only access to Google Sheets

  schemas:
    Spreadsheet:
      type: object
      description: A Google Sheets spreadsheet resource
      properties:
        spreadsheetId:
          type: string
          description: The ID of the spreadsheet
        properties:
          $ref: '#/components/schemas/SpreadsheetProperties'
        sheets:
          type: array
          items:
            $ref: '#/components/schemas/Sheet'
        spreadsheetUrl:
          type: string
          format: uri
          description: The URL of the spreadsheet

    SpreadsheetRequest:
      type: object
      description: Request body for creating a new spreadsheet
      properties:
        properties:
          $ref: '#/components/schemas/SpreadsheetProperties'
        sheets:
          type: array
          items:
            $ref: '#/components/schemas/Sheet'

    SpreadsheetProperties:
      type: object
      properties:
        title:
          type: string
          description: The title of the spreadsheet
          example: 'My Spreadsheet'
        locale:
          type: string
          description: The locale of the spreadsheet (BCP 47 language tag)
          example: 'en_US'
        timeZone:
          type: string
          description: The time zone of the spreadsheet (CLDR format)
          example: 'America/New_York'
        autoRecalc:
          type: string
          enum: [ON_CHANGE, MINUTE, HOUR]
          description: The amount of time to wait before volatile functions are recalculated

    Sheet:
      type: object
      properties:
        properties:
          $ref: '#/components/schemas/SheetProperties'

    SheetProperties:
      type: object
      properties:
        sheetId:
          type: integer
          description: The ID of the sheet. Must be non-negative
        title:
          type: string
          description: The name of the sheet
          example: 'Sheet1'
        index:
          type: integer
          description: The index of the sheet within the spreadsheet (0-indexed)
        sheetType:
          type: string
          enum: [GRID, OBJECT, DATA_SOURCE]
          description: The type of sheet
        hidden:
          type: boolean
          description: True if the sheet is hidden in the UI
        gridProperties:
          type: object
          description: Grid-specific properties
          properties:
            rowCount:
              type: integer
              description: The number of rows in the grid
            columnCount:
              type: integer
              description: The number of columns in the grid
            frozenRowCount:
              type: integer
              description: The number of rows that are frozen
            frozenColumnCount:
              type: integer
              description: The number of columns that are frozen

    ValueRange:
      type: object
      description: Data within a range of the spreadsheet
      properties:
        range:
          type: string
          description: The range the values cover, in A1 notation
          example: 'Sheet1!A1:B2'
        majorDimension:
          type: string
          enum: [ROWS, COLUMNS]
          description: The major dimension of the values
        values:
          type: array
          description: The data that was read or to be written
          items:
            type: array
            items:
              description: A cell value (string, number, boolean, or empty)

    BatchUpdateRequest:
      type: object
      properties:
        requests:
          type: array
          description: A list of updates to apply to the spreadsheet
          items:
            type: object
            description: A single kind of update to apply to a spreadsheet
        includeSpreadsheetInResponse:
          type: boolean
          description: Determines if the update response should include the spreadsheet resource
      required: [requests]

    BatchUpdateResponse:
      type: object
      properties:
        spreadsheetId:
          type: string
        replies:
          type: array
          items:
            type: object
        updatedSpreadsheet:
          $ref: '#/components/schemas/Spreadsheet'

    UpdateValuesResponse:
      type: object
      properties:
        spreadsheetId:
          type: string
        updatedRange:
          type: string
        updatedRows:
          type: integer
        updatedColumns:
          type: integer
        updatedCells:
          type: integer

    AppendValuesResponse:
      type: object
      properties:
        spreadsheetId:
          type: string
        tableRange:
          type: string
          description: The range (in A1 notation) of the table that values were appended to
        updates:
          $ref: '#/components/schemas/UpdateValuesResponse'

    ClearValuesResponse:
      type: object
      properties:
        spreadsheetId:
          type: string
        clearedRange:
          type: string

    DeveloperMetadata:
      type: object
      properties:
        metadataId:
          type: integer
          description: The spreadsheet-scoped unique ID that identifies the metadata
        metadataKey:
          type: string
          description: The metadata key
        metadataValue:
          type: string
          description: Data associated with the metadata's key
        location:
          type: object
          description: The location where the metadata is associated
        visibility:
          type: string
          enum: [DEVELOPER_METADATA_VISIBILITY_UNSPECIFIED, DOCUMENT, PROJECT]

    Error:
      type: object
      properties:
        code:
          type: integer
          description: HTTP status code
        message:
          type: string
          description: Human-readable error message
        status:
          type: string
          description: Error status

  responses:
    Unauthorized:
      description: Authentication required or access token invalid
      content:
        application/json:
          schema:
            $ref: '#/components/schemas/Error'
    Forbidden:
      description: Insufficient permissions to access this spreadsheet
      content:
        application/json:
          schema:
            $ref: '#/components/schemas/Error'
    NotFound:
      description: Spreadsheet or range not found
      content:
        application/json:
          schema:
            $ref: '#/components/schemas/Error'