SmartHR→バクラクのユーザー更新CSVを作成

はじめに

https://zenn.dev/nag8/articles/88be8d23693ca4
の機能拡張です。

googleグループやkintoneには対応できるようにしたので、続いてバクラクも変わるようにする。
なお、バクラクはAPIが現在なく、そのため

1. 当ツールで部署や権限変更要CSVを作成する
2. CSVをバクラクにインポートする

で更新する。

main.gs

function onOpen(){
  SpreadsheetApp
    .getUi()
    .createMenu('GAS')
    .addItem('バクラクシート更新', 'refreshBakurakuSheet')
    .addToUi();
}

// SmartHRからCrewシートを更新する
function refreshCrewSheet(){
  const sheetCrewList = getCrewListFromSheet();
  const employmentTypeDic = getEmploymentTypeDic();
  const kintoneMemberList = getMemberList();

  const outList = getCrewList().map(crew => {
    const sheetCrew = sheetCrewList.find(sc => crew.isSame(sc));
    sheetCrew ? crew.setAuthorityLevel(sheetCrew.getAuthorityLevel()) : crew.generateAuthorityLevel(employmentTypeDic);
    crew.findSetEmail(kintoneMemberList);
    return crew.getOutList();
  });

  refreshSheet(SHEET.crew.name, outList, SHEET.crew.column.id, SHEET.crew.row.data);

  refreshTeamSheet();
}

// SmartHRからTeamシートを更新する(今何人いるかなど)
function refreshTeamSheet(){
  const crewList = getCrewListFromSheet().filter(c => c.isSameStatus(CREW.status.employed) && c.haveEmail());
  const outsourcerList = getOutsourcerList();
  const teamList = getTeamListSetCrew(crewList, outsourcerList);

  setList(
    SHEET.team,
    SHEET.team.row.data,
    SHEET.team.column.memberNum,
    teamList.map(t => t.getOutList())
  );
}

// アラート。チームが0人でないか、不要なgoogleグループに入っていないか、変なCrew情報でないかを確認する
function alert(){
  const crewList = getCrewListFromSheet().filter(c => c.isSameStatus(CREW.status.employed) && c.haveEmail() && !c.isBeforeJoin());
  const outsourcerList = getOutsourcerList();
  const teamList = getTeamListSetCrew(crewList, outsourcerList);

  const alertList = [].concat(
    getAlertListTeam(teamList),
    getAlertListGoogleGroup(crewList, teamList),
    getAlertListCrew(crewList, teamList)
  );

  if(alertList.length){
    slackChannel(
      <token>,
      alertList.join('\n')
    );
  }
}

function getAlertListGoogleGroup(crewList, teamList){
  const googleGroupList = teamList.reduce((googleGroupList, team) => {
    return googleGroupList.concat(team.getGoogleGroup());
  }, []).filter(googleGroup => googleGroup !== '');

  return Array.from(new Set(googleGroupList)).reduce((alertList, googleGroup) => {
    const emailList = manageGoogleGroup.getGoogleGroupEmailList(googleGroup);

    const alertEmailList = crewList.reduce((alertEmailList, crew) => {
      const joinTeamList = teamList.filter(t => t.isCrew(crew));
      if(joinTeamList.some(t => t.isSameGoogleGroup(googleGroup))){
        if(!emailList.includes(crew.getEmail())) alertEmailList.push(`+ ${crew.getEmail()}`);
      }else{
        if(emailList.includes(crew.getEmail())) alertEmailList.push(`- ${crew.getEmail()}`);
      }
      return alertEmailList;
    }, []);

    if(alertEmailList.length) alertList.push(`${googleGroup} \n${alertEmailList.join('\n')}\n`);
    return alertList;
  }, []);
}


function getAlertListTeam(teamList){
  return teamList.reduce((alertTextList, team) => {
    if(team.isNoCrew()) alertTextList.push(`${team.getName()} のメンバーが0人\n`);
    return alertTextList;
  }, []);
}

