Googleフォームでイベント管理

tiger

2023年12月17日 13:45

はじめに

今回はQRコードを利用したイベントの管理をしたいと思います。

仕組み

プロセスは大きく2つ
1.申し込みの管理
2.イベント当日の出席管理
に分かれます。

【申し込みの管理】
①フォームから申し込み
②定員を更新
③QRコードを生成
④返信用本文を抽出&返信

【イベント当日の出席管理】
⑤QRコードスキャン
⑥出席記録

それでは早速作っていきます。

手順①フォーム作成

質問項目の設定

氏名
参加日時(ラジオボタン、チェックボックスのいずれでも可)

※参加日時の定員の記載方法は全角の（）で囲い、残'人数'名としてください。それ以外は自由に作成してもらって大丈夫です。

↓赤枠のIDを控えておいてください。

メールアドレスの収集

設定タブ > デフォルト > デフォルトでメールアドレスを収集する

スプレッドシートと連携

回答 > スプレッドシートへリンク

手順②スプレッドシートの用意

フォームの回答1シート

これは特に何も触らず、そのままで大丈夫です。

定員シート

ここに開催日ごとの定員情報を格納します。

【カラム構成】

A列:日程
B列:定員
C列:出席確認

※C列は自動算出されるので、空白で問題ありません。

オプションシート

メール送信やその他諸々の設定を行います。

【カラム構成】

A1:送信元アドレス
A2:表示名
A3:件名
A4:CC
A5:BCC
A6:返信先アドレス
A7:フォームのID※1
A8:定員を変更したい質問の番号※2
A9:日程回答集計列※3
A10:QRコード保存用フォルダID
A11:返信用本文ID

※1.手順①で控えたフォームのIDを格納してください。

※2.フォームでの日程選択質問の番号です。メールアドレス収集の分は質問にカウントしません。今回の場合だと2になります。

※3.フォームの回答1シートでの日程選択の回答が集計される列です。今回の場合は4になります。

出席確認シート

【カラム構成】

A列:スキャン
B列:参加者氏名
C列:開催日

返信用本文の用意

メール冒頭によくある「○○様」の部分はプログラムで定義してありますので、ここではそれ以降の文章を記述してください。

尚、申し込み内容の確認に必要な情報は回答により変動するので、その部分は"${day}$"としてプログラムから置換します。

赤枠のIDをスプレッドシートのオプションのB11にコピペしてください。

QRコード保存用フォルダ作成

マイドライブ > 新規フォルダ作成

↓の赤枠のIDをオプションシートのB10にコピペ

手順③GAS

GASとは・・・

Google Apps Scriptという、Googleのサービスをカスタムできるプログラミングのサービスです。(Microsoft officeでいうVBAみたいな)

これを使って申し込み時の定員更新や返信を自動で実行します。

スプレッドシート > 拡張機能 > App Script

するとこのような画面に行くので、一旦function myfunction()…を全て削除。

そこに↓を貼り付ける

// フォームへの回答受信時に毎回自動的に実行される関数
function exe(){
  // 処理中に回答が上書きされるのを防ぐ為、コンテナされているスプレッドシートをロック
  const lock = LockService.getDocumentLock()
  if(lock.tryLock(1000*20)){
    const ss = SpreadsheetApp.getActive()
    const ansSh = ss.getSheetByName("フォームの回答 1")
    const opSh = ss.getSheetByName("オプション")
    const optionValues = opSh.getDataRange().getValues() 

    const formId = optionValues[6][1] //GooleフォームのID
    const choiceIndex = optionValues[7][1] - 1   // 定員を変更したい質問の番号-1

    const limSh = ss.getSheetByName("定員")
    const choiceCol = optionValues[8][1]   // 変更したい質問の回答列

    // reloadChoices関数を呼び出し、フォームの定員を更新⇒戻り値として申込日時の定員超過是非を受け取りdateに格納
    let date = reloadChoices(formId,choiceIndex,choiceCol,ansSh,limSh)
    const answer = ansSh.getRange(ansSh.getLastRow(),1,1,ansSh.getLastColumn()).getValues()

    // 定員が超過していないイベントのみ、QRコードを発行
    const folderId = optionValues[9][1] // QRコードを格納するフォルダのID
    const name = answer[0][2]
    let success = []
    let error = []
    let attachments = Object.keys(date).map(key => {
      if(date[key] == true){
        const fileName = `${name}　様 ${key}`
        let data = `${name}＊${key}`
        success.push(key)
        return createQR(data,folderId,fileName,"png")
      }else{
        error.push(key)
      }
    })

    // 発行したQRコードをZipファイルにまとめる
    const file = Utilities.zip(attachments,`${name}　様　QRコード.zip`)

    // 自動返信
    const toAdress = answer[0][1]
    const fromAdress = optionValues[0][1]
    const fromName = optionValues[1][1];
    const mailsubject = optionValues[2][1];
    const cc = optionValues[3][1];
    const bcc = optionValues[4][1];
    const replyTo = optionValues[5][1];
    const docId = optionValues[10][1]
    let text = DocumentApp.openById(docId).getBody().getText()
    let replace = ""
    success.length > 0 ? replace += "\n下記日程でお申込みを承りました。\n\n【お申込み日時】\n\n・" + success.join("\n・") + "\n\n添付にてQRコードを送付していますので入場の際にご提示をよろしくお願いいたします\n":"";
    error.length > 0 ? replace += "\n下記日程のお申込みに失敗しました。\n\n【お申込み失敗日時】\n\n・" + error.join("\n・") + "\n\n大変申し訳ございませんが定員超過により正常に予約できませんでした。":"";
    let body = text.replace("${day}$",replace)
    sendEmail(toAdress,mailsubject,name,fromAdress,fromName,cc,bcc,replyTo,file,body)
  }
}

