GoogleAppsScriptとスプレッドシートでバーコード出席確認システムを作ってみよう！

はじめに

この記事は身の回りの困りごとを楽しく解決！ by Works Human Intelligence Advent Calendar 2023の19日目として投稿させて頂いております！

ある団体から、「毎回イベントの出席確認をするのに手動でやるのは面倒だから、バーコードリーダーを使って子供たちやスタッフの出席確認をしてスプレッドシートにまとめるものが欲しい。」という依頼を頂き、開発することになりました。
ですが、、GASを触るのは初めてだったので勉強しながらやっていきます！

団体からの要望を確認するとこんな感じになりました。
・GoogleFormsに答えるとスプレッドシートに登録と、確認メールを送って欲しい。
・日付を指定して出席シートを作成してほしい。
・後で出席を追加する事ができるようにして欲しい。
・バーコードを読み取ったものを見えるように貯めて、最後にボタンクリックで処理して欲しい。
・バーコードの最初の桁をみると、スタッフか学生スタッフか参加者かわかるようにして欲しい。

では作っていきましょう！

システム概要

このような形でシステムのデータの流れができています。
緑の矢印はそのシートに処理をするわけではなく、データを獲得しているという矢印です。

全部スプレッドシートで確認したいとのご要望を頂いていたので、登録データも全てスプレッドシート管理にしてすぐに閲覧できるようにしています。また、バーコードを生成して写真として保存して欲しいとの事だったので Google Drive のフォルダに、名前付きのバーコード写真を保存する形で処理をしています。

機能1 GoogleForms提出時の処理について

まず、登録用Formsを作成します。
そしてメールを送り返す処理を作っていきますが、これはGoogle Formsの設定から回答のコピーを回答者に送信をすれば完了するので設定します。

次に、スプレッドシートへの登録をしていきます。

スプレッドシートにリンクから自分のリンクしたいスプレッドシートを選択してください。
なければ新しいスプレッドシートを作れるのでそれで作るのもありです！
うまくできるとGoogleFormsのアイコンがついたシートができます！

最後に作成したスプレッドシートにGoogleAppsScriptを結びつけます。
拡張機能からAppsScriptをクリックすると、そのスプレッドシートに対してのGoogleAppsScriptを書くことができます。

これで準備は完了です！
GoogleAppsScript(GAS)でプログラムを書いてみましょう。

code.js

//gsだけどjsにするとハイライトつくのでこうしてます。

function onFormSubmit(e) {

  //対象にしたいシート名を入力
  var sheetN = 'Formsの回収シート名';
  //上の図であれば'フォームの回答1'

  let spreadsheet = SpreadsheetApp.getActive();//おまじない
  let sheet = spreadsheet.getSheetByName(sheetN);//form結果の読み込み

  let last = sheet.getLastRow();//最後の行に書き込むためそのための行指定。

  if (last !== 1) {
    let num_cell = sheet.getRange(last, 13).setNumberFormat("0000000");//会員番号
    let barcode_cell = sheet.getRange(last, 14);//バーコード書くとこ
    let name_cell = sheet.getRange(last, 4).getDisplayValue();
    let job_cell = sheet.getRange(last, 3).getDisplayValue();

    //3,4などの数字はformsの質問番目で変わるので自分で適宜変更してください。
    //シートにバーコードを生成するのは、全質問の最後にしましょう。今回は、13問あるので14列目に生成します。

    Logger.log(job_cell);//確認用logなくてもいいよ

    switch (job_cell) {

      case "一般参加者":
        num = last - 1 + 2000000;
        num = String(num);
        break;

      case "学生スタッフ":
        num = last - 1 + 1000000;
        num = String(num);
        break;

      case "大人スタッフ":
        num = last - 1;
        num = String(num);
        num = num.padStart(7, "0");
        break;

    }
    //選択式質問にしてセルに入ってる文字列を読み取って分けるようにしてます。
    //大人スタッフは頭が0,学生スタッフは1,一般参加者は2になるようにしてます。



    num_cell.setValue(num);

    Logger.log(num_cell.getValue());

    let formula = '=image("https://free-barcode.com/barcode.asp?bc1="&';
    let formula1 = "M" + last;//行を変えて下さいね
    let formula2 = '&"&bc2=6&bc3=3&bc4=1.05&bc5=1&bc6=1&bc7=Arial&bc8=15&bc9=1",1)';
    let _formula = formula + formula1 + formula2;
    
    sheet.setRowHeight(last, 75);
    
    barcode_cell.setFormula(_formula);

    //保存したいWebpフォーマットの画像URLを変数で定義 
    //バーコードリーダーによってちょっと値が変わるので注意
    let imageUrl = "https://free-barcode.com/barcode.asp?bc1=" + num + "&bc2=6&bc3=3&bc4=1.05&bc5=1&bc6=1&bc7=Arial&bc8=15&bc9=1";
    //UrlFetchAppで画像URLにリクエストし、画像ファイルを取得
    let image = UrlFetchApp.fetch(imageUrl).getBlob();

    let image_jpg = image.setName(name_cell);

    //png変換
    image_jpg = image.getAs(MimeType.PNG);

    //GoogleDriveに取得した画像を保存
    var folder = DriveApp.getFolderById("自身のgoogledrive idを取得");
    folder.createFile(image_jpg);


  }

}

