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

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

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

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

活用の具体例

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

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

申請一覧シート

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

審査シート

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

コメントシート

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

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

審査、コメントが申請一覧シートに統合できた。1つのセルに複数のレコードが詰め込まれている。

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

セルに保存する

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

配列を保存する

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

// JSONにしてシートに書き込む
sheet.getRange("A1").setValue(JSON.stringify(fruits));
セルにJSON化した配列が記録される

オブジェクトを保存する

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

// JSONにしてシートに書き込む
sheet.getRange("A1").setValue(JSON.stringify(fruit));
セルに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));
セルにJSON化した配列が記録される

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

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

配列を取得して利用する

セルに記録されているJSON化した配列
// セルからデータを取得する
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	お知らせ	実行完了

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

セルに記録されているJSON化したオブジェクト
// セルからデータを取得する
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	お知らせ	実行完了

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

セルに記録されているJSON化したオブジェクトの配列
// セルからデータを取得する
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	お知らせ	実行完了

デメリット

この方法は、シンプルなツールやアプリを短時間で、簡単に開発するのに便利ですが、デメリットもあります。

それは、シートのデータを集計したり、グラフ化したり・・・という際に、折角のスプレッドシートの機能(関数やグラフ機能)を利用しづらくなってしまう点です。

countifやピボットテーブルが使えれば一瞬でデータが集計できるのに・・・
この手法を使った場合は、おそらく、データ集計のためにスクリプトを作る必要が出てくると思います。

折角スプレッドシートでアプリを作ったのに、その機能をフル活用できなくなるのは勿体無いですね。

コメント