🌐

[GAS]班編成表(シフト表)を作成する

2024/03/17に公開

JavaScript

GAS

Google Sheets

tech

本記事の分類

学習ノート

機能

特定の属性が2種類ある
各属性それぞれに対し有意である要素をなるべく均等にグループ分けする。

想定シーン

技術スタッフと事務スタッフを各班になるべく均等に配置したい。
また、事務所管轄市町に在住のスタッフを各班になるべく均等に配置したい。
スキル上級者をなるべく均等に配置したい。
また、男女比をなるべく均等に配置したい。
etc...

本記事では、土木事務所での異常気象待機班編成表作成(上記1.)を想定し説明

仕様

職員の班割当表（シフト表）を作成
使用言語は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 smallGroupCount = 1.0;  //小班数、班内に小班(A班B班等)を設ける場合、班長副班長の重みを小班数で乗算
const sortGroupMembersWithCareerAfterAllMembersAssigned = false; //班編成後班員を役職順に並べ替える場合はtrue

someIndexes = {
  section: 0,
  engineerStaff: 1,
  mentainanceStaff: 2,
  career: 3,
  liveNearOffice: 4,
  newComerStaff: 5,
  engineerStaffWeight: 6,
  liveNearOfficeStaffWeight: 7,
  name: 11
}

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

function defineStateElementsOfGroups(){
  for (let i = 0; i < groupCount; i++){
    stateOfGroups.push({    
      groupNo: i,
      membersCount:0,
      totalEngineerWeight: 0,
      totalLiveNearOfficeWeight: 0,
    });
  }
}

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;
}

const leadersCountInGroup =2;

function assignLeaderAndViceLeader(members){
  for(let count = 0 ; count < leadersCountInGroup; count++){
    let workingArray = [];
    for(let i = 0; i < groupCount; i++){
      const staff = members.shift();
      workingArray.push(staff);
    }
    if (count) {
      workingArray = descendingSortData(workingArray,someIndexes.engineerStaffWeight);
      ascendingSortData(stateOfGroups,'totalEngineerWeight');
    }
    for(let i = 0; i < groupCount; i++){
      const staff = workingArray.shift();
      groups[stateOfGroups[i].groupNo].push(staff);                                                                   ////groups[stateOfGroups[i].groupNo].push(staff);
      stateOfGroups[i].membersCount += 1;
      if (staff[someIndexes.engineerStaffWeight])
      stateOfGroups[i].totalEngineerWeight += staff[someIndexes.engineerStaffWeight]*smallGroupCount;
      if (staff[someIndexes.liveNearOfficeStaffWeight])
      stateOfGroups[i].totalLiveNearOfficeWeight += staff[someIndexes.liveNearOfficeStaffWeight]*smallGroupCount;
    }
  }
  return members;
}

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

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

function countFulfilledGroups(stateOfGroups){
  const fullfilledGroups = stateOfGroups.fileter((group) => {
    return group.membersCount === maxNumberOfPeopleInGroup;
  })
  return fullfilledGroups.length;
}

let allEngineerExtracted = false;
let allEngineerAssigned = false;

function assignGroupMembers(members){                                                                       
  for(let count = 0 ; count < maxNumberOfPeopleInGroup - leadersCountInGroup; count++){
    if(!allEngineerAssigned){
      ascendingSortData(stateOfGroups,'totalEngineerWeight');
    }
    if(allEngineerAssigned){
      ascendingSortData(stateOfGroups,'totalLiveNearOfficeWeight');
    }      
    let workingArray = [];

    for(let i = 0; i < groupCount; i++){
      if (!allEngineerExtracted && !members[0][someIndexes.engineerStaff]){
        members = descendingSortData(members,someIndexes.liveNearOfficeStaffWeight);
        allEngineerExtracted = true;
      }
      member = members.shift();
      workingArray.push(member);
    }
    for(let i = 0; i < groupCount; i++){
      if(stateOfGroups[i].membersCount === maxNumberOfPeopleInGroup) return;
      if(!allEngineerAssigned && !workingArray[0][someIndexes.engineerStaff]){
        const extractStateOfGroups = stateOfGroups.slice(i);
        const sortedExtractStateOfGroups = ascendingSortData(extractStateOfGroups,'totalLiveNearOfficeWeight');
        stateOfGroups.splice(i, extractStateOfGroups.length, ...sortedExtractStateOfGroups);
        allEngineerAssigned = true;
      }
      const member = workingArray.shift();
      if(!member)return;
      groups[stateOfGroups[i].groupNo].push(member);
      stateOfGroups[i].membersCount += 1;
      if(member[someIndexes.engineerStaffWeight]) stateOfGroups[i].totalEngineerWeight += member[someIndexes.engineerStaffWeight];
      if(member[someIndexes.liveNearOfficeStaffWeight]) stateOfGroups[i].totalLiveNearOfficeWeight += member[someIndexes.liveNearOfficeStaffWeight];
    }

  }
  return groups;
}

function assignNewcomers(newcomers){
    newcomers.forEach((newcomer) => {
    const newcomerSection = newcomer[someIndexes.section];
    const groupIndex = groups.findIndex((group) =>{
      return group[0][someIndexes.section] === newcomerSection
    } )
    const stateOfGroupsIndex = stateOfGroups.findIndex((stateOfGropup) => {
      return stateOfGropup.groupNo === groupIndex;
    })
    groups[groupIndex].push(newcomer);
  })
}

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);
let newcomers  = extractNewcomers(weightAddedMembers);
let sortedMembersWithCareer = ascendingSortData(weightAddedMembers,someIndexes.career);
let remainingMembers = assignLeaderAndViceLeader(sortedMembersWithCareer);
const stateOfGroupsAddedNewcomerWeight = addNewcomerWeightsToStateOfGropups(newcomers);
let sortedRemainingMembersWithEngineerWeight = descendingSortData(remainingMembers,someIndexes.engineerStaffWeight);
assignGroupMembers(sortedRemainingMembersWithEngineerWeight);
assignNewcomers(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したところ、たたき台としては十分な成果を出すことができましたので、同じような境遇にある方の役に立てばと思い記事にして残すことにしました。

今後

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