Akerunのデータで出社率を分析するスプレッドシート

この記事は、Lancers（ランサーズ） Advent Calendar 2023 の4日目の記事です。

はじめに

弊社ではリモートワークと出社を組み合わせたハイブリッドな勤務スタイルを採用しています。
そこで気になるのが、会議室やフリーアドレス席の混雑状況です。「座れない」や「オフィスで会議室が予約できない」などの事態を避けるために、出社率をモニタリングすることにしました。
そこで出社率モニタリングツールを作成しました。

ツールの処理概要

1日1回、夜に動かします。

1. Akerunから今日のドア開閉記録をAPIで取得して、スプレッドシートの「data」シートに貯めておきます
2. 「data」シートの情報を元に集計を行い、「モニタリングシート」に転記します
   • ついでにgoogleカレンダーから会議室の予約を取得して、会議室利用率も算出します

※後、ついでにAkerunの電池残量を調べて少なかったらSlackに通知しています。Akerunさんは電池を先送りで送ってもらえているので、通知があれば交換するような運用です。

ツール

dataシート

モニタリングシート

configシート

遠方在住者など、ハイブリッド勤務が無理な方は以下表で管理

処理

main.gs

// 今日のドア開閉記録を持ってきてdataシートに追記する
function refreshDataSheet(){
  const akerunManager = new AkerunManager();
  const today = dayjs.dayjs().locale('ja');

  // うちは1000を超えることがないので以下で大丈夫ですが、大きな会社さんでは適宜調整ください。
  const accessList = akerunManager.getAccessList(1000).filter(a => a.isSameDay(today));
  const akerunUserList = akerunManager.getUserList();

  // 弊社ではkintoneを使っているので、そこからスタッフの部署情報を取得。後で集計のときに部署ごとの情報を出すときに使用
  const departmentList = KintoneData.getDepartmentList();
  const emailList = getEmailListFromDataSheet();
  // kintoneからスタッフの情報を取得。メールアドレスでakerunと紐づけを行う。
  const kintoneMemberList = KintoneData.getMemberList();

  const memberList = emailList.map(email => {
    const member = new Member();
    member.setEmail(email);
    member.findSetKintoneMember(kintoneMemberList);
    return member;
  }).concat(
    kintoneMemberList
      .filter(km => {
        // 在職者とメール一致でフィルタリング。filterとmap使うならreduce使えばよかった。
        return km.isSameEnrollmentStatus(KintoneData.MEMBER.status.enrollment.enrolled) && !emailList.includes(km.getEmail());
      })
      .map(km => {
        const member = new Member();
        member.setKintoneMember(km);
        return member;
      })
  ).map(member => {
    member.getKintoneMember().findSetDepartmentList(departmentList);
    member.findSetAkerunUser(akerunUserList);
    member.findsetTodayAccessList(accessList);
    return member;
  });

  // dataシートに記載。2回行っているのは1回目で人の情報（部署異動や入退職に対応）と、2回目で出社記録
  setList(
    SHEET.data,
    SHEET.data.row.data,
    SHEET.data.column.email,
    memberList.map(member => member.getOutList())
  );
  const columnIndex = SHEET.data.column.data + getdayList().findIndex(day => day.isSame(today, 'day'));
  const outList = memberList.map(member => [member.getTodayLog(getDayOfWeekFromDataSheet(columnIndex))]);

  setList(
    SHEET.data,
    SHEET.data.row.data,
    columnIndex,
    outList
  );

  const total = outList.reduce((total, row) => {
    if(ARRIVAL.text.weekdayList.includes(row.shift())) total++;
    return total;
  }, 0);
  if(total === 0) return;

  // 今日の出社人数を通知
  slackChannel(
    // webhook,
    `:lancers: 今日の出社人数 :lancers:\n${total}人です`
  );
}

// モニタリングシートを更新する集計処理
function refreshWeekSheet(){
  const reportWeek = new ReportWeek();
  reportWeek.refreshWeekSheet();
}

