# 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:** 8 total users, 0 monthly users, 100.0% 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/platform/actors/running/actors-in-store#pay-per-event ## What's an Apify Actor? Actors are a software tools running on the Apify platform, for all kinds of web data extraction and automation use cases. 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. In JavaScript/TypeScript projects, use official [JavaScript/TypeScript client](https://docs.apify.com/api/client/js.md): ```bash npm install apify-client ``` In Python projects, use official [Python client library](https://docs.apify.com/api/client/python.md): ```bash pip install apify-client ``` In shell scripts, use [Apify CLI](https://docs.apify.com/cli/docs.md): ````bash # MacOS / Linux curl -fsSL https://apify.com/install-cli.sh | bash # Windows irm https://apify.com/install-cli.ps1 | iex ```bash In AI frameworks, you might use the [Apify MCP server](https://docs.apify.com/platform/integrations/mcp.md). If your project is in a different language, use 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 '' with your token const client = new ApifyClient({ 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 '' with your token. client = ApifyClient("") # 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("šŸ’¾ Check your data here: https://console.apify.com/storage/datasets/" + run["defaultDatasetId"]) for item in client.dataset(run["defaultDatasetId"]).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": { "command": "npx", "args": [ "mcp-remote", "https://mcp.apify.com/?tools=xevri/sheets-engine-read-api-basic", "--header", "Authorization: Bearer " ] } } } ``` ## OpenAPI specification ```json { "openapi": "3.0.1", "info": { "title": "Google Sheets Database Engine", "description": "Turn any public Google Sheet into a searchable database. \nStop parsing huge CSVs, use simple MongoDB syntax to extract exactly what you need. \n\nFeaturing smart type handling. \n\nFrontend safe and developer-ready.\n\nUnlimited Sheets .. Unlimited Queries\n\nšŸš€ Run your first query now!", "version": "1.0", "x-build-id": "tW7isZxgGh719erNk" }, "servers": [ { "url": "https://api.apify.com/v2" } ], "paths": { "/acts/xevri~sheets-engine-read-api-basic/run-sync-get-dataset-items": { "post": { "operationId": "run-sync-get-dataset-items-xevri-sheets-engine-read-api-basic", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for its completion, and returns Actor's dataset items in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } }, "/acts/xevri~sheets-engine-read-api-basic/runs": { "post": { "operationId": "runs-sync-xevri-sheets-engine-read-api-basic", "x-openai-isConsequential": false, "summary": "Executes an Actor and returns information about the initiated run in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK", "content": { "application/json": { "schema": { "$ref": "#/components/schemas/runsResponseSchema" } } } } } } }, "/acts/xevri~sheets-engine-read-api-basic/run-sync": { "post": { "operationId": "run-sync-xevri-sheets-engine-read-api-basic", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for completion, and returns the OUTPUT from Key-value store in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } } }, "components": { "schemas": { "inputSchema": { "type": "object", "required": [ "sheetUrl" ], "properties": { "sheetUrl": { "title": "Google Sheet URL", "type": "string", "description": "Public URL of the Google Sheet to read from." }, "sheetName": { "title": "Sheet Name", "type": "string", "description": "Name of the sheet tab to read data from.", "default": "Sheet1" }, "conditions": { "title": "Query Conditions", "type": "object", "description": "MongoDB-style query object to filter the data.", "default": {} } } }, "runsResponseSchema": { "type": "object", "properties": { "data": { "type": "object", "properties": { "id": { "type": "string" }, "actId": { "type": "string" }, "userId": { "type": "string" }, "startedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "finishedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "status": { "type": "string", "example": "READY" }, "meta": { "type": "object", "properties": { "origin": { "type": "string", "example": "API" }, "userAgent": { "type": "string" } } }, "stats": { "type": "object", "properties": { "inputBodyLen": { "type": "integer", "example": 2000 }, "rebootCount": { "type": "integer", "example": 0 }, "restartCount": { "type": "integer", "example": 0 }, "resurrectCount": { "type": "integer", "example": 0 }, "computeUnits": { "type": "integer", "example": 0 } } }, "options": { "type": "object", "properties": { "build": { "type": "string", "example": "latest" }, "timeoutSecs": { "type": "integer", "example": 300 }, "memoryMbytes": { "type": "integer", "example": 1024 }, "diskMbytes": { "type": "integer", "example": 2048 } } }, "buildId": { "type": "string" }, "defaultKeyValueStoreId": { "type": "string" }, "defaultDatasetId": { "type": "string" }, "defaultRequestQueueId": { "type": "string" }, "buildNumber": { "type": "string", "example": "1.0.0" }, "containerUrl": { "type": "string" }, "usage": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "integer", "example": 1 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } }, "usageTotalUsd": { "type": "number", "example": 0.00005 }, "usageUsd": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "number", "example": 0.00005 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } } } } } } } } } ```