【GAS活用システム①-2】立替と会費の精算を自動化する・設定編

Hoshi-nok（ほしのっく）

2023年12月10日 18:56

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

前回より、GAS活用システム第一弾として「立替と会費の精算システム」を紹介しています。前回の【GAS活用システム①-1】では使用方法編を先にご紹介しました。

今回は、GASをフル活用して、「立替と会費の精算」を行うための設定手順をみていきましょう。

【集計表】シートのテンプレート作業

テンプレートをコピーして保存

いちばん初めの手順は、【集計表】シートのテンプレートをコピーして保存することです。

Googleアカウントでログイン後、以下をクリックしてください。

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

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

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

【集計表】シートの編集

テンプレートをコピーして保存したら、次は【集計表】シートを編集します。

はじめに、セルのA2のタイトル「202X年X月〇〇パーティ・立替と会費の精算」を変更しましょう。年や月は不要でしたら削除しても。パーティでも旅行でも、とにかく、何の精算かわかるようにタイトルをつけましょう。

ここで入力するタイトルは、中黒（・）より前の文言を、後で自動生成する、フォームのタイトルやファイル名として使用します。

次に、参加者（セルのA5～A20）と分類（セルのB4～B7）の値を変更してください。

今回のテンプレートには、あらかじめ、サンプルデータとして、参加者の名前（日本で多い名字の上位16位まで）と保育園の謝恩会あたりをイメージした分類を設定してあります。

この時、参加者を追加するなら行の追加、分類を追加するなら列の追加をすることになりますが、以下の名前付き範囲が既にテンプレートに定義済みになっている点に注意してください。

集計表シートのA5:A20が「参加者リスト」
集計表シートのB4:F4が「分類リスト」

これらの名前付き範囲は、GASで使用します。行や列の追加をした場合、参加者の範囲に「参加者リスト」、分類の範囲に「分類リスト」の名前付き範囲が正しく設定されているか、確認しておいてください。

名前付き範囲は、メニューからデータ＞名前付き範囲を選択することで、確認・修正ができます。

参加者や分類は後でも変更可能なので、この時点で確定しないでも大丈夫です。ある程度修正したら、先に進みましょう。

GAS(スクリプト)のコピペと実行

セキュリティ上の問題で、テンプレートのスプレッドシートにはスクリプトを含んでいません。以下の手順でプロジェクトを作成し、スクリプトを設定します。

今回はスクリプトを２つのファイルに分けて登録していきます。１つ目のファイル、コード.gsには、独自メニューを追加するスクリプトを、２つ目はフォーム作成.gsという名前にし、フォーム作成時に一度だけ実行するスクリプトを登録します。

スクリプトをコピペ

スプレッドシートのメニューバーから、拡張機能 > Apps Script を選択します。

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

「function myFunction ( ) { }」のところに、上書きする形で下記のコードをコピぺします。

function onOpen() {
  let ui = SpreadsheetApp.getUi(); 
  ui.createMenu('GAS活用システム') 
    .addItem('タイムスタンプで並べ替え', 'sortByTimestamp') 
    .addItem('名前・分類で並べ替え', 'sortByPeople')
    .addSeparator()
    .addItem('フォーム上の名前と分類の選択肢を更新', 'updateChoiceValues') 
    .addToUi();
}

function updateChoiceValues(){
  //アクティブなスプレッドシートから名前付き範囲の値を一次元配列で取得
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let people = ss.getRangeByName("参加者リスト").getValues().flat();
  let categories = ss.getRangeByName("分類リスト").getValues().flat();
  
  //フォームの対象項目のプルダウンの選択肢を配列の値で更新
  let form = FormApp.openByUrl(ss.getFormUrl());  //連携しているフォームを取得 
  let items = form.getItems();                    //フォーム内の全アイテムを取得

  //フォームの0から数えて0番目のアイテムにプルダウンの選択肢を配列peopleの値で更新
  items[0].asListItem().setChoiceValues(people);

  //フォームの0から数えて1番目のアイテムのラジオボタンの選択肢を配列categoriesの値で更新  
  items[1].asMultipleChoiceItem().setChoiceValues(categories);  
}

function sortByPeople() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //一番左のシートを取得
  let range = sheet.getDataRange();
  let dRange = range.offset(1, 0, range.getNumRows() - 1); //見出し行を除く

  // sort({ソートをかける列の指定}, {true: 昇順, false: 降順})
  dRange.sort([{column: 2, ascending: true}, //第1キー：B列（名前）, 昇順
               {column: 3, ascending: true}  //第2キー：C列（分類）, 昇順
              ])
}

