【GAS】Googleスプレッドシートをデータベースにするプログラムを作った

Googleスプレッドシートをデータベースのように扱えるプログラムを作ったので公開します。

ただし、まだいくつかの機能が不十分の未完成です。スプレッドシートにどのようにアクセスしているかがよくわかるように細かい処理は一旦省いています。今回のソースコードは最後に掲載しています。完成したらGitHub上で公開したいなと思っています。

このプログラムの概要

下図のように１つのスプレッドシートをデータベース化して複数のユーザーからアクセスするような仕組みです。読込み、新規追加、更新、レコード削除というプログラムはデータベース側のGASに書かれていて、ユーザー側でそれをコールして使用します。

 

 

データベース側のシートはどのようになっているかというと、まずデータテーブルがありデータはここに書かれます。下図は「KEN」となっていますが名前は任意です。日本語名がOKかどうかはまだ試してはいませんが英語名が無難です。もう１つ「QUERY」というシートがあって、中身は何も書かれていない空シートです。ユーザー側の指示に応じてここに関数式を書き込んでデータを検索させています。Excelマクロも同じですが膨大な量のデータから検索する場合は関数式と組み合わせた方が速度的に有利だからです。

 

 

GASのソースコードはこのページの最後に記載しています。

 

未完成部分・改善必要な点

• １列目をrow()関数でオートンバー化しているが、行削除すると採番し直してしまうのでユニークではあるがコロコロ変わってしまう。なので複数人で処理が集中すると事故りそう。→オートナンバーをプログラムで処理するか…。
• １行目からデータになっていて、データベースにおけるフィールド名というものが存在せず。フィルターをかけるときは列名（A, B, …）。→項目行を設けてプログラム内で列名変換させるか…。
• 主キーを設定するようになっていない。→今後追加する予定。空白を許可しないようにする。
• 最終行を検索する際に空白行があるとその部分をノーカウントして正しい行番号を返せない。→上の主キー（空白不可）が設定できればこの列で検索する。
• データ型の設定はしていないので数値、文字列どちらも許してしまう。→これはこのままかな。あまり複雑化させたくないので。

上記改善してももちろん大規模で使うようなデータベースにはなりえません。

 

ただ小規模で使う分には有効じゃないかなと思います。少なくとも会社でよくやるExcelファイルでのデータ管理のようなケースでは置き換え可能だと思います。Excelはやはり重いしファイルが壊れる確率は結構高いです。Googleスプレッドシートは壊れる心配はほぼないし何より軽快です。また、Webページに表現できる点も業務アプリに適しているんじゃないかなと思います。

 

使い方

今度はユーザー側の使い方についてです。

今回のデータベースとしては郵便局サイトから全国データを仕込んでいます。テーブル名（シート名）を「KEN」としています。

 

データベースのプログラムをライブラリとして取り込み済みです。ライブラリ名を「db」としました。ライブラリの設定方法はこちらをご覧ください。

 

それではユーザー側での使い方を列挙します。（データテーブルのD列に郵便番号データが入っています）

データ参照

var buf = db.readRecords("KEN", "WHERE D=5406123");
//あいまい検索の場合
var buf = db.readRecords("KEN", "WHERE D LIKE '540612%'");
//bufにヒットしたデータが配列で格納される
 

 

データ更新

//更新したくない箇所は空欄（null）
data = [,,,,,,'大阪府','大阪市中央区','城見ＭＩＤタワー（18F）',,,,,,];
var b = db.updateRecord("KEN", data, "WHERE D=5406118");
//あいまい検索の場合（ヒットしたデータをすべて書き換え）
var b = db.updateRecord("KEN", data, "WHERE D LIKE '540611%'");
//b=trueで成功
 

 

新規登録

data = [,,9999999,,,,'大阪府','大阪市中央区','城見ＭＩＤタワー（18F）',,,,,,];
var b = db.insertRecord("KEN", data);
//b=trueで成功
 

 

削除

var b = db.deleteRecords("KEN", "WHERE D=9999999");
//b=trueで成功
 

 

 

実行速度について

各機能について処理時間を測定しました。いずれも3秒以下で処理できました。LIKE句を使ったあいまい検索だともう少し遅くなりますがそれでも予想以上の速度でした。今回使用したデータは124,500行あります。このデータ量でこの速度であれば業務システムとしては十分でしょう。

 

ソースコード

 

const sheetID = "シートID";

//読み込み
function readRecords(table, filter){
  var sheet = SpreadsheetApp.openById(sheetID).getSheetByName('QUERY');
  if(sheet==null) return null;

  sheet.getRange("A1").setValue("=QUERY("+table+"!A:J,\""+filter+"\")");
  var c = sheet.getLastColumn();
  var r = sheet.getLastRow();
  var data = sheet.getRange(1,1,r,c).getValues();

  return data;
}

//新規追加
function insertRecord(table, data){
  var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(table);
  if(sheet==null) return false;

  row = getLastIndex(table);
  if(row==-1) return false;

  for(var i=0; i<data.length; i++){
    if(data[i]!=null){
      sheet.getRange(row+1, i+2).setValue(data[i]);
    }
  }

  return true;
}

function aa(){
  data = [,,,,,,'大阪府','大阪市中央区','城見ＭＩＤタワー（18F）',,,,,,];
  var b = updateRecord("KEN", data, "WHERE D LIKE '5406118%'");
  console.log(b);
}

//更新
function updateRecord(table, data, filter){
  var buf = readRecords(table, filter);
  if(buf=='#N/A') return false;
  var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(table);
  if(sheet==null) return false;

  for(var i=0; i<buf.length; i++){
    var row = buf[i][0];
    for(var j=0; j<data.length; j++){
      if(data[j]!=null){
        sheet.getRange(row,j).setValue(data[j]);
      }
    }
  }
  return true;
}


//削除
function deleteRecords(table, filter) {
  var buf = readRecords(table, filter);
  if(buf=='#N/A'){
    return false;
  }
  var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(table);
  if(sheet==null) return false;

  for(var i=buf.length-1; i>=0; i--){
    var id = buf[i][0];
    if(isFinite(id)) sheet.deleteRows(id);
  }

  return true;
};

function getLastIndex(table){
  var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(table);
  if(sheet==null) return -1;
  var maxRow = sheet.getMaxRows();
  if(sheet.getRange(maxRow,2).getValue()!=""){
    //もしデータが最終行まで来ていたら一番下に空行を100行追加する
    sheet.insertRowsAfter(maxRow, 100);
  }
  var row = sheet.getRange(sheet.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

  return row;
}
 

 

 - Google Apps Script