// ついでの電池残量確認処理
function alert(){

  const akerunManager = new AkerunManager();
  const accessList = akerunManager.getAccessList();
  const text = akerunManager.getAkerunList().reduce((alertList, akerun) => {
    const alertText = akerun.getAlertText(accessList);
    if(!alertText) return alertList;
    return alertList.concat(alertText);
  }, []).join('\n\n');

  // 後はSlackに通知させてください。
  // 一応、GASが動いていることの確認のために以下文字を入れています。
  // text === '' ? '電池は全部オッケーです！': text
}

// akerunAPIは定期的なtokenリフレッシュが必要なのでそれを実行
function tokenRefresh(){
  const akerunManager = new AkerunManager();
  akerunManager.refreshToken();
}

sheet.gs

const SHEET = {
  data: {
    name: 'data',
    row: {
      day: 1,
      dayOfWeek: 2,
      data: 3,
    },
    column: {
      email: 1,
      name: 2,
      enrollmentStatus: 3,
      employmentStatus: 4,
      department: 5,
      data: 6,
    },
  },
  week: {
    name: 'モニタリング',
    row: {
      day: 1,
      data: 2,
    },
    column: {
      type: 1,
      department: 2,
      data: 4,
    },
  },
  config: {
    name: 'config',
    row: {
      data: 2,
    },
    column: {
      email: 1,
    },
  },
};


function getEmailListFromDataSheet(){
  return getSheetData(SHEET.data).map(row => row[SHEET.data.column.email - 1]);
}

function getdayList(){
  const outList = getSheetDataFull(SHEET.data)[SHEET.data.row.day - 1];
  [...new Array(SHEET.data.column.data - 1)].forEach(_ => outList.shift());
  return outList.map(text => dayjs.dayjs(text));
}

function getWeekList(){
  const outList = getSheetDataFull(SHEET.week)[SHEET.week.row.day - 1];
  [...new Array(SHEET.week.column.data - 1)].forEach(_ => outList.shift());
  return outList.map(text => dayjs.dayjs(text));
}

function getDayOfWeekFromDataSheet(columnIndex){
  return getSheet(SHEET.data.name).getRange(SHEET.data.row.dayOfWeek, columnIndex).getValue();
}

function getMemberList(){
  const dayList = getdayList();
  return getSheetData(SHEET.data).map(row => {
    const member = new Member();
    member.setDataFromDataSheet(row, dayList);
    return member;
  });
}

function getOutdwellerEmailList(){
  return getSheetData(SHEET.config).map(row => row[SHEET.config.column.email - 1]);
}

class/Access.gs

class Access{
  constructor(json){
    this.accessed_at = dayjs.dayjs(json.accessed_at);
    this.akerunName = json.akerun.name;
    this.userId = json.user?.id;
  }

  isSameAkerunName(name){
    return this.akerunName === name;
  }

  isSameDay(day){
    return this.accessed_at.isSame(day, 'd');
  }

  getUserId(){
    return this.userId;
  }
}

class/Akerun.gs

var AKERUN = {
  name: {
    entrance: {
      // 文字は念のため実態と変えてます
      main: 'メインエントランス',
      tempDoor: 'tempDoor',
    },
  },
};

class Akerun{
  constructor(res){
    this.name = res.name;
    this.battery_percentage = res.battery_percentage;
    this.text = `ドア名: ${this.name}`
      + `\n本体: ${res.battery_percentage}`
      + `\nnfc_reader_inside: ${res.nfc_reader_inside.battery_percentage}`
      + `\nnfc_reader_outside: ${res.nfc_reader_outside.battery_percentage}`
      + `\ndoor_sensor: ${res.door_sensor.battery_percentage}`;


    this.alertList = [
      ['Akerun Pro', res.battery_percentage],
      ['ドアセンサー', res.door_sensor.battery_percentage],
      ['IC カードリーダー（外側）', res.nfc_reader_outside.battery_percentage],
      ['IC カードリーダー（内側）', res.nfc_reader_inside.battery_percentage]
    ].reduce((alertList, row) => {

      if(this.name.startsWith('OLD')) return alertList;

      const name = row[0];
      const batteryPercentage = row[1];

      if(batteryPercentage !== null && batteryPercentage < 50){
        return alertList.concat(`  ${name}の残量が50%以下: ${batteryPercentage}`);
      }
      return alertList;
    }, []);
  }

