見出し画像

【GAS活用システム⑥】現金と預金口座を一括管理する

Hoshi-nok（ほし のっく）

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

前回の【GAS活用システム⑤】では、サークルや同好会、PTAや父母会、地域のスポーツクラブ、町内会などなどの会計係が使用する「現金出納帳」を作成する方法をご紹介しました。

Googleフォームの「ファイルのアップロード」機能を使って、領収書・レシートの画像を明細に添付できる、便利な機能つきの「現金出納帳」でしたが、今回はこれにさらに追加していきたいと思います。

何を追加するかというと、「預金口座残高」です。その団体がある程度の金額を保持している場合は、専用の預金口座をつくって管理していることが多いのではないでしょうか。

本来、預金口座は「預金出納帳」なるもので管理するようですが、今回は、「現金出納帳」に「口座残高」の列を追加して、手元の現金残高と口座残高を一つのシートで管理してみましょう。

あわせて、少し応用的なGoogleフォームの作成方法をご紹介していきたいと思います。

ということで、GAS活用システムの第６弾は、第５弾で紹介した「現金出納帳」のバージョンアップ版を扱っていきます。


まずはテンプレートのコピーから

今回は、Googleスプレッドシートのテンプレートを用意しました。Googleアカウントでログイン後、下記のリンクをクリックして、テンプレートを表示します。

メニューバーが表示されない場合は、ツールバーの一番右の v をクリックすれば、メニューが表示されます。

左上メニューより「ファイル」＞「コピーを作成」を選択します（Googleアカウントでログインしていないと選べません）

ファイル名を変えて、自分のドライブ上にコピーします。フォルダをマイドライブ以外に変更することも可能です。

コピーが終了したら、改めてシートを確認しましょう。2つシートがあり、１つ目は少しばかりカラフルな「現金出納帳」シートです。

もう１つは「収支集計」シートです。このシートには科目の一覧とまだ空欄の合計金額のセルがあります。この時点で、さっそく収入の科目と支出の科目をご自分の団体の決められた科目名に変更しておきましょう。科目の追加・削除をする場合は、収入または支出の区分の中で、行の挿入・削除して対応してください。

”回答に応じて質問を表示する”フォームの作成

今回作成する応用的なフォームですが、作成手順の前に、先にそのフォームのイメージをご紹介します。

作成するフォームのイメージ

Googleフォームは、「回答に応じてセクションに移動」するように設定することで、選択肢に応じて条件分岐をすることができます。

今回の口座残高つきの現金出納帳では、初めに選択した入力区分の値に応じて、下記のように質問（入力項目）を変えていきたいと思います。

  • 収入を入力する時は、科目（収入の科目のみをリスト）と収入金額を表示する

  • 支出を入力する時は、科目（支出の科目のみをリスト）と支出金額、領収書を添付するための質問を表示する

  • 口座の入出金を入力する時は、口座入出金金額を表示する

こうすることで入力しやすくなり、また、入力間違いを防ぎこともできます。

Googleフォームってこんなことできるんだー、と初めて知った時は感心しました。後でちょっと困った事態に陥りましたが、、、その事態の詳細と解決策はこの記事の後半で説明していきたいと思います。

まずは、条件分岐するフォームを作成していきましょう。作成方法も上記のイメージさえしっかり掴めていれば、難しくありませんよ。

フォームの作成手順

それでは、実際に条件分岐するフォームを作成する手順をみていきましょう。

Googleドライブでフォームを作成するフォルダに移動したら、左上の「＋ 新規」をクリックします。

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

次に、フォームのタイトル、フォームのファイル名を変更します。

「無題の質問」を「入力区分」に変更します。回答形式は「ラジオボタン」のままで、選択肢として、「収入の入力」「支出の入力」「口座の入出金の入力」の３つの選択肢を入力します。入力区分は必須にします。

続けて、セクションを追加していきます。

追加された２個目のセクションの名前として、「収入の情報入力」と入力したら、このセクションに質問を追加します。

質問名を「科目」、回答形式を「プルダウン」に変更します。

プルダウンの選択肢として、収入の科目を設定するのですが、ここでさきほどコピーしたスプレッドシータを表示し、収入の科目の一覧（フォームから入力しない「前年度繰越金」を除く）をコピーして

フォームの選択肢で貼り付けすると

選択肢が一気に設定できます。科目は必須にします。

続けて、このセクションに残り二つの質問を追加します。

  • 摘要（回答形式は「記述式」）

  • 収入金額（回答形式は「記述式」。その他のオプション（質問の右下《︙》） ＞ 回答の検証 ＞ 「数値」の「整数」 を指定する。必須にする。）

