# GASsma

> GASsma is a Google Apps Script (GAS) spreadsheet library that provides Prisma-like syntax for treating Google Sheets as databases. It enables CRUD operations, filtering, sorting, aggregation, and advanced querying on spreadsheet data using familiar TypeScript/JavaScript methods.

GASsma transforms Google Sheets into powerful data storage solutions without requiring traditional database setup. The library offers type-safe operations with comprehensive error handling and supports both Google Apps Script environments and local development with Clasp.

**Core Features:**
- Prisma-inspired API with TypeScript support
- Complete CRUD operations (Create, Read, Update, Delete)
- Advanced filtering with logical operators (AND, OR, NOT)
- Data aggregation and grouping capabilities
- Flexible querying with select, omit, orderBy, pagination
- Automatic type inference and validation
- Support for multiple data types (string, number, boolean, Date, null)
- **Relations**: oneToMany, oneToOne, manyToOne, manyToMany with include and where filters
- **Nested Write**: create/connect/connectOrCreate within create method
- **Nested Write (update)**: update/delete/deleteMany/disconnect/set within update method
- **Referential Actions**: onDelete/onUpdate (Cascade, SetNull, Restrict, NoAction)
- **Atomic Number Operations**: increment/decrement/multiply/divide for update data
- **Case-insensitive filtering**: mode: "insensitive" for equals/not/contains/startsWith/endsWith
- **OrderBy nulls control**: { sort: "asc", nulls: "first" | "last" } syntax
- **Relation counting**: _count support in include and select
- **limit option**: limit parameter for updateMany/deleteMany
- **Negative take**: take negative values to get records from end
- **Relation orderBy**: sort by manyToOne/oneToOne fields or _count
- **Cursor pagination**: cursor-based pagination in findMany, count, aggregate
- **Column-to-column comparison**: fields property (FieldRef) for comparing columns in where
- **Global omit**: default field exclusion per sheet via GassmaClient constructor
- **defaults**: auto-set field values on create (static values or functions)
- **updatedAt**: auto-timestamp on create/update
- **ignore**: complete field exclusion from all operations (read, write, where)
- **ignoreSheets**: complete sheet exclusion from gassma client
- **Select relation options**: specify select/where/orderBy/include on relation fields within select
- **map**: field name mapping (code name ↔ spreadsheet header)
- **mapSheets**: sheet name mapping (code name ↔ spreadsheet sheet name)
- **autoincrement**: auto-increment using LockService + PropertiesService (GAS only)
- **CLI tools**: generate (--watch), init, validate, format, version commands
- **gassma.config.ts**: configuration file with defineConfig/satisfies GassmaConfig, env() helper, schema path and datasource.url
- **datasource block**: schema-level datasource url for auto spreadsheet ID embedding
- **enum @map**: enum member name mapping for spreadsheet values

**Installation:** `npm i gassma`
**Script ID for GAS:** `1ZVuWMUYs4hVKDCcP3nVw74AY48VqLm50wRceKIQLFKL0wf4Hyou-FIBH`
**Current Version:** 1.0.0

## Client Initialization

```typescript
import { Gassma } from "gassma";

// Initialize with specific spreadsheet ID
const gassma = new Gassma.GassmaClient("your-spreadsheet-id");

// Or use active spreadsheet in GAS environment
const gassma = new Gassma.GassmaClient();

// With options
const gassma = new Gassma.GassmaClient({
  relations: {
    Users: {
      posts: { type: "oneToMany", to: "Posts", field: "id", reference: "authorId" }
    }
  },
  omit: { Users: { password: true } },         // Global omit
  defaults: { Users: { role: "USER" } },        // Default values on create
  updatedAt: { Users: "updatedAt" },            // Auto-timestamp
  ignore: { Users: ["secret"] },                // Exclude field entirely
  ignoreSheets: ["Logs"],                       // Exclude sheet entirely
  map: { Users: { firstName: "名前" } },        // Field name mapping
  mapSheets: { Users: "ユーザー一覧" },          // Sheet name mapping
  autoincrement: { Users: "id" },               // Auto-increment
});

// Access specific sheet (direct property access)
const userSheet = gassma.Users;

// Configure data range (optional)
userSheet.changeSettings(2, 1, 10); // Start row 2, columns A-J
```

