# 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)

**Installation:** `npm i gassma`
**Script ID for GAS:** `1ZVuWMUYs4hVKDCcP3nVw74AY48VqLm50wRceKIQLFKL0wf4Hyou-FIBH`
**Current Version:** 0.8.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();

// Access specific sheet
const userSheet = gassma.sheets.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
```

**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" }
});
```

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

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

**upsertMany(upsertData: UpsertData)** - Update or create
```typescript
const result = userSheet.upsertMany({
  where: { email: "john@example.com" },
  update: { lastLogin: new Date() },
  create: { name: "John", email: "john@example.com", age: 25 }
});
// Returns: { count: number }
```

**deleteMany(deleteData: DeleteData)** - Delete records
```typescript
const result = userSheet.deleteMany({
  where: { active: false }
});
// 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

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

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

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

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

// Pagination
take: 20,    // Limit results
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 };
```

## 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