【GAS】Googleフォームとスプレッドシートを連携してフォームの選択肢を自動更新する方法

Googleフォームで予約受付をしているけど、予約がダブルブッキングして困ったという経験はありませんか?
予約が重複してしまうと、せっかく予約をしてくれたお客様に迷惑をかけてしまったり、クレームの原因となったりしてしまいます

こーすけ先生

そんな悩みを解消するために、今回はGAS(Google Apps Script)を駆使して、Googleフォームとスプレッドシートを効果的に連携させる方法をご紹介します

これにより、予約が満席になった瞬間も見逃さず、リアルタイムで更新が可能となり、ダブルブッキングのリスクを大幅に軽減できます

この記事では、GAS(Google Apps Script)を駆使して、Googleフォームとスプレッドシートの強力な連携を実現する方法をご紹介します

本記事の内容
  • Googleフォームとスプレッドシートの連携を行う
  • スプレッドシートで予約情報を管理し、予約枠が埋まった瞬間、Googleフォームをリアルタイムで更新するテクニックを紹介
こーすけ先生

リアルタイムで予約情報をGoogleフォームに自動反映できるのが偉すぎる

この記事を読めば、Googleフォームからの予約情報をスプレッドシートに反映させ、スプレッドシートから空き情報を読み取り、リアルタイムで予約可能日を更新する方法を学ぶことができます!めちゃくちゃ便利なので、是非読んでいってください!

こーすけ先生

予約管理の手間を大幅に削減し、スムーズな運用を実現できるので、ぜひ読んでみてください

この記事の執筆者について
  • GASの人
  • ITベンダSEとして12年勤務する中で民間、金融、官公庁の現場を一通り経験済
  • 現在は公務員をやりながら起業に向けて着々と準備中
GASなら任せろ!

​GASを極めたい方や、業務の効率化を図りたい方は、ぜひこの記事を読んください!
難しいことはGASに任せて、我々人間は楽しちゃいましょう!

こーすけ先生

当ブログでは実際に仕事でGASを扱っている私が、GASの魅力について徹底的に取り上げていきます!

目次

本記事で取り組む演習
  • Googleフォームのフォーム項目をGASで取得
  • Googleフォームを取得した値を用いてスプレッドシートを更新する
  • スプレッドシートの内容を用いてGoogleフォームを更新
  • 予約受付結果をメールで送信

【事前準備】Googleフォームとスプレッドシートを準備する

こーすけ先生

準備が不要な方は【STEP1】までジャンプしてください!

こーすけ先生

今回の起動元となるフォームを作成していきましょう!

どのようなフォームでもよいのですが予約受付フォームっぽく以下のようなフォームを作成しました

スクロールできます
項目名 タイプ必須
予約者氏名記述式
予約者メールアドレス記述式
予約日プルダウン

参考に私が作成したフォームは以下となります

こーすけ先生

Googleフォームを作成したら、右上の「プレビュー」ボタンを押下して、一度回答してみましょう!のちほど回答データを取得するためだけなので、どのような回答でも構いません

回答が1つもないと、エラーが起こってしまうので、必ず何かしら回答をしておいてくださいね!

予約日空き状況を管理するスプレッドシートを作成

Googleスプレッドシートを用いて、予約日空き状況を管理するシートを作成しましょう
どんな様式でも構いませんがこのシートのカラム順を前提に今回のプログラムは作成します

こーすけ先生

私は以下のように作成しました

このシートの予約受付日に記載の2024/1/1から1/8までを、先述のGoogleフォームの予約日に初期セットアップしています

GASエディタ上で使うので、作成したスプレッドシートのIDを事前に取得しておきましょう!

こーすけ先生

一応、スプレッドシートのリンクも載せておきます
必要に応じてコピペし、自分のスプレッドシートに貼り付けてください

【STEP1】Googleフォームのフォーム項目をGASで取得する

準備したGoogleフォームからコンテナバインド型でGASプログラムを記述していきましょう

こーすけ先生

Googleフォームからコンテナバインド型でGASを開くやり方が分からない!という場合は、こちらを参考にしてください!

ひとまず、Googleフォームの回答内容を読み取れるか確認するため、以下のプログラムを実行してみてください
(回答がないとエラーになってしまうため、1つで良いので適当に回答してみてから実行してください)

