見出し画像

【GAS活用システム②-1】チケットの枚数管理をする・基礎設定編

Hoshi-nok（ほし のっく）

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

【GAS活用システム】の第二弾は、「チケットの枚数管理」です。

コンサートとかライブとか演劇とか町内会のイベントとか、、、チケットの販売枚数を管理したい場合に使えます。対象者はちょっと限定されますかね。

チケット管理というと、一般予約や決済方法、座席管理、、、などなど、かなり大掛かりな話と勘違いされてしまいそうなので、あくまでチケットの「枚数」管理です。

誰が何枚チケットを売ったか、手作業ならノートなどに記入したり、エクセルなら一行ずつ入力したりしますよね？あれをスプレッドシートやGASを使って、ちょっといい感じにしてみよう、という話です。

チケットを売る人はサークルやグループ内のいわゆる仲間うちで、一人あたりのノルマなどはないけれど、誰が何枚売ったか、全体で合計何枚売れたのかを管理したい、という場合を対象として想定しています。

GASを利用してのチケットの枚数管理は以下のように行います。

  • データの入力は、Googleフォームから行います。チケット担当者・会計担当者が入力する場合でも、データを１件ずつ入力するのであれば、フォームから入力するのが楽で圧倒的におすすめです。

  • オプション）フォームから入力されたら、チケット担当者・会計担当者に「誰が何枚売って販売合計金額がいくら」かわかるようなLINE通知を自動送信します。

  • スプレッドシートの【チケット明細】シートにはフォームから入力されたデータが蓄積され、【チケット集計】シートには人毎の販売枚数と販売金額が自動集計されるようにしておきます。

  • オプション）日単位の販売枚数（例えば、一日の終わりに当日の販売枚数）とそれまでの販売合計枚数を、LINEで自動通知します。

LINE通知はお好みで。グループ全体に送付する必要がないなら、グループ内のごく一部のメンバーだけ、もしくは管理者個人に送付するだけでもよいと思います。


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

設定の仕方は、【GAS活用システム①】の「立替と会費の精算システム」とほぼ同様で、テンプレートのコピーから行います。

前回の「立替と会費の精算システム」では、使用方法の説明を設定前に行いましたが、今回はシンプルなので、さっそく設定に入っていきましょう。

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

いちばん初めの手順は、【チケット集計】シートのテンプレートをコピーして保存することです。
Googleアカウントでログイン後、以下をクリックしてください。

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

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

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

【チケット集計】シートの編集

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

はじめに、セルのA1のタイトル「〇〇コンサート・チケット枚数管理」を変更しましょう。ここで入力するタイトルは、後で自動生成する、フォームのタイトルやファイル名としてそのまま使用されます。

次に、大人代金と学生代金の具体的な金額（単価）をB4とC4セルに入力してください。今回は、大人代金と学生代金の二種類のチケットがある前提で話を進めていきます。

続けて、チケットを売る人の名前（セルのA7～A22）の値を変更してください。今回のテンプレートには、前回同様、サンプルデータとして、日本で多い名字の上位16位までの名前を設定してあります。

今回、チケットを売る人はサークルやグループ内のいわゆる仲間うちを想定しています。

フォームでは「誰が」販売したか、リストから選択するようにし、【チケット集計】シートでは名前毎に販売した枚数を自動集計します。

シートに定義された「名前付き範囲」から、フォームの名前のプルダウンの選択肢を生成します。

シート側で、人の名前を追加するなら行の追加、削除するなら行の削除をすることになりますが、名前付き範囲が上記のように定義されている点にご注意ください。

他にも、テンプレートには、以下の計４つの名前付き範囲が既に定義済みになっています。

  • 【チケット集計】シートのA7:A22が「名前リスト」

  • 【チケット集計】シートのB4:C4が「チケット単価」

  • 【チケット集計】シートのB23が「大人合計枚数」

  • 【チケット集計】シートのC23が「学生合計枚数」

