GoogleスプレッドシートでAssociationを扱うO/Rマッパーもどき AssocSheets

Googleスプレッドシートをデータベース代わりに使うときに、関連付け（Association）も扱いたいですよね。

Apps Scriptから使えるQuery専用のO/Rマッパー（クエリービルダー）もどき AssocSheets を作ってみました。

使い方

Apps Scriptのスクリプトエディターで、ライブラリーを追加します。

スクリプトID
1qcppOv1DeiBBzUgiwo2yHm0rKld5nndxHI5cw_u1iFC3xUbyFAT5jc6J

JSONオブジェクトの取得

スプレッドシートとシート名を指定し、toJson()でJSONオブジェクトを取得します。

例として、次のsuppliersシートを取り上げます。

id	name
1	supplier1
2	supplier2
3	supplier3

openByUrl(url).define(sheetName).toJson()

スプレッドシートのURLurlとシート名sheetNameを指定して、JSON形式でデータを取得します。

//GoogleスプレッドシートのURL
const URL = 'https://docs.google.com/spreadsheets/d/...';
const suppliers = AssocSheets.openByUrl(URL)
  .define('suppliers').toJson();

suppliersはこうなります。

suppliers

[
  {
    "id": 1,
    "name": "supplier1"
  },
  {
    "id": 2,
    "name": "supplier2"
  },
  {
    "id": 3,
    "name": "supplier3"
  }
]

1対1

has_oneとbelongs_toに対応します。

先程のsuppliersシートに加えて、次のaccountsシートがある場合で説明します。

id	supplier_id	account_number
1	1	for_supplier1
2	2	for_supplier2
3	3	for_supplier3

（https://railsguides.jp/association_basics.html より引用）

hasOne(sheetName, foreignKey, options = { primaryKey, propName }?)

define()で指定したシートが「1対1」の関連を持つシート名sheetNameと、外部キーforeignKeyを指定します。オプションで、プライマリーキーprimaryKey（デフォルトはid）と、プロパティ名propName（デフォルトはsheetNameと同じ）を指定できます。

const suppliers = AssocSheets.openByUrl(URL)
  .define('suppliers')
  .hasOne('accounts', 'supplier_id', { propName: 'account' }).toJson();

suppliersはこうなります。

suppliers

[
  {
    "id": 1,
    "name": "supplier1",
    "account": {
      "id": 1,
      "supplier_id": 1,
      "account_number": "for_supplier1"
    }
  },
  {
    "id": 2,
    "name": "supplier2",
    "account": {
      "id": 2,
      "supplier_id": 2,
      "account_number": "for_supplier2"
    }
  },
  {
    "id": 3,
    "name": "supplier3",
    "account": {
      "id": 3,
      "supplier_id": 3,
      "account_number": "for_supplier3"
    }
  }
]

belongsTo(sheetName, foreignKey, options = { primaryKey, propName }?)

define()で指定したシートが「従属」の関連を持つシート名sheetNameと、外部キーforeignKeyを指定します。オプションで、プライマリーキーprimaryKey（デフォルトはid）と、プロパティ名propName（デフォルトはsheetNameと同じ）を指定できます。

const accounts = AssocSheets.openByUrl(URL)
  .define('accounts')
  .belongsTo('suppliers', 'supplier_id', { propName: 'supplier' }).toJson();

accountsはこうなります。

accounts

[
  {
    "id": 1,
    "supplier_id": 1,
    "account_number": "for_supplier1",
    "supplier": {
      "id": 1,
      "name": "supplier1"
    }
  },
  {
    "id": 2,
    "supplier_id": 2,
    "account_number": "for_supplier2",
    "supplier": {
      "id": 2,
      "name": "supplier2"
    }
  },
  {
    "id": 3,
    "supplier_id": 3,
    "account_number": "for_supplier3",
    "supplier": {
      "id": 3,
      "name": "supplier3"
    }
  }
]

1対多

has_manyに対応します。

次のauthorsシートと、

