スプレッドシートのセルに配列やオブジェクトを記録する

Googleスプレッドシートをデータベース代わりに使って、シンプルなWebアプリやツールを開発することがあると思います。

いわゆるRDB的なイメージでテーブル（シート）の構成を設計すると、シートの枚数が増えてしまい、データ同士を結合する処理が複雑になったりして、ちょっと面倒だなぁ・・・と思う時もあります。

そんな時、セルに配列やオブジェクトを保存してしまえば、シートの枚数を増やさずに対応できる場合がありますので、具体例と方法を紹介します。

活用の具体例
配列やオブジェクトをセルに記録して使う方法
デメリット

活用の具体例

例えば、簡単な社内ワークフローアプリを作ろうと思った場合・・・

まずは、申請情報を管理する申請一覧シートを作り・・・

「申請一覧」シート。1行目はヘッダー行で、A列から順に「申請番号」「申請者」「件名」「ステータス」「申請日」となっている。 — 申請一覧シート

審査フローや審査履歴を管理する審査シートを作り・・・

「審査」シート。1行目はヘッダー行で、A列から順に「申請番号」「連番」「審査担当者」「審査結果」「審査日」となっている。 — 審査シート

コメントを管理するコメントシートを作る・・・

「コメント」シート。1行目はヘッダー行で、A列から順に「申請番号」「コメント」「登録者」「登録日」となっている。 — コメントシート

・・・という感じになるのではないかと思います。（雑ですみません）
サクっと作るにしては、ちょっと重いなぁ・・・。

しかし、データの管理に配列やオブジェクトを使えば、申請一覧シートだけで済ますことができます。

「申請一覧」シート。先ほどの申請一覧シートの項目に加え、「審査」列と「コメント」列が追加されている。この2つの列のセルには、複数のデータがカンマ区切りで入力されている。 — 審査、コメントが申請一覧シートに統合できた。1つのセルに複数のレコードが詰め込まれている。

配列やオブジェクトをセルに記録して使う方法

セルに保存する

配列やオブジェクトをセルに保存する時は、JSON.stringifyでJSONにしてsetValueします。

配列を保存する

// 配列を作成する
const fruits = ['りんご', 'ぶどう', 'みかん', 'なし'];

// JSONにしてシートに書き込む
sheet.getRange("A1").setValue(JSON.stringify(fruits));

シートのA1セルに、配列が入力されている。 — セルにJSON化した配列が記録される

オブジェクトを保存する

// オブジェクトを作成する
const fruit = { name: 'りんご', color: 'red', price: '100' };

// JSONにしてシートに書き込む
sheet.getRange("A1").setValue(JSON.stringify(fruit));

シートのA1セルに、オブジェクトが入力されている。 — セルにJSON化したオブジェクトが記録される

オブジェクトの配列を保存する

// オブジェクトの配列を作成する
const fruits = [
  { name: 'りんご', color: 'red', price: '100' },
  { name: 'ぶどう', color: 'purple', price: '400' },
  { name: 'みかん', color: 'orange', price: '70' },
  { name: 'なし', color: 'light-yellow', price: '100' },
];

// JSONにしてシートに書き込む
sheet.getRange("A1").setValue(JSON.stringify(fruits));

シートのA1セルに、オブジェクトの配列が入力されている。 — セルにJSON化した配列が記録される

セルから取得して利用する

セルに記録した配列やオブジェクト（をJSONにしたもの）を、スクリプトで使用する時は、JSON.parseでパースします。

配列を取得して利用する

// セルからデータを取得する
const fruits = JSON.parse(sheet.getRange("A1").getValue());

// スクリプトで使用する
for (const fruit of fruits) {
  console.log(fruit);
}

17:51:00	お知らせ	実行開始
17:51:00	情報	りんご
17:51:00	情報	ぶどう
17:51:00	情報	みかん
17:51:00	情報	なし
17:51:01	お知らせ	実行完了

オブジェクトを取得して利用する

// セルからデータを取得する
const fruit = JSON.parse(sheet.getRange("A1").getValue());

// スクリプトで使用する
Object.keys(fruit).forEach(key => 
  console.log(`${key}: ${fruit[key]}`)
)

17:48:29	お知らせ	実行開始
17:48:29	情報	name: りんご
17:48:29	情報	color: red
17:48:29	情報	price: 100
17:48:30	お知らせ	実行完了

オブジェクトの配列を取得して利用する

// セルからデータを取得する
const fruits = JSON.parse(sheet.getRange("A1").getValue());

// スクリプトで使用する
for (const fruit of fruits) {
  console.log(fruit);
}

17:40:29	お知らせ	実行開始
17:40:29	情報	{ name: 'りんご', color: 'red', price: '100' }
17:40:29	情報	{ name: 'ぶどう', color: 'purple', price: '400' }
17:40:29	情報	{ name: 'みかん', color: 'orange', price: '70' }
17:40:29	情報	{ name: 'なし', color: 'light-yellow', price: '100' }
17:40:30	お知らせ	実行完了