function gaslog_formSubmit(e) {
  let itemResponses;
  // フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
  if (e !== undefined) {
    itemResponses = e.response.getItemResponses();
  } else {
    const wFormRes = FormApp.getActiveForm().getResponses();
    itemResponses =  wFormRes[wFormRes.length-1].getItemResponses();
  }
  console.log(itemResponses);
}
21:02:40	お知らせ	実行開始
21:02:40	情報	[ { toString: [Function],
    getFeedback: [Function],
    getItem: [Function],
    setFeedback: [Function],
…
    getResponse: [Function],
    getScore: [Function],
    setScore: [Function] } ]
21:02:41	お知らせ	実行完了
こーすけ先生

これだとどんな値がとれているかはわかりませんが、ばっちりとオブジェクトは取得できていますので準備万端です!

今回もdayjsライブラリを使って日付の操作を行うため、ライブラリを追加しておいてください

Day.jsをライブラリに追加する

STEP
メニューバーから「+(ライブラリを追加)」をクリック
STEP
出てきたダイアログのスクリプトIDに以下のIDを入力し、「検索」をクリック

1ShsRhHc8tgPy5wGOzUvgEhOedJUQD53m-gd8lG2MOgs-dXC_aCZn9lFB

STEP
「追加」をクリックすると、GAS上で「Day.jsライブラリ」を使用できるようになる
こーすけ先生

必要に応じて上記記事を参考にしてね!

【STEP2】Googleフォームから取得した値を用いてスプレッドシートを更新する

次に、回答内容を用いて予約日空き状況シートを更新していきましょう

こーすけ先生

では、どのように更新していくかを考えてみます

  1. 回答内容の予約日と一致する予約受付日を探す
  2. 予約受付日の空き枠が予約済を超えていないかを確認する
  3. 超えていなければ予約可とし、予約済をインクリメントする
こーすけ先生

こんなカンジになるかと思います!

// スプレッドシート_予約空き状況
const SPREAD_ID = '**事前準備で取得しておいたIDを記述**';

// スプレッドシート_予約空き状況シートのカラム情報
const COL = {
  DAY:1,
  FREE:2,
  RESERVE:3
};

/**
 * 予約受付フォームが送信された際のトリガーメソッド
 * @param {EventObject} e - イベントオブジェクト
 */
function gaslog_formSubmit(e) {
  const activeForm = FormApp.getActiveForm()
        , wSheet = SpreadsheetApp.openById(SPREAD_ID).getSheets()[0]
        , vals = wSheet.getDataRange().getValues()

  let itemResponses;
  // フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
  if (e !== undefined) {
    itemResponses = e.response.getItemResponses();
  } else {
    const wFormRes = activeForm.getResponses();
    itemResponses =  wFormRes[wFormRes.length-1].getItemResponses();
  }

  vals.shift();
  // 回答から空き状況を更新する
  itemResponses.forEach(function(itemResponse){
    switch (itemResponse.getItem().getTitle()) {
      case '予約日':
        vals.forEach(function(val, idx){
          // 予約日と台帳上の日付を比較して同日を判定
          if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) {
            // 空き枠 > 予約済なら予約成功
            if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
              val[COL.RESERVE-1]++;
              wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);
            } 
          }
        });
        break;
      default:
        break;
    }
  });
こーすけ先生

これを実行すると、先ほどお試し送信した回答に応じて予約日空き状況シートの予約済が更新されます

ここでのポイントは

vals.shift();

とし、wSheet.getDataRange().getValues();で取得した値リストの1行目(ヘッダー)を削除しています

こーすけ先生

こうすると、ヘッダーかどうかをループ処理の中で判定する必要がなくなるのでおすすめです

【STEP2】の補足説明

こーすけ先生

準備が不要な方は【STEP3】までジャンプしてください!

// スプレッドシート_予約空き状況シートのカラム情報
const COL = {
  DAY:1,
  FREE:2,
  RESERVE:3
};

COL オブジェクト: 予約情報を格納するスプレッドシートの各列の位置情報が入ります
DAY 列は予約受付日、FREE 列は空き枠の数、RESERVE 列は予約済の数を示しています

こーすけ先生

セルを定数化する方法は便利なので、是非覚えてね!

 let itemResponses;
  // フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
  if (e !== undefined) {
    itemResponses = e.response.getItemResponses();
  } else {
    const wFormRes = activeForm.getResponses();
    itemResponses =  wFormRes[wFormRes.length-1].getItemResponses();
  }
こーすけ先生