  isEntrance(){
    return [
      AKERUN.name.entrance.main,
      AKERUN.name.entrance.tempDoor
    ].includes(this.name);
  }

  getAlertText(accessList){
    if(this.isEntrance() && !accessList.some(access => access.isSameAkerunName(this.name))){
      this.alertList.push(`  直近100件のログに該当なし`);
    }

    if(!this.alertList.length) return undefined;

    return `ドア名: ${this.name}`
      + `\n${this.alertList.join('\n')}`;
  }
}

class/AkerunManager.gs

class AkerunManager{
  constructor(){
    this.clientId = PropertiesService.getScriptProperties().getProperty('clientId');
    this.clientSecret = PropertiesService.getScriptProperties().getProperty('clientSecret');
    this.organizationId = PropertiesService.getScriptProperties().getProperty('organizationId');
    this.token = {
      access: PropertiesService.getScriptProperties().getProperty('accessToken'),
      refresh: PropertiesService.getScriptProperties().getProperty('refreshToken'),
    };
  }

  getAkerunList(){
    // https://developers.akerun.com/#authentication

    const options = {
      headers : {
        'Authorization': `Bearer ${this.token.access}`,
      },
      method : 'get',
    };
  
    let res = UrlFetchApp.fetch(`https://api.akerun.com/v3/organizations/${this.organizationId}/akeruns`, options);
    res = JSON.parse(res);
    return res.akeruns.map(json => new Akerun(json));
  }


  getAccessList(limit){
    // https://developers.akerun.com/#list-access

    if(limit === undefined){
      limit = 100;
    }
    const options = {
      headers : {
        'Authorization': `Bearer ${this.token.access}`,
      },
      method : 'get',
    };
  
    let res = UrlFetchApp.fetch(`https://api.akerun.com/v3/organizations/${this.organizationId}/accesses?limit=${limit}`, options);
    res = JSON.parse(res);
    return res.accesses.map(json => new Access(json));
  }

  getUserList(){
    // https://developers.akerun.com/#user
    const options = {
      headers : {
        'Authorization': `Bearer ${this.token.access}`,
      },
      method : 'get',
    };
  
    let res = UrlFetchApp.fetch(`https://api.akerun.com/v3/organizations/${this.organizationId}/users?limit=1000`, options);
    res = JSON.parse(res);
    return res.users.map(json => new User(json));
  }

  refreshToken(){
    // https://developers.akerun.com/#access-token

    const options = {
      'method' : 'post',
      'contentType' : 'application/json',
      'payload' : JSON.stringify({
        'grant_type': 'refresh_token',
        'client_id': this.clientId,
        'client_secret': this.clientSecret,
        'refresh_token': this.token.refresh,
      }),
    };
  
    let res = UrlFetchApp.fetch('https://api.akerun.com/oauth/token', options);
    res = JSON.parse(res);
    this.token.access = res.access_token;
    this.token.refresh = res.refresh_token;

    PropertiesService.getScriptProperties().setProperties({
      'accessToken': res.access_token,
      'refreshToken': res.refresh_token,
    });
  }
}

class/Arrival.gs

const ARRIVAL = {
  text: {
    notEnrolled: '-',
    notWork: '',
    home: '宅',
    holidayList: ['土', '日', '祝'],
    weekdayList: ['月', '火', '水', '木', '金'],
  },
};

class Arrival{
  constructor(arrivalText, day){
    this.day = day;
    this.arrivalText = arrivalText;
  }

  isSameWeek(day){
    return day.isSame(this.day, 'week');
  }

  isOffice(){
    return ARRIVAL.text.weekdayList.includes(this.arrivalText);
  }

  isWorkingDay(){
    return [ARRIVAL.text.home].concat(ARRIVAL.text.weekdayList).includes(this.arrivalText);
  }

  isSameArrivalText(text){
    return this.arrivalText === text;
  }
}

class/Department.gs

class Department{
  constructor(member){
    this.name = member.getDepartment();
    this.memberList = [member];
  }

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

