【自前でBO Tech】GAS（Google Apps Script）とスプレッドシートを連携させて収支の週次レポートをオートメーション化してみた

こんにちは、LIGセブ支社代表のせいと（@seito_horiguch）です。

最近「Fin Tech」とか「HR Tech」とかって言葉が流行っていますが、「BO Tech（バックオフィス テック）」という言葉もあるそうです。

なんか響きがいいですね。BO Tech。

つーわけで今回は手数が多いバックオフィス系の作業をちょっとしたテクノロジーで解決しようと思います。これがBO Techになるかわかりませんが、とりあえず言いたいだけなので言わせてください。BO Tech。

「どんな仕事だってテクノロジーで効率化できまっせ。ほにゃららTechしていこうぜ」というコンセプトのもと、進めていきたいと思います。

テーマ：収支の週次報告

さて、今回のテーマとなるやっつけたい非効率的な作業はこちら。

 
※ 実際のレポート内容をお見せするのはまずいので、数字はすべてダミーです。

なにかというと、週次の収支報告です。

LIGではある程度のポジション以上のメンバーは毎週金曜日の午後に自チームの収支・売上状況を集計し、それをSlackにて本社役員に報告しなければなりません。

義務なんですけど、これがだるくて僕はあまりできていません。てへ。

上記のように、元となる収支データは各リーダーにスプレッドシートにて、統一されたフォーマットで支給されており、これを見れば一目瞭然です。

が、収支報告はここから下記のデータを抽出して報告形式を再形成しなければなりません。

• 目標売上
• 実際の売上
• 実際の売上（見込み）
• 達成率

これらを月ごと、クオーターごと、上期下期ごとの3つの観点から報告しなければなりません。

で、今のところどうやっているかというと、スプレッドシートを目視で見ながらポチポチと……。非効率で発狂しそうなので、もうオートメーション化しちゃいます。

課題と解決策

前提となる仕様

簡単なルーティン業務であれば簡単な方法はいくつも思いつきますが、今回のこれはちと厄介な仕様です。

厄介ポイント

1. 決められたフォーマットで報告しなければならない
2. 最新のデータでなければいけないが、報告日が毎週金曜日なので実質木曜夜〜金曜夕方までの24時間以内に集計の必要がある
3. 月が変わる、クオーターが変わる、上期下期が変わる、のタイミングで報告すべき事項も変わる
4. その他、【課題と対応策（具体的に）】というテーマで何らかのコメントを書かないといけない

とくに3、4が動的な仕様で厄介ですね。

4はもうAIでもないと無理そうなんで今回は諦めますが、3くらいであれば自動化できそうなのでやってみましょう。

使用するもの：GAS（Google Apps Script）

今回は元となるデータがスプレッドシートで作成されているので、GAS（Google Apps Script）を使います。

GAS（Google Apps Script）

GAS（Google Apps Script）とは、Google カレンダー、Gメール、Drive、スプレッドシートなどといったGoogle Appsを自前で拡張するためのスクリプト言語のこと。
JavaScriptとクリソツってかもうほぼJavaScriptみたいなもんだからフロントエンドエンジニアのみんなはガンガン使っていこうな。

今回は収支表のスプレッドシートを拡張させたいので、シートを開いたら「ツール → スクリプトエディタ」と進めましょう。

開くとGASの編集画面になります。

GASを書くにあたり、とりあえず今の時点では下記だけ押さえておきましょう。

• GASでは拡張子が.gsになる。
• gsファイルを新規作成したい場合は「ファイル → 新規作成 → スクリプト　ファイル」

実際に書いたコードと設計

さて、百聞は一見に如かず。

GASで実際に書いたサンプルコードはLIG Philippinesの「GitLab」と個人の「Google Drive」に置きましたのでご参照あれ。

サンプルコード

スプレッドシートのデモ
auto-generator-of-finance-reports in Gitlab

設計

じゃあどういう順番に書いてどう解決すっかなーっていう設計の部分ですが、ざっくりこんな感じで。

1. あらかじめシートのセルから値を抽出し、取り出すいように変数か配列かにしておく
2. レポートのフォーマットに合わせてテキストとセルの値を組み合わせる
3. 月をまたいだりなどレポート内容が変わるタイミングを考慮して、2の中に条件分岐を含める
4. 1-3が毎週決められたタイミングで実行されるようタイマーを設定

ファイル構造

次に、冗長になるのを避けるため、gsファイルを3つに分割しました（GASでもJavaScript同様、ファイルを分けて管理できるっぽいです）。

