スプレッドシートとGoogle Apps Scriptで作る簡易出席記録

はじめに

こんにちは！
この記事では、小規模な習い事教室のためにスプレッドシートとGoogle Apps Script（GAS）で簡易な出席記録表を作ったときの方法を書きます。
より良い方法もあると思いますが、一例として参考になれば幸いです。

スプレッドシートの構成

• 生徒一覧シート
• メインシート
  • 生徒一覧から一人だけ選んで表示できる（データの入力規則とVLOOKUPを利用）
  • 後述するスクリプトを実行できるボタン（スクリプト割り当てた図形）を配置
• 出席記録シート
• あの人しばらく来ていないなシート（最後に書きます）

スクリプト

レッスンしたら出席記録シートに追加する関数

function LessonInput(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainst = ss.getSheetByName('メインシート');
  const countst = ss.getSheetByName('出席記録シート');
  const name = mainst.getRange('生徒氏名のセル').getValue();
  const id = mainst.getRange('生徒番号のセル').getValue();
  const dayTime = new Date();
  const data = [[id,name,dayTime]]
  const nextRow = countst.getLastRow() + 1;
  
  countst.getRange(nextRow,1,1,data[0].length).setValues(data);
}

あの人しばらく来ていないなシートとは

この出席記録表を使う習い事教室には、決まった曜日に来るコースのほかに、単発レッスンのプランもありました。
「あの人しばらく来ていないなシート」は、退会の連絡はないけど3ヶ月以上来ていないな…という方のリストをスクリプトで出力するためのシートです。
このシート自体は一行目だけに項目名を書いて、下記スクリプトでリストを出します。

function NoncountList(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const infost = ss.getSheetByName('生徒一覧シート');
  const countst = ss.getSheetByName('出席記録シート');
  const listst = ss.getSheetByName('あの人しばらく来ていないなシート');
  //関数実行時を基準に3ヶ月前の日付を取る
  const date = new Date();
  const limitdate = date.setMonth(date.getMonth()-3);

  //生徒一覧シートのB列に生徒番号、C列に氏名、P列に退会かどうか記載しているのでそこまで取っています
  let infoarray = infost.getRange(2,2,infost.getLastRow() - 1,16).getValues(); 
  //P列が空白なら未退会
  infoarray = infoarray.filter(array => array[14] == (''));

  //3ヶ月間の出席記録を取得
  let countarray = countst.getRange(2,1,infost.getLastRow() - 1,3).getValues();
  countarray = countarray.filter(array => Number(array[2]) >= limitdate);

  //未退会の配列から出席記録がある人を取り除く
  let resultarray = infoarray.filter((a) => {
    return countarray.findIndex((b) => a[0] === b[0]) === -1;
  });
  //番号、氏名のみにする
    resultarray = resultarray.map(array => [array[0],array[1]]);

  //あの人しばらく来ていないなシートの二行目以降に入力があればクリアする
  const list_last_row = listst.getLastRow();
  if(list_last_row > 1){
    listst.getRange(2,1, list_last_row -1,2).clearContent();
    }
  //シートに出力
  listst.getRange(2,1, resultarray.length,2).setValues(resultarray);

}

おわりに

読んでいただき、ありがとうございました！
疑問点や改善案などありましたら、コメントください。