次に、３個目のセクションを追加し、「支出の情報入力」と名前を入力します。そのセクションに以下の項目を追加します。

  • 科目（回答形式は「プルダウン」。収入の科目同様、スプレッドシートから支出の科目をコピペする。必須にする）

  • 摘要（回答形式は「記述式」）

  • 支出金額（回答形式は「記述式」。その他のオプション （質問の右下《︙》）＞ 回答の検証 ＞ 「数値」の「整数」 を指定する。必須にする。）

  • 領収書日付（回答形式は「日付」。その他のオプション ＞ 年を含める、のチェックを外す）

  • 取引先・店舗（回答形式は「記述式」）

  • 領収書・レシートの画像（回答形式は「ファイルのアップロード」）

続けて、最後の４個目のセクションを追加後、「口座の入出金の情報入力」と名前を入力したら、以下の項目を追加します。

  • 口座入出金金額（回答形式は「記述式」。その他のオプション （質問の右下《︙》）＞ 回答の検証 ＞ 「数値」の「整数」 を指定する。その他のオプション ＞ 説明 を選択して、説明として、"現金を口座へ入金した場合はプラスの金額を、口座から引き出した場合はマイナスの金額を入力してください"と入力する。必須にする。）

  • 摘要（回答形式は「記述式」）

これで４個のセクションが定義できました。

続けて、条件分岐の設定をしていきましょう。一番初めのセクションの「入力区分」の質問を表示し、その他のオプション（右下の《︙》）から「回答に応じてセクションに移動」を選択します。

これで選択肢の右に、どのセクションに進むかを指定するプルダウンが表示されるようになるので

上から順番に、セクション２に移動、セクション３に移動、セクション４に移動を選択します。

最後に、各セクションの終わりの動作について設定していきます。「次のセクションに進む」がデフォルトになっています。セクション１は入力区分の選択肢によって条件分岐の設定したので、ここは変更なしで大丈夫です。

セクション２では、入力が終わったら、それぞれフォームを送信したいので、「次のセクションに進む」を「フォームを送信」に変更します。

セクション３も同様に「フォームを送信」に変更します。

これで設定が終わりました。右上のプレビューをクリックして、フォームが正しく遷移するか確認しましょう。

初めのページ（セクション１）は入力区分のみが表示されます。ここで一つずつ選択肢を選んで「次へ」をクリックして、それぞれ設定したセクションが表示されるか確認しましょう。

それぞれのセクションで設定された項目が表示されるか、科目のリストではそれぞれ収入の科目・支出の科目が表示されるか、セクションの最後に「送信」ボタンがあるかを確認します。

これで条件分岐のフォームが完成しました。

便利な条件分岐のフォーム、しかし、、、

ここまでだと、条件分岐のフォームはとても便利そうですが、、、実は一つ大きな問題があります。

通常であれば、ここでスプレッドシートをリンクするところですが、

試しにリンクにより新規作成されたスプレッドシートを見てみると、「科目」の列が２つ、「摘要」の列が３つ、できてしまっていることがわかります。

あ、そういうこと？とピンときた方もいらっしゃるかもしれませんが、そうなのです、セクション毎の項目がそのまま横並びで表示されているんですね。色分けしてみると、わかりやすいでしょうか。

フォームのセクションを分けた場合、各セクションに設定した質問は、たとえそれが同じ名前であっても、別の質問として認識されてしまうのです。

今回の場合は、２つある「科目」は1つの列に、３つある「摘要」は１つの列にまとめたいところです。

ですが、残念ながら、名前が同じであれば自動的に１つの列に集約するとか、設定により複数の質問を１つの列に集約するといったことはできないようです。

では、条件分岐のフォームはあきらめるか、このまま使用するしかないのでしょうか。

いえいえ、自動的にデータの挿入をする「スプレッドシートにリンク」は使用せずに、フォームから送信されたデータをスプレッドシートに挿入するスクリプト、Google Apps Script (GAS)を登録すれば、異なる質問の回答を一つの列に集約することが可能です。

今回は、先ほどテンプレートをコピーしたスプレッドシートの、少しばかりカラフルな「現金出納帳」シートに、GASでフォームから送信されたデータを挿入していきたいと思います。

フォームから送信されたデータをスプレッドシートに挿入するGAS

それでは、GASを登録していきます。今回は、フォーム側のGASを登録してみます。

フォーム側にGAS登録

フォームの管理画面の「送信」ボタンの右隣のその他《︙》をクリックし

スクリプトエディタを選択します。

Apps Scriptのエディタが表示されました。

function myFunction() {} に上書きする形で、下記のスクリプトをコピペします。

