Skip to main content

Retrieve Data

Retrieve data from Google Spreadsheets with flexible options. Supports three operation modes: single range retrieval, multiple ranges (batch), and spreadsheet metadata.

Overview​

The Retrieve Data tool is a comprehensive solution for reading data from Google Sheets. It supports:

  • Three operation modes: Single range, multiple ranges (batch), spreadsheet metadata
  • Flexible response formats: Raw arrays, enriched with metadata, or structured objects
  • All value render options: Formatted values, unformatted values, or formulas
  • Robust validation: Spreadsheet verification, range validation, error handling

Prerequisites​

  • A connected Google Account with OAuth authentication
  • Google Sheets API enabled in your Google Cloud project
  • Setup Guide

Parameters​

Core Parameters​

ParameterTypeRequiredDescription
spreadsheet_idstringYesGoogle Spreadsheet ID from URL
operation_modestringYesOperation mode: single_range, multiple_ranges, or metadata

Mode-Specific Parameters​

ParameterTypeRequiredDescription
rangestringYes*A1 notation range (*required for single_range mode)
rangesarray[string]Yes**Array of A1 notation ranges (**required for multiple_ranges mode)
sheet_namestringNoDefault sheet name when range lacks sheet prefix

Response Format Parameters​

ParameterTypeDefaultDescription
value_render_optionstringFORMATTED_VALUEHow values are rendered: FORMATTED_VALUE, UNFORMATTED_VALUE, or FORMULA
response_formatstringwith_metadataResponse format: raw_array, with_metadata, or objects
include_empty_rowsbooleantrueWhether to include completely empty rows
date_time_render_optionstringFORMATTED_STRINGDate/time format: SERIAL_NUMBER or FORMATTED_STRING

Metadata Mode Parameters​

ParameterTypeDefaultDescription
include_grid_databooleanfalseFor metadata mode: include cell data (makes response larger)

Usage Examples​

Example 1: Basic Single Range (Formatted Values)​

Read a simple range with default formatting.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "single_range",
    "range": "Sheet1!A1:C5"
  }
}

Response:

{
  "success": true,
  "message": "Data retrieved successfully",
  "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
  "spreadsheet_url": "https://docs.google.com/spreadsheets/d/...",
  "operation": "single_range",
  "range": "Sheet1!A1:C5",
  "values": [
    ["Name", "Email", "Status"],
    ["Alice", "alice@example.com", "Active"],
    ["Bob", "bob@example.com", "Inactive"]
  ],
  "row_count": 3,
  "column_count": 3,
  "cell_count": 9,
  "value_render_option": "FORMATTED_VALUE"
}

Example 2: Single Range with Unformatted Values​

Get raw, unformatted values (useful for numbers, dates).

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "single_range",
    "range": "Sales!A1:C10",
    "value_render_option": "UNFORMATTED_VALUE"
  }
}

Example 3: Single Range with Formulas​

Retrieve formulas instead of calculated values.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "single_range",
    "range": "Calculations!D1:D10",
    "value_render_option": "FORMULA"
  }
}

Response will include formulas:

{
  "values": [
    ["Total"],
    ["=SUM(A1:C1)"],
    ["=SUM(A2:C2)"]
  ]
}

Example 4: Multiple Ranges (Batch Read)​

Efficiently read multiple non-contiguous ranges in one call.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "multiple_ranges",
    "ranges": [
      "Sales!A1:B5",
      "Summary!D1:E3",
      "Metrics!G10:H15"
    ],
    "value_render_option": "FORMATTED_VALUE"
  }
}

Response:

{
  "success": true,
  "message": "Data retrieved successfully",
  "operation": "multiple_ranges",
  "ranges_data": [
    {
      "range": "Sales!A1:B5",
      "values": [["Date", "Amount"], ["2025-01-01", "$1,000"]],
      "row_count": 2,
      "column_count": 2
    },
    {
      "range": "Summary!D1:E3",
      "values": [["Metric", "Value"], ["Total", "$5,000"]],
      "row_count": 2,
      "column_count": 2
    }
  ],
  "total_ranges": 2
}

Example 5: Retrieve Spreadsheet Metadata​

Get list of sheets, dimensions, and properties without reading cell data.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "metadata"
  }
}

