prismaのような感覚でスプレッドシートを操作できるGASライブラリ "GASsma" #JavaScript

はじめに

みなさんNode.jsのORMは何が好きですか？TypeORMとかSequelizeとかあると思います。私はprismaが一番好きです。理由は大学時代に習ったMongoDBのクエリとどことなく似ていてとっつきやすかったからです。

ところでGoogle Apps Script(GAS)でスプレッドシートを操作するスクリプトを書いた際、非常に面倒だと思ったことはありませんか？(唐突)例えば以下のデータがあるとします。

このデータから「年齢の列が25歳以上の行を抜き出し、名前の昇順に並び替え、行を連想配列(キーは列名)に変換し、その配列で出力する」スクリプトを書きたいとします。
その場合以下のコードになるはずです。

gasExample1.js

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を用いて書いていきます。

gassmaExample.js

const gassma = new Gassma.GassmaClient();

function myFunction() {
  const result = gassma.sheets.シート名.findMany({
    where: {
      年齢: {
        gte: 25
      }
    },
    orderBy: {
        名前: "asc"
    }
  });

  console.log(result);
}

はい、"インスタンスを生成してfindManyのメソッドを叩くだけ"です。列名もライブラリが自動で読んでくれます。prismaを触ったことがある方は分かると思いますが、文法はほぼ同じです。一応解説をすると

gassmaExample.js

where: {
    年齢: {
        gte: 25
    }
}

で年齢が25歳以上と条件を表すものをwhereの中に入れます。ちなみに25歳以上30歳以下だと以下のように書けます。

gassmaExample.js

where: {
    年齢: {
        gte: 25,
        lte: 30
    }
}

出力時の並び替えの基準を書くのがorderByであり、

gassmaExample.js

orderBy: {
    名前: "asc"
}

"asc"と書けば昇順、"desc"と書けば降順となります。もし、名前が被っていた時、住所で並び替えさせたい場合はこう書きます。

gassmaExample.js

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	重複を削除する	可

example.js

  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	更新するデータを指定する	不可

example.js

  gassma.sheets.シート名.updateMany({
    where: {
      名前: "田中"
    },
    data: {
      年齢: 23
    }
  });

upsert

upsert()はシートから特定の行を更新するが、特定の行が見つからなかった場合、新しい行として追加したいときに使います。
クエリは以下の通りです。

名前	内容	省略
where	条件を指定する	不可
update	更新するデータを指定する	不可
create	見つからなかった際に登録する行の内容	不可

example.js

  gassma.sheets.シート名.upsert({
    where: {
      名前: "田中"
    },
    update: {
      年齢: 23
    },
    create: {
      名前: "田中",
      年齢: 23,
      住所: "Sendai",
      郵便番号: "XXX-XXXX"
    }
  });

create, createMany

create(), createMany()は新しく行を追加したいときに使います。
クエリは以下の通りです。

名前	内容	省略	メモ
data	登録する行の内容	不可	createは辞書、createManyは配列で指定

example.js

  gassma.sheets.シート名.createMany({
    data: [
      {
        名前: "佐藤",
        年齢: 30,
        住所: "Fukuoka",
        郵便番号: "XXX-XXXX"
      },
      {
        名前: "石川",
        年齢: 28,
        住所: "Yokohama",
        郵便番号: "XXX-XXXX"
      }
    ]
  });

deleteMany

deleteMany()は行を削除したいときに使います。
クエリは以下の通りです。

名前	内容	省略
where	条件を指定する	不可

example.js

  gassma.sheets.シート名.deleteMany({
    where: {
      年齢: {
        lt: 18
      }
    }
  });

aggregate

aggregate()はヒットしたデータの合計等統計を取りたい際に使います。
クエリは以下の通りです。

名前	内容	省略	メモ
where	条件を指定する	可
take	取得数を決める	可
skip	任意の行数をスキップできる	可
_avg	平均を求める	可	数値のみ
_sum	合計を求める	可	数値のみ
_count	ヒット数を求める	可	空のセルの場合そのセルはカウントされない
_max	最大値を求める	可	文字列や日付でも可
_min	最小値を求める	可	文字列や日付でも可

example.js

  gassma.sheets.シート名.aggregate({
    where: {
      住所: "Nagoya"
    },
    _avg: {
      年齢: true
    },
    _count: {
      名前: true
    }
  })

count

count()はヒットしたデータのヒット数を求めたい際に使います。
クエリは以下の通りです。

名前	内容	省略
where	条件を指定する	可
take	取得数を決める	可
skip	任意の行数をスキップできる	可

example.js

  gassma.sheets.シート名.count({
    where: {
      年齢: {
        gte: 20
      }
    }
  });

changeSettings

chageSettings()はシートのテーブルの開始位置を指定する際に使います。
分かりやすく説明すると例えば以下のようにテーブルがシートの左上に来ていない際に使います。

上の画像の場合入出力処理を行う前に以下のように設定すると動作するようになります。

example.js

  // 列名がある位置の行番号, 開始列番号, 終了列番号
  gassma.sheets.シート名.changeSettings(4, 2, 5);

ローカルで開発する

claspを用い、GASをローカルで開発する場合はnpm等のパッケージ管理ツールで型ファイルがインストールできるので補完が効きます。

$ npm i gassma

最後に

まだ開発し始めてから日が経っておらず、β版です...

こちらで開発しているのでよければスター等していただけると私が泣いて喜びます😭😭😭