【GAS】活動受付・管理システムをスプレッドシートで作ってみた

今回は、初めてGAS(Google App Script)を扱います。
「活動受付・管理システム」を作ることになった経緯としては、
毎週不定期で行われる活動の参加者募集をLINEの投票等で行っていたが、その管理が面倒すぎたため、スプレッドシートで一括管理したかった
といった感じになります。

実は本記事のものはVer2になるのですが、Ver1に比べかなり処理が軽量になったので、個人的にはなかなかいいんじゃないかと思ってます。

ちなみに、上記の活動運営の引き継ぎとして作ってるんで、わかりやすくするためにもちょこちょこ更新してくと思います。（コードの変数名も適当ですし）
まあ、ばっちこい本業の勉強をせにゃならん時期なのでわかりませんけどね～

更新履歴
- 6/12
「活動受付・管理システム」の概要
「活動受付・管理システム」の使い方
「活動受付・管理システム」の作り方
今後の課題
参考サイト

更新履歴

6/12

希望者の列入力規定を設定する関数を作り忘れていたので、追加しました。

「活動受付・管理システム」の概要

管理者が、予定一覧に[活動日、活動開始時刻、活動終了時刻、活動場所]を記入し、T列の[状態]を募集中にすることで、行の色が緑に代わります。
活動希望者が、希望者の列に自分の名前をプルダウンリストから選択することで、過去の参加回数と先着順によって優先度が計算され、隣に書き込まれます。
また、活動日になると管理者に詳細のメールが届くようにもしてみました。

「活動受付・管理システム」の使い方

【初期設定】活動者の追加

こちらに活動者の情報を記入していきます。

【初期設定】活動施設の追加

プルダウンリストに表示する活動施設を書き込んでいきます。

【初期設定】活動開始・終了時刻の追加

プルダウンリストに表示する活動開始・終了時刻を追加していきます。

【管理者】活動予定日の追加

活動予定日欄をダブルクリックするとカレンダーが表示されるので、そこから予定日を選択します。
活動開始・終了時刻欄にプルダウンリストから時刻を選択し、入力します。

活動場所欄にプルダウンリストから活動場所名を選択し、入力します。
状態欄にプルダウンリストから「募集中」を選択し入力します。

【参加者】参加希望の入力

希望者欄1~5に自分の名前をプルダウンリストから選択し入力します。
すると、優先度が隣に自動的に書き込まれます。

【管理者】参加希望の締め切り

優先度が高い順に参加欄にプルダウンリストから「参加」を選択して入力し、状態欄にプルダウンリストから「決定」を選択し入力します。

【管理者】活動が終了したら

状態欄にプルダウンリストから「終了」を選択し入力します。

【管理者】活動が中止になったら

状態欄にプルダウンリストから「中止」を選択し入力します。
自動的にすべての参加希望者が「不参加」とされます。

「活動受付・管理システム」の作り方

本システムはスプレッドシート上での関数による処理は一切しておらず、全てGAS上で処理しているので
コードをコピペしてトリガーを設定するだけで処理を回せます。
また、欄の名前や色、入力規定をすべて自動で設定してくれる処理も書きましたので、最初にそれを実行するだけで下準備も完了します。

手順①「新規スプレッドシートを作成する」

↓から新しい空白のスプレッドシートを作成します。
docs.google.com

手順②「シートを追加して名前を変更する」

GAS上でのシートの判別はシートの名前で行っているため、「予定一覧」と「データ」と、間違わないように変更してください。

手順③「GASの編集画面に移動する」

では本題のGAS編集画面に移動していきます。

手順④「コードをコピペする」

続いて以下のコードをすべてコピペします。

const color=[['#76a5af','#a2c4c9'],['#93c47d','#b6d7a8'],['#ffd966','#ffe599'],['#f6b26b','#f9cb9c'],['#e06666','#ea9999'],['#77d9a8','#bfe4ff'],['#c8c8cb','#84919e']];
const arr_day = new Array('日', '月', '火', '水', '木', '金', '土');

