給与計算をGoogleスプレッドシートとGoogle Apps Script で！

こんにちは。制作担当のユースケです。

いきなりですが、みなさん給与計算ってどうしてますか？

小規模店舗だと専用アプリ入れるのも大げさですよね。
かといって、全部手作業なんて時間ばかり取られて大変です。

そこで【Googleスプレッドシート】と【Google Apps Script】を使って、
自動化しましょうというのが今日のお題です！
（※Google Apps Script（以下GAS）はJavaScriptで操作するスクリプト環境です。）

Google スプレッドシート
Google Apps Script

Excelでいいじゃんという声もあるかもしれませんが、ファイル共有とか考えると現実的じゃないかなということで
Web上で簡単に共有できるGoogleサービスを採用です！無料ですし。

計算ルールは以下のとおり。
　■ 基本給は900円
　■ 朝9時までは1000円
　■ 夜22時以降は1125円
　■ 8時間以上の勤務は1125円

まずは、スプレッドシートのデータを取得しましょう。

  // Activeなシートを取得
  var sheet = SpreadsheetApp.getActiveSheet();
　// データが入力されている範囲を取得
  var rows = sheet.getDataRange();
  // 取得範囲の行数を取得
  var numRows = rows.getNumRows();
  // 取得範囲のデータを配列で取得
  var vals = rows.getValues();

取得したデータは、行ごとのデータをまとめた多次元配列なので
行位置を指定することで各行の値を操作できます。
今回は勤務時間の入力開始位置からループさせて処理します。

  
  for  (var i= 7; i <= numRows -1; i++) {
    var st, ed, re;
    try {
      var row = vals[i];
      st = row[2];  // 始業時刻
      ed = row[3];  // 終業時刻
      re = row[4];  // 休憩時間
      
      // 結果の出力位置を取得
      if (row[6].indexOf("通常時間") >= 0) sr = i + 1;
      
      // 始業時刻が空の場合、次行へ
      if (st == "") continue;
      
      var st_h = st.getHours();
      var st_m = st.getMinutes();
      var ed_h = ed.getHours();
      var ed_m = ed.getMinutes();
      
      var t1 = st_h * 60 + st_m;
      var t2 = ed_h * 60 + ed_m;

    } catch (e) {
      Browser.msgBox(i + "行目にエラーがあります");
      break;
    }
  }

ループ内で「始業時刻」と「終業時刻」を取得し、分単位に変換しています。

「始業時刻」と「終業時刻」から勤務時間を算出します。
「休憩時間」を控除したのち、時給金額に応じた時間帯ごとに勤務時間を算出します。

    var min0 = t2 - t1;    // 勤務時間

    // 休憩時間を控除
    if (re != "") {
      var re_h = re.getHours();
      var re_m = re.getMinutes();
      var t3 = re_h * 60 + re_m;
      min0 -= t3;
    }

    // 深夜時間を算出
    var tN = LATE_NIGHT * 60;
    if (tN < t2) {
      min0 -= (t2 - tN);
      min3 += (t2 - tN);
    }
    
    // 8時間超過分を算出
    if (min0 > 480) {
      min4 += (min0 - 480);
      min0 = 480;
    }
    
    // 早朝時間を算出
    var tM = EARLY_MOR * 60;
    if (tM > t1) {
      if (tM > t2) tM = t2;
      min0 -= (tM - t1);
      min2 += (tM - t1);
    }
    
    min1 += min0;

ループ内で集計した各時間をもとに給与を計算し、
勤務時間と給与をスプレッドシートへ出力します。

  // スプレッドシートへの書き込み
  var setVal = function (name, value) {
    sheet.getRange(name).setValue(value);
  }

  // 分単位を「h:m」表記に変換
  var m2h = function (min) {
    var h = Math.floor(min / 60);
    var m = min % 60;
    return h + ":" + ("0" + m).slice(-2);
  }
  
  // 勤務時間を出力
  var min_all = min1 + min2 + min3 + min4;
  setVal("I"+sr, m2h(min1));           // 通常
  setVal("I"+(sr+1), m2h(min2));       // 早朝
  setVal("I"+(sr+2), m2h(min3+min4));  // 深夜+超過
  setVal("I"+(sr+3), m2h(min_all));    // 総計
  
