---
title: "API Reference"
description: "Comprehensive overview of the CRUD class methods."
---

# API Reference

This page lists all public methods available in the **Google Apps Script CRUD Class for Google Sheets** library, along with their parameters, return values, and a brief description.

## `init(dbName, dbId?)`

- **Description**: Creates or opens a spreadsheet database.
- **Parameters**:
  - `dbName` _(string)_: The name of the database.
  - `dbId` _(string, optional)_: The ID of the Google Spreadsheet. If not provided, a new spreadsheet is created.
- **Returns**: An instance of the `DB` class.

---

## `createTable(config)`

- **Description**: Creates a new table (sheet) in the spreadsheet.
- **Parameters**:
  - `config` _(Object)_: Configuration object for the table.
    - **tableName** _(string)_: Name of the table.
    - **historyTableName** _(string, optional)_: Name of the history table for deleted records (defaults to `DELETED_<tableName>`).
    - **fields** _(Object)_: Key-value pairs defining field names and their types.
      - _Supported Types:_ `boolean`, `string`, `date`, `number`
- **Returns**: An object with `{ status, message }` or `{ status, error }`.

---

Here is the API reference documentation for your function:

---

## `putTableIntoDbContext`

- **Description**: Adds a table to the database context by storing its schema definition. If the table is already present in the context, an error is returned.
- **Parameters**:

  - `config` **(Object)**  
     Table configuration object containing:
    - `config.tableName` **(string)**
      The name of the table to be added.
    - `config.fields` **(Object)**
      An object defining the fields of the table.
    - `config.historyTableName` **(string, optional)**
      If provided, it is ignored by this method. History tables are managed during table creation, not when adding schema to context.

- **Returns**:
  **(Object)** – Status of the operation:
  - If the table is successfully added:
    ```json
    {
      "status": 200,
      "message": "Table added to the schema"
    }
    ```
  - If the table is already present in the database context:
    ```json
    {
      "status": 500,
      "error": "Error when trying to put table in context of the database: Already in context"
    }
    ```

## `create(tableName, data, keyOrder, addUpdatePolicy?)`

- **Description**: Inserts a new record or updates an existing one based on an optional policy.
- **Parameters**:
  - `tableName` _(string)_  
    The name of the table.
  - `data` _(object)_  
    The record data to insert or update.
  - `keyOrder` _(string[])_  
    The exact field order to be written.
  - `addUpdatePolicy?` _(object)_  
    An optional policy object. Example:
    ```js
    { key: 'email', value: 'test@example.com' }
    ```
    If a record with `{ email: 'test@example.com' }` already exists, it will be updated instead of creating a new row.
- **Returns**: An object with `{ status, id, action }` or `{ status, error }`.

---

## `read(tableName, id)`

- **Description**: Retrieves a single record by its ID.
- **Parameters**:
  - `tableName` _(string)_
  - `id` _(number | string)_
- **Returns**: `{ status, data }` or `{ status, error }`.

---

## `update(tableName, id, data, keyOrder, typesChecked?, addUpdatePolicy?)`

- **Description**: Updates an existing record.
- **Parameters**:
  - `tableName` _(string)_  
    The name of the table.
  - `id` _(number | string)_  
    The record ID to update.
  - `data` _(object)_  
    The updated data.
  - `keyOrder` _(string[])_  
    The order of fields in the sheet.
  - `typesChecked?` _(boolean)_  
    Whether or not you’ve already validated data types before calling.
  - `addUpdatePolicy?` _(object)_  
    Similar to the create policy, if you want to handle “upsert” logic.
- **Returns**: An object with `{ status, id, data, action }` or `{ status, error }`.

---

## `remove(tableName, historyTableName, id)`

- **Description**: Moves (soft-deletes) a record from its main table into the specified history table.
- **Parameters**:
  - `tableName` _(string)_  
    The table to remove from.
  - `historyTableName` _(string)_  
    The corresponding history table.
  - `id` _(number | string)_  
    The ID of the record to remove.
- **Returns**: `{ status, message }` or `{ status, error }`.

---

## `removeWithCascade(tableName, historyTableName, id)`