これらの名前付き範囲は、GASやスプレッドシートの関数で使用します。

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

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

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

今回は、すべてのスクリプトをファイル：コード.gsに登録します。

スクリプトをコピペ

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

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

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

function onOpen() {
  let ui = SpreadsheetApp.getUi(); 
  ui.createMenu('GAS活用システム') 
    .addItem('フォーム上の名前のリストを更新', 'updatePeopleList') 
    .addToUi();
}

function updatePeopleList(){
  //アクティブなスプレッドシートから名前付き範囲"名前リスト"の値を一次元配列で取得
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let people = ss.getRangeByName("名前リスト").getValues().flat();
  
  //フォームの対象項目のプルダウンの選択肢を配列の値で更新
  let form = FormApp.openByUrl(ss.getFormUrl());  //連携しているフォームを取得 
  let items = form.getItems();                //フォーム内の全アイテムを取得
  let item = items[0];                        //0番目から数えて何番目のアイテムか指定
  item.asListItem().setChoiceValues(people);  //配列peopleの値でプルダウンの選択肢を更新
}

function submitForm(e) {
  //スプレッドシードの一番左のシートを取得
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; 

  //合計金額を計算する数式をセットするセルを取得する
  const row = e.range.getRow();  //シートに書き込まれたデータの行番号
  let totalPriceCell = sheet.getRange(row, 5);  //合計金額は5列目

  //対象のセルに合計金額を計算する数式をR1C1形式で入力
  totalPriceCell.setFormulaR1C1("SUMPRODUCT(チケット単価,R[0]C[-2]:R[0]C[-1])")
                .setNumberFormat("[$¥-411]#,##0"); //通貨書式を指定

  //フォームでの回答内容をe.valuesで一時配列で取得して分割代入で個別の変数に代入
  let [timeStamp, name, adult, student, description] = e.values

  const msg = '\n' + name + 'さんのチケット販売情報'
            + (adult && ('\n大人：' + adult + '枚'))
            + (student && ('\n学生：' + student + '枚'))
            + (description && ("\n備考：" + description))
            + '\n合計金額:'+totalPriceCell.getValue()+'円';
  
  const token = "★トークンをここに設定★"; //トークンを設定
  //sendLine(msg, token);  //sendLine関数でmsgをtoken宛にLINE通知
}

function sendLine(msg,token){
  
  let options = {
    "method": "post",
    "Content-Type" : "application/x-www-form-urlencoded",
    "payload": "message=" + msg,
    "headers":{"Authorization" : "Bearer " + token}
  };
  UrlFetchApp.fetch("https://notify-api.line.me/api/notify",options);
}

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

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

onOpen関数の実行

プロジェクトを保存後、一番上に定義している関数onOpenが、「デバッグ」と「実行ログ」の間に表示されるのを確認したら、「実行」ボタンをクリックします。

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

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

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

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

フォームの作成

今回は、フォームを手動で作成してみましょう。質問が４つだけの非常にシンプルなフォームです。

スプレッドシートを保存しているフォルダで、新規をクリックして、Googleフォーム > 空白のフォームを選択します。

  • 左上のファイル名「無題のフォーム」を変更すると、フォームのタイトルも同時に変更されます。

  • １つ目の「無題の質問」を「名前」に変更し、形式をラジオボタンから「プルダウン」に変更します。また、「必須」のボタンをオンに設定します。名前の選択肢はこの時点では登録しません。

  • ＋（質問を追加）をクリックします。

  • ２つ目の質問の名前を「大人チケット枚数」、形式を「プルダウン」、選択肢として1から5まで（必要に応じてより大きな数字まで）の値を設定します。

  • ３つ目の質問の「学生チケット枚数」も、「大人チケット枚数」と同様に、形式を「プルダウン」、1から5まで選択肢を設定します。

  • 最後に4つ目の質問は、質問名を「備考」、形式を「段落」にします。

