【Google App Script】経理業務の入金消込・支払消込をGASで自動化してみた

本記事の内容について

経理業務の入金消込・支払消込をGoogle App Scriptで自動化し、
業務効率化ができたので本記事にてアウトプット投稿します！

経理業務の入金消込・支払消込について

入金消込・支払消込

経理業務の入金消込・支払消込とは、
発生した債権(売掛金、未収入金など)・債務（買掛金、前受金など）に対応した入金、出金による債権・債務の消滅の仕訳を起票すること です。

平たく書きますと
債権がある＝お金がもらえるはず、債務がある＝お金を支払うはず、なのですが
その債権・債務に対して、お金をもらったか？お金を支払ったか？を記録する仕訳のことです。

以降、債権の入金消込について記載します。
※債務の出金消込については発生と消滅が貸借逆になるのみです

入金消込を正しくできているときは？

入金消込は取引先ごとに行います。

ある取引先Xに対して債権が発生した分と同じ金額だけ入金されて債権が消滅していれば
正しく入金消込（出金消込）ができていると言えます。

入金消込にミスがあるときは？

対して、債権に対する入金消込が正しくされていない場合は
ある取引先Xに対して債権が発生した分と入金により消滅する債権の金額が相違あるときです。

本決算で、会計年度を通して取引先ごとに入金消込・支払消込が正しく行われているかを総ざらいしてチェックすることになったのですが、手作業では非常にめんどくさいのです。。

自動化 Before / After

前提

Googleスプレッドシートにて、下図のような表で入金消込チェックを行います。
Z列：借方(債権発生)、AA列：貸方(債権消滅)で同じ金額同士色を塗ります。
色が塗られていないセルは同じ金額の対となる発生／消滅がないのでAD列に「異常値」フラグをつけることとします。

自動化前(手作業)

目視で金額が同じ発生／消滅のセルを探し→色を塗り→色が塗られていないセルを探し→「異常値」フラグを付与‥を取引先の数だけひたすら。。

自動化後(Google App Script実装)

GASコードにより自動化しました。

実装したGoogle App Scriptコード

入金消込 Google App Scriptコード

function colorMatch() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var zRange = sheet.getRange("Z2:Z");  // Z列（借方金額）
  var aaRange = sheet.getRange("AA2:AA"); // AA列（貸方金額）
  var adRange = sheet.getRange("AD2:AD"); // AD列（正常値/異常値）

  // Z列、AA列、AD列のリセット（色をクリア、異常値をクリア）
  zRange.setBackground(null);  // Z列の背景色をリセット
  aaRange.setBackground(null); // AA列の背景色をリセット
  adRange.setValue('');        // AD列の値をリセット

  var zValues = zRange.getValues();  // Z列（借方金額）
  var aaValues = aaRange.getValues(); // AA列（貸方金額）
 
  var colorMap = {}; // 発生額と消滅額の対応回数を記録するオブジェクト
  var colorList = [
    '#FF0000', // 赤
    '#0000FF', // 青
    '#00FF00', // 緑
    '#FFFF00', // 黄
    '#FF00FF', // 紫
    '#00FFFF'  // シアン
  ]; // 色リスト
 
  // Z列の発生額に対応する消滅額を検索
  for (var i = 0; i < zValues.length; i++) {
    var occurence = zValues[i][0];
   
    if (occurence !== "") { // Z列に値がある場合のみ検索
      for (var j = 0; j < aaValues.length; j++) {
        var discharge = aaValues[j][0];
       
        if (occurence === discharge && discharge !== "") { // AA列にも値がある場合のみ
          // すでに色が塗られているセルには対応しない
          var zCell = sheet.getRange(i + 2, 26); // Z列のセル
          var aaCell = sheet.getRange(j + 2, 27); // AA列のセル
          if (zCell.getBackground() !== '#ffffff' && zCell.getBackground() !== '') {
            // Z列のセルがすでに色塗りされている場合はスキップ
            continue;
          }
          if (aaCell.getBackground() !== '#ffffff' && aaCell.getBackground() !== '') {
            // AA列のセルがすでに色塗りされている場合はスキップ
            continue;
          }
         
          // 発生額と消滅額が一致した場合
          var key = occurence.toString();
         
          // 対応回数を更新
          if (!(key in colorMap)) {
            colorMap[key] = 0;
          }
          colorMap[key]++;
         
          // 対応回数に基づいて色を設定
          var colorIndex = (colorMap[key] - 1) % colorList.length;
          var color = colorList[colorIndex];
         
          // Z列とAA列に色を塗る
          sheet.getRange(i + 2, 26).setBackground(color);  // Z列（26列目）
          sheet.getRange(j + 2, 27).setBackground(color);  // AA列（27列目）
         
          break;  // 一度対応が決まったら次へ
        }
      }
    }
  }

  // Z列の値があり、色が塗られていないセルに「異常値」を入力
  for (var k = 0; k < zValues.length; k++) {
    var zCell = sheet.getRange(k + 2, 26); // Z列のセル
    if (zValues[k][0] !== "" && (zCell.getBackground() === '#ffffff' || zCell.getBackground() === '')) {
      sheet.getRange(k + 2, 30).setValue('異常値');  // AD列（30列目）
    }
  }
 
  // AA列の値があり、色が塗られていないセルに「異常値」を入力
  for (var k = 0; k < aaValues.length; k++) {
    var aaCell = sheet.getRange(k + 2, 27); // AA列のセル
    if (aaValues[k][0] !== "" && (aaCell.getBackground() === '#ffffff' || aaCell.getBackground() === '')) {
      sheet.getRange(k + 2, 30).setValue('異常値');  // AD列（30列目）
    }
  }
}

実際には

• 上記の処理を取引先ごとに分かれた総勘定元帳シートに対して繰り返し処理
• 「異常値」の取引レコードをある1シートに集約し、精査
  という流れで要精査のレコードを抽出していきました。

終わりに

手作業もやってみた所感、(作業工数)∝(取引先数)、(取引先との取引数)そのものでしたが、今回のGASコードを実装したことで、取引先数、取引先数が増えても工数にはほとんど影響しなくなったと思います。
経理業務をスケーラブルにするにおいて大きな味方のGAS、これからも使うしかない