- **Description**: Removes a parent record **and** automatically deletes or archives the related records from any associated junction tables.
- **Parameters**:
  - `tableName` _(string)_
  - `historyTableName` _(string)_
  - `id` _(number | string)_
- **Returns**: `{ status, message }` or `{ status, error }`.

---

## `getAll(tableName, options?, useCache?)`

- **Description**: Retrieves all records from a table with optional pagination, sorting, and caching.
- **Parameters**:
  - `tableName` _(string)_
  - `options?` _(object)_
    - `page` _(number)_: Page number (for pagination).
    - `pageSize` _(number)_: Records per page.
    - `sortBy` _(string)_: Field name to sort by.
    - `sortOrder` _(string)_: `'asc'` or `'desc'`.
  - `useCache?` _(boolean)_  
    Defaults to `true`.
- **Returns**: `{ status, data, message }` or `{ status, error }`.

---

## `readIdList(tableName, ids)`

- **Description**: Retrieves multiple records by an array of IDs in a single call.
- **Parameters**:
  - `tableName` _(string)_
  - `ids` _(number[])_  
    An array of IDs to fetch.
- **Returns**:
  - `status` (200 or 500)
  - `data`: Array of found records
  - `notFound`: Array of missing IDs
  - `message`: Additional info about found and missing IDs

---

## `getRelatedRecords(foreignKey, tableName, field, fieldIndex, options = {}, useCache = false)`

- **Description**: Fetches all records in a given table that match a **numeric foreign key** in a specified column.
- **Parameters**:
  - `foreignKey` _(number)_  
    The foreign key value to filter by.
  - `tableName` _(string)_  
    The name of the table (sheet) where the records reside.
  - `field` _(string)_  
    The column header (in the library’s “fields” object) that contains the foreign key.
  - `fieldIndex` _(number)_  
    The zero-based index of the field in the sheet.
  - `options?` _(object)_
    - `page?: number`
    - `pageSize?: number`
    - `sortBy?: string`
    - `sortOrder?: 'asc' | 'desc'`
  - `useCache?` _(boolean)_  
    Whether to retrieve from an existing cache if available (default: `false`).
- **Returns**:
  - On success: `{ status: 200, data: any[], message: string }`
  - On failure: `{ status: 500, error: string }`

---

## `getRelatedRecordsWithTextFinder(foreignKey, tableName, field, fieldIndex, options = {}, useCache = false)`

- **Description**: Similar to `getRelatedRecords`, but uses Google Apps Script’s `TextFinder` to locate matches. This can be useful when the foreign key is stored in a way that exact numeric matching might fail, or if you have textual variations.
- **Parameters**:
  - `foreignKey` _(number)_  
    The foreign key value to filter by. Must be a number, though it’s matched using TextFinder as a string.
  - `tableName` _(string)_  
    The table (sheet) to search in.
  - `field` _(string)_  
    The column header (in the “fields” object) that contains the foreign key.
  - `fieldIndex` _(number)_  
    The zero-based column index for the foreign key field.
  - `options?` _(object)_
    - `page?: number`
    - `pageSize?: number`
    - `sortBy?: string`
    - `sortOrder?: 'asc' | 'desc'`
  - `useCache?` _(boolean)_  
    Whether to retrieve from an existing cache if available (default: `false`).
- **Returns**:
  - On success: `{ status: 200, data: any[], message: string }`
  - On failure: `{ status: 500, error: string }`

---

## `createWithLogs(tableName, data, keyOrder, addUpdatePolicy?)`

- **Description**: Enhanced version of `create` with detailed logging for debugging.
- **Parameters**: Same as `create()` method.
- **Returns**: Same as `create()` method with additional logging output.

---

## `updateWithLogs(tableName, id, data, keyOrder, typesChecked?, addUpdatePolicy?)`

- **Description**: Enhanced version of `update` with detailed logging for debugging.
- **Parameters**: Same as `update()` method.
- **Returns**: Same as `update()` method with additional logging output.

---

## `getRelatedRecordsWithFilter(foreignKey, tableName, field, fieldIndex, options?, useCache?)`

- **Description**: Enhanced version of `getRelatedRecords` but using a filter() function instead of a for loop.
- **Parameters**: Same as `getRelatedRecords()` method.
- **Returns**: Same as `getRelatedRecords()` method.

