GASで使えるORMライブラリ

はじめに

本記事はプロもくチャット Adevent Calendar2023の23日目です

https://qiita.com/advent-calendar/2023/puromoku

普段からGASを使った業務自動化・効率化の開発をすることが多いので
開発作業をスピードアップしたり、メンテナンスのコストを下げるために
ORMもどきを作ってみました👌

実際にあった(かもしれない)案件の話を通じて
開発の経緯と機能について紹介していきます

シチュエーション

知り合いから、ツール制作の依頼を受けました

🦊：リサーチ作業を効率化したい。。。
🦊：URLを一杯指定するから、商品価格をドカッと取って来れない？？？
🦍：へい (こんな仕様でいくか)

仕様案その１

• スプレッドシート + GASの形式で提供
• mainシートにURLと商品価格を記載する
• シートの1行目は見出し行
• シートのA列は商品URL
• ボタンクリックで、URLを取得して商品価格をスクレイピング
• 取得した価格をB列に反映

🦍：実装してみた

main.gs

// 1. シートから値を取ってくる
const s = SpreadSheetApp.getSheetByName('main');
const values = s.getRange('A1:B5').getValues();

// 2. forループでA列のurlを対象にスクレイピング.取得した値はprices[]にセット
const prices = [];
for(let i=1; i<values.length; i++) { // i = 0はheadなのでスキップ
  const url = values[i][0];
  const price = scrapeProc(url); // scrapeProcの実装は省略
  prices.push([price]);
}

// 3. pricesの値をB列にセット
s.getRange('B2:B5').setValues(prices);

🦍：出来たっす
🦊：やるやん！それなら、ついでにこんなことも出来ると嬉しいんやが。。。。

追加要望その１

• 指定したデータだけ更新したいので、A列にチェックボックスを作ってほしい
• チェックしていない行は無視
• 価格以外に商品名もスクレイピングしてシートに記録して欲しい
• 後ろに列を追加してメモ欄として使いたいし、何なら列の順番も変わる可能性がある

🦍：ヒェッ

---

...数時間後

🦍：こんな感じかな

仕様案その２

• A列はチェックボックス
• A1:ZZ1のどこかの列にURL,価格,商品名,メモ欄がある
• ボタンクリックで、チェックボックスがTRUEの行だけを更新
• URLから、商品価格・商品名をスクレイピングして対応する列に反映
• 価格・商品名以外の列は元のデータを据え置く

🦍：実装してみた

main.gs

// 1. シートから値を取ってくる
const s = SpreadSheetApp.getSheetByName('main');
const values = s.getRange('A1:ZZ').getValues();

// 2. 1行目(見出し)だけをheadに保持
const head = values[0];

// 3. url,価格,タイトルが何列目にあるか確認
const url_index = head.indexOf('url');
const check_index = head.indexOf('check');
const price_index = head.indexOf('price');
const title_index = head.indexOf('title');

// 4. forループでurlを対象にスクレイピング
const prices = [];
const titles = [];
for(let i=1; i<values.length; i++) { // i = 0はheadなのでスキップ
  const check = values[i][check_index];
  // 4-1 チェックありならスクレイピング
  if(check === 'TRUE') {
    const url = values[i][url_index];
    const { price, title } = scrapeProc(url); // 価格と商品名を返すものとする
    prices.push([price]);
    titles.push([title]);
  }
  // 4-2 チェックなしなら上書きしても大丈夫なように、現在値をセット
  else {
    const price = values[i][price_index];
    const title = values[i][title_index];
    prices.push([price]);
    titles.push([title]);
  }
}

// 5. 列の位置を列名(A,B,C...)に変換(indexToColの実装は省略)
const price_col = indexToCol('A', price_index);
const title_col = indexToCol('A', title_index);

// シートに書込み
s.getRange(`${price_col}2:${price_col}${values.length}`).setValues(prices);
s.getRange(`${title_col}2:${title_col}${values.length}`).setValues(titles);

🦍：できたけど、列追加の度にこれとかやってられんな

※ループの内側でsetValueすればもっとシンプルなコードになりますが、パフォーマンスの観点から非推奨です

🦊：ナイス！追加でデータ取得時刻の列も欲しい！あとスプシに書込みしたいから、開始する行は2行目よりも下で！何なら商品の画像も表示して欲しいし、ついでにCSV変換して一括でデータを出力する機能もetc...

🦍：くぁwせdrftgyふじこlp

スプレッドシートを扱うときに辛いポイント

• データが2次元配列で返ってくるので、開始セルを起点とした行列のインデックス番号が必要
• 読み書きするのにgetRange()で返ってくるRangeオブジェクトを経由しないといけない
• コードの可読性が低い
• getValue() / setValue()が遅いので、大量のデータだとパフォーマンス悪化

特にこの辺がつらい。ホントめんどくさい。

// 3. url,価格,タイトルが何列目にあるか確認
const url_index = head.indexOf('url');
const check_index = head.indexOf('check');
const price_index = head.indexOf('price');
const title_index = head.indexOf('title');

// 5. 列の位置を列名(A,B,C...)に変換(indexToColの実装は省略)
const price_col = indexToCol('A', price_index);
const title_col = indexToCol('A', title_index);

っていうかもう生きるのがめんどくさい。チーズ蒸しパンになりたい

スプレッドシートの読み書きの当たり前を見直す

これを...

• データが2次元配列で返ってくるので、行番号・列番号が必要
• 読み書きするのにgetRange()を経由しないといけない
• コードの可読性が低い
• getValue() / setValue()が遅いので、大量のデータだとパフォーマンス悪化