function submitForm(e) {
  const ss = SpreadsheetApp.openById("★★シートのID★★");
  const sh = ss.getSheets()[0];           //一番左のシート

  // フォームの送信内容を取得
  const items = e.response.getItemResponses();
  let responses = items.map(item => item.getResponse());
  const today = Utilities.formatDate(new Date(), 'JST', 'yyyy/MM/dd');

  //switch文による複数の条件分岐
  switch (responses[0]) {
    case '収入の入力':
      var [entryClass, category, comment, incomeAmount] = responses;    
      break;
    case '支出の入力':
      if ( responses[6] != undefined) {      //ファイルがアップロードされているなら
        var [entryClass, category, comment, spendAmount, receiptDate, shop, fileId] = responses;
        var receiptImage = DriveApp.getFileById(fileId).getUrl();
      }else{
        var [entryClass, category, comment, spendAmount, receiptDate, shop] = responses;
      }        
      break;
    case '口座の入出金の入力':
      var [entryClass, bankAmount, comment] = responses;   
      break;
    default:
  }

  //「現金出納帳」シートの最終行に行を追加
  sh.appendRow([today, entryClass, category, comment, incomeAmount, spendAmount, bankAmount, , , receiptDate, shop, receiptImage]);

  //書式と計算式を設定
  const lastRow = sh.getLastRow();    //最終行の行番号を取得
  sh.getRange(lastRow,10).setNumberFormat('yyyy"/"mm"/"dd');     //領収書日付の書式
  sh.getRange(lastRow,5,1,5).setNumberFormat('[$¥-411]#,##0');   //通貨の書式
  sh.getRange(lastRow,8).setFormulaR1C1("R[-1]C+RC[-1]");        //口座残高の計算式
  sh.getRange(lastRow,9).setFormulaR1C1("R[-1]C+RC[-4]-RC[-3]-RC[-2]");  //手許現金残高の計算式
  sh.insertRowAfter(lastRow);         //最後に交互の背景色を拡張させるために一行挿入
}

変更する箇所は一ヶ所だけ、2行目の、★★シートのID★★ の部分だけです。ここにはスプレッドシードのIDを貼りつけます。スプレッドシートのIDはURLの下記の部分になります。この部分をコピーします。

エディタに戻って、2行目の、★★シートのID★★ の部分に上書きする形でそのIDを貼りつけます。両端のダブルコーテーション（”）は上書きせず、残すように気をつけましょう。

このIDの指定によりどのスプレッドシートに書き込むかを指定しているわけです。

必要に応じてプロジェクトの名前を変更し、保存ボタンでプロジェクトを保存します。

これでsubmitForm関数の登録が完了しました。

トリガーの設定

続けて、登録したsubmitForm関数を、フォーム送信のタイミングで実行する、トリガーを設定します。

右下の「トリガーを追加」ボタンをクリックします。

実行する関数として「submitForm」が表示されていることを確認し、イベントの種類を選択で「フォーム送信時」を選択したら、「保存」ボタンをクリックします。

ここで、いきなりアカウントの選択が求められます。未承認のプロジェクトの関数をトリガーで指定したためです。さくっと、プロジェクトの承認をしていきましょう。以下のように英語で表示される場合は、左下のリストから日本語を選択することができます。

自分のアカウントを選択します。

詳細のリンクをクリックします。

脅しのような表現に臆せずに、「[プロジェクト名]（安全ではないページ）に移動」を選択します。

「許可」ボタンをクリックします。

プロジェクトが承認され、無事、トリガーが登録されました。

これでフォームからデータが送信されたら、トリガーによりsubmitForm関数が実行され、submitForm関数が現金出納帳シートにデータを書き込んでくれる手筈が整いました。

フォームからデータの送信

それでは、実際にフォームからデータを入力・送信してみましょう。

その前に、あらかじめ、スプレッドシートの現金出納帳シートを表示して、2行目の前年度繰越金に対し、「収入金額」、「口座残高」、「手許現金残高」の金額を入力しておきます。収入金額 ＝ 口座残高 ＋ 手許現金残高 になるように入力します。

フォームの管理画面の右上のプレビューをクリックして、フォームを表示して

収入を入力して、送信ボタンをクリックしてみましょう。

スプレッドシートを確認すると、、、収入のデータが記録されました！収入金額は手許現金残高に加算されます。

同じ手順で、支出のデータをフォームから入力・送信します。スプレッドシートで、科目や摘要が収入と同じ列に書き込まれること、また支出金額が手許現金残高から減算されることを確認しましょう。

支出の場合は、領収書のデータも正しく登録されるか確認しておきます。

最後の、口座の入出金の入力ですが、説明に書いたように、口座に入金するならプラスの金額、口座から出金するならマイナスの金額を入力します。つまり、口座からみたプラスマイナスの金額を入力していきます。

口座の入出金の入力の場合、口座出金金額が記録されます。入金（プラスの金額）ならば口座残高が加算され、手許現金残高が減算されます。出金（マイナスの金額）であればその逆となります。もちろん、手許現金残高から口座へ入金、口座から出金して手許残高へ、というお金の流れを意識したものです。

 ここで、改めて、「口座残高」と「手許現金残高」に設定されている数式についてみていきましょう。まず「口座残高」のセルに設定されている数式は、（1つ上のセル ＋ 左隣のセル(口座入出金金額)）です。

