はじめに
みなさんNode.jsのORMは何が好きですか？TypeORMとかSequelizeとかあると思います。私はprismaが一番好きです。理由は大学時代に習ったMongoDBのクエリとどことなく似ていてとっつきやすかったからです。
ところでGoogle Apps Script(GAS)でスプレッドシートを操作するスクリプトを書いた際、非常に面倒だと思ったことはありませんか？(唐突)例えば以下のデータがあるとします。
このデータから「年齢の列が25歳以上の行を抜き出し、名前の昇順に並び替え、行を連想配列(キーは列名)に変換し、その配列で出力する」スクリプトを書きたいとします。
その場合以下のコードになるはずです。
function myFunction() {
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const hogeSheet = sheet.getSheetByName("シート名");
const rowLength = hogeSheet.getLastRow() - 1;
if (rowLength === 0){
console.log([]);
return;
};
const data = hogeSheet.getRange(2, 1, rowLength, 4).getValues();
const gte25Data = data.filter((row) => row[1] >= 25);
const gte25DataSorted = gte25Data.sort((a, b) => a[0] >= b[0] ? 1 : -1);
const gte25DataSortedDict = gte25DataSorted.map((row) => {
return {
名前: row[0],
年齢: row[1],
住所: row[2],
郵便番号: row[3]
}
});
console.log(gte25DataSortedDict);
}
まだコードは短い方ですが、「同じ名前があった場合は住所の昇順に並び替える」、「年齢が20歳以上60歳以下かつ、住所が東京の人の平均年齢を求める」等条件が増えていくとコードはより複雑になっていき、コード管理が大変になっていきます。それにGASを触る人全員がJavaScriptにある程度知見を持っている人だとは限らず、軽くしかJavaScriptを触れない人にとって、そのような複雑なコードを書くのは難しいです。さらにgetRange()をする際、行番号や列番号を数えながら実装する必要があり、コードレビューの際スプレッドシートと比較しながら検証しなければならないので大変です。ですが業務の都合上スプレッドシートをデータベースとして利用しているのでどうしてもGASで複雑な抽出コードを書かないといけない人もいらっしゃるでしょう(僕の学生時代のアルバイト先がそうでした)。
そんな方におすすめしたいGASライブラリを作成しました。名前は"GASsma"です。
prismaみたいな感覚で書ける
では先ほどと同様に「年齢の列が25歳以上の行を抜き出し、名前の昇順に並び替え、行を連想配列に変換し、その配列で出力する」スクリプトをGASsmaを用いて書いていきます。
const gassma = new Gassma.GassmaClient();
function myFunction() {
const result = gassma.sheets.シート名.findMany({
where: {
年齢: {
gte: 25
}
},
orderBy: {
名前: "asc"
}
});
console.log(result);
}
はい、"インスタンスを生成してfindManyのメソッドを叩くだけ"です。列名もライブラリが自動で読んでくれます。prismaを触ったことがある方は分かると思いますが、文法はほぼ同じです。一応解説をすると
where: {
年齢: {
gte: 25
}
}
で年齢が25歳以上と条件を表すものをwhereの中に入れます。ちなみに25歳以上30歳以下だと以下のように書けます。
where: {
年齢: {
gte: 25,
lte: 30
}
}
出力時の並び替えの基準を書くのがorderByであり、
orderBy: {
名前: "asc"
}
"asc"と書けば昇順、"desc"と書けば降順となります。もし、名前が被っていた時、住所で並び替えさせたい場合はこう書きます。
orderBy: {
名前: "asc",
住所: "asc"
}
基本的に仕様はprismaとほぼ似ているので他にも行追加のcreate、createMany、削除のdeleteMany、行更新のupdateManyや統計用のaggregate等があります。
メソッドとしては以下の通りです。
| メソッド | 内容 |
|---|---|
| findMany | 条件にヒットしたものの全てを返す |
| findFirst | 条件にヒットしたものの最初の行を返す |
| updateMany | 条件にヒットしたものを更新する |
| upsert | 条件にヒットしたものを更新するが、なければ新しい行として追加する |
| create | 新しい行を追加する |
| createMany | 新しい行を複数行追加する |
| deleteMany | 条件にヒットしたものを複数削除する |
| aggregate | 条件にヒットしたものの数や合計、平均、最大最小値を返す |
| count | 条件にヒットしたものの数を返す |
| changeSettings | シートのテーブルの開始位置を指定する |
groupByはまだありませんが実装予定です。
導入方法
スプレッドシートから拡張機能 > Apps ScriptでGASを開き、「ライブラリ」の+マークをクリックします。
そうすると以上の画面が出てくると思うので、スクリプトIDに以下の文字列を入力してください。
1ZVuWMUYs4hVKDCcP3nVw74AY48VqLm50wRceKIQLFKL0wf4Hyou-FIBH
以下のようになれば成功です。
あとは追加ボタンを押すとライブラリが適用されます。
ライブラリの下に「Gassma」と書かれていれば適用されています。
それではざっくりgassmaの機能について解説していきます。
機能解説
findMany, findFirst
findMany()、findFirst()はシートから特定のデータを抽出したい時に使います。
クエリは以下の通りです。
| 名前 | 内容 | 省略 |
|---|---|---|
| where | 条件を指定する | 可 |
| select | 取得したい列名を指定 | 可 |
| orderBy | 取得した行のリストを並び替える | 可 |
| take | 取得数を決める | 可 |
| skip | 任意の行数をスキップできる | 可 |
| distinct | 重複を削除する | 可 |
gassma.sheets.シート名.findMany({
where: {
//ANDで複数条件書ける(ORやNOTもある)
AND: [
{
住所: "東京",
},
{
住所: "大阪",
},
],
年齢: {
gte: 25,
},
},
orderBy: {
名前: "asc",
},
select:{
名前: true,
年齢: true
},
skip: 2,
take: 5,
//distinctは配列でも書ける
distinct: "郵便番号",
});
whereを省略した場合全てのデータが取得できます。
また、whereの際のfilterの条件は以下の通りです。
| 名前 | 内容 | 省略 |
|---|---|---|
| equals | 等しいか | 可 |
| not | 等しくないか | 可 |
| in | リストに存在するか | 可 |
| notIn | リストに存在しないか | 可 |
| lt | 未満 | 可 |
| lte | 以下 | 可 |
| gt | より大きい | 可 |
| gte | 以上 | 可 |
| contains | 指定した文字列が含まれているか | 可 |
| startsWith | 指定した文字列から始まっているか | 可 |
| endsWith | 指定した文字列で終わっているか | 可 |
updateMany
updateMany()はシートから特定の行を更新する際に使います。
クエリは以下の通りです。
| 名前 | 内容 | 省略 | メモ |
|---|---|---|---|
| where | 条件を指定する | 不可 | 将来的には省略可にする予定 |
| data | 更新するデータを指定する | 不可 |
gassma.sheets.シート名.updateMany({
where: {
名前: "田中"
},
data: {
年齢: 23
}
});
upsert
upsert()はシートから特定の行を更新するが、特定の行が見つからなかった場合、新しい行として追加したいときに使います。
クエリは以下の通りです。
| 名前 | 内容 | 省略 |
|---|---|---|
| where | 条件を指定する | 不可 |
| update | 更新するデータを指定する | 不可 |
| create | 見つからなかった際に登録する行の内容 | 不可 |
gassma.sheets.シート名.upsert({
where: {
名前: "田中"
},
update: {
年齢: 23
},
create: {
名前: "田中",
年齢: 23,
住所: "Sendai",
郵便番号: "XXX-XXXX"
}
});
create, createMany
create(), createMany()は新しく行を追加したいときに使います。
クエリは以下の通りです。
| 名前 | 内容 | 省略 | メモ |
|---|---|---|---|
| data | 登録する行の内容 | 不可 | createは辞書、createManyは配列で指定 |
gassma.sheets.シート名.createMany({
data: [
{
名前: "佐藤",
年齢: 30,
住所: "Fukuoka",
郵便番号: "XXX-XXXX"
},
{
名前: "石川",
年齢: 28,
住所: "Yokohama",
郵便番号: "XXX-XXXX"
}
]
});
deleteMany
deleteMany()は行を削除したいときに使います。
クエリは以下の通りです。
| 名前 | 内容 | 省略 |
|---|---|---|
| where | 条件を指定する | 不可 |
gassma.sheets.シート名.deleteMany({
where: {
年齢: {
lt: 18
}
}
});
aggregate
aggregate()はヒットしたデータの合計等統計を取りたい際に使います。
クエリは以下の通りです。
| 名前 | 内容 | 省略 | メモ |
|---|---|---|---|
| where | 条件を指定する | 可 | |
| take | 取得数を決める | 可 | |
| skip | 任意の行数をスキップできる | 可 | |
| _avg | 平均を求める | 可 | 数値のみ |
| _sum | 合計を求める | 可 | 数値のみ |
| _count | ヒット数を求める | 可 | 空のセルの場合そのセルはカウントされない |
| _max | 最大値を求める | 可 | 文字列や日付でも可 |
| _min | 最小値を求める | 可 | 文字列や日付でも可 |
gassma.sheets.シート名.aggregate({
where: {
住所: "Nagoya"
},
_avg: {
年齢: true
},
_count: {
名前: true
}
})
count
count()はヒットしたデータのヒット数を求めたい際に使います。
クエリは以下の通りです。
| 名前 | 内容 | 省略 |
|---|---|---|
| where | 条件を指定する | 可 |
| take | 取得数を決める | 可 |
| skip | 任意の行数をスキップできる | 可 |
gassma.sheets.シート名.count({
where: {
年齢: {
gte: 20
}
}
});
changeSettings
chageSettings()はシートのテーブルの開始位置を指定する際に使います。
分かりやすく説明すると例えば以下のようにテーブルがシートの左上に来ていない際に使います。
上の画像の場合入出力処理を行う前に以下のように設定すると動作するようになります。
// 列名がある位置の行番号, 開始列番号, 終了列番号
gassma.sheets.シート名.changeSettings(4, 2, 5);
ローカルで開発する
claspを用い、GASをローカルで開発する場合はnpm等のパッケージ管理ツールで型ファイルがインストールできるので補完が効きます。
$ npm i gassma
最後に
まだ開発し始めてから日が経っておらず、β版です...
こちらで開発しているのでよければスター等していただけると私が泣いて喜びます😭😭😭