Relation Definition

By defining relations between multiple sheets, you can retrieve related data with include and filter using relation conditions in where.

Example Sheets

The following sheets are used as examples throughout the relation documentation.

Users Sheet

id	name	email
1	Alice	alice@example.com
2	Bob	bob@example.com
3	Charlie	charlie@example.com

Posts Sheet

id	title	authorId	published
1	First Post	1	true
2	How to use GAS	1	true
3	Draft Article	2	false

Profiles Sheet

id	userId	bio
1	1	I'm an engineer
2	2	I'm a designer

Tags Sheet

id	name
1	GAS
2	JavaScript

PostTags Sheet (Junction Table)

postId	tagId
1	1
1	2
2	1

Basic Definition

You can define relations between sheets by passing a relations option to the GassmaClient constructor.

const gassma = new Gassma.GassmaClient({
  relations: {
    // Sheet name (must match the actual sheet name in the spreadsheet)
    Users: {
      // Relation name (any name you choose; becomes the key used in include and where)
      posts: {
        type: "oneToMany",
        to: "Posts",
        field: "id",
        reference: "authorId",
      },
    },
  },
});

When specifying a spreadsheet ID, pass id along with it:

const gassma = new Gassma.GassmaClient({
  id: "XXXXXXXXXXXXXXXXXXX",
  relations: {
    // ...
  },
});

Relation Definition Keys

Key	Description	Optional	Notes
type	Type of relation	Required	oneToMany / oneToOne / manyToOne / manyToMany
to	Target sheet name	Required
field	Column name on the source sheet	Required	FK or PK
reference	Column name on the target sheet	Required
through	Junction table settings	Optional	Required for manyToMany
onDelete	Action on delete	Optional	Cascade / SetNull / Restrict / NoAction

Relation Types

oneToMany (One-to-Many)

A relationship where one parent record has multiple child records.

Example: One user has multiple posts

relations: {
  Users: {
    posts: {
      type: "oneToMany",
      to: "Posts",
      field: "id",          // Users PK
      reference: "authorId", // Posts FK
    },
  },
}

Returns an array when retrieved with include.

manyToOne (Many-to-One)

The reverse direction of oneToMany. Defines a reference from child records to the parent record.

Example: Retrieve the author (user) from a post

relations: {
  Posts: {
    author: {
      type: "manyToOne",
      to: "Users",
      field: "authorId",  // Posts FK
      reference: "id",    // Users PK
    },
  },
}

Returns a single object or null when retrieved with include.

oneToOne (One-to-One)

A relationship where one record is linked to exactly one other record.

Example: User and profile

relations: {
  Users: {
    profile: {
      type: "oneToOne",
      to: "Profiles",
      field: "id",       // Users PK
      reference: "userId", // Profiles FK
    },
  },
}

Returns a single object or null when retrieved with include. An error is thrown if multiple records have the same reference value.

manyToMany (Many-to-Many)

Defines a many-to-many relationship through a junction table.

Example: Posts and tags

relations: {
  Posts: {
    tags: {
      type: "manyToMany",
      to: "Tags",
      field: "id",          // Posts PK
      reference: "id",      // Tags PK
      through: {
        sheet: "PostTags",    // Junction table sheet name
        field: "postId",      // FK for Posts in the junction table
        reference: "tagId",   // FK for Tags in the junction table
      },
    },
  },
}

Returns an array when retrieved with include.

Defining Multiple Relations

You can define multiple relations for a single sheet and across multiple sheets.

const gassma = new Gassma.GassmaClient({
  relations: {
    Users: {
      posts: {
        type: "oneToMany",
        to: "Posts",
        field: "id",
        reference: "authorId",
      },
      profile: {
        type: "oneToOne",
        to: "Profiles",
        field: "id",
        reference: "userId",
      },
    },
    Posts: {
      author: {
        type: "manyToOne",
        to: "Users",
        field: "authorId",
        reference: "id",
      },
      tags: {
        type: "manyToMany",
        to: "Tags",
        field: "id",
        reference: "id",
        through: {
          sheet: "PostTags",
          field: "postId",
          reference: "tagId",
        },
      },
    },
  },
});

Validation

If there are errors in the relation definition, an error is thrown when creating the GassmaClient instance.

Error	Cause
RelationSheetNotFoundError	Sheet name specified in relations key, to, or through.sheet does not exist
RelationMissingPropertyError	type / to / field / reference is missing. through is missing for manyToMany
RelationInvalidPropertyTypeError	Property type is not string
RelationInvalidTypeError	type is not one of the 4 types
RelationInvalidOnDeleteError	onDelete is not one of the 4 types
RelationColumnNotFoundError	Column specified in field / reference does not exist in the sheet