スプレッドシートのデータをGoogle Apps Scriptで圧縮してみた

はじめに

2019年12月3日にGoogle Fusion Tablesの提供が終了するらしく、Google Apps ScriptのWebアプリケーションで使っている無料のデータベースが使えなくなる。
インフラコスト0でデータベースを運用したいのでGoogleスプレッドシートに移行することを考えたが、200万500万セル以上書き込めない問題があり、データを圧縮できないかと考えた。
Google Fusion Tables のご提供終了のお知らせ

問題点

例としてこのような上記画像のような日報兼退勤記録がある
このまま数年間複数ユーザが使うと200万セルの壁にぶつかりアプリケーションが正しく動作しなくなると想定される。
※1つのセルに格納できる最大文字数は50000文字

JSON文字列として1つのセルにまとめてみる

json文字列を格納するためにjsonシートを作成

コード

スクリプトエディタは[ツール]→[スクリプトエディタ]から立ち上げる

コード.gsに以下のソースを貼り付ける

コード.gs

function setJSON(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('json');
  //jsonシートの1,1（A1）にシート1のデータをJSON文字列として書き込む
  sheet.getRange(1,1).setValue(JSON.stringify(getData()));
}

//src https://qiita.com/takatama/items/7aa1097aac453fff1d53
//戻り値 シート1のJSONデータ
function getData() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');
  var rows = sheet.getDataRange().getValues();
  var keys = rows.splice(0, 1)[0];
  return rows.map(function(row) {
    var obj = {};
    row.map(function(item, index) {
      obj[String(keys[index])] = String(item);
    });
    return obj;
  });
}

出力

``setJSON()``を実行

JSON文字列として20セルのデータが1つのセルに入りました！！

JSON文字列をgzipで通常用いられている圧縮ルーチンを使って圧縮、複合

※1つのセルに50000文字を超える場合に使用

http://www.onicos.com/staff/iz/amuse/javascript/expert/
今回使うコードはこちらから引用

http://www.onicos.com/staff/iz/amuse/javascript/expert/deflate.txt
圧縮の処理を行いたいので、
上記のコードをコピーしてdeflate.gsを作成後、貼り付ける

http://www.onicos.com/staff/iz/amuse/javascript/expert/inflate.txt
複合の処理を行いたいので、
上記のコードをコピーしてinflate.gsを作成後、貼り付ける

コード.gsを以下のように変更する

コード.gs

function setJSON(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('json');
  //jsonシートの1,1（A1）にシート1のデータをJSON文字列を圧縮して書き込む
  sheet.getRange(1,1).setValue(deflate(JSON.stringify(getData())));
}

function getJSON(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('json');
  var data = sheet.getRange(1,1).getValue();
  
  Logger.log('文字列の長さ ' + data.length);
  //複合関数を呼び出し複合する
  Logger.log(inflate(data));
}

//src https://qiita.com/takatama/items/7aa1097aac453fff1d53
//戻り値 シート1のJSONデータ
function getData() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');
  var rows = sheet.getDataRange().getValues();
  var keys = rows.splice(0, 1)[0];
  return rows.map(function(row) {
    var obj = {};
    row.map(function(item, index) {
      obj[String(keys[index])] = String(item);
    });
    return obj;
  });
}

// 圧縮関数
function deflate(val) {
    val = encodeURIComponent(val); // UTF16 → UTF8
    val = zip_deflate(val); // 圧縮
    return val;
}

// 復号関数
function inflate(val) {
    val = zip_inflate(val); // 復号
    val = decodeURIComponent(val); // UTF8 → UTF16
    return val;
}

圧縮してみた

コード.gsのsetJSON()を実行後、jsonシートを確認すると、
以下のように圧縮されていてますね！

複合してみた

コード.gsのgetJSON()を実行後、スクリプトエディタの[表示]→[ログ]から確認すると
以下のように複合されていますね！

最後に

ここまでしてスプレッドシートを使う人多くはないだろう
筆者の環境で圧縮したい実際のデータを入れてみると、圧縮、複合に数分要することがわかったので、Webアプリケーションのデータベースとしてはこの手法は向いていないことがわかった。

小規模の場合は計算にそこまで時間がかからないので、問題はなさそう
小規模の場合は圧縮する必要がない...