function getAlertListCrew(crewList, teamList){

  let slackAuthorityDic = {};
  slackAuthorityDic[MEMBER.status.member.owner.primary] = 5;
  slackAuthorityDic[MEMBER.status.member.owner.normal] = 4;
  slackAuthorityDic[MEMBER.status.member.admin] = 3;
  slackAuthorityDic[MEMBER.status.member.normal] = 2;
  slackAuthorityDic[MEMBER.status.guest.multi] = 1;
  

  const slackMemberList = getMemberListFromSheet();
  
  return crewList.reduce((alertList, crew) => {
    const joinTeamList = teamList.filter(team => team.isCrew(crew));
    const slackMember = find(slackMember => slackMember.isSameEmail(crew.getEmail()));
    
    if(!joinTeamList.length || slackMember === undefined) return alertList;
    const maxSlackAuthority = joinTeamList.reduce((authorityIndex, team) => {
      if(authorityIndex < slackAuthorityDic[team.getSlackAuthority()]) authorityIndex = slackAuthorityDic[team.getSlackAuthority()];
      return authorityIndex;
    }, 0);
      
    if(slackAuthorityDic[slackMember.getStatus()] !== maxSlackAuthority){
      alertList.push(`slack権限相違: ${crew.getName()} slack: ${slackMember.getStatus()} sheet: ${Object.keys(slackAuthorityDic).find(key => slackAuthorityDic[key] === maxSlackAuthority)}`);
    }

    const errorMessage = crew.getErrorMessage();
    if(errorMessage) alertList.push(errorMessage);

    return alertList;
  }, []);
}

function updateKintoneGroup(){
  const crewList = getCrewListFromSheet().filter(c => c.isSameStatus(CREW.status.employed) && c.haveEmail());
  const outsourcerList = getOutsourcerList();
  const teamList = getTeamListSetCrew(crewList, outsourcerList);

  const kintoneGroupList = teamList.reduce((kintoneGroupList, team) => {
    const kintoneGroup = team.getKintoneGroup();
    if(kintoneGroup === '') return kintoneGroupList;
    const group = kintoneGroupList.find(group => group.code === kintoneGroup);
    if(group !== undefined){
      group.member = group.member.concat(team.getCrewMailList());
    }else{
      kintoneGroupList.push({
        code: kintoneGroup,
        member: team.getCrewMailList(),
      });
    }
    return kintoneGroupList;
  }, []);

  const kintoneUserCodeList = getUserCodeList();

  kintoneGroupList.forEach(kg => {
    kg.member = Array.from(new Set(kg.member)).filter(mail => mail.length && kintoneUserCodeList.includes(mail));
    refreshGroupUsers(kg.code, kg.member);
  });
}


function refreshKintoneGroupList(){
  refreshGroupUsers(kg.code, kg.member);
}

function refreshBakurakuSheet(){
  const bakurakuCrewList = getBakurakuCrewList();
  const crewList = getCrewListFromSheet().filter(crew => crew.isSameStatus(CREW.status.employed) && crew.haveEmail());
  const outsourcerList = getOutsourcerList();
  const teamList = getTeamListSetCrew(crewList, outsourcerList);
  // 基本は部署名をCSVに反映するが、同名の部署の場合があるのでチームコードへの換算を行う。
  // 以下リストはbeforeに部署名、afterにチームコードを持つ
  const bakurakuManualTeamList = getBakurakuManualTeamList();

  let errorMessage = {
    noIdNum: 0,
  };
  const outList = crewList.map(crew => {
    
    const bakurakuCrew = new BakurakuCrew();
    bakurakuCrew.setDataFromSmartHRCrew(crew, teamList, bakurakuManualTeamList);
    bakurakuCrew.findSetId(bakurakuCrewList);
    if(bakurakuCrew.isNoId()) errorMessage.noIdNum++;
    return bakurakuCrew.getOutList();
  });

  // 全データidがある想定だが、ない場合の検知目的
  Browser.msgBox(`idなし: ${errorMessage.noIdNum}`);

  refreshSheet(SHEET.bakurakuCrewOut.name, outList, SHEET.bakurakuCrewOut.column.id, SHEET.bakurakuCrewOut.row.data);
}