　onFormSubmit(e)関数はトリガーの設定が必要です。

トリガーを追加をクリックしてからトリガーを
下のように設定するとformsが提出された時に動くようになってくれます。
イベントの種類をフォーム送信時にしてあげるのを忘れないように注意！

こんな感じで、登録番号を今の行とスタッフの種類に応じて番号を設定、バーコード生成サイトから登録番号とバーコードリーダーに応じた画像を取得しています。最後にGoogleDriveに保存の処理をしています。

GoogleDrive idの補足説明ですが
保存したいフォルダにアクセスしてそのURLのここに生成のところにある長い文字列がGoogleDrive idになります。

実際にはこんな感じです。
ちゃんと頭の数字でスタッフの見分けができるようになっています！

このようにフォルダに保存もできています！

という事でバーコード生成とform処理ができるようになりました！
次はバーコード処理機能と出席確認シートを作成できるようにしていきましょう。

機能2 バーコードでの出席確認と出席確認シートを作る

今度のスクリプトは出席確認と出席確認シートを作るものになります！
一から解説をしていきます、、、。

まずスプレッドシートにてボタンを作成することにしましょう。

挿入から図形描画を選択します。

その後図形描画画面が開いた後は、丸と四角が重なっているみたいな図形をクリックして出てきた選択肢から図形をクリックします。そうすると図形一覧が出てきますので、ボタンみたいな形の図形をクリックします。

範囲を選択してお好みの大きさのボタンを作りましょう

テキストとか色も変えれます！
画像の大きさ変わっちゃったのは許してくださいませ、、、。

テキストはボタンの真ん中くらいにカーソル合わせてダブルクリック

保存して終了押すとこんな感じになります。

実際にはこのようにしています。
バーコードを処理するためにbarcodeという名前のシートを作成してください。
ボタンを二つ、名前は下記を参考に作ってください。
またバーコードはA列の2行目から処理していくので、そこには被せないようにしてください。

このボタン2つに割り当てるスクリプトを書きましょう！

まずはまたAppsScriptを開いていきましょう。
先程書いたコードの下に追記する形で書いていきます。

code.js