こんな感じにしたい

• 行列番号からの解放
• getRange()からの卒業
• 高い可読性
• 速い実行速度

つくってみた

https://github.com/soyalumno/table_lib

• データアクセスをkey-value形式(オブジェクト)にして
  行列のインデックス指定を不要に
• データ範囲指定はインスタンス生成時の1回のみ
  データの追加や更新は範囲を意識しなくてOKに
• SheetsAPIを利用することで処理を高速化

---

という感じで、今までとデータのアクセス方法をガラっと変えてみました。
先ほどのシチュエーションと同じ処理を実装してみた結果がこちらです

main.gs

// 1. シート名・主キーを指定してTableインスタンスを生成
const tbl = table_lib.buildTable('main!A1:ZZ', 'url');

// 2. 対象のデータだけを処理
for(hash of tbl.hashes) {
  if(hash['check'] === 'TRUE') {
    const { price, title } = scrapeProc(hash['url']);
    hash['price'] = price;
    hash['title'] = title;
  }
};

// 3. 更新したデータをシートに書込み
tbl.save();

ロジックが明確になって素敵ですね

main.gs

  if(hash['check'] === 'TRUE') {
    const { price, title } = scrapeProc(hash['url']);
    hash['price'] = price;
    hash['title'] = title;
+   hash['udpate_at'] = Utilities.formatDate(new Date(), 'JST', 'yyyy-MM-dd');
  }

新しい列が増えても1行で終わります。好き

解説

❓このクラスは何者?

✅GASのコードとスプレッドシートの読み書きを繋ぐORMとして動作します

ORMとは

ORM（Object-Relational Mapping）は、プログラミングにおける概念で、オブジェクト指向プログラミングと関係データベースのデータをマッピングするための技術です。ORMは、オブジェクト指向プログラミングにおいて使用されるオブジェクトと、関係データベースのテーブルやレコードとの間の対応関係を自動的に処理することで、開発者がデータベースを操作するための低レベルのSQLコードを直接記述する必要性を軽減します

クラス図

buildTableを実行すると、指定した範囲のデータを以下の構成で抽象化します

• Table : 範囲クラス。データの検索・追加や、シートの初期化などを管理
• Row : 1行分のデータクラス。行番号プロパティや2次元配列への変換、Row同士の比較処理を管理
• Hash : データの実体。見出しをkey、セルの値をvalueとしたオブジェクト(valueはstring型)

---

❓どうやって２次元配列をオブジェクトに変換してる？

✅以下の関数で実現しています

同じ名前の列が複数ある場合、列番号が一番大きいものを取得します

  /** 二次元のテーブルデータを見出しをキーとしたオブジェクト配列に変換する */
  toRecords(df: any[][]): Hash[] {
    const [head, ...values_arr] = df;
    return values_arr.map((values) =>
      head.reduce((acc, col, i) => {
        acc[col.toString()] = values[i]?.toString() || '';
        return acc;
      }, {})
    );
  }

---

❓スキーマはどこで定義する？

✅ORMもどきなので、スキーマらしいスキーマはありません(テーブル間の関連も持ちません)

スレッドシートの見出し行の内容が変わると
いままでアクセス出来たプロパティが存在しなくなり、エラーとなる可能性があります。

TypeScript環境であれば、インスタンス生成時に型を指定することで
Tableが取り扱うkeyの範囲を制限できます

new Table<THeader, TRow, THash>(range, primary_key);

スキーマ
スキーマは、データベースやデータ構造の設計や定義を表すものです。データベーススキーマは、データベース内のテーブル、列、関係、インデックスなどの要素とそれらの関係を定義します。データ構造のスキーマは、データの組織や形式、フィールドの定義、関連性、制約などを定義します

---

❓データ型は定義できない？

✅出来ません。セルの値をそのままシンプルに文字列として取得します

そのため、数式を読み取ると計算結果の値だけが取得されます
ただし、書込み時には文字列で数式をセットすることが可能です

上記の仕様により、数式が含まれる範囲をTableクラスで扱う場合は注意が必要です
読み出した値は数式の計算結果なので、
取得した値をそのままシートに書き込むと、数式が上書きされます

つまり
「=Today()って書いてたのに、気がついたら日付が固定されとる...!?」
みたいなことが起きます

---

❓Tableクラスのprimary_keyってなに？

✅主キーです。findRecord() / updateRecord()で対象となるデータを検索するために使用します

ただし、主キーの重複管理は行わないため、同じ主キーのデータが複数存在する場合は
行番号の一番小さいデータを検索結果として扱います

findRecord()よりもhashes.find()した方が便利だったりする

---

❓データを削除することは出来る？

✅指定したデータを削除する機能はありません

ですが、Tableクラスのプロパティhashesから対象のデータを削除して
resetTableを実行することで、削除後のデータをシートに反映することが可能です
(データの挿入についても同様です)

---

❓HashとRowのデータ構造が似ててややこしい

✅大体のメソッドはHash | Rowの両方のデータ型に対応しているので、
あまり気にしなくても良いです.通常はHashを利用して、行番号が欲しい時はRowを参照してください

安心してください。Type Guardしてます

---

まとめ

GAS用のORMもどきを作成してみました

ソースコードと合わせてGASのライブラリとして公開しているので、
開発体験を上げたかったり、開発速度にこだわりたい方は導入してみてください

https://github.com/soyalumno/table_lib