【GAS活用システム⑤】現金出納帳を作成する

Hoshi-nok（ほしのっく）

2024年4月19日 23:58

Google Apps Script (GAS)を、もっと身近に、日々の暮らしに。

皆さんは、サークルや同好会、PTA、地域のスポーツクラブ、町内会などで、会計担当になったことはありますか。人が集まって活動すれば、お金の流れがあり、そのお金の流れを管理するためにグループ内で会計係が必要になりますよね。

私も保育園の父母会で会計係をしたことがあります。前任者から引き継いだ手順は、まさかの、帳簿の科目毎のページに、明細を一行ずつ手で記入し、領収書をのりで貼りつける、昔ながらの完全手作業でした。。

あまりの効率の悪さに耐えられず、当時はエクセルで現金出納帳を作成しましたが、今だったら、間違えなく、Googleフォームとスプレッドシートの組合せで作成します。

ということで、GAS活用システムの第５弾は、会計係のための「現金出納帳」です。私がGoogleフォームとスプレッドシートの組合せをおすすめする理由については、具体的な利点とともに後ほど説明しますね。

サークルや同好会、PTAや父母会、地域のスポーツクラブ、町内会などなどの会計係で、手書きやエクセルで管理しているけど、もっといい方法はないかな、と探している方には必見の内容です。

Googleアカウントさえあれば、無料で簡単に作成できますよ。

フォームを作成

まず、現金出納帳の明細を入力するためのフォームを作成してみましょう。

今回は、手動でフォームを作成していきます。フォームに登録するのは以下の項目てす。

科目
摘要
収入金額
支出金額
領収書日付
取引先・店舗
領収書・レシートの画像

なお、入力日については、スプレッドシートにリンクすると自動設定される、A列のタイムスタンプを利用するため、入力項目としては設定しません。

フォームの基本設定

Google ドライブへアクセスし、Googleフォームを保存するフォルダに移動してから、左上の「新規」をクリックします。

Googleフォーム > 空白のフォームを選択します。

無題のフォームが作成されました。

まずタイトルを変更しましょう。

「2024年度現金出納帳」など、お好きな名前に変更します。もちろん、団体名を入れても。あわせて、左上のフォームの名前も変更しておきます。

一つ目の質問として「無題の質問」が「ラジオボタン」の回答形式で自動生成されています。

これを「科目」の「プルダウン」に変更します。

プルダウンの選択肢として、収入と支出の科目（分類）を設定します。科目は各団体で決められたものがあると思いますので、前年度のものをコピーしてください。

ちなみに、プルダウンの選択肢ですが、複数の選択肢をテキストなどからコピーして、フォームでペーストすることで一括登録できますよ。

この「科目」だけは「必須」にしておきます。

続けて、「質問を追加」をクリックして以下の項目を追加していきます。

摘要（回答形式は「記述式」）
収入金額（回答形式は「記述式」。その他のオプション＞回答の検証＞「数値」の「整数」を指定する）
支出金額（回答形式は「記述式」。その他のオプション＞回答の検証＞「数値」の「整数」を指定する）
領収書日付（回答形式は「日付」。その他のオプション＞年を含める、のチェックを外す）
取引先・店舗（回答形式は「記述式」）
領収書・レシートの画像（回答形式は「ファイルのアップロード」）

すべてではありませんが、一部の画面ショットを載せておきます。

「領収書日付」の「日付」形式では、「年を含める」がデフォルトになっていますが、チェックを外すと、月と日だけ入力する形式になります。

そして、最後の「領収書・レシートの画像」ですが、回答形式として「ファイルのアップロード」を選択すると

以下のようなメッセージが表示されますので、確認して「次へ進む」を選択します。

その後、以下のような詳細設定が表示されます。必要に応じて変更します。

以上で質問の設定が完了しました。あとはお好みですが、一番上の「テーマのカスタマイズ」を選択して

ヘッダーの画像や背景の色などを変更することができます。

スプレッドシートにリンク

続けて、作成したフォームを新規スプレッドシートにリンクしていきます。「回答」のリンクをクリックします。

「スプレッドシートにリンク」をクリックします。

新しいスプレッドシートを作成を選択して、作成をクリックします。

フォームに紐づいた新規スプレッドシートが作成されました。シート名は「フォームの回答 1」になっています。

スプレッドシートでの編集

シート名「フォームの回答 1」をクリックして「現金出納帳」に変更します。

続けて、見やすくするため、交互の背景色を定義していきます。5〜8行程度の範囲を選択し、メニューの表示形式＞交互の背景色で好みの色を選択します。

続けて、E列の支出金額とF列の領収書日付の間に１列追加します。

新しいF列の見出しに「差引残高」と入力します。このF列「差引残高」は、フォームに紐づいていない列ということになります。

データの入力と数式と書式の設定

データの手入力とフォームからの送信

それでは、はじめに前年度繰越金の金額をスプレッドシートに直接入力していきましょう。タイムスタンプに日付、科目に「前年度繰越金」、収入金額と差引残高に同じ金額を入力します。

続けて、作成したフォームからテストデータを入力してみます。フォームの編集画面からプレビューをクリックします。

収入の科目を選択して、収入金額を入力したら、「送信」ボタンをクリックします。

スプレッドシートを確認してみると、、、先にスプレッドシートに直接手入力していた「前年度繰越金」のデータより前にフォームから送信されたデータが挿入されてしまいました。

詳しくはよくわかりませんが、どうやらフォームから送信されたデータを書き込む際に、あらかじめ手入力されたデータが認識されておらず、ないものとして、2行目に書き込まれてしまっているようです。

