🌐

[GAS]異常気象時待機班編成表を作成する

2024/04/29に公開

JavaScript

GAS

tech

本記事の分類

学習ノート

機能

異常気象時待機班編成表（たたき台）を作成する。
前回のcodeから機能を異常気象時待機班編成表の作成に特化。
https://zenn.dev/hasenori/articles/2e4a1b21fe08ab

想定シーン

土木事務所等の異常気象時待機班編成表の作成。

仕様

職員の班割当表（シフト表）を作成
使用言語はGAS(≓Java Script)
DatabaseはGoogle Sheets
班長・副班長は役職が上位の職員を割当
同一班内に同じ課の職員を２人以上割当てない
各班になるべく均等に技術職員を割当
各班になるべく均等に事務所近隣の市町在住の職員を割当

Database

Google Sheetsに入力するデータの構成は以下のとおり
実際にcodeで参照するdataは、”所属ID”列から”新採FLAG”列及び”氏名”列の入力値
入力dataの説明
職員一覧表等のデータを活用
"所属ID"：名簿の掲載順に合わせ、総務課staffは0、用地課staffは1,･･･と昇順で採番
”技術職FLAG”：技術staffに1を採番
”保全職FLAG”：保全staffに1を採番
”役職ID”：名簿の掲載順に合わせ、課長は0、副主幹は1,･･･と昇順で採番
”保全職FLAG”：保全staffに1を採番
”職場近隣在住FLAG”：事務所管内市町在住のstaffに1を採番
”新採FLAG”：新年度採用のstaffに1を採番

所属ID	技術職FLAG	保全職FLAG	役職ＩＤ	職場近隣在住FLAG	所属	所属略称	役職	氏名
0			0	1	総務課	(総務)	総務課長	伊藤優花
0			2	1	総務課	(総務)	係長	鈴木二郎



8	1	1	6	1	保全第二課	(保2)	技師	佐藤花子
8	1	1	6	1	保全第二課	(保2)	技師	田中一郎

Code

以下のとおり

createStaffTable

const groupCount = 11;  //作成班数、必要に応じ数字を変更
const sectionCount = 11; //課の数
const leadersCountInGroup =2; //班長、副班長配置の場合２
const smallGroupCount = 2.0;  //小班数、班内に小班(A班B班等)を設ける場合、班長副班長の重みを小班数で乗算
const assignSameSectionMembersInSameGroup = false; //同じ班内に同じ課の職員配置可の場合はture
const assignSectionChiefAndEngineerToLeader = true; //課長と技術職を班長にする場合はtrue、役職順で（建築職GLを）班長にする場合は false
const sortGroupMembersWithCareerAfterAllMembersAssigned = false; //班編成後班員を役職順に並べ替える場合はtrue

const someIndexes = {
  section: 0,
  engineerStaff: 1,
  mentainanceStaff: 2,
  career: 3,
  liveNearOffice: 4,
  newComerStaff: 5,
  //isSectionChiefAssignedNewcomer: 6,
  engineerStaffWeight: 6,
  liveNearOfficeStaffWeight: 7,
  id: 8,
  name: 12,
}

function fetchMembers() {
  const range = SpreadsheetApp.getActiveRange();
  let members = range.getValues();
  return members;
}

function defineStateElementsOfGroups() {
  for (let i = 0; i < groupCount; i++) {
    let groupState = {    
      groupNo: i,
      membersCount: 0,
      engineersCount: 0,
      isAssignedNewcomer: false,
      newcomerSectionNumber: 99,
      totalEngineerWeight: 0,
      totalLiveNearOfficeWeight: 0
    };
    for (let j = 0; j < sectionCount; j++) {
      groupState[`isMemberInSection${j}`] = false; 
    }
    stateOfGroups.push(groupState);
  }
}

function makeGroupContainers(){
  for(i = 0; i < groupCount; i++){
    let group = [];
    groups.push(group);
  }
}

const quarter = 0.25;
const half = 0.5;
const one = 1.0; 