Response:

{
  "success": true,
  "message": "Metadata retrieved successfully",
  "operation": "metadata",
  "title": "Team Dashboard",
  "locale": "en_US",
  "timezone": "America/Los_Angeles",
  "sheets": [
    {
      "sheet_id": 0,
      "title": "Sales",
      "index": 0,
      "row_count": 1000,
      "column_count": 26,
      "frozen_row_count": 1
    },
    {
      "sheet_id": 123456,
      "title": "Summary",
      "index": 1,
      "row_count": 500,
      "column_count": 10
    }
  ],
  "sheet_count": 2
}

Example 6: Objects Format (Headers as Keys)​

Convert rows to objects using first row as keys.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "single_range",
    "range": "Users!A1:D4",
    "response_format": "objects"
  }
}

Response:

{
  "success": true,
  "message": "Data retrieved successfully",
  "operation": "single_range",
  "range": "Users!A1:D4",
  "headers": ["Name", "Email", "Age", "City"],
  "data": [
    {"Name": "Alice", "Email": "alice@example.com", "Age": "30", "City": "NYC"},
    {"Name": "Bob", "Email": "bob@example.com", "Age": "25", "City": "SF"},
    {"Name": "Carol", "Email": "carol@example.com", "Age": "35", "City": "LA"}
  ],
  "row_count": 4,
  "data_row_count": 3
}

Example 7: Raw Array Format (Simple Output)​

Get minimal response with just the 2D array.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "single_range",
    "range": "Data!A1:B3",
    "response_format": "raw_array"
  }
}

Response:

{
  "success": true,
  "message": "Data retrieved successfully",
  "values": [
    ["Header1", "Header2"],
    ["Value1", "Value2"],
    ["Value3", "Value4"]
  ]
}

Example 8: Filter Empty Rows​

Exclude completely empty rows from results.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "single_range",
    "range": "Data!A1:C20",
    "include_empty_rows": false
  }
}

Example 9: Use Sheet Name Default​

Provide range without sheet prefix, use sheet_name parameter.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "single_range",
    "range": "A1:D10",
    "sheet_name": "January 2025"
  }
}

Example 10: Date/Time as Serial Numbers​

Get dates and times as numeric serial numbers.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "single_range",
    "range": "Logs!A1:C100",
    "date_time_render_option": "SERIAL_NUMBER"
  }
}

Example 11: Multiple Ranges with Objects Format​

Batch read multiple ranges, each converted to objects.

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "multiple_ranges",
    "ranges": ["Users!A1:C10", "Products!A1:D20"],
    "response_format": "objects"
  }
}

Example 12: Metadata with Grid Data​

Get metadata including actual cell values (large response).

{
  "tool": "google_sheets_Retrieve_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "metadata",
    "include_grid_data": true
  }
}

Operation Modes Explained​

Single Range Mode​

Purpose: Read data from one specific range

When to use:

  • Reading a table of data
  • Getting specific cell values
  • Extracting a report section
  • Reading form responses

Parameters:

  • Required: range (A1 notation)
  • Optional: sheet_name (used when range lacks sheet prefix)

Example flow:

  1. Specify spreadsheet ID and range
  2. Tool fetches data from Google Sheets API
  3. Returns data in chosen format

Tip: Include sheet name in range (e.g., Sheet1!A1:D10) or use sheet_name parameter for cleaner code.

Multiple Ranges Mode​

Purpose: Read data from multiple non-contiguous ranges in one API call (efficient batch operation)

When to use:

  • Reading headers and data separately
  • Getting data from multiple sheets
  • Extracting scattered data points efficiently
  • Combining data from different areas

Parameters:

  • Required: ranges (array of A1 notation ranges)
  • Optional: sheet_name (default for ranges without sheet prefix)

Example flow:

  1. Specify array of ranges
  2. Tool makes single batch API call
  3. Returns array of results, one per range

Tip: Much more efficient than multiple single-range calls. Use this when you need data from 2+ locations.

Metadata Mode​

Purpose: Get spreadsheet structure and properties without reading cell data

When to use:

  • Discovering available sheets before reading
  • Validating sheet names exist
  • Getting spreadsheet structure info
  • Checking dimensions before operations