「手許現金残高」のセルに設定されている数式は、（1つ上のセル ＋ 4つ左のセル(収入金額) ー 3つ左のセル(支出金額) ー 2つ左のセル(口座入出金金額)）です。

「手許現金残高」の列の位置については、正直悩みました。はじめは「支出金額」と「口座入出金金額」の間かな？と思ったのですが、口座入出金額が入力されると現金残高も増減するので、理屈的にはこの位置だろうと、最終的に口座残高の右隣（I列）にしています。

収支集計シートで科目ごとの合計値を集計するには

【GAS活用システム⑤】現金出納帳を作成するで紹介したように、収支集計シートにはSUMIFS関数を設定すれば、科目ごとの合計値を集計できます。

今回の現金出納帳シートでは、科目がC列、収入金額がE列、支出金額がF列になるため、前回と数式が少しだけ異なります。

例えば、収入科目の合計金額のC2セルに設定する数式は
=SUMIFS('現金出納帳'!$E$2:$E,'現金出納帳'!$C$2:$C, B2)

支出科目の合計金額のC9セルに設定する数式は
=SUMIFS('現金出納帳'!$F$2:$F,'現金出納帳'!$C$2:$C, B9)

となります。収入合計、支出合計、収支差額にはあらかじめ数式が設定してあります。

日付を入力可能にするなら

上記の例では、スプレッドシートのA列の「日付」には、フォームからデータを入力した当日の日付を設定していますが、フォームから日付を入力したい場合は以下のように設定してみてください。

  • フォームの2個目のセクション（収入の情報入力）、3個目のセクション（支出の情報入力）、4個目のセクション（口座の入出金の情報入力）のそれぞれ１つ目に、質問名「日付」、回答形式「日付」の項目を追加する。

  • GASの「//switch文による複数の条件分岐」以降を下記のコードで書き換えて保存する。

  //switch文による複数の条件分岐
  switch (responses[0]) {
    case '収入の入力':
      var [entryClass, date, category, comment, incomeAmount] = responses;    
      break;
    case '支出の入力':
      if ( responses[7] != undefined) {      //ファイルがアップロードされているなら
        var [entryClass, date, category, comment, spendAmount, receiptDate, shop, fileId] = responses;
        var receiptImage = DriveApp.getFileById(fileId).getUrl();
      }else{
        var [entryClass, date, category, comment, spendAmount, receiptDate, shop] = responses;
      }        
      break;
    case '口座の入出金の入力':
      var [entryClass, date, bankAmount, comment] = responses;   
      break;
    default:
  }

  //「現金出納帳」シートの最終行に行を追加
  sh.appendRow([(date || today), entryClass, category, comment, incomeAmount, spendAmount, bankAmount, , , receiptDate, shop, receiptImage]);

  //書式と計算式を設定
  const lastRow = sh.getLastRow();    //最終行の行番号を取得
  sh.getRange(lastRow,1).setNumberFormat('yyyy"/"mm"/"dd');      //入力日の書式
  sh.getRange(lastRow,10).setNumberFormat('yyyy"/"mm"/"dd');     //領収書日付の書式
  sh.getRange(lastRow,5,1,5).setNumberFormat('[$¥-411]#,##0');   //通貨の書式
  sh.getRange(lastRow,8).setFormulaR1C1("R[-1]C+RC[-1]");        //口座残高の計算式
  sh.getRange(lastRow,9).setFormulaR1C1("R[-1]C+RC[-4]-RC[-3]-RC[-2]");  //手許現金残高の計算式
  sh.insertRowAfter(lastRow);         //最後に交互の背景色を拡張させるために一行挿入
}

上記手順により、フォームから日付が入力されればその日付が入り、入力されなければ入力日が自動的に設定されるようになります。

終わりに

以上が、「口座残高と手許現金残高が一つのシートで管理できる」現金出納帳のバージョンアップ版のご紹介でした。

サークルや同好会、PTAや父母会、地域のスポーツクラブ、町内会などの会計係・会計担当の方に使っていただけたら嬉しいです。

また、技術的には、条件分岐のフォームの作成方法、GASで異なる質問の回答をスプレッドシートの1つの列に書き込む方法をご紹介しました。何かの参考にしてもらえたら、それもまた嬉しいです。

領収書・レシートの画像のアップロードの詳細などについては、前回の記事）で細かく説明していますので、今回は省きました。以下の記事（↓）です。

また、うっかり長文になってしまいました。最後まで読んでくださって、ありがとうございます。

それでは、また。

いいなと思ったら応援しよう！