function addEngineerStaffWeight(members){
  members.forEach((member)=>{
    if(member[someIndexes.engineerStaff] && member[someIndexes.newComerStaff]) return member[someIndexes.engineerStaffWeight] = quarter;
    if(member[someIndexes.mentainanceStaff] ) return member[someIndexes.engineerStaffWeight] = one;
    if(member[someIndexes.engineerStaff] ) return member[someIndexes.engineerStaffWeight] = half;
  })
  return members;
}

function addLiveNearOfficeWeight(members){
  members.forEach((member)=>{
    if(member[someIndexes.liveNearOffice]) return member[someIndexes.liveNearOfficeStaffWeight] = one;
  })
  return members;
}

function addWeight(members){
  let addedWeightMembers = addEngineerStaffWeight(members);
  addedWeightMembers = addLiveNearOfficeWeight(addedWeightMembers);
  return addedWeightMembers;
}

function ascendingSortData(data,index){
  data.sort((a,b)=>{
    return a[index] - b[index];
  })
  return data;
}

function descendingSortData(data,index){
  data.sort((a,b)=>{
    return b[index] - a[index];
  })
  return data;
}


let allSectionChiefExtracted = false;
let allSectionChiefAssigned = false;
const sectionChiefCareerIndex = 0;  //課長職の役職IDは0

function assignLeader(members){
    let workingArray = [];
    for(let i = 0; i < groupCount; i++){
      if(assignSectionChiefAndEngineerToLeader){
        //課長が全員選出されていなくて、次のメンバーが課長でなければ、エンジニアウェイトで並び替え、課長全て選出をtrue
        if (!allSectionChiefExtracted && members[0][someIndexes.career] !== sectionChiefCareerIndex){
          members = descendingSortData(members,someIndexes.engineerStaffWeight);
          allSectionChiefExtracted = true;
        }
      }
      const member = members.shift();
      workingArray.push(member);
    }
    for(let i = 0; i < groupCount; i++){
      const member = workingArray.shift();
      groups[stateOfGroups[i].groupNo].push(member);
      stateOfGroups[i].membersCount += 1;
      if(member[someIndexes.engineerStaff]) stateOfGroups[i].engineersCount += 1;
      if(member[someIndexes.engineerStaffWeight])
      stateOfGroups[i].totalEngineerWeight += member[someIndexes.engineerStaffWeight]*smallGroupCount;
      if(member[someIndexes.liveNearOfficeStaffWeight])
      stateOfGroups[i].totalLiveNearOfficeWeight += member[someIndexes.liveNearOfficeStaffWeight]*smallGroupCount;
      stateOfGroups[i][`isMemberInSection${member[someIndexes.section]}`] = true;
    }
  return members;
}

function countNumbersInStateOfGroups(stateOfGroupKey){
  let total =0;
  stateOfGroups.forEach((stateOfGroup) => {
    total += stateOfGroup[stateOfGroupKey];
  })
  return total;
}

function countRemainingMembersWithKey(members,memberKey){
  let total = 0;
  members.forEach((member) => {
    if(member[memberKey]) total += member[memberKey];
  })
  return total;
}

