thirayu
thirayu
googleフォームとスプレッドシートでデータベース管理をする

googleフォームとスプレッドシートでデータベース管理をする

こんにちは。カンムの平湯（ヒラユ）です。

この記事はカンムアドベンドカレンダー5日目の記事です。

昨日は人事のかつまたさんの「ポケモンカードのすゝめ」でした。やってたのはもう25年前とかですね。おばぁちゃんにカスミのデッキを買ってもらった記憶があります。ポケカ興味ある方はぜひ人事のかつまたさんにバトル申し込んでください。

さて

私はこれまで業務畑でオペレーションマンをやってきましたが、今年からPdMに役割を移行しつつあります。今よりもプロダクトに近い場所で力を発揮していくぜ！といった面構えで書いてます。

と言いつつ、オペレーション設計とか効率化とか好きなのは変わらずで、好きあらばGASを（google apps script）書いたりしていました。

そのなかで、会員登録の申請からデータベース管理をgoogleフォームとgoogleスプレッドシートでやってみたらどんな感じか試したことがあったので、そのへんを書きます。 （※セキュリティ面はgoogle側に依存しているのであくまで簡易なレコード管理というイメージです）

トピックはこんなところです。

  • googleフォームはとても便利だけど制約もあるよ
  • データベースの差分更新がこの1行でできるなんてスゴイ
  • キャンペーンコードを発番してメールを送る

シンプルな構成です

全体の構成はこんな感じです。googleフォーム経由でスプレッドシートに出力されたデータを一部加工して、差分を会員データベースに追加する仕組みです。さらに、外部のステータス情報をCSVで取り込んで更新をかけます。

image

googleフォームはとても便利だけど制約もあるよ

①フォーム上の入力制御があまりできない

  • データベースでは型を揃えておきたいのですが、フォーム側では制約しきれなかったので、一旦受け取って加工した上で取り込むことにしました。
  • 都道府県名「だけ」を取得したかったので、それらを入れた場合は削除する都道府県チェッカ関数だったり、数値だけがほしい項目に要らない文字列（「年」とか）が入っていたら削除する関数を入れて回避しました。
    •   //関数：「お住いの地域」に「都/府/県」が合った場合は削除
  function stateCheck(files, row, column){
    if(files[row][column].match(/.*[都府県]/) != null){
      var _stateName = files[row][column].slice(0, -1);
      files[row].splice(column, 1, _stateName);
      }
  };

  //関数：「経験年数」に数字以外があったら削除
  function yearCheck(files, row, column){
    if(isFinite(files[row][column]) == false){
      var _result = files[row][column].replace(/[^0-9]/g, '');
      files[row].splice(column, 1, _result);
    }
    return row += 1;
  };

②添付ファイル受けるにはいくつか制約がある

  • 添付ファイルを要求する場合は、回答者はgoogleユーザーに限定されます。
  • 添付ファイルは、フォーム作成者の個人ドライブに保存されます。
  • ファイルの数とサイズの指定が必要です。
googleフォームのキャプチャ
googleフォームのキャプチャ

データベースの差分更新がこの1行でできるなんてスゴイ

  • 申請があった都度、生データファイルを加工してぜんぶ張り替えるのは、データ量に寄っては負荷が大きい&時間がかかってしまうので、差分更新できるようにしました。
  • 二次元配列同士で差分を取得して、一番下のレコードに追加すれば更新完了です。
    • var aFile = [[山田, 兵庫, 20歳],[高橋, 鹿児島, 29歳],[田中, 北海道, 45歳]];
var bFile = [[山田, 兵庫, 20歳],[高橋, 鹿児島, 29歳]];

var Files = aFile.filter(function(e){return bFile.filter(function(f){return e.toString() == f.toString()}).length == 0});

Logger.log(Files); //[[田中, 北海道, 45歳]]
    二次元配列の “aFile（生データファイル）” と 二次元配列の ”bFile（）” の差分だけ取得する

  • ちなみに、トリガ設定による自動更新とファイルのメニューボタンにおる手動更新ができるようにしており、更新がない場合は「更新がありません」とメッセージボックスが出るようにしています。
    • image
image

キャンペーンコードを発番してメールを送る

差分更新されたあとに、キャンペーンコード（招待コードとかを想定）を発番します。そのキャンペンコードをサンクスメールに載せて送る仕組みになっています。

①スプレッドシートを強制的に更新させる

  • 前のブログでも書いた気がしますが、スプレッドシートを強制的に更新させるこれSpreadsheetApp.flush(); めちゃくちゃ大事です。
  • 更新したデータでさらに何かの仕組みを動かそうとしたとき、きちんと前処理が終わっている状態になってないとバグります。テストを何度かやって必要なとこにこれを置いてます。
    • SpreadsheetApp.flush(); //スプレッドシートを強制的に更新させる

②メール自動送信の発信者設定や実行権限の設定

  • 送信元メールアドレスを設定します。初期設定では作成者のメールアドレスになっているので、スクリプトで設定します。
  • 加えて、エイリアスを取得して、実行者がエイリアスを所有・設定していない場合にスクリプトを終了する設定にできます。（シートやGASのファイルへのアクセス制限は別途やる）
    • var fromAddress = "hogehuga@pemail.co.jp" //送信元メールアドレス

//メールエイリアスを取得し、変数aliasesに格納する
var aliases = GmailApp.getAliases();

//変数fromAddressに格納されているメールアドレスのメールエイリアスを所有・設定していない場合、スクリプトを終了する
if(aliases.indexOf(fromAddress) === -1) {
   return;
  }

おまけ

  • onEdit（e）で更新/編集されたセルを特定できます。例えば、編集したレコードでルール外の入力をしたらメッセージボックスで知らせたりできます。
  • 数値以外が入力されたときにこれあれば便利！と思って作ってたのですが、なんとスプレッドシートの既存機能である「データ入力規則」でできてしまいました。。
  • こんなものを書いていましたが、、
    • function onEdit(e) {
  var ss = SpreadsheetApp.openById('シートID').getSheetByName('シート1');
  var range = ss.getRange('A1:C10')

  var row = e.range.getRow();
  var col = e.range.getColumn();

  var data = ss.getRange(row, col).getValue();
  var columnAl = getColName(col);

  if(isNaN(data) ==true){
    Browser.msgBox(row +'行目の'+columnAl +'列に数値以外が入力されています。正しく金額を入力してください。');
  }


function getColName(num) {
  var ss = SpreadsheetApp.openById('シートID').getSheetByName('シート1');
  var result = ss.getRange(1, num);
  result = result.getA1Notation();
  result = result.replace(/\d/,'');
 
  return result;
}
}
  • “データの入力規則” で実現できました。スプレッドシートすごい！
image
image

おしまい