# Google Sheets Database Engine (`xevri/sheets-engine-read-api-basic`) Actor

Turn any public Google Sheet into a searchable database.
Stop parsing huge CSVs, use simple MongoDB syntax to extract exactly what you need.

Featuring smart type handling.

Frontend safe and developer-ready.

Unlimited Sheets .. Unlimited Queries

🚀 Run your first query now!

- **URL**: https://apify.com/xevri/sheets-engine-read-api-basic.md
- **Developed by:** [Xevri](https://apify.com/xevri) (community)
- **Categories:** Automation, Developer tools, Integrations
- **Stats:** 9 total users, 0 monthly users, 96.9% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

$0.002 / actor start

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

Actors are web data automations that power AI and operations. They run on the Apify platform to scrape websites, process data, connect APIs, and automate workflows.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.
Actors are written with capital "A".

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.
The best way to integrate Actors is as follows.

- **AI agents and MCP clients** — the [Apify MCP server](https://docs.apify.com/integrations/mcp.md) at `https://mcp.apify.com` (remote, streamable HTTP, OAuth on first use).
- **Agentic workflows and local Actor development** — [Agent Skills](https://apify.com/.well-known/agent-skills/index.json) with the [Apify CLI](https://docs.apify.com/cli/docs.md): `npm install -g apify-cli`, then `apify login`.
- **JavaScript/TypeScript projects** — the official [JS/TS client](https://docs.apify.com/api/client/js/docs.md): `npm install apify-client`.
- **Python projects** — the official [Python client](https://docs.apify.com/api/client/python/docs.md): `pip install apify-client`.
- **Any other language** — the [REST API](https://docs.apify.com/api/v2.md).

For usage examples, see the [API](#api) section below.

For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt).

# README

## Google Sheets Engine - Reader API Basic

A powerful Apify Actor that reads data from **public** Google Sheet using a limited **MongoDB-style** query language. It allows you to filter rows using complex conditions, logical operators, and regex, and robustly handles columns with mixed data types (numbers and text).

### Features

- **MongoDB-Style Syntax**: Use familiar operators like `$eq`, `$gt`, `$lt`, `$ne`, `$in`, `$regex`, `$and`, `$or`, `$not`.
- **Mixed Type Support**: Automatically handles columns containing both text and numbers (e.g., querying `A: 100` matches both numeric `100` and string `"100"`).
- **Complex Filtering**: Combine multiple conditions with nested logical groups.
- **Public Sheets**: Works with any Google Sheet that has "Anyone with the link" access.

### How to Get a Public Sheet URL

1. Open your Google Sheet.
2. Click the **Share** button in the top right corner.
3. Under **General access**, change the setting from **Restricted** to **Anyone with the link**. Make sure to select the 'View' Option.
4. Click **Copy link** and use this URL for the `sheetUrl` input field.

### Input Configuration

The Actor accepts the following input options:

| Field        | Type   | Required | Description                                                                   |
| ------------ | ------ | -------- | ----------------------------------------------------------------------------- |
| `sheetUrl`   | String | Yes      | The full URL of the public Google Sheet.                                      |
| `sheetName`  | String | No       | The name of the tab to read. Defaults to `"Sheet1"`.                          |
| `conditions` | Object | No       | A MongoDB-style query object to filter the data. Defaults to `{}` (read all). |

### Query Syntax Guide

The `conditions` object mirrors MongoDB query syntax. Keys represent **Column Letters** (e.g., "A", "B", "C") of the Google Sheet.

#### Basic Operators

| Operator | Description              | Example                                                                                     |
| -------- | ------------------------ | ------------------------------------------------------------------------------------------- |
| `$eq`    | Equal to                 | `{"A": { "$eq": 100 }}` or simple `{"A": 100}`                                              |
| `$ne`    | Not equal to             | `{"C": { "$ne": "Cancelled" }}`                                                             |
| `$gt`    | Greater than             | `{"D": { "$gt": 50 }}`                                                                      |
| `$gte`   | Greater than or equal    | `{"D": { "$gte": 50 }}`                                                                     |
| `$lt`    | Less than                | `{"E": { "$lt": 10 }}`                                                                      |
| `$lte`   | Less than or equal       | `{"E": { "$lte": 10 }}`                                                                     |
| `$regex` | Regular expression match | `{"B": { "$regex": ".*John.*" }}` (Note: Requires full string match, use `.*` for contains) |
| `$in`    | In a list of values      | `{"C": { "$in": ["Open", "Pending"] }}`                                                     |

#### Logical Operators

| Operator | Description | Example                                                        |
| -------- | ----------- | -------------------------------------------------------------- |
| `$and`   | Logical AND | `{"$and": [{"A": 1}, {"B": 2}]}` (Implicit for top-level keys) |
| `$or`    | Logical OR  | `{"$or": [{"C": "New"}, {"D": {"$gt": 100}}]}`                 |
| `$not`   | Logical NOT | `{"$not": {"C": "Archived"}}`                                  |

#### Mixed Data Type Handling

If a column contains both numbers and text (e.g., some cells are `100` and others are `"100"`), standard Google Queries often fail. This Actor solves this by automatically checking both representations for equality checks:

- Query: `{"A": 100}`
- Effective Logic: `A = 100 OR A = '100'`

### Usage Examples

#### 1. Simple Filtering

Select rows where Column A is 100 and Column B is "Pending".

```json
{
    "sheetUrl": "https://docs.google.com/spreadsheets/d/...",
    "sheetName": "Sheet1",
    "conditions": {
        "A": 100,
        "B": "Pending"
    }
}
```

#### 2. Complex Logic

Select rows where Column D (Price) is > 500 OR (Column C (Status) is "Urgent" AND Column E (Quantity) < 5).

```json
{
    "sheetUrl": "https://docs.google.com/spreadsheets/d/...",
    "conditions": {
        "$or": [
            { "D": { "$gt": 500 } },
            {
                "$and": [{ "C": "Urgent" }, { "E": { "$lt": 5 } }]
            }
        ]
    }
}
```

#### 3. Regex and Lists

Select rows where Column B (Name) starts with "A" or "B" AND Column C (Status) is one of "New", "Open".

```json
{
    "sheetUrl": "https://docs.google.com/spreadsheets/d/...",
    "conditions": {
        "B": { "$regex": "^[AB].*" },
        "C": { "$in": ["New", "Open"] }
    }
}
```

### Sample Output

The Actor returns a JSON object containing the operation status and the array of matching rows. The Actor automaticaly fetches column headers for easy access.

```json
[
    {
        "Order ID": "1001",
        "Name": "John Smith",
        "Email": "smith@test.com",
        "Date": "22-11-2023",
        "Address": "first smith street, canada",
        "Amount": "1200",
        "Payment": "Paid",
        "Order Status": "Shipped"
    }
]
```

### Local Development

1. Clone the repository.
2. Install dependencies: `npm install`.
3. Create `storage/key_value_stores/default/INPUT.json` with your input.
4. Run the actor: `npm start`.

### Known Issues

- **Mixed Data Types Constraint**: While this Actor attempts to handle mixed columns (text vs numbers) by checking multiple equalities, the underlying Google Visualization API enforces strict typing based on the majority data type of a column.
  - *Limitation*: Using string-specific operators (like `$regex` or internal `lower()`) on a column that Google has classified as "Numeric" will cause the query to fail.
  - *Workaround*: Ensure your columns are consistently typed in the source Sheet if you need enabling complex regex filtering. Simple equality checks (`$eq`, `$in`) usually work fine on mixed types thanks to our adapter.
- **Column Names vs Letters**: The Google Visualization API uses column letters (e.g., "A", "B", "C") for querying.
  - *Tip*: Always use column letters in your `conditions` object. You can check the output of a full read (empty conditions) to map your data to column letters if unsure.

### License

Copyright (c) 2024 **Xevri LTD UK**. All Rights Reserved.

Apify is granted a license to run this code on the Apify Platform. See the `LICENSE` file for details.

# Actor input Schema

## `sheetUrl` (type: `string`):

Public URL of the Google Sheet to read from.

## `sheetName` (type: `string`):

Name of the sheet tab to read data from.

## `conditions` (type: `object`):

MongoDB-style query object to filter the data.

## Actor input object example

```json
{
  "sheetUrl": "https://docs.google.com/spreadsheets/d/1be9JWQ6FXEi63lqqVXi3cjcsautNpFmJD9EXt1e39L0/edit?usp=sharding",
  "sheetName": "Orders",
  "conditions": {}
}
```

# Actor output Schema

## `dataset` (type: `string`):

The output dataset will contain a list of objects representing the rows from the Google Sheet.

Example:

```json
[
    {
        "Order ID": "1001",
        "Name": "John Smith",
        "Email": "smith@test.com",
        "Date": "22-11-2023",
        "Address": "first smith street, canada",
        "Amount": "1200",
        "Payment": "Paid",
        "Order Status": "Shipped"
    }
]
```

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "sheetUrl": "https://docs.google.com/spreadsheets/d/1be9JWQ6FXEi63lqqVXi3cjcsautNpFmJD9EXt1e39L0/edit?usp=sharding",
    "sheetName": "Orders"
};

// Run the Actor and wait for it to finish
const run = await client.actor("xevri/sheets-engine-read-api-basic").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "sheetUrl": "https://docs.google.com/spreadsheets/d/1be9JWQ6FXEi63lqqVXi3cjcsautNpFmJD9EXt1e39L0/edit?usp=sharding",
    "sheetName": "Orders",
}

# Run the Actor and wait for it to finish
run = client.actor("xevri/sheets-engine-read-api-basic").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "sheetUrl": "https://docs.google.com/spreadsheets/d/1be9JWQ6FXEi63lqqVXi3cjcsautNpFmJD9EXt1e39L0/edit?usp=sharding",
  "sheetName": "Orders"
}' |
apify call xevri/sheets-engine-read-api-basic --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,xevri/sheets-engine-read-api-basic"
        }
    }
}

```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/1ThJuRvMUHlPiRz5B/builds/tW7isZxgGh719erNk/openapi.json