Help us understand the problem. What is going on with this article?

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

はじめに

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

問題点

スクリーンショット 2019-06-03 14.24.51.png

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

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

スクリーンショット 2019-06-03 14.30.20.png

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

コード

スクリプトエディタは[ツール]→[スクリプトエディタ]から立ち上げる
スクリーンショット 2019-06-03 14.34.43.png

コード.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;
  });
}

出力

スクリーンショット 2019-06-03 14.37.56.png
setJSON()を実行

スクリーンショット 2019-06-03 14.40.07.png

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を作成後、貼り付ける
スクリーンショット 2019-06-03 14.44.24.png

http://www.onicos.com/staff/iz/amuse/javascript/expert/inflate.txt
複合の処理を行いたいので、
上記のコードをコピーしてinflate.gsを作成後、貼り付ける
スクリーンショット 2019-06-03 14.48.50.png

コード.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シートを確認すると、
以下のように圧縮されていてますね!
スクリーンショット 2019-06-03 14.57.05.png

複合してみた

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

最後に

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away