フリーランスの方の請求書を代わりに作ってあげるシステム

この記事は？

GoogleAppsScriptの基本的な機能の組み合わせで簡単な業務システムを作りました。
自動化に興味がある方、小さな会社の業務に携わっている方、個人事業主の方に役立つ記事になれば幸いです。

背景

弊社ではフリーランスの方とお仕事をさせていただく時、月末に請求書を作って頂いています。
ご自分のフォーマットがあって積極的に作って送っていただける方もいれば、
どうしても事務的なことは苦手！代わりに作ってよっ！って方もいます。
その場合、作業時間をお聞きして代わりに請求書を作っていたのですが、
ちょっと面倒であり、先日その作業を自動化しましたので、その内容を書こうと思います。

仕組み

流れ

月末になると「報酬の金額」や「振り込み口座」の情報を元に、請求書を次々と自動生成し、確認メールを送る、というシステムです。

• 1. GoogleSpreadSheetから複数人のデータを取得
• 2. 請求書のテンプレート(GoogleDocument)をコピー
• 3. 前記データを使ってコピーした請求書のテキストを置換する
• 4. 各人に請求書をGmailで送り、確認してもらう

補足

今回ご紹介する仕組みと連動した、報酬や源泉徴収税を自動計算する仕組みがあるのですが、記事が長くなるので割愛します。

実装

サンプルデータ

• 請求書生成のための情報
• 請求書テンプレート

コード