  addMemberList(member){
    this.memberList.push(member);
  }

  getOutList(weekList){
    let outList = [];

    outList = [
      ['部', this.name, '週2出社達成率'].concat(
        weekList.map(week => {
          const enrolledMemberList = this.memberList.filter(member => member.isEnrolled(week));
          return `=iferror(${enrolledMemberList.filter(member => member.isArrivalTwice(week)).length} / ${enrolledMemberList.length}, 0)`;
        })
      )
    ];

    ARRIVAL.text.weekdayList.forEach(dayOfWeek => {
      outList.push(['部', this.name, `曜日毎:${dayOfWeek}`].concat(weekList.map(week => {
        const enrolledMemberList = this.memberList.filter(member => member.isEnrolled(week));
        return `=iferror(${enrolledMemberList.filter(member => member.getArrivalNum(week, dayOfWeek) > 0).length} / ${enrolledMemberList.length}, 0)`;
      })));
    });

    return outList;
  }
}

class/GoogleCalendar.gs

class GoogleCalendar{
  constructor(id){
    this.calendar = CalendarApp.getCalendarById(id);
  }

  getOutList(weekList){

    const getEventHours = event => {
      return dayjs.dayjs(event.getEndTime()).diff(dayjs.dayjs(event.getStartTime()), 'hour', true);
    };

    return ARRIVAL.text.weekdayList.reduce((outList, dayOfweek, index) => {
      return outList.concat(
        [['部屋', this.calendar.getName(), `利用率:${dayOfweek}`].concat(weekList.map(week => {
          const eventList = this.calendar.getEventsForDay(week.add(index, 'day').toDate());
          return `=${eventList.reduce((total, event) => total + getEventHours(event), 0)}/8`;
        }))]
      );
    }, []);
  }
}

class/Member.gs

class Member{
  constructor(){
    this.kintoneMember;
    this.akerunUser;
    this.todayAccessList = [];
    this.rowIndex;

    this.email;
    this.department;
    this.employmentStatus;
    this.arrivalList = [];
    this.name;
  }

  setEmail(email){
    this.email = email;
  }

  setKintoneMember(kintoneMember){
    this.kintoneMember = kintoneMember;
  }

  setDataFromDataSheet(row, dayList){
    this.department = row[SHEET.data.column.department -1];
    this.name = row[SHEET.data.column.name -1];
    this.email = row[SHEET.data.column.email -1];
    this.employmentStatus = row[SHEET.data.column.employmentStatus -1];
    [...new Array(SHEET.data.column.data - 1)].forEach(_ => row.shift());
    this.arrivalList = row.map((arrivalText, index) => new Arrival(arrivalText, dayList[index]));
  }

  findSetKintoneMember(kintoneMemberList){
    this.kintoneMember = kintoneMemberList.find(km => km.isSameMail(this.email));
  }

  findSetRowIndex(emailList){
    this.rowIndex = emailList.findIndex(email => this.isSameEmail(email));
  }

  findSetAkerunUser(akerunUserList){
    this.akerunUser = akerunUserList.find(user => this.isSameEmail(user.getEmail()));
  }

  findsetTodayAccessList(accessList){
    this.todayAccessList = accessList.filter(a => this.akerunUser?.isSameId(a.getUserId()));
  }

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

  isNewMember(){
    return this.rowIndex === -1;
  }

  getArrivalNum(week, dayOfWeek){
    const weekArrivalList = this.arrivalList.filter(arrival => arrival.isSameWeek(week));
    const arrivalFormula = dayOfWeek === undefined ? (arrival => arrival.isOffice())
      : (arrival => arrival.isSameArrivalText(dayOfWeek));
    return weekArrivalList.filter(arrivalFormula).length;
  }

  isArrivalTwice(week){
    return this.getArrivalNum(week) > 1;
  }

  isEnrolled(week){
    return this.getArrivalNum(week, ARRIVAL.text.notEnrolled) === 0;
  }

  isTargetArrivalCount(outdwellerEmailList){
    return !outdwellerEmailList.includes(this.getEmail())
      && this.employmentStatus !== KintoneData.MEMBER.status.employment.partTime;
  }