主要なコード

コードぜんぶ書くと長ったらしいので、本記事では一部だけ抜粋して解説します。

下記は主要なファイルであるmain.gsのコードをピックアップ。

function main() {

  //Difine Spreadsheet App
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  //Get sheet
  var sheet = spreadsheet.getSheetByName('集計');
  var range = sheet.getRange('A1:BZ59');
  var data = config(range);

  //Define array for report texts
  var reports = [];
  
  //Half yearly report
  var createHalfYearlyReport = function(){

    //Get data of this month
    var monthVal = getMonthKey() + 1; //Exact number of month
    var halfYear;
    
    // Judge "1st half of Year" or "2nd half of Year"
    if(4 <= monthVal && monthVal <= 9){
      halfYear = 'firstHalfYear';
    } else {
      halfYear = 'secondHalfYear';
    }
    
    reports.push(createReport(data[halfYear], halfYear));
  };
  
  //Quarter
  var createQuarterReport = function(){

    //Get data of this month
    var monthVal = getMonthKey() + 1; //Exact number of month
    var quarter;
    
    if(4 <= monthVal && monthVal <= 6){
      quarter = 'quarter1';
    } else if(7 <= monthVal && monthVal <= 9){
      quarter = 'quarter2';
    } else if(10 <= monthVal && monthVal <= 12){
      quarter = 'quarter3';
    } else { // Jan - May
      quarter = 'quarter4';
    }
    
    reports.push(createReport(data[quarter], quarter));
  };
  
  //3 monthly reports
  var createMonthlyReport = function(){
    
    //Get data of this month
    var monthVal = getMonthKey() + 1; //Exact number of month
    var monthIndex = getMonthKey(); // for array
    var monthName;
    
    var i = 0;
    var limit = 3;
    
    // Repeat 3 times
    while(i < limit){
      
      //Avoid to get minous values
      if(monthIndex + i < 0){
        monthIndex = 0;
      }
      if(monthVal + i < 1){
        monthVal = 1;
      }
      
      monthName = getMonthName(monthIndex + i);
      reports.push(createReport(data.month[monthName], monthVal + i));
      
      i++;
      
      //If the month is over Dec(11), it will be reseted to jan(0)
      if(monthIndex + i > 11){
        monthIndex = -2;
      }
      if(monthVal + i > 12){
        monthVal = -1;
      }
    }
  };
  
  //Run functions & Unify Report
  var updateReport = function(){
    reports.push(getGroupName());
    createHalfYearlyReport();
    createMonthlyReport();
    createQuarterReport();
    reports.push('【課題と対応策（具体的に）】\n' +'{ ここに任意のテキストを入れる }');
    
    //Write to sheet
    spreadsheet.getSheetByName('レポート').getRange(1, 1).setValue(reports.join('\n'));
    
    //For debug
    Logger.log(reports.join('\n'));
  }
  updateReport();
}

エンジニアの方へ。下記ハマりポイントっす。

• GASでES6は対応してないっぽい
• window.alert()やconsole.log()は使えないっぽい。代わりにLogger.log()を使おう。実行結果はcommand+Entryで確認できる
• 特別何かしなくてもファイル分割できるし複数ファイルをまたいで変数を再利用したり関数を実行することはできる
• ただし、グローバル関数は指定できないっぽい。今回どうやっているかは実際にconfig.gsを参考にしてちょ

実行・タイマー設定

さて、これにてスクリプトは書き終わりましたので実行ボタンを押せばレポート文章を生成できます。

が、毎回手動ではだるいのでタイマーを設定し、毎週決められた時間に自動で実行されるよう設定します。

設定方法はめっちゃ簡単で、「時計マークをクリック → トリガー設定画面に遷移 → トリガーを追加 → 編集画面でタイミングやらを設定」でいけます。

（本当はこれに加えてSlack連携までやろうかと思いましたが、【課題と対応策（具体的に）】は自前で書かなきゃいけないのと、Slack連携は管理者権限が必要なのであまり好き勝手できないことからやめておきました）

まとめと宣伝（採用のお知らせ）

いかがだったでしょうか。

テクノロジーを使えばこういった日々の雑務も効率化することができます。

これ以外にも、僕はPythonやGoogle extensionで単純作業を自動化したりしているので、次回そういった話ができればと思います。

あと関係ないですがLIGセブ支社では各種ポジション募集中なので、興味ある方はぜひ！

LIGはWebサイト制作を支援しています。ご興味のある方は事業ぺージをぜひご覧ください。