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".

{
    "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).

{
    "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".

{
    "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.

[
    {
        "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.