Team.gs

class Team{
  constructor(row){
    this.name = row[SHEET.team.column.name - 1];
    this.authorityLevel = row[SHEET.team.column.authorityLevel - 1];
    this.department = row[SHEET.team.column.department - 1];
    this.position = row[SHEET.team.column.position - 1];
    this.kintoneGroup = row[SHEET.team.column.kintoneGroup - 1];
    this.googleGroup = row[SHEET.team.column.googleGroup - 1];
    this.slack = {
      authority: row[SHEET.team.column.slack.authority - 1],
      usergroupId: row[SHEET.team.column.slack.usergroupId - 1],
      usergroupMemberList: undefined,
    };
    this.bakurakuAuthority = {
      user: row[SHEET.team.column.bakurakuAuthority.user - 1],
      expenses: row[SHEET.team.column.bakurakuAuthority.expenses - 1],
      expensesApprove: row[SHEET.team.column.bakurakuAuthority.expensesApprove - 1],
      invoice: row[SHEET.team.column.bakurakuAuthority.invoice - 1],
      credit: row[SHEET.team.column.bakurakuAuthority.credit - 1],
      team: row[SHEET.team.column.bakurakuAuthority.team - 1],
    };
    this.crewMailList = [];
  }

  isSameAuthorityLevel(authorityLevel){
    if(this.authorityLevel === '') return true;
    return this.authorityLevel === authorityLevel;
  }

  isMatchDepartment(department){
    return this.department === '' || department?.includes(this.department);
  }

  isMatchPosition(position){
    return this.position === '' || position?.includes(this.position);
  }

  isCrew(crew){
    return this.crewMailList.includes(crew.getEmail());
  }

  isNoCrew(){
    return this.crewMailList.length === 0;
  }

  isSameName(teamName){
    return this.name === teamName;
  }

  isSameSlackAuthority(slackAuthority){
    return slackAuthority === this.slack.authority;
  }

  isSameGoogleGroup(googleGroup){
    return this.googleGroup === googleGroup;
  }

  findSetCrewMailList(crewList){
    this.crewMailList = crewList.reduce((mailList, crew) => {
      if(crew.isSameteam(this)) mailList.push(crew.getEmail());
      return mailList;
    }, []);
  }

  findSetCrewFromOutsourcerList(outsourcerList){
    outsourcerList.forEach(outsourcer => {
      if(this.isSameName(outsourcer.teamName)) this.crewMailList.push(outsourcer.email);
    });
  }

  setCrewMailList(mailList){
    this.crewMailList = mailList;
  }

  getCrewMailList(){
    return this.crewMailList;
  }

  getName(){
    return this.name;
  }

  getSlackAuthority(){
    return this.slack.authority;
  }

  getKintoneGroup(){
    return this.kintoneGroup;
  }

  getGoogleGroup(){
    return this.googleGroup;
  }

  getUserGroupMemberIdList(){
    if(this.slack.usergroupId === '') return [];
    if(this.slack.usergroupMemberList === undefined){
      this.slack.usergroupMemberList = getUserGroupMemberIdList(this.slack.usergroupId);
    }
    return this.slack.usergroupMemberList;
  }

  getBakurakuAuthorityUser(){
    return this.bakurakuAuthority.user;
  }

  getBakurakuAuthorityExpenses(){
    return this.bakurakuAuthority.expenses;
  }

  getBakurakuAuthorityExpensesApprove(){
    return this.bakurakuAuthority.expensesApprove;
  }

  getBakurakuAuthorityInvoice(){
    return this.bakurakuAuthority.invoice;
  }

  getBakurakuAuthorityCredit(){
    return this.bakurakuAuthority.credit;
  }

  getBakurakuAuthorityTeam(){
    return this.bakurakuAuthority.team;
  }
  
  getOutList(){
    return [this.crewMailList.length];
  }
}

sheet.gs