　// 給与を出力
  var s1 = (PAY_1 / 60) * min1;
  var s2 = (PAY_2 / 60) * min2;
  var s3 = (PAY_3 / 60) * (min3 + min4);
  var s0 = Math.ceil(s1 + s2 + s3);
  setVal("J"+sr, s1);
  setVal("J"+(sr+1), s2);
  setVal("J"+(sr+2), s3);
  setVal("J"+(sr+3), s0);
  
};

最後に作った関数を実行するトリガーを実装します。
今回はメニューに追加してみました。

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "計算実行",
    functionName : "calc"
  }];
  sheet.addMenu("給与計算", entries);
};

ということで、纏めると下記のようになります。

var PAY_1 = 900;     // 通常
var PAY_2 = 1000;    // 早朝
var PAY_3 = 1125;    // 深夜
var PAY_4 = 1125;    // 8時間以上勤務
var EARLY_MOR = 9;   // 早朝時給の終了時間
var LATE_NIGHT = 22; // 深夜時給の開始時間

function calc() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var vals = rows.getValues();
  var sr = 0;
  
  var min1 = 0;  // 通常
  var min2 = 0;  // 早朝
  var min3 = 0;  // 深夜
  var min4 = 0;  // 8時間以上勤務
  
  for  (var i= 7; i <= numRows -1; i++) {
    var st, ed, re;
    try {
      var row = vals[i];
      st = row[2];  // 始業時刻
      ed = row[3];  // 終業時刻
      re = row[4];  // 休憩時間
      
      // 結果の出力位置を取得
      if (row[6].indexOf("通常時間") >= 0) sr = i + 1;
      
      // 始業時刻が空の場合、次行へ
      if (st == "") continue;
      
      var st_h = st.getHours();
      var st_m = st.getMinutes();
      var ed_h = ed.getHours();
      var ed_m = ed.getMinutes();
      
      var t1 = st_h * 60 + st_m;
      var t2 = ed_h * 60 + ed_m;

    } catch (e) {
      Browser.msgBox(i + "行目にエラーがあります");
      break;
    }

    var min0 = t2 - t1;

    // 休憩時間を控除
    if (re != "") {
      var re_h = re.getHours();
      var re_m = re.getMinutes();
      var t3 = re_h * 60 + re_m;
      min0 -= t3;
    }

    // 深夜時間を算出
    var tN = LATE_NIGHT * 60;
    if (tN < t2) {
      min0 -= (t2 - tN);
      min3 += (t2 - tN);
    }
    
    // 8時間超過分を算出
    if (min0 > 480) {
      min4 += (min0 - 480);
      min0 = 480;
    }
    
    // 早朝時間を算出
    var tM = EARLY_MOR * 60;
    if (tM > t1) {
      if (tM > t2) tM = t2;
      min0 -= (tM - t1);
      min2 += (tM - t1);
    }
    
    min1 += min0;
  }
  
  var setVal = function (name, value) {
    sheet.getRange(name).setValue(value);
  }
  var m2h = function (min) {
    var h = Math.floor(min / 60);
    var m = min % 60;
    return h + ":" + ("0" + m).slice(-2);
  }
  
  var min_all = min1 + min2 + min3 + min4;
  setVal("I"+sr, m2h(min1));           // 通常
  setVal("I"+(sr+1), m2h(min2));       // 早朝
  setVal("I"+(sr+2), m2h(min3+min4));  // 深夜+超過
  setVal("I"+(sr+3), m2h(min_all));    // 総計
  
  var s1 = (PAY_1 / 60) * min1;
  var s2 = (PAY_2 / 60) * min2;
  var s3 = (PAY_3 / 60) * (min3 + min4);
  var s0 = Math.ceil(s1 + s2 + s3);
  setVal("J"+sr, s1);
  setVal("J"+(sr+1), s2);
  setVal("J"+(sr+2), s3);
  setVal("J"+(sr+3), s0);
  
};

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "計算実行",
    functionName : "calc"
  }];
  sheet.addMenu("給与計算", entries);
};

いかがでしたでしょうか。
GASを使えば、Googleスプレッドシートもワンランク上の使い方ができます。
しかもGASはスプレッドシート専用ではなく、Googleの他のサービスでも使えるので
是非いろいろ試してみてください。
イイ使い方があったら、こっそり教えてくださいね。(笑)