GQuery

Yet another Google Sheets ORM for Apps Script, supporting advanced features like joins, Query Visualization Language, and the Advanced Sheet Service. Inspired by vlucas' sheetquery.

Index

• Ways to Install
  • (Recommended) As a NPM Package
  • As an Apps Script Library
  • As a Standalone Script
• Type-Safe Queries with Standard Schema
  • Type Inference Only
  • Runtime Validation
  • Handling Validation Errors
• Usage
  • Using GET
  • Using GET MANY
  • Using QUERY
  • Using UPDATE
  • Using APPEND
  • Using DELETE
  • Using JOINS
• Benchmarking Performance

Ways to Install

Setting Your Project Up

To use GQuery, you must first enable the Google Sheets API in your Apps Script project. To do this:

1. Go to your project editor and click on the plus button next to "Services"
2. Find "Google Sheets API" in the list and click on it to add it to your project.
3. Leave the version as-is and the identifier as-is, then click Add.

(Recommended) As a NPM Package

If you use a build toolchain in your Apps Script project, like Rollup or Vite, this is the preferred installation method.

• To install via the command line: npm install @imreallyliam/gquery
• To add in your package.json dependencies: "@imreallyliam/gquery": "^1.5.2"

You'll call the GQuery class via new GQuery()

As an Apps Script Library

For traditional Apps Script projects, it is possible to import the Apps Script code as a library.

1. Go to your project editor and press the plus button next to "Libraries"
2. Enter the following script ID: 1UqTjUrX6rnMMzbYJPJRPk3cmLCYc7n7FZwZq6Q7gG-j3rTqj15LC953B Then press Look up
3. Select a version, generally you will want to choose the latest pinned release. (The highest number that isn't HEAD) The development branch can sometimes be unstable.
4. Change the identifier if desired, press Add.

You'll call the GQuery class via new GQuery.GQuery() (The first GQuery is your identifier)

As a Standalone Script

You can also copy and paste the code from dist/bundle.global.js directly into your Apps Script project as a standalone script file. It is recommended to go to a tag release and copy from there to ensure stability. (ex. v1.5.0) The file type does not matter and can be placed in a .gs file without issue.

You'll call the GQuery class via new GQuery.GQuery() (The first GQuery is your identifier)

Type-Safe Queries with Standard Schema

GQuery supports Standard Schema — a common interface implemented by popular schema libraries like Zod, Valibot, and ArkType. Passing a schema to .from() gives you fully typed rows across all operations without adding any runtime dependency to GQuery itself.

Note: Standard Schema support requires a TypeScript build toolchain (e.g. the NPM package install method). It has no effect in plain .gs files.

Type Inference Only

Pass your schema as the second argument to .from(). GQuery uses the schema's output type to type all rows returned by .get(), .update(), and .append() — with no runtime cost. The .where() filter function and .update() callback are also typed automatically.

import { z } from "zod";

const EmployeeSchema = z.object({
  Name: z.string(),
  Email: z.string().email(),
  Department: z.string(),
  Active: z.boolean(),
  StartDate: z.date(),
});

const gq = new GQuery("your-spreadsheet-id");

// result.rows is typed as GQueryRow<{ Name: string; Email: string; ... }>[]
const result = gq
  .from("Employees", EmployeeSchema)
  .where((row) => row.Active) // row.Active is typed as boolean
  .get();

// TypeScript will error if the wrong shape is passed
gq.from("Employees", EmployeeSchema).append({
  Name: "Alice",
  Email: "alice@example.com",
  Department: "Engineering",
  Active: true,
  StartDate: new Date(),
});

You can also specify a type manually without a schema using the generic type parameter — this is a compile-time assertion only and performs no validation:

type EmployeeRow = { Name: string; Department: string; Active: boolean };

const result = gq.from<EmployeeRow>("Employees").get();
// result.rows is GQueryRow<EmployeeRow>[]

Runtime Validation

By default, the schema is used purely for TypeScript types. To also validate each row at runtime, pass validate: true to .get(), .update(), or .append(). GQuery will run each row through the schema's validate() function and throw a GQuerySchemaError if any row fails.

// Validates every row returned from the sheet against EmployeeSchema
const result = gq.from("Employees", EmployeeSchema).get({ validate: true });

// Validates each item before writing to the sheet
gq.from("Employees", EmployeeSchema).append(
  {
    Name: "Bob",
    Email: "not-an-email",
    Department: "Design",
    Active: true,
    StartDate: new Date(),
  },
  { validate: true }, // throws GQuerySchemaError — Email fails .email()
);

Google Apps Script limitation: Only synchronous schema validation is supported. Zod and Valibot both validate synchronously by default. If a schema's validate() returns a Promise, GQuery will throw immediately.

Handling Validation Errors

GQuerySchemaError extends Error and exposes the full list of issues from the schema library, plus the raw row that failed.

import { GQuerySchemaError } from "@fcps-tssc/GQuery";

try {
  const result = gq.from("Employees", EmployeeSchema).get({ validate: true });
} catch (e) {
  if (e instanceof GQuerySchemaError) {
    console.error("Validation failed:", e.message);
    // e.issues — ReadonlyArray<{ message: string; path?: ... }>
    // e.row    — the raw row object that failed
    e.issues.forEach((issue) => console.error(issue.message));
  }
}

---

Usage

This chart shows a quick overview of the different functions GQuery offers.

| Function | Description | .from() | .select() | .where() | .join() | | :-------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-----: | :-------: | :------: | :-----: | | GET | Retrieve row(s) in a single sheet. | Y | Y | Y | Y | | GET MANY | Retrieve rows from multiple sheets. | N | N | N | N | | QUERY | Retrieve data from a single sheet via the Google's Query Visualization Language. | Y | Y | Y | Y | | UPDATE | Update rows in a single sheet. | Y | Y | Y | Y | | APPEND | Add rows to a single sheet. | Y | Y | Y | Y | | DELETE | Delete rows from a single sheet. | Y | Y | Y | Y |

Modifier Functions

| Function | Description | | -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | FROM | Used to select the target sheet for all following queries. Optionally accepts a Standard Schema for typed results. Returns a GQueryTable. | | SELECT | Used to select specific columns to return. Returns a GQueryTableFactory. | | WHERE | Used to filter rows based on a condition. Returns a GQueryTableFactory. | | JOIN | Used to join with another sheet based on the sheet's column, a join column, and allows a selection of different columns to return. Returns a GQueryTableFactory. |

Using GET

Using GET requires that you first specify a sheet to query with the .from() function, then you can optionally specify columns to return with .select() and filter rows with .where(). Optionally, .join() can be used to include columns from other sheets. Finally, you call .get() to execute t