家賃計上をGASで簡単に【経理・労務実務】
一つの請求書に、計上月が異なる情報が登録されているとき。
家賃の請求書であったり、デポジットを含んだ取引をしている請求書などので、単純な仕訳にするとBSの精査が必要になるケースを簡単に解決できるモデルです。
文章だけでは理解しにくいのでこちらのスプレッドシートを手元にコピーしてどうぞ
「請求書例」「入力」「計上」の３つのシートで構成されています。
請求書の例
一つの請求書に、「前払」「当月」「翌月」３つの支払いサイクルが混在しているケース、一枚二枚ならなんとでもなりますが、複数枚あったり、担当者が変わったりするとBSの精査が必要になり、結構時間がかかります。
ではどうするか
まずは請求書情報をスプレッドシートにリスト化
シート「入力」ご参考ください
こんなイメージです。フォームの違いはともあれ、データ化してるっていう会社は少なくはないのではないでしょうか。
計上まで自動化するこだわりとしては、
・各項目をすべて表示する。
・項目に対応するBS/PLを明記する
・支払合計ベースで作成する
・それを基準に、いつの費用か記載する
という点です。
各社合計入れてますので、支払に活用することもできますが、それはオマケです。
※2023年10月以降のインボイス制度のおかげで、税込入力・税抜入力どちらがいいのかという複雑性は上がりましたが、本稿ではGASを使って仕訳を生成することが目的、また、実践的なGAS活用事例が目的ですので各項目は税込み表示でエビデンスがそろっていると考えてください。
支払計上
詳細はリンク先の「計上」をご覧ください
ここは「入力」から転記するだけにしています。「5月」を「6月」に変更すると「6月」の支払額が反映されます。
事前準備は、BSの勘定科目・支払い時に発生する口座項目が必要です。
スクリプトです。
スクリプト【支払】
function shiharai() {
var SS = SpreadsheetApp.getActiveSpreadsheet()
var entersheet = SS.getSheetByName("入力")
var actsheet = SS.getSheetByName("計上")
var month = actsheet.getRange("B3").getValue()
Logger.log(month)
for(i=1;i<=10;i++){
var column = entersheet.getRange(3,6-1+i,1,1)
var target = column.getValue()
//Logger.log(target)
if(target==month){
Logger.log(i)
var value1 = entersheet.getRange(3+1,6-1+i,1,1).getValue()
var value2 = entersheet.getRange(3+2,6-1+i,1,1).getValue()
var value3 = entersheet.getRange(3+3,6-1+i,1,1).getValue()
var value4 = entersheet.getRange(3+4,6-1+i,1,1).getValue()
var value5 = entersheet.getRange(3+5,6-1+i,1,1).getValue()
var value6 = entersheet.getRange(3+6,6-1+i,1,1).getValue()
var value7 = entersheet.getRange(3+7,6-1+i,1,1).getValue()
var value8 = entersheet.getRange(3+8,6-1+i,1,1).getValue()
}
}
actsheet.getRange(5+1,3,1,1).setValue(value1)
actsheet.getRange(5+2,3,1,1).setValue(value2)
actsheet.getRange(5+3,3,1,1).setValue(value3)
actsheet.getRange(5+4,3,1,1).setValue(value4)
actsheet.getRange(5+5,3,1,1).setValue(value5)
actsheet.getRange(5+6,3,1,1).setValue(value6)
actsheet.getRange(5+7,3,1,1).setValue(value7)
actsheet.getRange(5+8,3,1,1).setValue(value8)
}
説明
「計上」で指定した月の支払いを「入力」からとってきて張り付けています。
基本的な構成は「getValue」→「setValue」なのですが、
「指定した月」に対応する「入力」の列をとってくるところがポイントです。
var month = actsheet.getRange("B3").getValue() Logger.log(month)
⇒ 月の指定です。
for(i=1;i<=10;i++){
var column = entersheet.getRange(3,6-1+i,1,1)
var target = column.getValue()
⇒ 全部の月を参照し、target と moth が一致したときは、値をとるというスクリプトです。
//Logger.log(target)
if(target==month){
Logger.log(i)
var value1 = entersheet.getRange(3+1,6-1+i,1,1).getValue()
var value2 = entersheet.getRange(3+2,6-1+i,1,1).getValue()
var value3 = entersheet.getRange(3+3,6-1+i,1,1).getValue()
var value4 = entersheet.getRange(3+4,6-1+i,1,1).getValue()
var value5 = entersheet.getRange(3+5,6-1+i,1,1).getValue()
var value6 = entersheet.getRange(3+6,6-1+i,1,1).getValue()
var value7 = entersheet.getRange(3+7,6-1+i,1,1).getValue()
var value8 = entersheet.getRange(3+8,6-1+i,1,1).getValue()
}
必要な構文
今回は、「繰り返し」と「条件一致」で作成しました。LOOKUP関数と考え方は近いかもしれないですね。
費用計上
5月の費用を集めようとすると、「4月払いの列」「6月払いの列」からデータを持ってこなくてはいけません。
スクリプト【費用計上】
function hiyou() {
var SS = SpreadsheetApp.getActiveSpreadsheet()
var entersheet = SS.getSheetByName("入力")
var actsheet = SS.getSheetByName("計上")
var month = actsheet.getRange("B16").getValue()
Logger.log(month)
for(i=1;i<=10;i++){
var column = entersheet.getRange(3,6-1+i,1,1)
var target = column.getValue()
//Logger.log(target)
if(target==month){
Logger.log(i)
var value1 = entersheet.getRange(3+1,6-1+i-1,1,1).getValue()
var value2 = entersheet.getRange(3+2,6-1+i+0,1,1).getValue()
var value3 = entersheet.getRange(3+3,6-1+i+1,1,1).getValue()
var value4 = entersheet.getRange(3+4,6-1+i-1,1,1).getValue()
var value5 = entersheet.getRange(3+5,6-1+i+1,1,1).getValue()
var value6 = entersheet.getRange(3+6,6-1+i-1,1,1).getValue()
var value7 = entersheet.getRange(3+7,6-1+i+1,1,1).getValue()
var value8 = entersheet.getRange(3+8,6-1+i-1,1,1).getValue()
}
}
actsheet.getRange(18+1,3,1,1).setValue(value1)
actsheet.getRange(18+2,3,1,1).setValue(value2)
actsheet.getRange(18+3,3,1,1).setValue(value3)
actsheet.getRange(18+4,3,1,1).setValue(value4)
actsheet.getRange(18+5,3,1,1).setValue(value5)
actsheet.getRange(18+6,3,1,1).setValue(value6)
actsheet.getRange(18+7,3,1,1).setValue(value7)
actsheet.getRange(18+8,3,1,1).setValue(value8)
}ほとんど同じなのですが「value1」をくらべてみると
【支払】
var value1 = entersheet.getRange(3+1,6-1+i,1,1).getValue()
【費用計上】
var value1 = entersheet.getRange(3+1,6-1+i-1,1,1).getValue()
getRange内「6-1+i」と「6-1+i【-1】」というふうに、「-1」がポイントです。value2 は「+0」、value3 は「+1」
「前払い」なら「-1」、「当月」なら「+0」、「翌月払い」はら「+1」それぞれ設定することで、ほしいセルの値を取り出すことができます。
このスクリプトが実用的か？
といわれると、そうでもないですよね。
このサイトの基本設定が「初心者にやさしい」「二次配列をつかわない」という縛りで作成したので汎用性が広くありません。
取引先が増えたり、項目が増えたりするとちまちまスクリプトの修正が必要です。仕上がりデータも有料Saasのように、会計ソフト形式にCSVでダウンロードされたりしたら最高ですよね。
二次配列を学ぶともっと汎用性高く仕上げられますので、二次配列に踏み出してみるきっかけになれば幸いです。
どんな記事ならサポートしてみようと思えるか、ご要望ありましたら教えてくださいね。