## Core API Methods

### Query Operations

**findFirst(findData: FindData)** - Find single record
```typescript
const user = userSheet.findFirst({
  where: { email: "john@example.com" },
  select: { name: true, age: true }
});
// Returns: Record<string, any> | null
```

**findFirstOrThrow(findData: FindData)** - Find single record or throw
```typescript
const user = userSheet.findFirstOrThrow({
  where: { email: "john@example.com" }
});
// Returns: Record<string, any>
// Throws NotFoundError if no record found
```

**Note**: findFirst does not support `take`, `skip`, or `distinct` (use findMany for those). `findMany`'s `take` accepts negative values to get records from the end. `skip` with negative values throws `GassmaSkipNegativeError`.

**findMany(findData: FindData)** - Find multiple records
```typescript
const adults = userSheet.findMany({
  where: { age: { gte: 18 } },
  orderBy: { name: "asc" },
  take: 10,
  skip: 0
});
// Returns: Record<string, any>[]
```

### Data Manipulation

**create(createData: CreateData)** - Create single record
```typescript
const newUser = userSheet.create({
  data: { name: "John Doe", age: 25, email: "john@example.com" },
  select: { name: true, age: true },  // Optional: shape returned fields
  // omit: { email: true },           // Or exclude fields (cannot use with select)
  // include: { posts: true },        // Optional: include related records
});
```

**createMany(createManyData: CreateManyData)** - Create multiple records
```typescript
const result = userSheet.createMany({
  data: [
    { name: "Alice", age: 30 },
    { name: "Bob", age: 25 }
  ]
});
// Returns: { count: number }
```

**createManyAndReturn(createManyData: CreateManyAndReturnData)** - Create multiple records and return them
```typescript
const result = userSheet.createManyAndReturn({
  data: [
    { name: "Alice", age: 30 },
    { name: "Bob", age: 25 }
  ],
  // select: { name: true },       // Optional
  // omit: { email: true },        // Optional
  // include: { posts: true },     // Optional
});
// Returns: Record<string, any>[]
```

**update(updateData: UpdateData)** - Update single record
```typescript
const result = userSheet.update({
  where: { name: "John" },
  data: { salary: 80000 },
  select: { name: true, salary: true },  // Optional
  // omit: { password: true },           // Or exclude fields
  // include: { posts: true },           // Optional: include related records
});
// Returns: Record<string, any> | null
```

**updateMany(updateData: UpdateData)** - Update records
```typescript
const result = userSheet.updateMany({
  where: { department: "Engineering" },
  data: { salary: 80000 },
  limit: 10  // Optional: limit affected records
});
// Returns: { count: number }
```

**updateManyAndReturn(updateData: UpdateData)** - Update records and return them
```typescript
const result = userSheet.updateManyAndReturn({
  where: { department: "Engineering" },
  data: { salary: 80000 }
});
// Returns: Record<string, any>[]
```

**upsert(upsertData: UpsertData)** - Update or create single record
```typescript
const result = userSheet.upsert({
  where: { email: "john@example.com" },
  update: { lastLogin: new Date() },
  create: { name: "John", email: "john@example.com", age: 25 }
});
// Returns: Record<string, any>
```

**delete(deleteData: DeleteData)** - Delete single record
```typescript
const result = userSheet.delete({
  where: { name: "John" },
  select: { name: true, email: true }
});
// Returns: Record<string, any> | null
```

**deleteMany(deleteData: DeleteData)** - Delete records
```typescript
const result = userSheet.deleteMany({
  where: { active: false },
  limit: 5  // Optional: limit affected records
});
// Returns: { count: number }
```

### Analytics and Aggregation

**count(countData: CountData)** - Count records
```typescript
const totalUsers = userSheet.count({
  where: { active: true }
});
// Returns: number
```

**aggregate(aggregateData: AggregateData)** - Perform aggregations
```typescript
const stats = userSheet.aggregate({
  where: { department: "Engineering" },
  _avg: { salary: true },
  _max: { age: true },
  _min: { age: true },
  _count: { id: true }
});
// Returns: { _avg: {...}, _max: {...}, _min: {...}, _count: {...} }
```

