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

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

https://railsguides.jp/association_basics.html

Apps Scriptから使えるQuery専用のO/Rマッパー（クエリービルダー）もどき [AssocSheets](https://script.google.com/d/1qcppOv1DeiBBzUgiwo2yHm0rKld5nndxHI5cw_u1iFC3xUbyFAT5jc6J/edit?usp=sharing) を作ってみました。

# 使い方

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

スクリプトID
`1qcppOv1DeiBBzUgiwo2yHm0rKld5nndxHI5cw_u1iFC3xUbyFAT5jc6J`

## JSONオブジェクトの取得

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

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

| id | name |
|---|---|
| 1 | supplier1 |
| 2 | supplier2 |
| 3 | supplier3 |

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

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

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

suppliersはこうなります。

```json: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 |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/6760/d341b72c-23a6-3b9a-680b-33868e96deda.png)
（https://railsguides.jp/association_basics.html より引用）

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

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

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

suppliersはこうなります。

```json: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`と同じ）を指定できます。

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

```

accountsはこうなります。

```json: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 |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/6760/8e96b4d2-d125-a352-9e25-f5d137e2730c.png)
（https://railsguides.jp/association_basics.html より引用）

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

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

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

authorsはこうなります。

```json: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 |

で説明します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/6760/b7cb5a6e-989e-12cb-1a1c-35785416daf7.png)
（https://railsguides.jp/association_basics.html より引用）

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

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

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

physiciansはこうなります。

```json: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は、

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

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

```json: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は、

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

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

```json: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 は、

```javascript
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();
```

こうなります。

```json
[
  {
    "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