id	name
1	author1
2	author2
3	author3

booksシートで説明します：

id	author_id	name
1	1	book1
2	1	book2
3	2	book3

（https://railsguides.jp/association_basics.html より引用）

hasMany(sheetName, foreignKey, options = { primaryKey, propName }?)

define()で指定したシートが「1対多」の関連を持つシート名sheetNameと、外部キーforeignKeyを指定します。オプションで、プライマリーキーprimaryKey（デフォルトはid）と、プロパティ名propName（デフォルトはsheetNameと同じ）を指定できます。

const authors = AssocSheets.openByUrl(URL)
  .define('authors')
  .hasMany('books', 'author_id').toJson();

authorsはこうなります。

authors

[
  {
    "id": 1,
    "name": "author1",
    "books": [
      {
        "id": 1,
        "author_id": 1,
        "name": "book1"
      },
      {
        "id": 2,
        "author_id": 1,
        "name": "book2"
      }
    ]
  },
  {
    "id": 2,
    "name": "author2",
    "books": [
      {
        "id": 3,
        "author_id": 2,
        "name": "book3"
      }
    ]
  },
  {
    "id": 3,
    "name": "author3",
    "books": []
  }
]

多対多

has_many throughに対応します。

physicianシートが、

id	name
1	physician1
2	physician2

patientsシートが、

id	name
1	patient1
2	patient2
3	patient3

さらに、中間テーブル（交差テーブル）appointmentsが次の場合、

physician_id	patient_id	appointment_date
1	1	2022/02/19
1	2	2022/02/20
2	2	2022/02/20

で説明します。

（https://railsguides.jp/association_basics.html より引用）

hasMany().through(sheetName, parentForeignKey, childForeignKey)

define()で指定したシートとhasMany()で指定したシートが「多対多」の関連を持ち、中間テーブル（交差テーブル）で関連付けている場合につかいます。中間テーブルのシート名sheetNameと、define側の外部キーparentForeignKey、hasMany側の外部キーchildForeignKeyを指定します。

const physicians = AssocSheets.openByUrl(URL)
  .define('physicians')
  .hasMany('patients')
  .through('appointments', 'physician_id', 'patient_id').toJson();

physiciansはこうなります。

physicians

[
  {
    "id": 1,
    "name": "physician1",
    "patients": [
      {
        "appointment_date": "2022-02-18T15:00:00.000Z",
        "id": 1,
        "name": "patient1"
      },
      {
        "appointment_date": "2022-02-19T15:00:00.000Z",
        "id": 2,
        "name": "patient2"
      }
    ]
  },
  {
    "id": 2,
    "name": "physician2",
    "patients": [
      {
        "appointment_date": "2022-02-19T15:00:00.000Z",
        "id": 2,
        "name": "patient2"
      }
    ]
  }
]

組み合わせ

複雑なオブジェクトを作るために、belongsTo()、hasOne()、hasMany()、そしてdefine()を組み合わせて使うことができます。いくつか例を示します。

hasMany() + hasOne()

先程のauthorsシート、booksシートに加えて、addressシートが次の場合、

id	author_id	address
1	1	address_for_author1
2	2	address_for_author2
3	3	address_for_author3

次のauthorsは、

const authors = AssocSheets.openByUrl(URL)
  .define('authors')
  .hasMany('books', 'author_id')
  .hasOne('address', 'author_id').toJson();

こうなります（addressが一つ）。

authors

[
  {
    "id": 1,
    "name": "author1",
    "address": {
      "id": 1,
      "author_id": 1,
      "address": "address_for_author1"
    },
    "books": [
      {
        "id": 1,
        "author_id": 1,
        "name": "book1"
      },
      {
        "id": 2,
        "author_id": 1,
        "name": "book2"
      }
    ]
  },
  {
    "id": 2,
    "name": "author2",
    "address": {
      "id": 2,
      "author_id": 2,
      "address": "address_for_author2"
    },
    "books": [
      {
        "id": 3,
        "author_id": 2,
        "name": "book3"
      }
    ]
  },
  {
    "id": 3,
    "name": "author3",
    "address": {
      "id": 3,
      "author_id": 3,
      "address": "address_for_author3"
    },
    "books": []
  }
]