Returns:

  • Spreadsheet title, locale, timezone
  • List of all sheets with dimensions
  • Sheet IDs, indices, frozen rows/columns
  • Optionally: grid data (if include_grid_data: true)

Example flow:

  1. Request metadata
  2. Tool fetches spreadsheet properties
  3. Returns structured sheet information

Tip: Use this first to discover sheet names, then use single_range or multiple_ranges to read actual data.

Response Format Options​

raw_array (Simple)​

Returns: Just the 2D array of values

Best for:

  • Minimal response size
  • When you only need the data
  • Piping to other systems
  • Simple data extraction

Example:

{
  "success": true,
  "values": [["A1", "B1"], ["A2", "B2"]]
}

Returns: 2D array plus metadata (range, counts, etc.)

Best for:

  • Most use cases (good balance)
  • When you need context about the data
  • Debugging and verification
  • Logging and auditing

Example:

{
  "success": true,
  "spreadsheet_id": "...",
  "spreadsheet_url": "...",
  "range": "Sheet1!A1:C5",
  "values": [[...]],
  "row_count": 5,
  "column_count": 3,
  "cell_count": 15
}

objects (Structured)​

Returns: First row as headers, remaining rows as objects

Best for:

  • Working with structured data
  • JSON APIs
  • When you need key-value pairs
  • Database-like access patterns

Example:

{
  "success": true,
  "headers": ["Name", "Age"],
  "data": [
    {"Name": "Alice", "Age": "30"},
    {"Name": "Bob", "Age": "25"}
  ]
}

Requirement: First row must contain headers.

Value Render Options​

FORMATTED_VALUE (Default)​

Returns: Values as they appear in the Google Sheets UI

Best for:

  • Most use cases
  • When you want human-readable data
  • Dates formatted as text
  • Currency with symbols ($1,000)
  • Percentages (50%)

Example: $1,234.56, 01/15/2025, 50%

UNFORMATTED_VALUE​

Returns: Raw underlying values without formatting

Best for:

  • Programmatic processing
  • Calculations
  • Raw numeric values
  • Date serial numbers (if not using date_time_render_option)

Example: 1234.56, 45678 (date serial), 0.5 (percentage as decimal)

FORMULA​

Returns: Formulas instead of calculated values

Best for:

  • Auditing calculations
  • Copying formula logic
  • Understanding sheet structure
  • Debugging formulas

Example: =SUM(A1:A10), =IF(B1>10,"High","Low")

Note: Cells without formulas return their values normally.

Common Use Cases​

1. Read Table Data​

Scenario: Read a complete data table for processing

Workflow:

  1. Use single_range mode with table range
  2. Use response_format: "objects" for structured access
  3. Process each row as an object
{
  "operation_mode": "single_range",
  "range": "Customers!A1:E100",
  "response_format": "objects"
}

2. Dashboard Data Collection​

Scenario: Collect metrics from multiple sheet locations

Workflow:

  1. Use multiple_ranges mode
  2. Specify all metric ranges
  3. Combine results in your application
{
  "operation_mode": "multiple_ranges",
  "ranges": [
    "Summary!B2",
    "Sales!D1:D12",
    "Metrics!A5:C5"
  ]
}

3. Discover Available Sheets​

Scenario: List all sheets before reading data

Workflow:

  1. Use metadata mode to get sheet list
  2. Parse sheet names
  3. Use sheet names in subsequent read operations
{
  "operation_mode": "metadata"
}

4. Export for Backup​

Scenario: Export sheet data for backup/archival

Workflow:

  1. Use single_range with full sheet range
  2. Use value_render_option: "UNFORMATTED_VALUE" for raw data
  3. Store in your backup system

5. Form Response Processing​

Scenario: Read and process form responses

Workflow:

  1. Use single_range with response range
  2. Use response_format: "objects" for easy processing
  3. Filter empty rows with include_empty_rows: false

6. Data Validation​

Scenario: Check if data exists before operation

Workflow:

  1. Use single_range to read target range
  2. Check if values array is empty
  3. Proceed with operation or notify user

7. Multi-Sheet Report Aggregation​

Scenario: Combine data from multiple sheets

Workflow:

  1. Use metadata to discover sheets
  2. Use multiple_ranges to read from each sheet
  3. Aggregate data in your application