---

## `getRelatedRecordsWithLogs(foreignKey, tableName, field, fieldIndex, options?, useCache?)`

- **Description**: Enhanced version of `getRelatedRecords` with detailed logging.
- **Parameters**: Same as `getRelatedRecords()` method.
- **Returns**: Same as `getRelatedRecords()` method with additional logging output.

---

## `deleteRelatedJunctionRecords(tableName, junctionHistoryTableName, fkIndex, id)`

- **Description**: Deletes related records from junction tables when a parent record is removed.
- **Parameters**:
  - `tableName` _(string)_: The parent table name.
  - `junctionHistoryTableName` _(string)_: The junction table's history table.
  - `fkIndex` _(number)_: The foreign key column index.
  - `id` _(number)_: The ID of the parent record to remove.
- **Returns**: `{ status, count, message }` or `{ status, error }`.

---

## `getCreationResult()`

- **Description**: Gets the result status of the last table creation operation.
- **Parameters**: None.
- **Returns**: `{ status, message }` or `{ status, error }`.

---

## `applyColorScheme(tableName, colorScheme)`

- **Description**: Applies a color scheme to a table for better visualization.
- **Parameters**:
  - `tableName` _(string)_  
    The name of the table (sheet).
  - `colorScheme` _(string)_  
    One of: `red`, `blue`, `green`, `orange`, `purple`.
- **Returns**: `{ status, message, data }`  
  Where `data` includes `{ headerColor, color1, color2 }`. Throws an error if the color scheme is invalid.

---

## Many-to-Many Relationship Methods

### `createManyToManyTableConfig(config)`

- **Description**: Builds a config object for a **junction** (relation) table.
- **Parameters**:
  - `config` _(Object)_
    - `entity1TableName` _(string)_
    - `entity2TableName` _(string)_
    - `fieldsRelatedToBothEntities?` _(Object)_  
      Additional fields to store in the relationship, e.g. `{ quantity: "number" }`.
- **Returns**:
  - On success: `{ status: 200, data: { tableName, historyTableName, fields }, message }`
  - On error: `{ status: 500, error }`

### `createJunctionRecord(junctionTableName, data, keyOrder)`

- **Description**: Creates a new record in the junction table, preventing duplicate relationships.
- **Parameters**:
  - `junctionTableName` _(string)_
  - `data` _(Object)_  
    Must contain the two foreign keys, e.g. `{ project_id, employee_id }`.
  - `keyOrder` _(string[])_  
    The field order in the sheet.
- **Returns**: `{ status, id, action }` or `{ status, error }`.

### `getJunctionRecords(junctionTableName, sourceTableName, targetTableName, sourceId, options?)`

- **Description**: Fetches related records from a **many-to-many** relationship.
- **Parameters**:
  - `junctionTableName` _(string)_  
    The junction table that links `sourceTableName` and `targetTableName`.
  - `sourceTableName` _(string)_  
    The "origin" table name.
  - `targetTableName` _(string)_  
    The "destination" table name to retrieve data from.
  - `sourceId` _(number)_  
    The ID in the source table.
  - `options?` _(object)_  
    Sorting & pagination options (`sortBy`, `sortOrder`, `page`, `pageSize`, etc.).
- **Returns**: `{ status, data: any[], message, metadata }` or `{ status, error }`.

### `updateJunctionRecord(junctionTableName, id, data, keyOrder)`

- **Description**: Updates a record in the junction table, preventing duplicates for the same pair of foreign keys.
- **Parameters**:
  - `junctionTableName` _(string)_
  - `id` _(number)_
  - `data` _(object)_
  - `keyOrder` _(string[])_
- **Returns**: `{ status, id, data, action }` or `{ status, error }`.

### `checkTableIntegrity(junctionTableName, junctionHistoryTableName)`

- **Description**: Validates foreign key references in a junction table. Moves invalid rows (where parent records no longer exist) to the junction table's history.
- **Returns**: `{ status, count, message }` or `{ status, error }`

---

## Locking Methods

### `releaseLocks()`

- **Description**: Frees script-level locks held by the current `DB` instance.
- **Returns**: `void`.