hasMany() + hasMany()

また、次のauthorsは、

const authors = AssocSheets.openByUrl(URL)
  .define('authors')
  .hasMany('books', 'author_id')
  .hasMany('address', 'author_id').toJson();

こうなります（addressが複数）。

authors

[
  {
    "id": 1,
    "name": "author1",
    "books": [
      {
        "id": 1,
        "author_id": 1,
        "name": "book1"
      },
      {
        "id": 2,
        "author_id": 1,
        "name": "book2"
      }
    ],
    "address": [
      {
        "id": 1,
        "author_id": 1,
        "address": "address_for_author1"
      }
    ]
  },
  {
    "id": 2,
    "name": "author2",
    "books": [
      {
        "id": 3,
        "author_id": 2,
        "name": "book3"
      }
    ],
    "address": [
      {
        "id": 2,
        "author_id": 2,
        "address": "address_for_author2"
      }
    ]
  },
  {
    "id": 3,
    "name": "author3",
    "books": [],
    "address": [
      {
        "id": 3,
        "author_id": 3,
        "address": "address_for_author3"
      }
    ]
  }
]

define() + define()

先程のauthorsシート、booksシート、addressシートに加えて、shopsシートとbooks_shopsシートが次の場合、

id	shop_name
1	shop1
2	shop2
3	shop3
4	shop4

book_id	shop_id
1	1
2	1
1	2
2	2
3	2

次の authors は、

const authors = AssocSheets.openByUrl(URL)
  .define('authors').hasMany('books', 'author_id').hasMany('address', 'author_id')
  .define('books').hasMany('shops').through('books_shops', 'book_id', 'shop_id')
  .toJson();

こうなります。

[
  {
    "id": 1,
    "name": "author1",
    "books": [
      {
        "id": 1,
        "author_id": 1,
        "name": "book1",
        "shops": [
          {
            "book_id": 1,
            "shop_id": 1,
            "id": 1,
            "shop_name": "shop1"
          },
          {
            "book_id": 1,
            "shop_id": 2,
            "id": 2,
            "shop_name": "shop2"
          }
        ]
      },
      {
        "id": 2,
        "author_id": 1,
        "name": "book2",
        "shops": [
          {
            "book_id": 2,
            "shop_id": 1,
            "id": 1,
            "shop_name": "shop1"
          },
          {
            "book_id": 2,
            "shop_id": 2,
            "id": 2,
            "shop_name": "shop2"
          }
        ]
      }
    ],
    "address": [
      {
        "id": 1,
        "author_id": 1,
        "address": "address_for_author1"
      }
    ]
  },
  {
    "id": 2,
    "name": "author2",
    "books": [
      {
        "id": 3,
        "author_id": 2,
        "name": "book3",
        "shops": [
          {
            "book_id": 3,
            "shop_id": 2,
            "id": 2,
            "shop_name": "shop2"
          }
        ]
      }
    ],
    "address": [
      {
        "id": 2,
        "author_id": 2,
        "address": "address_for_author2"
      }
    ]
  },
  {
    "id": 3,
    "name": "author3",
    "books": [],
    "address": [
      {
        "id": 3,
        "author_id": 3,
        "address": "address_for_author3"
      }
    ]
  }
]

ソースコード

プロダクトコードはこちらです。
https://script.google.com/d/1qcppOv1DeiBBzUgiwo2yHm0rKld5nndxHI5cw_u1iFC3xUbyFAT5jc6J/edit?usp=sharing

テストコード（スプレッドシート）はこちらです。
https://docs.google.com/spreadsheets/d/1Uwi-yPDQz3axXGwZkvbQ4_D9CL-10K_OjvLAExVzq6I/edit?usp=sharing