/**　初期設定用関数　**/
function initialization() {
  const tags=['活動日','開始時刻','終了時刻','活動場所','希望者1','優先度','参加','希望者2','優先度','参加','希望者3','優先度','参加','希望者4','優先度','参加','希望者5','優先度','参加','状態','結果'];
  const tags2=['学籍番号','氏名','学年','学科','参加回数','参加回数調整','','活動場所','','活動時間'];

  //スプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('予定一覧');
  var sheet_data = spreadsheet.getSheetByName('データ');

  sheet_data.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/1,/*行範囲(1~)*/1,/*列範囲(1~)*/6).setBackground(color[1][0]);
  sheet_data.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/8,/*行範囲(1~)*/1,/*列範囲(1~)*/1).setBackground(color[1][0]);
  sheet_data.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/10,/*行範囲(1~)*/1,/*列範囲(1~)*/1).setBackground(color[1][0]);

  //日付の入力規定・表示形式を設定
  var cell = sheet.getRange(2,1,sheet.getMaxRows()-1,1);
  var rule = SpreadsheetApp.newDataValidation().requireDate().build();
  cell.setDataValidation(rule);
  cell.setNumberFormat('MM/DD')

  //開始・終了時刻の入力規定・表示形式を設定
  var range = sheet_data.getRange(2,10,sheet.getMaxRows()-1,1);
  range.setNumberFormat('@');
  cell = sheet.getRange(2,2,sheet.getMaxRows()-1,2);
  rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
  cell.setDataValidation(rule);
  cell.setNumberFormat('@');

  //参加者の入力規定を設定
  range = sheet_data.getRange(2,2,sheet.getMaxRows()-1,1);
  for(var i=0;i<5;i++){
    cell = sheet.getRange(2,5+3*i,sheet.getMaxRows()-1,1);
    rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
    cell.setDataValidation(rule);
  }

  //活動場所の入力規定を設定
  range = sheet_data.getRange(2,8,sheet.getMaxRows()-1,1);
  cell = sheet.getRange(2,4,sheet.getMaxRows()-1,1);
  rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
  cell.setDataValidation(rule);

  values = ['参加','不参加'];
  for(var i=0;i<5;i++){
    cell = sheet.getRange(2,7+3*i,sheet.getMaxRows()-1,1);
    rule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
    cell.setDataValidation(rule);
  }

  values = ['募集中','決定','終了','中止'];
  cell = sheet.getRange(2,20,sheet.getMaxRows()-1,1);
  rule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
  cell.setDataValidation(rule);

  
  sheet.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/1,/*行範囲(1~)*/1,/*列範囲(1~)*/4).setBackground(color[1][0]);
  for(var i=0;i<5;i++){
    sheet.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/5+i*3,/*行範囲(1~)*/1,/*列範囲(1~)*/1).setBackground(color[i][0]);
    sheet.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/6+i*3,/*行範囲(1~)*/1,/*列範囲(1~)*/2).setBackground(color[i][1]);
  }
  for(var i=0;i<21;i++){
    sheet.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/1+i,/*行範囲(1~)*/1,/*列範囲(1~)*/1).setValue(tags[i]);
    sheet.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/1+i,/*行範囲(1~)*/1,/*列範囲(1~)*/1).setHorizontalAlignment('center');
  }

  for(var i=0;i<10;i++){
    sheet_data.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/1+i,/*行範囲(1~)*/1,/*列範囲(1~)*/1).setValue(tags2[i]);
    sheet_data.getRange(/*始行(1,2,3)*/1,/*始列(A,B,C)*/1+i,/*行範囲(1~)*/1,/*列範囲(1~)*/1).setHorizontalAlignment('center');
  }

  //縦線設定
  var border=[4,7,10,13,16,19];
  for(var i=0;i<6;i++){
    sheet.getRange(1,border[i],sheet.getMaxRows(),1).setBorder(false, false, false, true, false, false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  }

  //横線設定
  sheet.getRange(1,1,1,21).setBorder(false, null, true, null, null, null,'black',SpreadsheetApp.BorderStyle.SOLID_THICK);

}