//code.gsですが、ハイライトの関係上これにしています。
function attendance() {

  const spreadsheet = SpreadsheetApp.getActive();//Activeなシートを獲得

  const ui = SpreadsheetApp.getUi();//uiを表示するための変数

  const res = ui.alert("注意！", "今日の日付のスプレッドシートに出席処理を行いますか？", ui.ButtonSet.YES_NO_CANCEL);//アラートを表示して分岐処理をします。

  let sheet_name = "error";//初期値がerror処理用の値になっています。

  switch (res) {

    case ui.Button.YES:
      const date = new Date();//日付オブジェクト獲得
      sheet_name = Utilities.formatDate(date, "JST", "yyyy/M/d");//形式をJSTにformatする
      ui.alert(sheet_name + "に書き込み処理を行います。シートが存在しない場合は今日のシートに書き込みがされます");

      const write_sheet = spreadsheet.getSheetByName(sheet_name);//書き込むシートの確認

      //なかったときは今日のシートを作ります
      if (write_sheet === null) {
        sheet_name = createTodaySheet();
      }

      break;

    case ui.Button.NO:
      //error
      let res_name = ui.prompt("シートの名前を入力してください(yyyy/M/dの日付になっています)");
      res_name = res_name.getResponseText();

      sheet_name = res_name;
      ui.alert(sheet_name + "に書き込み処理を行います。シートが存在しない場合はerror表示がされます");
      break;

    default:
      break;

  }

  write_data(sheet_name);//読み取ったバーコードのログイン処理を行い中で書き込みまで行う

  ui.alert("全ての処理を終了しました");

};

function createTodaySheet() {

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();//Activeなシートを獲得

  const ui = SpreadsheetApp.getUi();//uiを表示するための変数

  let newSheet = spreadsheet.insertSheet();//スプレッドシート内に新しいシートを挿入

  const date = new Date();//日付オブジェクト獲得

  let sheet_name = Utilities.formatDate(date, "JST", "yyyy/M/d");//形式をJSTにformatする

  newSheet.setName(sheet_name);//名前設定

  ui.alert(sheet_name + "シートを作成しました");

  //format 書き込み

  const database = spreadsheet.getSheetByName('Formsの回収シート名');//databaseファイルになります。

  let dataRange = database.getRange(1, 2, 1, 13);//nameのcellを取得する
  //最初は必ず日付が入ってしまうので、1,1ではなく1,2から始めます。
  //質問数で変わるので1,13の13の部分は適宜変更してください。

  let writeRange = newSheet.getRange(1, 1, 1, 12);//書き込むrangeの取得
  //質問数で変わるので1,12の12の部分は適宜変更してください。上のdatarangeの最後の値-1です。
  //今回であれば13までなので13-1で12になっています。

  dataRange.copyTo(writeRange);//copyします。

  return sheet_name;

};

function write_data(sheet_name) {

  const spreadsheet = SpreadsheetApp.getActive();//Activeなシートを獲得

  const ui = SpreadsheetApp.getUi();//uiを表示するための変数

  const barcode = spreadsheet.getSheetByName('barcode');//barcodeデータ読み取りシート

  const database = spreadsheet.getSheetByName('Formsの回収シート名');//databaseファイル

  //実際の処理はここから

  for (var i = 2; i <= barcode.getLastRow(); i++) {//barcode.getLastRowでbarcodeの最後の列を取り込み

    var cell_tmp = 'A' + i;//何番目か指定している一度名前をよけてstringに合体している

    let barcode_cell = barcode.getRange(cell_tmp);//最初のcellを獲得する

    let id = barcode_cell.getValue();//barcodeのidを獲得する

    let id_cell = (id % 1000000) + 1;//これでdatabaseの何行目かを取得

    let dataRange = database.getRange(id_cell, 2, 1, 12);//nameのcellを取得する

    //id_cell部分の補足
    //idは7桁で振られているためこのように書くことができる
    //Code39での実装になる→バーコードリーダーの型式から
    //https://barcode.design/jpindex.asp
    //ただしこの場合は会員が99万9999人を超えてしまうとエラーがでる。
    //その場合は新しくバーコードを振りなおす必要がある。今回は市の人口に基づき100万人以上の利用を想定していない

    if (sheet_name === "error") {

      //error処理
      ui.alert("セルにdataが存在しないか、書き込むシートが重複、またはありません。処理を終了します");

      break;

    } else {

      record_data(sheet_name, dataRange);//データ範囲とシート番号に基づき記録を行う

    }

    barcode_cell.clearContent();//barcodeのcellの中身消す

  }

};