function assignViceLeader(members,requiredMembersCount){
  let workingArray = [];
  workingArray = members.splice(0,requiredMembersCount); 
  for(let i = 0; i < requiredMembersCount; i++){
    const member = workingArray.shift();
    const stateOfGroupIndex = stateOfGroups.findIndex((stateOfGroup) => {
      if(!assignSameSectionMembersInSameGroup){
        return !stateOfGroup[`isMemberInSection${member[someIndexes.section]}`] && (stateOfGroup.membersCount !== leadersCountInGroup) && !(!stateOfGroup.engineersCount && !member[someIndexes.engineerStaff]);
      }
      if(assignSameSectionMembersInSameGroup)
        return (stateOfGroup.membersCount !== leadersCountInGroup) && !(!stateOfGroup.engineersCount && !member[someIndexes.engineerStaff]);
    })
      groups[stateOfGroups[stateOfGroupIndex].groupNo].push(member);
      stateOfGroups[stateOfGroupIndex].membersCount += 1;
      if(member[someIndexes.engineerStaff]) stateOfGroups[stateOfGroupIndex].engineersCount += 1;
      //if(member[someIndexes.isSectionChiefAssignedNewcomer]) {
      //  stateOfGroups[stateOfGroupIndex].isAssignedNewcomer = true;
      //  stateOfGroups[stateOfGroupIndex].newcomerSectionNumber = member[someIndexes.section];
      //}
      if (member[someIndexes.engineerStaffWeight])     stateOfGroups[stateOfGroupIndex].totalEngineerWeight += member[someIndexes.engineerStaffWeight]*smallGroupCount;
      if (member[someIndexes.liveNearOfficeStaffWeight])
      stateOfGroups[stateOfGroupIndex].totalLiveNearOfficeWeight += member[someIndexes.liveNearOfficeStaffWeight]*smallGroupCount;
      stateOfGroups[stateOfGroupIndex][`isMemberInSection${member[someIndexes.section]}`] = true;
  }
  return members; //残りのメンバーをリターン
}

function assignSeniorStaffToViceLeader(members,count){
  let workingArray = [];
  workingArray = members.splice(0,count); 
  for(let i = 0; i < count; i++){
    const member = workingArray.shift();
    const stateOfGroupIndex = stateOfGroups.findIndex((stateOfGroup) => {
      if(!assignSameSectionMembersInSameGroup){
        return !stateOfGroup[`isMemberInSection${member[someIndexes.section]}`] && (stateOfGroup.membersCount !== leadersCountInGroup) ;
      }
      if(assignSameSectionMembersInSameGroup)
        return (stateOfGroup.membersCount !== leadersCountInGroup);
    })
      groups[stateOfGroups[stateOfGroupIndex].groupNo].push(member);
      stateOfGroups[stateOfGroupIndex].membersCount += 1;
      if (member[someIndexes.liveNearOfficeStaffWeight])
      stateOfGroups[stateOfGroupIndex].totalLiveNearOfficeWeight += member[someIndexes.liveNearOfficeStaffWeight]*smallGroupCount;
      stateOfGroups[stateOfGroupIndex][`isMemberInSection${member[someIndexes.section]}`] = true;
  }
}



function extractMembers(members,membersKey){
  const membersCount = members.filter(member => member[membersKey]).length;
  return descendingSortData(members,membersKey).splice(0,membersCount);
}

function extractNewcomers(members){
  const newcomersCount = members.filter(member => member[someIndexes.newComerStaff]).length;
  return descendingSortData(members,someIndexes.newComerStaff).splice(0,newcomersCount);
}

function extractEngineers(members){
  const engineersCount = members.filter(member => member[someIndexes.engineerStaff]).length;
  return descendingSortData(members,someIndexes.engineerStaff).splice(0,engineersCount);
}

function addNewcomerWeightsToStateOfGropups(newcomers){
  newcomers.forEach((newcomer) => {
    // const newcomerSection = newcomer[someIndexes.section];
    // const groupIndex = groups.findIndex((group) =>{
    //   return group[0][someIndexes.section] === newcomerSection || group[1][someIndexes.section] === newcomerSection;
    // } )
    // const stateOfGroupIndex = stateOfGroups.findIndex((stateOfGropup) => {
    //   return stateOfGropup.groupNo === groupIndex;
    // })
    // stateOfGroups[stateOfGroupIndex].membersCount += 1;
      if (newcomer[someIndexes.engineerStaffWeight])
      stateOfGroups[stateOfGroupIndex].totalEngineerWeight += newcomer[someIndexes.engineerStaffWeight];
      if (newcomer[someIndexes.liveNearOfficeStaffWeight])
      stateOfGroups[stateOfGroupIndex].totalLiveNearOfficeWeight += newcomer[someIndexes.liveNearOfficeStaffWeight];
  })
}