**groupBy(groupByData: GroupByData)** - Group and aggregate
```typescript
const departmentStats = userSheet.groupBy({
  by: ["department"],
  _count: { id: true },
  _avg: { salary: true },
  having: { _count: { id: { gte: 5 } } }
});
// Returns: Record<string, any>[]
```

## Query Options

### Where Conditions
```typescript
// Basic equality
where: { name: "John", age: 25 }

// Filter conditions
where: {
  age: { gte: 18, lt: 65 },
  name: { contains: "John" },
  email: { endsWith: "@company.com" },
  status: { in: ["active", "pending"] }
}

// Logical operators
where: {
  OR: [
    { department: "Engineering" },
    { department: "Design" }
  ],
  AND: [
    { age: { gte: 18 } },
    { active: true }
  ],
  NOT: { status: "deleted" }
}
```

### Filter Conditions
- `equals`: Exact match
- `not`: Not equal
- `in`: Value in array
- `notIn`: Value not in array
- `lt`, `lte`: Less than (or equal)
- `gt`, `gte`: Greater than (or equal)
- `contains`: String contains substring
- `startsWith`: String starts with
- `endsWith`: String ends with
- `mode`: `"insensitive"` for case-insensitive matching (works with equals/not/contains/startsWith/endsWith)
- Filter values can use `FieldRef` for column-to-column comparison (equals/lt/lte/gt/gte/contains/startsWith/endsWith)

### Column-to-Column Comparison (fields)
```typescript
// Compare columns within the same row using controller.fields
userSheet.findMany({
  where: { firstName: { equals: userSheet.fields.lastName } }
});

// With comparison operators
userSheet.findMany({
  where: { age: { lt: userSheet.fields.maxAge } }
});
```

### Select and Omit
```typescript
// Select specific fields
select: { name: true, email: true }

// Select with relation options (like include inside select)
select: {
  id: true,
  name: true,
  posts: {                          // Relation field with options
    select: { title: true },
    where: { published: true },
    orderBy: { createdAt: "desc" },
  },
  _count: true,                     // Count all relations
}

// Omit specific fields (cannot use with select)
omit: { password: true, internalId: true }

// Global omit (per-sheet default exclusion)
const gassma = new Gassma.GassmaClient({
  omit: { Users: { password: true } }
});
// Override global omit with omit: { password: false }
// select overrides global omit entirely
```

### Ordering and Pagination
```typescript
// Single field ordering
orderBy: { name: "asc" }

// Multiple field ordering
orderBy: [
  { department: "asc" },
  { salary: "desc" }
]

// Null positioning control
orderBy: { age: { sort: "asc", nulls: "last" } }

// Relation field ordering (manyToOne/oneToOne only)
orderBy: { author: { name: "asc" } }

// Relation _count ordering (oneToMany/manyToMany only)
orderBy: { posts: { _count: "desc" } }

// Cursor-based pagination
cursor: { id: 10 },
take: 5,     // 5 records starting from cursor

// Pagination
take: 20,    // Limit results (negative: from end)
skip: 40,    // Offset results
```

### Distinct Values
```typescript
// Get unique values for single field
distinct: "department"

// Get unique combinations
distinct: ["department", "level"]
```

## Data Types

**Supported Types:**
- `string`: Text values
- `number`: Numeric values
- `boolean`: True/false values
- `Date`: Date objects
- `null`: Empty/null values

**Type Definitions:**
```typescript
type GassmaAny = string | number | boolean | Date | null;
type AnyUse = { [key: string]: GassmaAny };
```

## Relations

### Relation Types
- `oneToMany`: One parent has many children (e.g., User → Posts). Include returns array.
- `manyToOne`: Many children belong to one parent (e.g., Post → User). Include returns object or null.
- `oneToOne`: One-to-one relationship (e.g., User → Profile). Include returns object or null.
- `manyToMany`: Many-to-many via junction table (e.g., Post → Tags). Include returns array.