e は関数の引数として渡されているもので、これは予約受付フォームが送信されたときにトリガーされるイベントオブジェクトを指します!

もし eundefined である場合、つまり直接関数が呼び出された場合、最新のフォームの回答情報を取得します

eundefined の場合、最新の回答情報から各質問に対する回答を取得し、itemResponses 変数に代入します(wFormRes.length-1 は、配列の最後の要素のインデックスを指す)

itemResponses.forEach(function(itemResponse){
    switch (itemResponse.getItem().getTitle()) {
      case '予約日':
        vals.forEach(function(val, idx){
          // 予約日と台帳上の日付を比較して同日を判定
          if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) {
            // 空き枠 > 予約済なら予約成功
            if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
              val[COL.RESERVE-1]++;
              wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);
            } 
          }
        });
        break;
      default:
        break;
    }
  });
こーすけ先生

この部分は、フォームの回答情報を元にして、スプレッドシート内の予約台帳を更新するための処理です!

switch文を使い、フォームの回答に対する質問タイトルに基づいて処理を分岐しています

vals.forEach(function(val, idx)

スプレッドシート内の各行に対して処理を行います
val はスプレッドシート内の1行分のデータを表し、idx はその行のインデックスです

// 予約日と台帳上の日付を比較して同日を判定
if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) 

フォームから送信された回答の「予約日」と、スプレッドシート上の台帳の各行の「予約日」を比較して、同じ日であるかを判定します

そして、予約日が同じである場合、以下の処理を実行します

if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
  val[COL.RESERVE-1]++;
  wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);

このコードは、以下の条件に基づいて予約済みの数を更新します

  1. !val[COL.RESERVE-1]: 予約済みの数が0または未定義(undefined)である場合
  2.  Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1]): 空き枠の数が予約済みよりも多い場合

いずれかの条件が満たされると、以下の処理が実行されます

  1. val[COL.RESERVE-1]++;: 予約済みの数を1増やします
  2.  wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1])
    スプレッドシート上の対応するセルに新しい予約済みの数をセットします
    idx+2 は、スプレッドシート上の行番号に対応)

予約が成功した場合、val[COL.RESERVE-1] がインクリメントされ、対応するセルに新しい予約済の数がセットされます

こーすけ先生

予約済みの数を適切に更新し、スプレッドシート上に最新の予約情報を反映することができるよ!

こーすけ先生

「Day.js」はこんなカンジでめちゃくちゃ使い勝手良いライブラリなので、是非活用してみてくださいね~!

【STEP3】スプレッドシートの内容を用いてGoogleフォームを更新する

こーすけ先生

では今回の本題であるGoogleフォームの更新をしていきましょう

フォームを更新する場合や項目を新規に作成する場合は、どういった項目を使うのかを定義することがポイントです

更新をする場合

FormApp.getActiveForm().getItems().forEach(function(item) {
  item.asXXXXItem().setYYYYYY();
});

新規作成する場合

FormApp.getActiveForm().addXXXXItem().setTitle('AAAAAAAAA')
                                     .setYYYYYY();
こーすけ先生

今はざっくりとしたイメージを持つだけで構いません!
次回の記事で各種項目の新規作成・更新方法をお伝えします

// スプレッドシート_予約空き状況
const SPREAD_ID = '**事前準備で取得しておいたIDを記述**';

// スプレッドシート_予約空き状況シートのカラム情報
const COL = {
  DAY:1,
  FREE:2,
  RESERVE:3
};

/**
 * 予約受付フォームが送信された際のトリガーメソッド
 * @param {EventObject} e - イベントオブジェクト
 */
function gaslog_formSubmit(e) {
  const activeForm = FormApp.getActiveForm()
        , wSheet = SpreadsheetApp.openById(SPREAD_ID).getSheets()[0]
        , vals = wSheet.getDataRange().getValues()
        , dateLst = new Array();

  let itemResponses;
  // フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
  if (e !== undefined) {
    itemResponses = e.response.getItemResponses();
  } else {
    const wFormRes = activeForm.getResponses();
    itemResponses =  wFormRes[wFormRes.length-1].getItemResponses();
  }

  vals.shift();
  // 回答から空き状況を更新する
  itemResponses.forEach(function(itemResponse){
    switch (itemResponse.getItem().getTitle()) {
      case '予約日':
        vals.forEach(function(val, idx){
          // 予約日と台帳上の日付を比較して同日を判定
          if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) {
            // 空き枠 > 予約済なら予約成功
            if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
              val[COL.RESERVE-1]++;
              wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);
            } 
          }
        });
        break;
      default:
        break;
    }
  });

    // 空き枠がある日付のみでフォームを更新する
    vals.forEach(function(val){
      if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1]) > Number(val[COL.RESERVE-1])) {
        dateLst.push(dayjs.dayjs(val[COL.DAY-1]).format('YYYY/MM/DD'));
      }
    });
    if (dateLst.length > 0) {
      activeForm.getItems().forEach(function(item) {
        if (item.getTitle() == '予約日') {
          item.asListItem().setChoiceValues(dateLst);
        }
      });
    } else {
      // 全日程が受付終了となれば予約フォーム自体の受付を終了
      activeForm.setAcceptingResponses(false);
    }
}