// フォームの定員を更新する関数
function reloadChoices(formId,choiceIndex,choiceCol,ansSh,limSh){
  // フォームを取得
  const form = FormApp.openById(formId)
  const items = form.getItems()
  const id = items[choiceIndex].getId()
  const check = form.getItemById(id).asCheckboxItem()
  let choices = check.getChoices()
  choices = choices.map(choice => {
    return choice.getValue()
  })

  // 現在の予約情報を全て算出し、予約情報を空白なしの一次元配列に成形
  let reserved = ansSh.getRange(2,choiceCol,ansSh.getLastRow() - 1,1).getValues()

  reserved = reserved.map(arg => {
    if(arg[0].includes(",")){
      const value = arg[0].replace(/[\s\t\n]/g,"")
      return value.split(",")
    }else{
      return [arg[0]]
    }
  })

  let answers = reserved[reserved.length-1] //最終行だけ別の定数に格納
  answers = answers.map(arg => {
    const end = arg.indexOf("（残")
    return arg.substring(0,end)
  })

  reserved = reserved.flat() //一次元配列に変換

  // 予約情報のうち（残〇名）の部分を削除
  reserved = reserved.map(arg => {
    const end = arg.indexOf("（残")
    return arg.substring(0,end)
  })

  // 予約状況をカウント
  const count = {}
  reserved.forEach(arg=>{
    count[arg] = (count[arg] || 0)+1
  })

  // 定員情報を抽出して連想配列に変換
  let limits = limSh.getRange(2,1,limSh.getLastRow() - 1,limSh.getLastColumn()).getValues()

  let limit = {}
  limits.forEach(arg=>{
    return limit[arg[0]] = arg[1]
  })
  
  // 定員からカウントした数を引いて、選択肢を再生成
  let options = {}
  Object.keys(count).map(key =>{
    return options[key] = limit[key]-count[key]
  })
  
  // 選択肢を用意
  options = Object.keys(limit).map(key => {
    options[key] != undefined ? limit[key] = options[key] : "";
    limit[key] < 1 ? delete limit[key] : "";
    return `${key}（残${limit[key]}名）` 
  })

  // 最新の回答が定員超過していないかチェック
  let answer = {}
  answers.forEach(arg => {
    answer[arg] = true 
  })
  Object.keys(answer).forEach(key => {
    options[key] < 0 ? answer[key] = false :"";
  })

  // 定員が0以下のイベントを削除してフォームの選択肢を更新
  Object.keys(options).forEach(key => {
    options[key] < 0 ? delete options[key]:"";
  })
  options = options.map(arg => {
    return check.createChoice(arg)
  })
  check.setChoices(options)
  return answer
}

// QRコードを生成する関数
function createQR(data,folderId,fileName,extension){
 const url = "https://chart.googleapis.com/chart?"
  + "cht=qr"
  + `&chs=200x200` // 画像のサイズ
  + `&chl=${data}`//データ

  const option = {
    method:"GET"
  }
  const qr = UrlFetchApp.fetch(url,option).getBlob().setName(`${fileName}.${extension}`)
  const folder = DriveApp.getFolderById(folderId)
  folder.createFile(qr)
  return qr
}

// メールを送信する関数
function sendEmail(toAdress,subject,name,fromAdress,fromName,cc,bcc,replyTo,file,text) {   
  // オプションを設定する
  const option = {
    from:fromAdress,
    name:fromName,
    cc:cc,
    bcc:bcc,
    replyTo:replyTo,
    attachments:file
  }

  // メール本文を作成する
  const body =`${name}様\n\n${text}\n`

  // メールを送信する
  GmailApp.sendEmail(toAdress,subject,body,option)
}

// 出席情報をカウントする関数
function calcAttendees(){
  const ss = SpreadsheetApp.getActive()
  const atdSh = ss.getSheetByName("出席確認")
  let attendees = atdSh.getRange(2,3,atdSh.getLastRow() - 1,atdSh.getLastColumn()).getValues()
  
  // 現在の出席状況を開催日ごとにカウントしてオブジェクトに変換
  const count = {}
  attendees.forEach(arg=>{
    count[arg[0]] = (count[arg] || 0)+1
  })

  const limSh = ss.getSheetByName("定員")
  const limits = limSh.getRange(2,1,limSh.getLastRow() - 1,limSh.getLastColumn()).getValues()

  // 定員シートの情報をオブジェクトに変換し出席情報を初期化
  let limit = {}
  limits.forEach(arg =>{
    limit[arg[0]] = 0
  })

  // 初期化された出席情報と現在の出席情報を照合し、出席情報があるもののみ置換
  Object.keys(limit).forEach(key => {
    count[key] != undefined ? limit[key] = count[key] : "";
  })

  // 更新された出席情報をオブジェクトから多次元配列に変換
  const result = Object.keys(limit).map(key => {
    return [limit[key]]
  })
  limSh.getRange(2,3,result.length,1).setValues(result)

}

// シートが開いたときにタブを自動で生成する関数
function onOpen(){
  const ui = SpreadsheetApp.getUi()
  const menu = ui.createMenu('出席集計')
  menu.addItem('実行','calcAttendees')
  menu.addToUi()
}