### Include (findMany / findFirst)
```typescript
userSheet.findMany({
  include: {
    posts: true,                    // All related posts
    posts: {                        // With options
      where: { published: true },
      orderBy: { id: "desc" },
      skip: 0, take: 5,
      select: { title: true },
      include: { tags: true },      // Nested include
    },
    _count: true,                   // Count all relations
    _count: {                       // Count specific relations
      select: { posts: true },
      select: { posts: { where: { published: true } } },  // With filter
    },
  }
});
```

### Where Relation Filters (all query methods)
```typescript
// List relations (oneToMany, manyToMany): some, every, none
userSheet.findMany({ where: { posts: { some: { published: true } } } });
userSheet.findMany({ where: { posts: { every: { published: true } } } });
userSheet.findMany({ where: { posts: { none: { published: false } } } });

// Single relations (oneToOne, manyToOne): is, isNot
postSheet.findMany({ where: { author: { is: { name: "Alice" } } } });
postSheet.findMany({ where: { author: { isNot: null } } });
```

### Nested Write (create)
```typescript
// create: Create related record
postSheet.create({
  data: { title: "New", author: { create: { name: "Dave", email: "d@e.com" } } }
});

// connect: Link existing record
postSheet.create({
  data: { title: "New", author: { connect: { name: "Alice" } } }
});

// connectOrCreate: Link if exists, create if not
postSheet.create({
  data: { title: "New", author: { connectOrCreate: { where: { name: "Alice" }, create: { name: "Alice", email: "a@e.com" } } } }
});
```

### Nested Write (update)
```typescript
// update: Update related record
userSheet.update({
  where: { name: "Alice" },
  data: { posts: { update: { where: { id: 1 }, data: { title: "Updated" } } } }
});

// delete: Delete related record
userSheet.update({
  where: { name: "Alice" },
  data: { posts: { delete: { id: 3 } } }
});

// disconnect: Remove association (set FK to null)
postSheet.update({
  where: { id: 1 },
  data: { author: { disconnect: true } }
});

// set: Replace all associations
userSheet.update({
  where: { name: "Alice" },
  data: { posts: { set: [{ id: 1 }, { id: 2 }] } }
});
```

### Atomic Number Operations (update data)
```typescript
// increment, decrement, multiply, divide
userSheet.update({
  where: { name: "Alice" },
  data: { age: { increment: 1 } }
});
```

### onDelete (deleteMany) / onUpdate (update)
```typescript
// Cascade: Delete/update related records
// SetNull: Set FK to null
// Restrict: Error if related records exist
// NoAction: Do nothing (default)
relations: {
  Users: {
    posts: { type: "oneToMany", to: "Posts", field: "id", reference: "authorId", onDelete: "Cascade", onUpdate: "Cascade" }
  }
}
```

## Error Handling

**GassmaFindSelectOmitConflictError**: Thrown when both `select` and `omit` are used in the same query.

```typescript
// This will throw an error
userSheet.findMany({
  select: { name: true },
  omit: { email: true }  // Cannot use both select and omit
});
```

## Configuration

**changeSettings(startRowNumber, startColumnValue, endColumnValue)**
Configure the data range for a sheet:
```typescript
// Start from row 2, use columns A through J
userSheet.changeSettings(2, 1, 10);

// Start from row 3, use columns B through F
userSheet.changeSettings(3, "B", "F");
```

## Use Cases

1. **Simple Data Storage**: Replace complex database setups for prototypes and small applications
2. **Data Analysis**: Perform statistical analysis on spreadsheet data with familiar syntax
3. **Content Management**: Manage content, configurations, or user data in Google Sheets
4. **Reporting**: Generate reports and dashboards from spreadsheet data
5. **Automation**: Build Google Apps Script automations with powerful data querying
6. **Integration**: Bridge Google Sheets with web applications and APIs

## Environment Support

- **Google Apps Script**: Native support with script editor or Clasp
- **TypeScript**: Full type safety and autocompletion
- **JavaScript**: Works with vanilla JavaScript projects
- **Node.js**: Development and testing with Clasp

## Documentation

- [Official Reference](https://akahoshi1421.github.io/gassma-reference/): Complete API documentation
- [GitHub Repository](https://github.com/akahoshi1421/gassma): Source code and examples
- [Japanese README](docs/README.ja.md): Japanese language documentation