上記のようにすると以下のような機能が実現できます

  1. まだあまりがある日程のみがフォームで選択できる
  2. 全日程の予約が埋まった場合(dateLstの要素が0の場合)、フォームの受付を終了する
こーすけ先生

実行例はこんなカンジです!
予約空き状況が以下の通りだった場合…

こーすけ先生

フォームを更新すると、空き枠がない「1/2、1/3、1/6」がプルダウンから消えていることが確認できます!

【STEP3】の補足説明

こーすけ先生

準備が不要な方は【STEP4】までジャンプしてください!

    // 空き枠がある日付のみでフォームを更新する
    vals.forEach(function(val){
      if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1]) > Number(val[COL.RESERVE-1])) {
        dateLst.push(dayjs.dayjs(val[COL.DAY-1]).format('YYYY/MM/DD'));
      }
    });
    if (dateLst.length > 0) {
      activeForm.getItems().forEach(function(item) {
        if (item.getTitle() == '予約日') {
          item.asListItem().setChoiceValues(dateLst);
        }
      });
    } else {
      // 全日程が受付終了となれば予約フォーム自体の受付を終了
      activeForm.setAcceptingResponses(false);
    }
こーすけ先生

ここの部分は予約フォームの更新処理を行っています!

vals.forEach(function(val){
  if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1]) > Number(val[COL.RESERVE-1])) {
    dateLst.push(dayjs.dayjs(val[COL.DAY-1]).format('YYYY/MM/DD'));
  }
});

空きがある日付の場合、dayjs.dayjs(val[COL.DAY-1]) を使用してスプレッドシートから取得した日付データを dayjs ライブラリを使って処理し、’YYYY/MM/DD’ フォーマットで dateLst 配列に追加します

if (dateLst.length > 0) {
  activeForm.getItems().forEach(function(item) {
    if (item.getTitle() == '予約日') {
      item.asListItem().setChoiceValues(dateLst);
    }
  });
} else {
  // 全日程が受付終了となれば予約フォーム自体の受付を終了
  activeForm.setAcceptingResponses(false);
}
こーすけ先生