function sortByTimestamp() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //一番左のシートを取得
  let range = sheet.getDataRange();
  let dRange = range.offset(1, 0, range.getNumRows() - 1); //見出し行を除く

  // sort({ソートをかける列の指定}, {true: 昇順, false: 降順})
  dRange.sort([{column: 1, ascending: true} //第1キー：A列（タイムスタンプ）, 昇順
              ])
}

まぁまぁ長いですね。↓こんな感じにコード.gsにコピペしたら、さらに、＋をクリックして、スクリプトファイルを追加します。

名前に「フォーム作成」と入力して、下記のスクリプトをコピペします。

function createForm() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const title = ss.getSheetByName("集計表").getRange('A2').getValue();
  const formTitle = title.split("・")[0]+"・立替金の入力";   //タイトル
  const formDescription = "立替えたお金があれば入力してください"; //概要

  let form = FormApp.create(formTitle)
                    .setTitle(formTitle)
                    .setDescription(formDescription);

  //作成したフォームのファイルをマイドライブからスプレッドシートのあるフォルダへ移動する
  const ssId = ss.getId(); // スプレッドシートIDを取得
  const sheetFolder = DriveApp.getFileById(ssId).getParents().next(); // IDからスプレッドシートのフォルダを取得
  const formFile = DriveApp.getFileById(form.getId()); 
  formFile.moveTo(sheetFolder);

  //フォームの回答の保存先を集計表シートのスプレッドシートに設定
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ssId);

  //「名前」アイテム作成（プルダウンで）
  form.addListItem()
      .setTitle("名前")
      .setRequired(true);

  //「分類」アイテム作成（ラジオボタンで）
  form.addMultipleChoiceItem()
      .setTitle("分類")
      .setHelpText("不明な場合は、その他を選択してください")
      .setRequired(true);

  //「金額」アイテム作成（記述式で）
  form.addTextItem()
      .setTitle("金額")
      .setValidation(FormApp.createTextValidation().requireWholeNumber().build())
      .setRequired(true);

  //「備考」アイテム作成（記述式で）
  form.addParagraphTextItem()
      .setTitle("備考");

  //フォームと連携しているシート名を立替明細に変更
  SpreadsheetApp.flush();
  let sh = ss.getSheets()[0]  //一番左のシート名
  sh.setName("立替明細")   //シート名を変更
}

こんな感じになります。

「無題のプロジェクト」をクリックして、プロジェクト名を変更しておきます。

ツールバーの保存ボタンでプロジェクトを保存します。

フォーム作成のスクリプトを実行

保存後、ファイル作成.gsをクリックします。

ファイル作成.gsに一つだけ定義している関数CreateFormが、「デバッグ」と「実行ログ」の間に表示されるのを確認したら、「実行」ボタンをクリックします。

「承認が必要です」と言われます。

新規プロジェクトを実行する時に必要な「実行権限の承認」です。以下の手順で承認しましょう。
→ 「権限を確認」をクリック
→ Googleにログイン画面で、自分のアカウントを選択
→ 「詳細」、「[プロジェクト名]（安全ではないページ）に移動」を順番にクリック
→ 「許可」をクリックすることで、実行権限の承認完了

手順が不安な方は、【GAS活用①-3】のページで、画面ショットつきで説明しているので、そちらを参考にしてください。

承認の手順が終わると、CreateForm関数が実行され、実行ログに実行完了と表示されることを確認しましょう。

生成されたフォームの確認

続けて、スプレッドシートを保存しているフォルダを再表示すると、「202X年X月〇〇パーティ・立替金の入力」といった名前でフォームが作成されているはずなので、フォームを表示してみましょう。

スプレッドシートのメニューバーのツール＞フォームを管理＞フォームを編集、と、たどってもフォームが開きます。

以下のようなフォームが自動生成されていると思います。

フォームのファイル名は、集計シートのB2のタイトルの、中黒（・）より前の部分に、「・立替金の入力」をつけたものにしています。変更も可能です。

名前と分類の選択肢を自動生成するスクリプトを実行

フォームを生成した時点では、「名前」と「分類」には選択肢がありません。続けてスプレッドシートのメニューからこれらの選択肢を自動生成していきましょう。

Apps Scriptのスクリプトエディタに戻ります。