//
// 月末になったら呼び出される、複数の請求書を作成する関数です
// 時間で呼び出すためのトリガーの設定に関してはたくさん記事があります
//
function makeInvoices() {
  // noto: IDだけでなくSpreadSheetの編集用URLを書いておくと後々便利です
  // https://docs.google.com/spreadsheets/d/1SnJDaTkmsIYOUsMJIqSnw4OzsCyzpPwsP1DuNxF54ys/edit#gid=1600793921
  var sheet = SpreadsheetApp.openById("1SnJDaTkmsIYOUsMJIqSnw4OzsCyzpPwsP1DuNxF54ys");
  // SpreadSheetには2つのシートがあります
  // まずは1つ目を取得, そして2時限配列にしておきます
  var payed_sheet = sheet.getSheetByName("PAYED_DATA");
  var payed_values = payed_sheet.getRange(1, 1, payed_sheet.getLastRow (), payed_sheet.getLastColumn ()).getValues ();
  // そして2つ目を取得
  var contract_sheet = sheet.getSheetByName("CONTRACT_LIST");
  var contract_values = contract_sheet.getRange(1, 1, contract_sheet.getLastRow (), contract_sheet.getLastColumn ()).getValues ();
  // 共通で使えそうな変数はここで宣言しておきます
  var last_payed_row = payed_sheet.getLastRow() - 1;
  var year = payed_values[last_payed_row][0].slice(0, 4);
  var month = extractMonthNumber(payed_values[last_payed_row][0]);
  var date = getMonthEnd(Number(month));
  //
  // 人数分for文を回します, 1行目はヘッダ行なので飛ばします
  //
  for (var contract_row = 1; contract_row < contract_values.length; contract_row++) {
    //
    // SpreadSheetからデータを取得します
    //
    // 振込名で同一人物かマッチさせています, 他の値を使ったほうが良いかも知れません
    var payed_row = -1;
    for (var row = payed_values.length - 1; 0 < row; row--) {
      if (contract_values[contract_row][5] === payed_values[row][1]) {
        payed_row = row;
        break;
      }
    }
    // エラー処理
    if (payed_row === -1) {
      // 契約はあるが一度もお金を払っていない場合です
      Logger.log("error:" + contract_row);
      continue;
    }
    var compare_year = payed_values[payed_row][0].slice(0, 4);
    var compare_month = extractMonthNumber(payed_values[payed_row][0]);
    if (year != compare_year || month != compare_month) {
      // 選出した列が今月のものではなかったので飛ばします
      continue;
    }
    // --
    var name = contract_values[contract_row][10];
    //
    // 請求書を作ります
    // https://docs.google.com/document/d/1ZYxfyQVHN45CVQ_Q2O4R5NBiLe9XF-NRKPkM-n7ZcxY/edit
    // ↑テンプレートのURL
    //
    var template = DriveApp.getFileById("1ZYxfyQVHN45CVQ_Q2O4R5NBiLe9XF-NRKPkM-n7ZcxY");
    // コピーした請求書を保管したいフォルダをIDで指定しましょう
    var folder = DriveApp.getFolderById("INSERT_FOLDER_ID");
    // あとから分かりやすいよう適当なファイル名をつけます
    var title = year + "年" + month + "月_" + name + "_請求書";
    // ファイルを作るときにフォルダを指定することで、そこに置くことができます
    var newfile = template.makeCopy(title, folder);
    var body = DocumentApp.openById(newfile.getId()).getBody();
    //
    // 作った請求書の文字を置換していきます
    //
    body.replaceText("ACCOUNT_NAME", contract_values[contract_row][5]); // 名前重複があるので最初に
    body.replaceText("NAME", name);
    // 住所は連続した３つマスなので、まとめてやっちゃいます
    for (var idx = 0; idx < 3; idx++) {
      body.replaceText("ADDRESS_" + idx, contract_values[contract_row][7 + idx]);
    }
    body.replaceText("FEE", payed_values[payed_row][5] + payed_values[payed_row][6]);
    // 会計でマイナスはときに▲と表します
    body.replaceText("TAX", "▲" + payed_values[payed_row][7]);
    body.replaceText("TOTAL", payed_values[payed_row][8]);
    // --
    body.replaceText("BANK", contract_values[contract_row][2]);
    body.replaceText("BRANCH", contract_values[contract_row][3]);
    body.replaceText("ACCOUNT", contract_values[contract_row][4]);
    // --
    body.replaceText("YEAR", year);
    body.replaceText("MONTH", month);
    body.replaceText("DATE", date);
    //
    // 確認メールを送ります
    //
    var email = contract_values[contract_row][11];
    // 本人がGoogleのアカウントで見ることができるよう、アクセス権を加えます（閲覧のみ）
    newfile.addViewer(email);
    var mailBody = name + " 様\n\n" +
                   "お世話になっています。\n" +
                   "◯ ◯ です。\n\n" +
                   "ご請求書を代理で作成いたしました。\n" +
                   "お手数ですが以下のURLから内容をご確認していただき、\n" +
                   "不備がある場合は３日営業日以内にご指摘いただけないでしょうか。\n" +
                   newfile.getUrl() + "\n\n" +
                   "どうぞよろしお願いいたします。" +
                   "// --" +
                   "◯ ◯ ";
    MailApp.sendEmail(email, "【ご確認お願いします】" + title, mailBody);
  }
}
//
// 別のシステムで作成した報酬データには「2019年11月_基礎データ」等のタイトルがつけられています
// そのタイトルの文字情報を元に、月の番号のみを取り出す関数です 
//
function extractMonthNumber(text) {
  var regex = /年([^\]]+)月/;
  return text.match(regex)[1]; 
}
//
// 月末の日を取得する関数です, 閏年は考慮しません
//
function getMonthEnd (month) {
  switch (month) {
    case 2: return 28;
    case 4:
    case 6:
    case 9:
    case 11: return 30;
    default: return 31;
  }
};

結果例

対象者に届くメール

代理作成した請求書

まとめ

ここまで作っておいてなんですが…
このシステム、弊社では月に1人か2人しか利用しないので手作業を続けるのとどちらが良いかは微妙です。
でも同じ仕組みで100人くらいまでは対応できると思うので、万が一たくさん人が増えても大丈夫！

修正記録

2020/02/11

対象行の検索方法に不具合がありましたので修正しました。
源泉徴収税は消費税を加える前の金額で計算するものなので、この記事のように「内税」の場合は含まれる消費税額を引いて源泉徴収税を計算する必要がありました。社内のシステムは修正をしたのですが、この記事では修正箇所が多くなるので、誰か要望があれば対応をしようと思います。