function findIndexOfGroupWithMaxMembersCount(stateOfGroups) {
  const maxIndex = stateOfGroups.reduce((minIndex, currentGroup, currentIndex) => {
    return currentGroup.membersCount < stateOfGroups[minIndex].membersCount ? currentIndex : minIndex;
  }, 0); // 0 を初期値として使うことで、配列の最初の要素から比較を始めます。
  return maxIndex;
}


let allEngineerExtracted = false;
let allEngineerAssigned = false;
let allStaffLiveNearOfficeEctracted =false;
let allStaffLiveNearOfficeAssigned =false;
let assignRoundsIncludeLeaders;

function assignNotSuitableMember(member){
  ascendingSortData(stateOfGroups,'membersCount');
  groups[stateOfGroups[0].groupNo].push(member);
  stateOfGroups[0].membersCount += 1;
  if(member[someIndexes.engineerStaff]) stateOfGroups[0].engineersCount += 1;
  if (member[someIndexes.engineerStaffWeight])
  stateOfGroups[0].totalEngineerWeight += member[someIndexes.engineerStaffWeight];
  if (member[someIndexes.liveNearOfficeStaffWeight])
  stateOfGroups[0].totalLiveNearOfficeWeight += member[someIndexes.liveNearOfficeStaffWeight];
  stateOfGroups[0][`isMemberInSection${member[someIndexes.section]}`] = true;
}

function assignMembers(members){     //engineers,someIndexes.engineerStaffWeight,'totalEngineerWeight'
  const totalCount = countNumbersInStateOfGroups('membersCount');
  const membersLength = members.length;        
  for(let count = 0 ; count < membersLength; count++){
    assignRoundsIncludeLeaders = Math.floor((count + totalCount) / groupCount) + 1;
      const member = members.shift();
      const stateOfGroupIndex = stateOfGroups.findIndex((stateOfGroup) => {
        if(!assignSameSectionMembersInSameGroup) {
          return !stateOfGroup[`isMemberInSection${member[someIndexes.section]}`] && (stateOfGroup.membersCount < assignRoundsIncludeLeaders);
        }
        if(assignSameSectionMembersInSameGroup) 
          return stateOfGroup.membersCount !== assignRoundsIncludeLeaders;
      })
    if (stateOfGroupIndex === -1) {
      members.push(member); // Add the member back to the array
      assignNotSuitableMember(member);
      continue; // Continue to the next member
    }
    groups[stateOfGroups[stateOfGroupIndex].groupNo].push(member);
    stateOfGroups[stateOfGroupIndex].membersCount += 1;
    if(member[someIndexes.engineerStaff]) stateOfGroups[stateOfGroupIndex].engineersCount += 1;
    if (member[someIndexes.engineerStaffWeight])
    stateOfGroups[stateOfGroupIndex].totalEngineerWeight += member[someIndexes.engineerStaffWeight];
    if (member[someIndexes.liveNearOfficeStaffWeight])
    stateOfGroups[stateOfGroupIndex].totalLiveNearOfficeWeight += member[someIndexes.liveNearOfficeStaffWeight];
    stateOfGroups[stateOfGroupIndex][`isMemberInSection${member[someIndexes.section]}`] = true;
  }
}


const extractNames = groups => groups.map(group => group.map(member => member[someIndexes.name]));
const extractEngineerWeights = groups => groups.map(group => group.map(member => member[someIndexes.engineerStaffWeight]));
const extractLiveNearOfficeWeights = groups => groups.map(group => group.map(member => member[someIndexes.liveNearOfficeStaffWeight]));
const extractNewcomerFlags = groups => groups.map(group => group.map(member => member[someIndexes.newComerStaff]));

//
//実行
//

const fetchedMembers = fetchMembers();
let members = fetchedMembers;
const membersCount = members.length;
const maxNumberOfPeopleInGroup = Math.ceil(membersCount/groupCount); 
let stateOfGroups  = [];
defineStateElementsOfGroups();
let groups = [];
makeGroupContainers();
let weightAddedMembers = addWeight(members);