const SHEET = {
  crew: {
    name: 'crew',
    row: {
      data: 3,
    },
    column: {
      id: 1,
      empCode: 2,
      name: {
        family: 3,
        last: 4,
      },
      email: 5,
      status: 6,
      entered_at: 7,
      authorityLevel: 9,
      departmentList: [10, 12, 14],
    },
  },
  employmentType: {
    name: '[設定]雇用区分',
    row: {
      data: 2,
    },
    column: {
      employment_type: 1,
      authorityLevel: 2,
    },
  },
  team: {
    name: 'team',
    row: {
      data: 3,
    },
    column: {
      name: 1,
      authorityLevel: 2,
      department: 3,
      position: 4,
      memberNum: 5,
      kintoneGroup: 6,
      googleGroup: 7,
      slack: {
        authority: 9,
        usergroupId: 10,
      },
      bakurakuAuthority: {
        user: 11,
        expenses: 12,
        expensesApprove: 13,
        invoice: 14,
        credit: 15,
        team: 16,
      },
    },
  },
  outsourcer: {
    name: '手動更新',
    row: {
      data: 3,
    },
    column: {
      email: 1,
      teamName: 2,
    },
  },
  bakurakuCrewIn: {
    name: 'in_バクラクメンバー',
    row: {
      data: 2,
    },
    column: {
      id: 1,
      email: 3,
    },
  },
  bakurakuCrewOut: {
    name: 'out_バクラクメンバー',
    row: {
      data: 2,
    },
    column: {
      id: 1,
    },
  },
  bakurakuManualTeam: {
    name: '[設定]バクラク部署',
    row: {
      data: 2,
    },
    column: {
      before: 1,
      after: 2,
    },
  },
};

function getCrewListFromSheet(){
  return getSheetData(SHEET.crew).map(row => {
    const crew = new Crew();
    crew.setDataFromSheet(row);
    return crew;
  });
}

function getEmploymentTypeDic(){
  return getSheetData(SHEET.employmentType).reduce((json, row) => {
    json[row[SHEET.employmentType.column.employment_type - 1]] = row[SHEET.employmentType.column.authorityLevel - 1];
    return json;
  }, {});
}

function getTeamList(){
  return getSheetData(SHEET.team).map(row => new Team(row));
}

function getTeamListSetCrew(crewList, outsourcerList){
  return getTeamList().map(t => {
    t.findSetCrewMailList(crewList);
    t.findSetCrewFromOutsourcerList(outsourcerList);
    return t;
  });
}

function getBakurakuCrewList(){
  return getSheetData(SHEET.bakurakuCrewIn).map(row => {
    return {
      id: row[SHEET.bakurakuCrewIn.column.id -1],
      email: row[SHEET.bakurakuCrewIn.column.email -1],
    };
  });
}


function getOutsourcerList(){
  return getSheetData(SHEET.outsourcer).map(row => {
    return {
      email: row[SHEET.outsourcer.column.email -1],
      teamName: row[SHEET.outsourcer.column.teamName -1],
    };
  });
}

function getBakurakuManualTeamList(){
  return getSheetData(SHEET.bakurakuManualTeam).map(row => {
    return {
      before: row[SHEET.bakurakuManualTeam.column.before -1],
      after: row[SHEET.bakurakuManualTeam.column.after -1],
    };
  });
}

BakurakuCrew.gs

const BAKURAKU_CREW = {
  status: {
    valid: '有効',
  },
  authorityLevel: {
    general: '一般',
    viewOnly: '閲覧者',
    userAdmin: 'ユーザー管理者',
    admin: '管理者',
    inValid: '',
    approve : '承認',
  },
  position: {
    manager: 'マネージャー',
    member: 'メンバー',
  }
};

class BakurakuCrew{
  constructor(){
    this.email;
    this.name;
    this.id;
    this.emp_code;
    this.departmentText;
    this.authority = {
      user: BAKURAKU_CREW.authorityLevel.general,
      expenses: BAKURAKU_CREW.authorityLevel.inValid,
      expensesApprove: BAKURAKU_CREW.authorityLevel.inValid,
      invoice: BAKURAKU_CREW.authorityLevel.general,
      credit: BAKURAKU_CREW.authorityLevel.general,
    };
  }
  
