【スプレッドシート入門】自動入金突合を実現する！②

復習：振込人名義と金額で突合

前回、GASとスプレッドシートを使った、入金突合を実装しました。

この時は、繰り返し処理と条件分岐を使って、振込人名義と金額で突合して、突合処理を実装しました。

【スプレッドシート入門】自動入金突合を実現する！①

GoogleスプレッドシートとGASを活用した業務自動化の一例として、自導入kに突合を取り上げます 覚えておくべき使い方は立った二つ、「繰り返し処理」と「条件分岐」です。 復習：繰り返し処理と条件分岐 Googleスプレッドシートでは、GA...

hitotsu-eye.com

2022.12.20

ただ、このやり方だと、金額が完全に一致していなければ、突合はできません。
ところが、振り込みの間違いなどで、入金額は必ずしも一致するとは限らず、したがって、余剰や不足が発生した場合についての処理も考える必要があります。

そこで図のように、債権リストに債権残高の列を、入金リストに充当額の列をそれぞれ設定し、余剰や不足があっても処理ができるようにしていきたいと思います。

余剰や不足の処理分岐

条件分岐を作る

ここまで準備ができたところで、実際の金額を見ながら、条件分岐を作っていきます。

今回は、債権額ではなく、「債権残額」を確認する必要があるため、債権リストのD列ではなくF列を見る必要があることに注意します。

function myFunction() {
  const activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet()
  var creditsheet = activeSpreadSheet.getSheetByName('債権リスト');
  var paymentsheet = activeSpreadSheet.getSheetByName('入金リスト');
  for(let i = 2; i <= 5; i++) {
    for(let j = 2; j <= 5; j++) {
      if(creditsheet.getRange('E' + j).getValue() == paymentsheet.getRange('A' + i).getValue()){
        if(creditsheet.getRange('F' + j).getValue() == paymentsheet.getRange('B' + i).getValue()){
          // 金額一致の場合の処理
        }
        else if(creditsheet.getRange('F' + j).getValue() > paymentsheet.getRange('B' + i).getValue()){
          // 不足の場合の処理
        }
        else if(creditsheet.getRange('F' + j).getValue() < paymentsheet.getRange('B' + i).getValue()){
          // 余剰の場合の処理
        }
      }
    }
  }
}

それぞれの場合の処理

そのうえで、それぞれの場合の処理を入れていきます。

具体的には、金額一致・不足・余剰でそれぞれ次の通りになります。

• 金額一致
  →債権リストは残額を0円にし、すべて回収したことにして、入金済みにする
  　入金リストの充当金額も、入金額全額にする
• 不足
  →債権リストは残額を不足額にし、入金済みにはしない
  　入金リストの充当金額は、入金額全額となる
• 余剰
  →債権リストは残額を0円にし、すべて回収したことにして、入金済みにする
  　入金リストの充当金額は、回収額とし、残額を次の行に入れる

実装すると、次の通りになります。

function myFunction() {
  const activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet()
  var creditsheet = activeSpreadSheet.getSheetByName('債権リスト');
  var paymentsheet = activeSpreadSheet.getSheetByName('入金リスト');
  for(let i = 2; i <= 5; i++) {
    for(let j = 2; j <= 5; j++) {
      if(creditsheet.getRange('E' + j).getValue() == paymentsheet.getRange('A' + i).getValue()){
        if(creditsheet.getRange('F' + j).getValue() == paymentsheet.getRange('B' + i).getValue()){
          // 金額一致の場合の処理
          creditsheet.getRange('F' + j).setValue(0);
          creditsheet.getRange('G' + j).setValue('入金済');
          paymentsheet.getRange('D' + i).setValue(paymentsheet.getRange('B' + i).getValue()); 
        }
        else if(creditsheet.getRange('F' + j).getValue() > paymentsheet.getRange('B' + i).getValue()){
          // 不足の場合の処理
          creditsheet.getRange('F' + j).setValue(creditsheet.getRange('F' + j).getValue() - paymentsheet.getRange('B' + i).getValue());
          paymentsheet.getRange('D' + i).setValue(paymentsheet.getRange('B' + i).getValue()); 
        }
        else if(creditsheet.getRange('F' + j).getValue() < paymentsheet.getRange('B' + i).getValue()){
          // 余剰の場合の処理
          paymentsheet.getRange('D' + i).setValue(creditsheet.getRange('F' + j).getValue()); 
          creditsheet.getRange('F' + j).setValue(0);
          creditsheet.getRange('G' + j).setValue('入金済');
          paymentsheet.insertRowAfter(i)
          paymentsheet.getRange('A' + (i + 1)).setValue(paymentsheet.getRange('A' + i).getValue() ) 
          paymentsheet.getRange('B' + (i + 1)).setValue(paymentsheet.getRange('B' + i).getValue() - paymentsheet.getRange('D' + i).getValue() ) 
          paymentsheet.getRange('C' + (i + 1)).setValue(paymentsheet.getRange('C' + i).getValue() ) 
        }
      }
    }
  }
}

少々長いですが、一つ一つの処理は上記に記載した通りです。
余剰の場合には、先に入金リストの充当金額を入れないと、債権残額を先に更新してしまうと、「0」になってしまうので注意が必要です。

実行結果を確認

実行結果を確認すると、次の通り、綺麗に突合されていることが分かります。