残念ながら、この事象の原因や回避方法は不明です。が、対処方法として、この時点でデータの「並べ替え」を行うことで、この次にフォームから送信されたデータは最終行に書き込まれるようになります。

やってみましょう。メニューから、データ＞シートを並べ替え＞列Aを基準に昇順でシートを並べ替えを選択します。

データの並べ替えが完了しました。

これで次はフォームから送信したデータが4行目に挿入されると思います。あとで確認してみましょう。

数式と書式の設定

さて、3行目のF列の差引残高にはデータが設定されていません。このF列はさきほど手動で挿入した列なので、フォームからデータを送信しても、当然、データは設定されません。

F3のセルに数式（計算式）を設定してみましょう。設定したい計算式は、

前の行の差引残高（F2のセルの値）＋収入金額（D3のセルの値）ー支出金額（E3のセルの値）

となるので、以下の数式をF3のセルに設定します。
=F2+D3-E3

続けて、金額（D列の収入金額からF列の差引残高まで）の範囲に、通貨の書式設定をします。範囲指定をしてから、メニューから、表示形式＞数字＞通貨（端数切り捨て）を選択します。

次に、タイムスタンプ列を選択して、

メニューの表示形式＞数字＞ 2008/09/26(金) と指定します。

領収書日付にも同じように日付書式を指定しておきます。

領収書の画像つきで入力する

続けて今度はスマホでフォームにアクセスし、支出の金額を領収書の画像付きで送信してみましょう。

フォームのリンクを取得

スマホからデータを入力するためのフォームのリンクを取得します。フォームの編集画面の送信ボタンをクリックします。

フォームを送信画面で、送信方法で真ん中のリンクアイコンをクリックします。URLを短縮したい場合は、チェックボックスをクリックして表示されるリンクをコピーします。

スマホからこのリンクでフォームにアクセスします。

支出を領収書の画像つきで入力すると

フォームで支出の科目を選んで、支出金額を入力します。

さらに、領収書日付、取引先・店舗を入力し、「ファイルの追加」をクリックします。

ここで領収書またはレシートの写真を撮るか、あらかじめ撮っておいたものを選択します。

画像を指定できたら、送信します。

スプレッドシートで確認してみましょう。
まず、データが最終行に書き込まれました。

ですが、タイムスタンプや金額の書式は設定されていません。差引残高にも値は入っていません。

フォームから送信された行は、毎回、新規行として挿入されるため、書式や数式はGoogle Apps Script（GAS）で送信された都度、設定をする必要があります。これはこの後、設定していきます。

GASを設定する前に、領収書・レシートの画像を確認してみましょう。URLが設定されています。

URLにカーソルをおくと、、、なんということでしょう！？勝手にプレビューされました。

Googleフォームから明細入力をする利点

私が会計担当をしていた時に正直、面倒だなと思ったのが、エクセルにデータを入力するのと、領収書・レシートの管理でした。

なんとなく、パソコン立ち上げてエクセルファイルを開いて、、が面倒で、入力をためがちでした。ですが、この形式なら、スマホでGoogleフォームにアクセスして、一件ずつ登録するだけです。気持ちの面でハードルがぐっと下がるように思います。

そして、もうひとつの領収書・レシートの管理ですが、こんな風に領収書・レシートの画像が明細に添付できるなんて素晴らしくないですか！？

一点注意としては、Googleアカウントでログインしないとファイルのアップロードはできない、という制約があります。ですが、会計は通常、限られた人数が担当するので、レシートや領収書をアップロードする人は全員、Googleアカウントでログイン必須で！ということにしても問題はないと思われます。

スマホであれば領収書やレシートの写真を撮って、そのままアップロードできるので、「ファイルのアップロード」は現金出納帳にはまさにうってつけの回答形式といえると思います。

ちなみに、アップロードしたファイルはフォームと同じフォルダ内の、「フォーム名＋(File responses)」のフォルダに格納されます。

明細と画像が紐づけられているだけでなく、領収書・レシートの画像がこのフォルダに勝手にまとめられる、というのも良い所だと思います。

（オプション）GASの登録とトリガーの設定

ここまでは、GASを使わずに設定だけでできました。書式や数式は後でまとめて設定するので、正直、これで十分、と思われれば、GASの設定は不要です。

ですが、今回、GASとして登録するのは、フォームからデータが送信されたら書式や数式の設定をする、その部分だけです。スクリプトはかなり短めで、よろしければぜひ登録してみてください。

GASの登録

それでは、GASの登録方法からみていきましょう。スプレッドシートのメニューバーから、拡張機能 > Apps Script を選択します。

無題のプロジェクトが生成され、Apps Scriptのスクリプトエディタが表示されました。ファイル：コード.gsに、「function myFunction」が表示されています。

「function myFunction ( ) { }」のところに、上書きする形で下記のコードをコピぺします。function submitForm、つまり、submitForm関数を定義しています。

function submitForm(e) {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //一番左のシートを取得
  const row = e.range.getRow();   //シートに書き込まれたデータの行番号

  //書き込まれた行のA列（タイムスタンプ）とG列（領収書日付）に日付書式を設定
  sh.getRange(row,1).setNumberFormat('yyyy"/"mm"/"dd"("ddd")"');
  sh.getRange(row,7).setNumberFormat('yyyy"/"mm"/"dd"("ddd")"');

  //書き込まれた行のD列（収入金額）からF列（差引残高）までに通貨の書式を設定
  sh.getRange(row,4,1,3).setNumberFormat('[$¥-411]#,##0');

  //書き込まれた行のF列（差引残高）に数式を設定
  sh.getRange(row,6).setFormulaR1C1("R[-1]C+RC[-2]-RC[-1]");   
}