  setDataFromSheet(row){
    this.id = row[SHEET.crew.column.id - 1];
    this.email = row[SHEET.crew.column.email - 1];
  }

  setDataFromSmartHRCrew(crew, teamList, bakurakuManualTeamList){
    this.name = crew.getName();
    this.email = crew.getEmail();
    this.emp_code = crew.getEmpCode();

    const joinTeamList = teamList.filter(t => t.isCrew(crew));

    const getAuthority = (authorityList, authority) => {
      return authorityList.reduce((authority, a) => (a !== '') ? a : authority, authority);
    };

    this.authority.user = getAuthority(joinTeamList.map(t => t.getBakurakuAuthorityUser()), this.authority.user);
    this.authority.expenses = getAuthority(joinTeamList.map(t => t.getBakurakuAuthorityExpenses()), this.authority.expenses);
    this.authority.expensesApprove = getAuthority(joinTeamList.map(t => t.getBakurakuAuthorityExpensesApprove()), this.authority.expensesApprove);
    this.authority.invoice = getAuthority(joinTeamList.map(t => t.getBakurakuAuthorityInvoice()), this.authority.invoice);
    this.authority.credit = getAuthority(joinTeamList.map(t => t.getBakurakuAuthorityCredit()), this.authority.credit);

    this.departmentText = crew.getDepartmentList().reduce((dList, department) => {
      let departmentName = department.name;
      if(departmentName === '') return dList;

      bakurakuManualTeamList.forEach(mt => {
        departmentName = departmentName.replace(mt.before, mt.after);
      });

      const getpositionText = position => {
        const p = [
          BAKURAKU_CREW.position.manager
        ].find(p => position.includes(p));

        return (p === undefined) ? BAKURAKU_CREW.position.member : p;
      };

      return dList.concat(departmentName.split('/').pop() + `{{${getpositionText(department.position)}}}`);
    }, [])
    .concat(joinTeamList.reduce((dList, t) => {
      const teamName = t.getBakurakuAuthorityTeam();
      return (teamName === '') ? dList : dList.concat(`${teamName}{{${BAKURAKU_CREW.position.member}}}`);
    }, []))
    .join(';');
  }

  isSameEmail(email){
    return this.email === email;
  }

  isNoId(){
    return this.id === undefined;
  }

  findSetId(bakurakuCrewList){
    this.id = bakurakuCrewList.find(bc => this.isSameEmail(bc.email))?.id;
  }

  getOutList(){

    const sendMailFlg = '';
    const getAuthorityFlg = authority => authority !== '' ? 1 : 0;
    
    return [
      this.id,
      this.name,
      this.email,
      this.emp_code,
      this.authority.user,
      sendMailFlg,
      BAKURAKU_CREW.status.valid,
      this.departmentText,
      getAuthorityFlg(this.authority.expenses),
      (this.authority.expenses === BAKURAKU_CREW.authorityLevel.inValid) ? BAKURAKU_CREW.authorityLevel.viewOnly : this.authority.expenses,
      getAuthorityFlg(this.authority.invoice),
      this.authority.invoice,
      getAuthorityFlg(this.authority.expensesApprove),
      getAuthorityFlg(this.authority.credit),
      this.authority.credit
    ];
  }
}

Crew.gs

const CREW = {
  status: {
    employed: 'employed',
  },
  authorityLevel: {
    employee: '社員',
  },
  position: {
    manager: 'マネージャー',
  },
};

class Crew{
  constructor(){
    this.rowIndex;
    this.email;
    this.id;
    this.emp_code;
    this.name = {
      last: undefined,
      family: undefined,
    };
    this.departments;
    this.positions;
    this.employment_type;
    this.entered_at;
    this.emp_status;
    this.authorityLevel;
  }