そして、この部分でdateLst 配列に格納された予約可能な日付のリストを使用して、予約フォームを更新する処理を行っています

 if (item.getTitle() == '予約日') {
   item.asListItem().setChoiceValues(dateLst);

Googleフォームの「予約日」の選択肢に、予約可能な日付 (dateLst) をセットします

こーすけ先生

これにより、ユーザーはフォームで選択可能な日付が更新され、予約可能な日だけが選択肢として表示されます

// 全日程が受付終了となれば予約フォーム自体の受付を終了
 activeForm.setAcceptingResponses(false);

フォームに対して、受付を終了するメソッドを呼び出します

こーすけ先生

これにより、新たな回答の受け付けが停止されます

【STEP4】予約受付結果をメールで送信する

こーすけ先生

これはもうものすごく簡単ですね!

// スプレッドシート_予約空き状況
const SPREAD_ID = '**事前準備で取得しておいたIDを記述**';

// スプレッドシート_予約空き状況シートのカラム情報
const COL = {
const COL = {
  DAY:1,
  FREE:2,
  RESERVE:3
};

/**
 * 予約受付フォームが送信された際のトリガーメソッド
 * @param {EventObject} e - イベントオブジェクト
 */
function gaslog_formSubmit(e) {
  const activeForm = FormApp.getActiveForm()
        , wSheet = SpreadsheetApp.openById(SPREAD_ID).getSheets()[0]
        , vals = wSheet.getDataRange().getValues()
        , dateLst = new Array;

  let itemResponses;
  // フォームの回答をイベントオブジェクトまたはフォーム自身から取得する。
  if (e !== undefined) {
    itemResponses = e.response.getItemResponses();
  } else {
    const wFormRes = activeForm.getResponses();
    itemResponses =  wFormRes[wFormRes.length-1].getItemResponses();
  }

  vals.shift();
  // 回答から空き状況を更新する
  let replyAddress, reserveErr;
  itemResponses.forEach(function(itemResponse){
    switch (itemResponse.getItem().getTitle()) {
      case '予約者メールアドレス':
        replyAddress = itemResponse.getResponse();
        break;

      case '予約日':
        vals.forEach(function(val, idx){
          // 予約日と台帳上の日付を比較して同日を判定
          if (dayjs.dayjs(val[COL.DAY-1]).isSame(dayjs.dayjs(itemResponse.getResponse()), 'd')) {
            // 空き枠 > 予約済なら予約成功
            if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
              val[COL.RESERVE-1]++;
              wSheet.getRange(idx+2, COL.RESERVE).setValue(val[COL.RESERVE-1]);
              reserveErr = false;
            } else {
              reserveErr = true;
            }
          }
        });
        break;

      default:
        break;
    }
  });

  // 空き枠がある日付のみでフォームを更新する
  vals.forEach(function(val){
    if (!val[COL.RESERVE-1] || Number(val[COL.FREE-1])>Number(val[COL.RESERVE-1])) {
      dateLst.push(dayjs.dayjs(val[COL.DAY-1]).format('YYYY/MM/DD'));
    }
  });
  if (dateLst.length>0) {
    activeForm.getItems().forEach(function(item) {
      if (item.getTitle()=='予約日') {
        item.asListItem().setChoiceValues(dateLst);
      }
    });
  } else {
    // 全日程が受付終了となれば予約フォーム自体の受付を終了
    activeForm.setAcceptingResponses(false);
  }

  // 予約エラーとなった方には予約できなかった旨をメール送信
  if (reserveErr) {
    sendEmailEx(
      replyAddress,
      '【自動送信】予約不可',
      '予約を受け付けることができませんでした。'
    );
  } else {
    sendEmailEx(
      replyAddress,
      '【自動送信】予約可',
      '予約を受け付けました。'
    );
  }
}

/**
 * メール送信の裏技メソッド
 * @param {String} _recipient - 送信先
 * @param {String} _subject - 件名
 * @param {String} _body - 本文
 * @param {Object} _option - 送信オプション
 */
function sendEmailEx(_recipient, _subject, _body, _option) {
  // 引数の内容でメールを下書き保存する
  const mailDraft = GmailApp.createDraft(_recipient, _subject, _body, _option);
  // 下書き保存したメールIDから下書きを取得し、メール送信を依頼する
  GmailApp.getDraft(mailDraft.getId()).send();
}

Googleフォームを回答がある度に更新しているため、基本的には回答を送信すれば予約を受け付けることが可能です

しかしながら、ちょうど同じタイミングでフォームをひらいた方が複数いた場合、また両者が同じ日付を選んだ場合にダブルブッキングになることもあります

こーすけ先生

そのため、念のため、プログラムの中でも予約の可否を判断しています

まとめ

今回は「GASを用いてGoogleフォーム内の項目を自動更新しよう」ということで、Googleフォーム内の項目に設定するリストをスプレッドシートで管理し、回答を受け付けるごとにGoogleフォームを更新するということを実現しました

こーすけ先生

次回は、各項目の新規作成・更新方法をお伝えします

今回と次回予定のテクニックはGoogleフォームを業務活用する中で必須のテクニックとなります

ぜひ、皆さんも実業務での活用に取り組んでみてください
引き続き、GASを楽しんでいきましょう!!

こーすけ先生

X(旧:Twieer)にて、ブログの更新やQiita記事の更新、GAS情報をお届けしますので、是非フォローしてください!

100日後に起業する公務員

こーすけ先生

退職までの漫画をゆるくXにて更新中!
是非フォローしてね!

この記事が気に入ったら
いいね または フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

GASの人。ITベンダSEとして12年勤務し、民間、金融、官公庁の現場を一通り経験済。html、css、JavaScript、Java、PHPも分かります。最近は専らGASで小規模アプリケーションを頻繁に作成しています。GASのことなら何でもお任せあれ!現在は公務員として働きながら、起業に向けて着々と準備中です!

コメント

コメントする

CAPTCHA


目次