function record_data(sheet_name, dataRange) {

  const spreadsheet = SpreadsheetApp.getActive();//Activeなシートを獲得

  const ui = SpreadsheetApp.getUi();//uiを表示するための変数

  try {

    const write_sheet = spreadsheet.getSheetByName(sheet_name);//書き込むシートの確認

    write_sheet.insertRowsBefore(2, 1);//行を挿入します。

    let writeRange = write_sheet.getRange(2, 1, 1, 12);//範囲指定する

    dataRange.copyTo(writeRange);//copyする

  } catch {

    ui.alert("errorが発生したため書き込みできませんでした");

  }

};

function create_other_sheet(){

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();//Activeなシートを獲得

  const ui = SpreadsheetApp.getUi();//uiを表示するための変数

  let res_name = ui.prompt("作成するシートの名前を入力してください(yyyy/M/dの日付のフォーマットです)");

  res_name = res_name.getResponseText(); //textにして受け取ります。

  if(res_name != ""){

    let newSheet = spreadsheet.insertSheet();//スプレッドシート内に新しいシートを挿
    newSheet.setName(res_name);//名前設定

    const database = spreadsheet.getSheetByName('database');//databaseファイル

    let dataRange = database.getRange(1, 2, 1, 13);//nameのcellを取得する

    let writeRange = newSheet.getRange(1, 1, 1, 12);//範囲指定する

    dataRange.copyTo(writeRange);//copyする

    ui.alert(res_name + "シートを作成しました");

  }else{

    ui.alert("シート名を入力してください");

  }



}

あまりにも長いので、簡単に図で説明するとこのようになっています。
ボタンを押したときに今日のシートか確認を行い、シートが存在するかどうかを確認して
書き込み処理を行うといった形になっています。
また、別日に出席処理がまとめてできるように別日フォーマット作成スクリプトも書いています。

最後にスクリプトをボタンに割り当てます。
右上の点々をクリックしてスクリプトを割り当てをクリックします。

バーコード処理ボタンにはattendance関数を
日付を指定してシート作成ボタンにはcreate_other_sheet関数を割り当ててください。

実際にやってみる

実際にバーコードを打ち込んで処理をさせた結果がこちら！
今回は例として、2023/4/17というシートを作成して処理をさせてみましょう。

別日シート作成ボタンを押すとこのようにポップアップがでるので、従って入力するとできます。

日付も指定したものができています！

バーコードリーダーでバーコードを読み取ると数字が表示されるので貯めて行きます。

バーコード処理ボタンを押すとこんな感じです。

ちゃんと振り分けとGoogleFormsで登録した内容が全て表示されていますね。

ということで完成です！お疲れ様でした。

あとがき

初めてのGASでの作成ですのでプログラムに改良の余地はあると思います、、。何かわからないところや思った所があればコメントなりで教えてください、、！　反応出来ると思います。

このシステムを作っている時は、初めてGASだったので本やwebで勉強しながらこのコードを書いていました。学び始めは大変でしたが、システムのコードを書き始めてみると意外と使える関数が調べていくうちにたくさん見つかって楽にコーディングできた気がしています。Googleさんありがとう。

また、このシステムを導入してもらった団体ではうまくバグもなく現在も活用してもらえてるようで、すごく嬉しい気持ちです。課題を解決出来た事の喜びを知れた事は学生ながらすごく良い経験だったなと感じております。ご相談頂いた団体の皆様に深く御礼申し上げます。自分のシステムが動いてるって嬉しいよね。
ということで、皆さんも

このシステムで出席確認を手軽に楽にしてみてください！

パン屋さん。でした。