GAS: スプレッドシートをSQL風に操作するライブラリを作ってみた

Google Apps Scriptで、スプレッドシートをデータベースとして使いたいとき、毎回長いコード書くのめんどくさいなぁ・・・と思ったので、SQLっぽい構文でデータを操作できるライブラリを作ってみました。

誰でもご利用いただけるように公開していますので、興味あれば是非使ってみてください。

基本の使い方
使用例

基本の使い方

ライブラリの追加

スクリプトエディタの「ライブラリ」の + ボタンを押し、スクリプトIDに下記のIDを指定して検索する。

10SlidsgeSetyeNQDn0KooOAuGOAPBwyZRTPn5UJ06yQl0EMN7zDHojPx

使ってみる

下図のように、１行目が項目名、２行目以降がデータになっているシートを用意します。

下図では、columnsプロパティで対象の列を指定、whereプロパティで年齢と国を指定してデータを抽出しました。

使用例

この記事では、一部の使用例のみ紹介していきます。
詳細は、githubのreadmeをご確認ください。

Build software better, together

GitHub is where people build software. More than 150 million people use GitHub to discover, fork, and contribute to over 420 million projects.

select

columnsプロパティで取得対象の列を、whereプロパティで条件を指定できます。
whereプロパティで複数の条件を指定すると、AND条件になり、すべてを満たすレコードのみ抽出されます。
(OR条件にする場合は、whereOrプロパティを使用してください）

 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName("customers");
 
 const query = {
   columns: ["name", "age", "country"],
   where: {
     age: [">", 20],
     country: ["=", "USA"]
   }
 };
 
 const result = SSSQL.select(sheet, query);
 
 // result
 // [
 //   { name: "Alice", age: 30, country: "USA" },
 //   { name: "Bob", age: 25, country: "USA" }
 // ]

where（またはwhereOr）で指定できる比較演算子は、
=, <>, >, >=, <, <=, BETWEEN, NOT BETWEEN, IN, NOT IN, LIKE, NOT LIKEです。
詳細はgithubのreadme参照。

グループ化と集計も可能です。
集計関数として、COUNT, SUM, AVG, MIN, MAXが利用可能です。

const result = SSSQL.select(sheet, {
  groupBy: [
    ["job", "country"],
    { avg_salary: ["salary", "AVG"], max_salary: ["salary", "MAX"] }
  ]
});

// result
// [
//   { job: "Sales", country: "USA", avg_salary: 3500, max_salary: 7000 },
//   { job: "HR", country: "USA", avg_salary: 3800, max_salary: 8000 }
// ]

ソートも可能。

const result = SSSQL.select(sheet, {
  orderBy: { age: "ASC", name: "DESC" }
});

insert

１行挿入する場合はinsertを利用します。

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("customers");

const record = { 
  name: "Alice",
  age: 30,
  country: "USA"
};

const result = SSSQL.insert(sheet, record);
 
// result
// { name: "Alice", age: 30, country: "USA", job; null }

bulkInsert

複数行挿入する場合はbuklInsertを利用します。

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("customers");

const records = [
  { name: "Alice", age: 30, country: "USA" },
  { name: "Bob", age: 25, country: "USA" }
];

const result = SSSQL.bulkInsert(sheet, records);
 
// result
// [
//   { name: "Alice", age: 30, country: "USA", job: null },
//   { name: "Bob", age: 25, country: "USA", job: null }
// ]

update

行を更新する場合はupdateを利用します。

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("customers");

const query = {
  set: { phone: "090-1234-5678" },
  where: { id: ["=", "alice@example.com"] }
};

const result = SSSQL.update(sheet, query);

// result
// [
//   {
//     before: { id: "alice@example.com", name: "Alice", age: 30, country: "USA", phone: null },
//     after: { id: "alice@example.com", name: "Bob", age: 25, country: "USA", phone: "090-1234-5678" }
//   }
// ]

remove

行を削除する場合はremoveを利用します。（JSの仕様上、deleteという関数名にできなかった）

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("customers");

const query = {
  where: { id: "alice@example.com" }
}

const result = SSSQL.remove(sheet, query);

// result
// [
//   { id: "alice@example.com", name: "Alice", age: 30, country: "USA" }
// ]