  getEmail(){
    return this.email;
  }

  getRowIndex(){
    return this.rowIndex;
  }

  getArrivalRate(week){
    if(week.isAfter(dayjs.dayjs().add(-6, 'day'))) return '-';
    const weekArrivalList = this.arrivalList.filter(arrival => arrival.isSameWeek(week));
    return `=iferror(${weekArrivalList.filter(arrival => arrival.isOffice()).length}/${weekArrivalList.filter(arrival => arrival.isWorkingDay()).length}, 0)`;
  }

  getKintoneMember(){
    return this.kintoneMember;
  }

  getTodayLog(dayOfWeek){
    return !this.kintoneMember.isSameEnrollmentStatus(KintoneData.MEMBER.status.enrollment.enrolled) ? ARRIVAL.text.notEnrolled
      : ARRIVAL.text.holidayList.includes(dayOfWeek) ? ARRIVAL.text.notWork
      : this.todayAccessList.length ? dayOfWeek
      : ARRIVAL.text.home;
  }

  getDepartment(){
    return this.department;
  }

  getOutList(){
    return [
      this.kintoneMember.getEmail(),
      this.kintoneMember.getName(),
      this.kintoneMember.getEnrollmentStatus(),
      this.kintoneMember.getEmploymentStatus(),
      this.kintoneMember.getLatestDepartment()?.getDepartmentMain()
    ];
  }

  getOutListWeekList(weekList){

    return [
      ['人', this.name, '出社率'].concat(weekList.map(week => this.getArrivalRate(week)))
    ];
  }
}

class/ReportWeek.gs

class ReportWeek{
  // 週ごとの集計をまとめるクラス
  constructor(){
    this.outdwellerEmailList = getOutdwellerEmailList();
    this.memberList = getMemberList();
    this.weekList = getWeekList();
  }

  refreshWeekSheet(){

    const getReportByRoom = _ => {
      return [
        // 会議室カレンダーのID,
        // 会議室カレンダーのID,
        // 会議室カレンダーのID,
        // 会議室カレンダーのID
      ].reduce((outList, googleCalendarId) => {
        const calendar = new GoogleCalendar(googleCalendarId);
        return outList.concat(calendar.getOutList(this.weekList));
      }, []);
    };

    const getReportByDayOfWeek = _ => {
      return ARRIVAL.text.weekdayList.map(dayOfweek => {
        return ['全体', dayOfweek, '出社人数'].concat(this.weekList.map(week => {
          return this.memberList.reduce((total, member) => {
            if(member.getArrivalNum(week, dayOfweek) === 1) total++;
            return total;
          }, 0);
        }));
      });
    };

    const getReportByDepartment = _ => {
      return this.memberList.reduce((departmentList, member) => {
        if(!member.isTargetArrivalCount(this.outdwellerEmailList)) return departmentList;
        const department = departmentList.find(d => d.isSameName(member.getDepartment()));
        if(department === undefined) return departmentList.concat(new Department(member));
        department.addMemberList(member);
        return departmentList;
      }, []).reduce((outList, department) => outList.concat(department.getOutList(this.weekList)), []);
    };

    const getReportByMember = _ => {
      return this.memberList.reduce((outList, member) => {
        if(!member.isTargetArrivalCount(this.outdwellerEmailList)) return outList;
        return outList.concat(member.getOutListWeekList(this.weekList));
      }, []);
    };
  

    refreshSheet(
      SHEET.week.name,
      getReportByDayOfWeek().concat(getReportByRoom(), getReportByDepartment(), getReportByMember()),
      SHEET.week.column.type,
      SHEET.week.row.data
    );
  }
}

class/User.gs

// Akerunのユーザー
class User{
  constructor(json){
    this.id = json.id;
    this.name = json.name;
    this.mail = json.mail;
  }

  isSameId(id){
    return this.id === id;
  }

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

  getEmail(){
    return this.mail;
  }
}

まとめ、最後に

これで出社率を確認できるようになりました。Akerunを使っていて、出社率を算出したい方は参考にしてみてください。