スプレッドシートに独自メニューを追加するため、コード.gsのonOpen関数を実行します。もしくは、いったんスプレッドシートを閉じて再度開いてもonOpen関数が実行されます。

onOpen関数の実行により、スプレッドシートのメニューに、「GAS活用システム」が追加されるので、「フォーム上の名前と分類の選択肢を更新」を選びます。

スクリプトの実行が終了したら、フォームを確認します。

フォームの名前の選択肢が、集計表シートの名前付き範囲の「参加者リスト」から、分類の選択肢が、名前付き範囲の「分類リスト」から生成されました。

フォームの編集

【GAS活用システム①-1】でも説明したように、今回、自動生成したフォームは、項目として定義しているのは「名前」、「分類」、「金額」と「備考」の４つだけとシンプルにしています。

GASでフォームを一気に作成自動生成しましたが、出来上がったフォームのタイトルや、各項目の名前や説明文、必須かなどの定義は変更しても問題ありません。

項目を追加する場合は、選択肢を生成するスクリプトを少しだけ修正する必要があるので、この手順は次回説明します。

あとは、フォームの「テーマをカスタマイズ」を選択して、お好みの色やテーマを選びましょう。

ヘッダーの「画像をアップロード」を選択して、「テーマ」の中から画像を選んだり、画像をアップロードすることができます。画像にあわせて、背景などの色も一緒に変更されますが、色の変更も可能です。

なお、フォームは自動保存なので、自分で保存する必要はありません。

【立替明細】シートに追加の設定

スプレッドシート側に戻り、フォーム作成と同時にGASによって追加された【立替明細】シートを確認します。

【立替明細】シートはフォームと連携しています。見た目がさみしいので、まず色をつけましょう。5行目くらいまでの範囲を選択します。

メニューの表示形式＞交互の背景色で好みの色を選択します。

フォームからデータが追加されると、この背景色を設定された行がどんどん増えていくので、5行目くらいまで設定しておけば十分です。

続けて、名前付き範囲を３つ定義します。
B列をガツッとつかんで、メニューのデータ＞名前付き範囲を選びます。

立替明細シートのB列（'立替明細'!B:B）に「立替えた人」という名前をつけます。

同じ要領で、以下の名前付き範囲を定義します。
　立替明細シートのC列（'立替明細'!C:C）に「分類」
　立替明細シートのD列（'立替明細'!D:D）に「立替金額」

あらかじめ、【集計表】シートに定義されていた２つに追加して、【立替明細】シートに新たに３つが定義されたので、計５つの名前付き範囲が定義されていることになります。

【集計表】シートに関数を定義

続けて、【集計表】シートに戻り、セルのB5に以下の数式を貼りつけます。
=SUMIFS(立替金額,立替えた人,$A5,分類,B$4)

続けて、B5のセルの右下の青い丸を左クリックでつかんで、「立替合計」の左隣のセルまでドラッグします。

無事、式がコピーされたら、青い丸を再度左クリックでつかんで、今度は「合計」の一つ上の行のセルまでドラッグします。

これでほぼ設定は終了です。

動作確認とオプション

それでは、フォームからデータを入力してみましょう。

フォームの編集画面を開いて、プレビューボタンをクリックするか、

スプレッドシートから、ツール > フォームを管理 > 実際のフォームを開くを選択します。

適当にデータを入力して送信ボタンをクリックします。

フォームの送信が終了したら、スプレッドシートの【立替明細】シートを確認します。

データが無事に登録されていますね。【集計表】シートも確認してみましょう。

集計も期待通りにされています。

あとはオプションとして、フォームから立替データが入力されたら、LINE通知をして欲しい場合は、【GAS活用術④-1】Googleフォームから送信されたら内容をLINEで通知・ほぼコピペ編を参考に設定をしてみてください。

Googleフォーム側にスクリプトを登録する方法です。

フォームのリンクを取得してお知らせ

立替えをしてくれた参加者にお知らせするため、フォームのリンクを取得してみましょう。フォームの編集画面の送信ボタンをクリックします。

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

このリンクを参加者にお知らせして、立て替えたら入力をしてもらうようにしてください！

これで立替データがどんどん入力されるのを待つばかり。あとは当日に会費を決めて精算すれば、会計担当者のお仕事は終了です。

今回はすべての設定手順を一度にご紹介したので、長くなりました。次回は今回使用したスクリプトや関数の補足をしていきたいと思います。

この記事が気に入ったらサポートをしてみませんか？