/**　メイン処理　**/
function process() {
  //スプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('予定一覧');
  var dataLen = sheet.getDataRange().getValues().length;
  var myRange = sheet.getRange(/*始行(1,2,3)*/2,/*始列(A,B,C)*/1,/*行範囲(1~)*/dataLen,/*列範囲(1~)*/21);
  var sheet_s = myRange.getValues();

  var sheet_s_color = myRange.getValues();
  var sheet_s_font_color = myRange.getValues();

  var sheet_data = spreadsheet.getSheetByName('データ');
  var dataLen_data = sheet_data.getDataRange().getValues().length;
  var myRange_data = sheet_data.getRange(/*始行(1,2,3)*/2,/*始列(A,B,C)*/1,/*行範囲(1~)*/dataLen_data,/*列範囲(1~)*/6);
  var sheet_data_s = myRange_data.getValues();

  //色を入れる変数として初期化
  for(var i=0;i<dataLen;i++){
    for(var j=0;j<21;j++){
      sheet_s_color[i][j]=null;
    }
  }
  for(var i=0;i<dataLen;i++){
    for(var j=0;j<21;j++){
      sheet_s_font_color[i][j]='black';
    }
  }


  //セルの色を設定
  for(var i=0;i<dataLen;i++){
    if(sheet_s[i][0]!=''){
      if(sheet_s[i][19]=='募集中'){
        for(var j=0;j<21;j++){
          sheet_s_color[i][j]=color[5][0];
        }
      }else if(sheet_s[i][19]=='決定'){
        for(var j=0;j<21;j++){
          sheet_s_color[i][j]=color[5][1];
        }
      }else if(sheet_s[i][19]=='終了'){
        for(var j=0;j<21;j++){
          sheet_s_color[i][j]=color[6][0];
        }
      }else if(sheet_s[i][19]=='中止'){
        for(var j=0;j<21;j++){
          sheet_s_color[i][j]=color[6][1];
        }
        for(var j=0;j<5;j++){
          sheet_s[i][6+j*3]='不参加';
        }
      }
    }
  }

  //参加回数書き込み処理
  for(var i=0;i<dataLen_data-1;i++){
    var count=0;
    var name = sheet_data_s[i][1];
    if(name!=''){
      for(var j=0;j<dataLen;j++){
        for(var k=0;k<5;k++){
          if(name==sheet_s[j][4+k*3]&&sheet_s[j][6+k*3]=='参加'){
            sheet_s_font_color[j][4+k*3]='white';
            count++;
          }
        }
      }
    }
    if(sheet_data_s[i][5]!=''){
      sheet_data_s[i][4]=count+parseInt(sheet_data_s[i][5]);
    }else{
      sheet_data_s[i][4]=count+0;
    }
  }

  //優先度書き込み処理
  for(var i=0;i<dataLen;i++){
    var nums=[-1,-1,-1,-1,-1];
    for(var j=0;j<5;j++){
      var name = sheet_s[i][4+j*3];
      if(name != ''){
        for(var k=0;k<dataLen_data;k++){
          if(sheet_data_s[k][1]==name){
            nums[j]= sheet_data_s[k][4]+j*0.5;
          }
        }
      }
    }
    for(var l=0;l<5;l++){
      var rank=1;
      for(var n=-l;n<(-l+5);n++){
        if(l!=(l+n)&&nums[l]>nums[l+n]){
          if(nums[l+n]!=-1){
            rank++;
          }
        }
      }
      if(nums[l]!=-1){
        sheet_s[i][5+l*3]=rank+'位';
      }
    }
  }

  //文章書き込み処理
  for(var i=0;i<dataLen;i++){
    if(sheet_s[i][0]!=''){
      var dates=new Date(sheet_s[i][0]);
      var sentece=(dates.getMonth()+1)+'/'+dates.getDate()+'('+arr_day[dates.getDay()]+')は';
      var count=0;
      for(var j=0;j<5;j++){
        var name = sheet_s[i][4+j*3];
        if(name!='' && sheet_s[i][6+j*3]=='参加'){
          sentece+=name+'、';
          count++;
        }
      }
      if(count==0){
          sentece+='参加者が集まりませんでした'
        }
      sheet_s[i][20] = sentece;
    }
  }

  //シートに反映
  myRange.setBackgrounds(sheet_s_color);
  myRange.setFontColors(sheet_s_font_color);
  myRange_data.setValues(sheet_data_s);
  myRange.setValues(sheet_s);
}

/**　メール送信処理　**/
function sendMail(){
  //スプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('予定一覧');
  var dataLen = sheet.getDataRange().getValues().length;
  var myRange = sheet.getRange(/*始行(1,2,3)*/2,/*始列(A,B,C)*/1,/*行範囲(1~)*/dataLen,/*列範囲(1~)*/21);
  var sheet_s = myRange.getValues();

  var nowdate = new Date();

  for(var i = 0; i < dataLen; i++) {
    var status = sheet_s[i][19];
    var day = new Date(sheet_s[i][0]);
    var names = sheet_s[i][20];
    var basyo = sheet_s[i][3];
    var t_stert = sheet_s[i][1];
    var t_end = sheet_s[i][2];

    if(day.getMonth()==nowdate.getMonth() && day.getDate()==nowdate.getDate()){
      //メールの件名
      const subject = '【自動配信】本日活動あり';
      //メールの本文
      const body = '本日'+names+'が'+basyo+'で活動予定です。'+'時間は'+t_stert+'~'+t_end+'です。';
      
      //メールを送信する
      GmailApp.sendEmail('ここに管理者のメールアドレスを入力', subject, body);
    }  
  }
};