続けて、回答のタブをクリックします。

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

「既存のスプレッドシートを選択」して、選択リンクをクリックします。

フォームの回答をコピーするスプレッドシートとして、テンプレートをコピーしたスプレッドシートを選択します。

フォームと既存のスプレッドシートとの連携が完了し、【チケット集計】シートの左に【フォームの回答 1】シートができました。

一番左の列は「タイムスタンプ」で、その他に、フォームで作成した質問名と同じ列が自動的に作成されています。

名前のリストを自動生成するスクリプトを実行

フォームの一番初めの項目「名前」には選択肢を設定していませんでした。続けてスプレッドシートのメニューから「名前」の選択肢を自動生成していきます。

さきほど実行したonOpen関数の実行により、スプレッドシートのメニューに、「GAS活用システム」が追加されています。「GAS活用システム」メニューから、「フォーム上の名前のリストを更新」を選びます。

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

フォームの名前の選択肢が、【チケット集計】シートの名前付き範囲の「名前リスト」から生成されました。

onOpen関数や独自メニューの作成については【GAS活用術⑥】を、GASで名前付き範囲を元にフォームの質問の選択肢を生成する方法については【GAS活用術⑦-1】を参照してください。

フォームの編集

フォームの「テーマをカスタマイズ」を選択し、フォームのデザインを変更してもよいでしょう。

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

【チケット明細】シートに追加の設定

スプレッドシート側に戻り、まず、フォームとの連携で自動生成された「フォームの回答 1」というシートの名前を、「チケット明細」に変更します。

【チケット明細】シートはフォームと連携しているので、フォームから送信されたデータが自動的にこの【チケット明細】シートに蓄積されていきます。

メニューの 表示形式 ＞ 交互の背景色 で好みの色を選択すると、今後、フォームから入力されるデータが見やすくなります。

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

【チケット明細】シートのB列（'チケット明細'!B:B）に「販売した人」という名前をつけます。

同じ要領で、以下の名前付き範囲を定義します。
　チケット明細シートのC列（'チケット明細'!C:C）に「大人チケット枚数」
　チケット明細シートのD列（'チケット明細'!D:D）に「学生チケット枚数」

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

【チケット明細】シートの最後の作業として、E列に列を追加して、見出しに「合計金額」と入力してください。

【チケット明細】シートの他の列はフォームと連携しているので、入力すると自動的にデータが入りますが、この手動で追加した「合計金額」にはデータが入りません。

今回は、後の手順で、この列に合計金額を自動算出する数式をGASで設定していきたいと思います。

【チケット集計】シートに関数を定義

続けて、【チケット集計】シートに戻り、に以下の数式を貼りつけます。
「セルのB7の数式」 =SUMIFS(大人チケット枚数,販売した人,$A7)
「セルのC7の数式」 =SUMIFS(学生チケット枚数,販売した人,$A7)

続けて、B7とC7のセルを選択し、C7の右下の青い丸を左クリックでつかんで、「合計」の一つ上の行のセルまでドラッグします。

SUMIFS関数については、【GAS活用システム①-3】のスプレッドシートのSUMIFS関数で説明していますので、そちらを参考にしてください。

一度目の動作確認（フォームとシートの連携確認）

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

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

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

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

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

フォームから入力したデータが対応する列に設定され、タイムスタンプ列にも自動で送信日時が設定されると思います。ただ、手動で追加した合計金額の列には値は設定されていません。

【チケット集計】シートも確認してみましょう。

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

少し長くなりましたので、今回は基礎設定編としてこの辺で終わりにしたいと思います。今回の基礎設定編だけ行えば、最低限のチケット枚数管理はできそうです。

次回は、応用設定編として、スプレッドシートの【チケット明細】シートに手動で追加した列「合計金額」にGASで数式を設定したり、LINE通知を送ったりする方法を紹介していきます。

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