  setDataFromJson(json){

    const getName = (businessName, name) => businessName !== '' ? businessName : name;

    this.id = json.id;
    this.emp_code = parseInt(json.emp_code);
    this.name = {
      last: getName(json.business_first_name, json.first_name),
      family: getName(json.business_last_name, json.last_name),
    };
    this.departmentList = json.departments.map((d, index) => {
      return {
        name: d?.full_name,
        position: json.positions[index]?.name,
      };
    });
    this.employment_type = json.employment_type?.name;
    this.entered_at = dayjs.dayjs(json?.entered_at);
    this.emp_status = json.emp_status;
  }
  
  setDataFromSheet(row){
    this.id = row[SHEET.crew.column.id - 1];
    this.name = {
      last: row[SHEET.crew.column.name.last - 1],
      family: row[SHEET.crew.column.name.family - 1],
    };
    this.email = row[SHEET.crew.column.email - 1];
    this.emp_code = row[SHEET.crew.column.empCode - 1];
    this.entered_at = dayjs.dayjs(row[SHEET.crew.column.entered_at - 1]);
    this.emp_status = row[SHEET.crew.column.status - 1];
    this.authorityLevel = row[SHEET.crew.column.authorityLevel - 1];
    this.departmentList = SHEET.crew.column.departmentList.map(departmentIndex => {
      return {
        name: row[departmentIndex - 1],
        position: row[departmentIndex],
      };
    });
  }

  setAuthorityLevel(authorityLevel){
    this.authorityLevel = authorityLevel;
  }

  findSetEmail(kintoneMemberList){
    const member = kintoneMemberList.find(km => km.isSameId(this.emp_code));
    this.email = member?.getEmail();
  }

  isSame(crew){
    return this.id === crew.id;
  }

  isSameteam(team){
    return team.isSameAuthorityLevel(this.authorityLevel)
      && this.departmentList.some(d => team.isMatchDepartment(d.name)
      && team.isMatchPosition(d.position));
  }

  isSameStatus(emp_status){
    return this.emp_status === emp_status;
  }

  isSameAuthorityLevel(authorityLevel){
    return this.authorityLevel === authorityLevel;
  }

  isMatchDepartment(departmentName){
    return this.departmentList.some(d => d?.name?.includes(departmentName));
  }

  haveEmail(){
    return this.email.length > 0;
  }

  isBeforeJoin(){
    return dayjs.dayjs().isBefore(this.entered_at);
  }

  getName(){
    return `${this.name.family} ${this.name.last}`;
  }

  getEmail(){
    return this.email;
  }

  getEmpCode(){
    return this.emp_code;
  }

  getAuthorityLevel(){
    return this.authorityLevel;
  }

  generateAuthorityLevel(employmentTypeDic){
    this.authorityLevel = employmentTypeDic[this.employment_type];
  }

  getDepartmentList(){
    return this.departmentList;
  }

  getErrorMessage(){ 
    // エラー通知。今回は割愛
  }

  getOutList(){
    return [
      this.id,
      this.emp_code,
      this.name.family,
      this.name.last,
      this.email,
      this.emp_status,
      this.entered_at.format('YYYY/MM/DD'),
      this.employment_type,
      this.authorityLevel
    ].concat(
      this.departmentList.reduce((outList, department) => outList.concat(department.name, department.position), [])
    );
  }
}

smartHR.gs

const domain = 'https://xxxxxxxx.smarthr.jp';
const token = PropertiesService.getScriptProperties().getProperty('token');

function getCrewList(){
  let crewList = [];
  let index = 1;

  const params = {
    'method': 'GET',
    'headers': {'Authorization': `Bearer ${token}`,},
  };

  while(true){
    let response = UrlFetchApp.fetch(`${domain}/api/v1/crews?per_page=100&page=${index}`, params);
    let list = JSON.parse(response.getContentText());
    if(!list.length) break;
    crewList = crewList.concat(list.map(json => {
      const crew = new Crew();
      crew.setDataFromJson(json);
      return crew;
    }));
    index++;
  }
  return crewList;
}