//newcomers
let newcomers  = extractMembers(weightAddedMembers,someIndexes.newComerStaff);
// addFlagToSectionChiefAssignedNewcomer(weightAddedMembers,newcomers);

//leader
let sortedMembersWithCareer = ascendingSortData(weightAddedMembers,someIndexes.career);
let remainingMembers = assignLeader(sortedMembersWithCareer); // 課長と技術職をリーダー選出

//viceLeader 
//技術職がいないグループに技術職をあてる
const numberOfEngineersShort = groupCount - countNumbersInStateOfGroups('engineersCount');
let engineers;
let remainingEngineers;
if(numberOfEngineersShort){
  engineers = extractMembers(remainingMembers,someIndexes.engineerStaff);
  ascendingSortData(engineers,someIndexes.career);
  ascendingSortData(stateOfGroups,'totalEngineerWeight');
  const notAssignedMembers = assignViceLeader(engineers,numberOfEngineersShort);
  if(notAssignedMembers) remainingMembers.splice(remainingMembers.length,0,...notAssignedMembers);
}

//まだ副班長がわりあてられていないところに、職位が上の職員をあてる
const numberOfMembersShort = groupCount*leadersCountInGroup - countNumbersInStateOfGroups('membersCount');
if(numberOfMembersShort){
  ascendingSortData(remainingMembers,someIndexes.career);
  ascendingSortData(stateOfGroups,'membersCount');
  assignSeniorStaffToViceLeader(remainingMembers,numberOfMembersShort);
}

// engineer
engineers = extractMembers(remainingMembers,someIndexes.engineerStaff);
descendingSortData(engineers,someIndexes.engineerStaffWeight);
ascendingSortData(stateOfGroups,'totalEngineerWeight');
assignMembers(engineers);

//membersLiveNearOffice
let membersLiveNearOffice = extractMembers(remainingMembers,someIndexes.liveNearOffice);
descendingSortData(membersLiveNearOffice,someIndexes.liveNearOffice);
ascendingSortData(stateOfGroups,'liveNearOfficeWeight');
assignMembers(membersLiveNearOffice);

//rmainingMembers
descendingSortData(remainingMembers,someIndexes.career);
ascendingSortData(stateOfGroups,'membersCount');
assignMembers(remainingMembers);
assignMembers(newcomers);
if(sortGroupMembersWithCareerAfterAllMembersAssigned) {
    groups.forEach(group =>{
    ascendingSortData(group,someIndexes.career);
  })
}
let names = extractNames(groups);
let engineerStaffWeightArray = extractEngineerWeights(groups);
let liveNearOfficeWeights = extractLiveNearOfficeWeights(groups);
let newcomerFlags = extractNewcomerFlags(groups);
let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let resultSheet = spreadsheet.getSheetByName('result');
if (resultSheet) resultSheet.clear();
if (!resultSheet) {
  resultSheet = spreadsheet.insertSheet('result');
}

function addTotalWeights(){
  resultSheet.getRange(1, maxNumberOfPeopleInGroup + 1).setValue('重み付技術職合計');
  resultSheet.getRange(1, maxNumberOfPeopleInGroup + 2).setValue('近隣職員合計');
  for(let i = 0; i < groupCount; i++){
      resultSheet.getRange(i + 2, 1, 1, names[i].length).setValues([names[i]]);
      resultSheet.getRange(stateOfGroups[i].groupNo + 2, maxNumberOfPeopleInGroup + 1, 1, 1).setValues([[stateOfGroups[i].totalEngineerWeight]]);
      resultSheet.getRange(stateOfGroups[i].groupNo + 2, maxNumberOfPeopleInGroup + 2, 1, 1).setValues([[stateOfGroups[i].totalLiveNearOfficeWeight]]);
  }
}

