googleフォームとスプレッドシートとGASを使ってフリーアドレスの座席表を作って公開する！

050-VBA

2024.06.25

へっぽこヘタレシステム管理者の管理人です。

職場の座席をフリーアドレス化するかもしれない・・・

事になり、そうなると日々かわる座席表をどうしようかと悩んでいたところ・・・

googleフォームとスプレッドシートとGASを使って座席表をWEB公開する方法が

あることが分かりさっそく試しに作ってみることにしました。

やりたいことは次のとおりです。

座席ごとに座席番号が初期値設定されたgoogleフォームのQRコードを作る
QRコードをスマホで読み込む
名前を入れて送信する
WEB公開したスプレッドシートの座席表に名前が表示される
毎日午前0時になったら座席表がリセットされる

とりあえずやりたい事の動画イメージ
googleフォームを作る
GASを仕込む
GASが動くトリガーを設定する

とりあえずやりたい事の動画イメージ

とりあえずやりたい事の動画イメージは次のとおりです。

googleフォームを作る

まずはgoogleフォームを作ります。

お試しですので・・・

項目は【氏名】と【座席番号】のみです。

次に、座席番号ごとに、座席番号が初期値設定されたQRコードを作成します。

googleフォームに初期値を設定する方法はこちら

なお・・・

スプレッドシートにはURLからQRコードを自動で作る関数があります。

非常に便利ですね。

=image("https://api.qrserver.com/v1/create-qr-code/?size=142x142&data="& URLが記載されたセルを指定)

前述のgoogleフォームに初期値を設定する方法と

関数を使って別のシートに座席ごとにQRコードを作成します。

フォームから入力すると・・・

次のとおり行が追加されていきます。

タイムスタンプが付くので・・・

いつ誰がどこの席に座っていたかの履歴もず～っと残ります。

下図が座席表のイメージです。

席が埋まったセルに上書きで入力すると自動で更新されます。

GASを仕込む

つぎにGASを仕込みます。

GASの内容は次のとおりです。

フォームが送信されたらスプレッドシートに入力された行数と列数を取得する
行から座席番号（座席のセル）を取得する
行から氏名を取得する
座席表の当該セルに氏名を入力する

コードは次のとおりです。

function onEdit(e) {
  // イベントオブジェクトからアクティブシート名を取得
  var sheetName = e.source.getActiveSheet().getName();

  // 対象にしたいシート名
  var targetSheetName = 'フォームの回答 1';

  // 編集されたシート名と対象にしたいシート名が一致したら実行
  if (sheetName === targetSheetName) {
    // 編集されたセルの行数と列数を取得
    var row = e.range.getRow();
    var col = e.range.getColumn();

    // 座席表の更新
    var seatSheetName = '座席表';

    var sheetN = e.source.getSheetByName(targetSheetName);
    var sheetN2 = e.source.getSheetByName(seatSheetName);

    if (sheetN && sheetN2) {
      // 編集された行の特定の列の値を取得
      var zaseki = sheetN.getRange(row, 3).getValue();
      var zasekiNO = sheetN.getRange(row, 2).getValue();

      // 座席表シートに値を設定
      sheetN2.getRange(zaseki).setValue(zasekiNO); // ここで1列目に設定していますが、適切な列に変更してください
    }
  }
}

次に毎日午前0時なったら座席表をリセットします。

GASの内容は次のとおりです。

座席表のセル（B5・C5・B6・C6）をクリアする

ブログランキングにご協力ください！

コードは次のとおりです。

function myFunction() {
  var seatSheetName = '座席表';

  // スプレッドシートからシートオブジェクトを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(seatSheetName);

  if (sheet) {
    // 指定されたセル範囲の内容をクリア
    var ranges = ['B5:C5', 'B6:C6'];

    for (var i = 0; i < ranges.length; i++) {
      sheet.getRange(ranges[i]).clearContent();
    }
  } else {
    Logger.log('Sheet not found: ' + seatSheetName);
  }
}