8. Formula Audit​

Scenario: Review and document formulas

Workflow:

  1. Use value_render_option: "FORMULA"
  2. Read calculation ranges
  3. Document or analyze formula logic

9. Date-Based Filtering​

Scenario: Read data with specific date range

Workflow:

  1. Use date_time_render_option: "SERIAL_NUMBER"
  2. Filter by numeric date comparison
  3. Process matching rows

10. API Data Integration​

Scenario: Provide Google Sheets data via API

Workflow:

  1. Use response_format: "objects"
  2. Return directly as JSON API response
  3. Client receives clean key-value data

Troubleshooting​

"Spreadsheet not found" Error​

Cause: Invalid spreadsheet ID or no access permission

Solutions:

  • Verify spreadsheet ID from URL
  • Check spreadsheet is shared with your Google account
  • Ensure spreadsheet hasn't been deleted
  • Confirm OAuth account has access

"Sheet not found" Error​

Cause: Sheet name doesn't exist

Solutions:

  • Use metadata mode to list available sheets
  • Check sheet name spelling (case-sensitive)
  • Verify sheet hasn't been renamed or deleted

"Invalid range format" Error​

Cause: Range not in valid A1 notation

Solutions:

  • Use format: A1:D10 or Sheet1!A1:D10
  • Don't use row/column numbers (use letters for columns)
  • Verify no typos in range syntax

"Google Sheets API is not enabled" Error​

Cause: API not activated in Google Cloud project

Solutions:

  1. Go to Google Cloud Console
  2. Select your project
  3. Click "Enable" button
  4. Wait a moment for activation
  5. Try again

See: Setup Guide

"Authentication failed" Error​

Cause: OAuth token expired or revoked

Solutions:

  • Disconnect and reconnect your Google account
  • Verify credentials are still active
  • Check account hasn't revoked permissions

Empty Response​

Cause: Range contains no data or only empty cells

Solutions:

  • Verify range actually contains data in Google Sheets
  • Check if include_empty_rows: false is filtering all rows
  • Confirm you're reading the correct sheet/range

"Cannot use 'objects' response_format with empty data" Error​

Cause: Trying to use objects format with no data

Solutions:

  • Ensure range contains at least header row
  • Use with_metadata or raw_array format instead
  • Verify data exists before using objects format

Best Practices​

1. Use Metadata First for Discovery​

When working with unfamiliar spreadsheets:

  • First call with metadata mode
  • Discover available sheets and dimensions
  • Then read specific ranges

2. Choose Appropriate Response Format​

  • objects: For structured data with headers
  • with_metadata: For most use cases (good balance)
  • raw_array: For minimal responses or piping to other systems

3. Batch Multiple Ranges​

Instead of multiple single-range calls:

  • Use multiple_ranges mode
  • Single API call is faster and more efficient
  • Reduces rate limit consumption

4. Filter Empty Rows for Cleaner Data​

When reading dynamic ranges:

  • Set include_empty_rows: false
  • Removes blank rows from response
  • Cleaner data for processing

5. Use Appropriate Value Render Option​

  • FORMATTED_VALUE: For display to users
  • UNFORMATTED_VALUE: For calculations
  • FORMULA: For auditing/debugging

6. Handle Empty Data Gracefully​

Always check if values array is empty:

  • Prevents errors in downstream processing
  • Provide user-friendly messages
  • Validate data exists before operations

7. Specify Sheet Names Explicitly​

For maintainability:

  • Include sheet name in range: Sales!A1:C10
  • Makes code self-documenting
  • Avoids confusion with default sheet

8. Use Descriptive Range Names​

Instead of: A1:Z100 Use: DataTable!A1:Z100 with context

9. Test with Small Ranges First​

Before reading large datasets:

  • Test with small range (e.g., A1:C5)
  • Verify format and structure
  • Then scale to full range

10. Monitor Response Sizes​

For large datasets:

  • Consider pagination approach
  • Read in chunks if needed
  • Balance between API calls and response size

Credits Cost​

1 credit per operation (regardless of data size or number of ranges)

Cost is the same for:

  • Single range
  • Multiple ranges (batch)
  • Metadata

Support​

Need help? Check our troubleshooting guide or contact support.