function addColorsAndFontWeight() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('result'); 
  for (let i = 0; i < groups.length; i++) {
    for (let j = 0; j < groups[i].length; j++) {
      const cell = sheet.getRange(i + 2, j + 1);
      let weight = engineerStaffWeightArray[i][j];
      let fontColor = "#000000"; // 黒(デフォルト）
      if (weight === quarter) fontColor = "#FF0000"; // 赤
      if (weight === half) fontColor = "#0000FF"; // 青
      if (weight === one) fontColor = "#008000"; // 緑
      cell.setFontColor(fontColor);
      if (liveNearOfficeWeights[i][j] === one) cell.setBackground("#FFFF00"); // 黄色
      if (newcomerFlags[i][j]) cell.setFontWeight('bold'); // 太字
    }
  }
}

addTotalWeights();
addColorsAndFontWeight();

使い方

1.Google Sheets を開く
（以下はGoogle Sheets等の解説pageのURL、
　理解しやすかったため参考までにご紹介させていただきます。）

URL 1-1

URL 1-2

URL 1-3

2.”拡張機能”から”Apps Script”[図 2-1]を選択し、上記のcodeをコピペ[図 2-2]し保存

[図 2-1]

[図 2-2]

3.dataを入力し、data入力範囲を選択

[図 3-1]

4."Apps Script"のtab[図 4-1]をクリックしcodeの編集画面を表示し、▷実行[図 4-2]をクリック
”このアプリはGoogleで確認されていません”と出た場合は以下のpageを参照してください。

[図 4-1]

[図 4-2]

5.自動作成された"result"シート[図 5-1]を選択し、作成された編成表[図 5-2]を確認

[図 5-1]

[図 5-2]

作成された表の解説

font緑色：保全staff
font赤色：新年度採用の技術staff
font青色：上記を除く技術staff
font太字：新年度採用のstaff
cell黄色：事務所管内市町在住のstaff
重み付技術職合計：保全staffは1.0、新年度採用の技術staffは0.25、その他の技術staffは0.5と重み付したときの、各班の重みの合計値
近隣職員合計：管内市町在住staffは1.0と重み付したときの、各班の重みの合計値

表の修正

表に表示の合計値を参考に手動で各班間でstaffの入替

code概説（なるべく簡単に）

1.dataと箱の用意
1-1. staffのdataを入れる箱を用意する　
1-2. groupの箱を班数分用意する　
1-3. group内の技術staffの人数data等を入れる箱を班数分用意する　
1-4. 1-1の箱にstaffのdataを取込む

2.班長の配置
2-1. 1-1の箱内のstaffを役職上位から順に並び替える　
2-2. 1-1の箱から1-2の箱に各班一人ずつ入れる　
2-3. 1-3の箱内のdataを更新する。

3.技術staffの配置（副班長含む）
3-1. 1-3の箱を班内の技術pt合計値が少ない方から順に並び替える　
3-2. 1-1の箱内のstaffを技術pt上位から順に並び替える
3-3. 1-3の箱の情報を元に技術pt下位の班から2-2を実行
3-4. 2-3を実行

4.事務staffの配置
4-1. 技術ptを住所ptに読み替え、3-1～3-4を実行

code説明

2-1. 1-1の箱内のstaffを役職上位から順に並び替える　
3-1. 1-3の箱を班内の技術pt合計値が少ない方から順に並び替える　
3-2. 1-1の箱内のstaffを技術pt上位から順に並び替える
これら並び替えに配列のsort methodを使っています

2-2. 1-1の箱から1-2の箱に各班一人ずつ入れる　
この取出しと格納に配列のshift method と push method を使っています。

後書き

職場の上司から待機班編成表を作って欲しいと依頼を受けましたが、単純に名簿順に並べるわけにもいかず、加えて人数が多いため作表に時間がかかるとのことでした。
これはGASで解決できると思いcodingしたところ、たたき台としては十分な成果を出すことができましたので、同じような境遇にある方の役に立てばと思い記事にして残すことにしました。

今後

もりけん塾で学習を積み重ね、本記事のブラッシュアップと別記事の投稿をしたいと思います。