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

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

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

基本の使い方
使用例

基本の使い方

ライブラリの追加

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

10SlidsgeSetyeNQDn0KooOAuGOAPBwyZRTPn5UJ06yQl0EMN7zDHojPx

使ってみる

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

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

使用例

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

GitHub - fromqtop/SSSQL: SSSQL is a Google Apps Script library that allows you to flexibly manipulate data in Google Sheets using SQL-like queries.

SSSQL is a Google Apps Script library that allows you to flexibly manipulate data in Google Sheets using SQL-like queries. - fromqtop/SSSQL

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" }
// ]