bundleからアカウント一覧をスプレッドシートに出す

はじめに

https://bundle.jp/
bundleはAPIがあるので、各アカウントの情報を一旦スプレッドシートに出し、そこから利活用する。

利用目的は

• akerunのユーザーグループは現状100件までしか登録できないため、90以上になっていないか検知する
• kintoneでグループに1件も入っていない人がいないか検知する
• googleグループが入っていない人がいないか検知する

など。

なお、実際の処理はメンバーの中でも一部メンバーに限定しているため、

1. メンバー情報をbundleから取得
2. そこからフィルタリングしたメンバーのメールアドレスをkeyにしてアカウント情報を取得
   • メンバー分実行する

を行っている。このあたりは仕様に合わせて最適化できるかもしれない。

code

sheet.js

const SHEET = {
  id: '<sheet id>',
  member: {
    name: 'メンバー',
    row: {
      data: 2,
    },
    column: {
      id: 1,
    },
  },
  account: {
    name: 'アカウント',
    row: {
      data: 2,
    },
    column: {
      id: 1,
      member: {
        email: 2,
        code: 3,
      },
      name: 4,
      application: {
        name: 5,
        tenantName: 6,
      },
      displayRole: 7,
      status: 8,
      organizationList: 9,
      groupList: 10,
      
    },
  }
};

function getAccountListFromSheet(){
  return getSheetData(SHEET.account, SHEET.id).map(row => {
    const account = new Account();
    account.setDataFromSheet(row);
    return account;
  });
}

main.js

function main(){
  refreshSheet(
    SHEET.member.name,
    getMemberList().map(member => member.getOutList())
  );
}


function refreshAccountSheet(){
  
  const accountList = getMemberList().reduce((accountList, member) => {
    if(!member.isSameStatus('EMPLOYED')) return accountList;
    return accountList.concat(getAccountListByMember(member);
  }, []);

  refreshSheet(
    SHEET.account.name,
    accountList.map(account => account.getOutList())
  );
}

class/Member.js

class Member{
  constructor(json){
    this.code = json.code;
    this.employmentType = json.employmentType?.name;
    this.name = json.displayName;
    this.url = json.url;
    this.email = json.primaryEmail;
    this.status = json.status;
  }

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

  isSameEmploymentType(employmentType){
    return this.employmentType === employmentType;
  }

  getCode(){
    return this.code;
  }

  getEmail(){
    return this.email;
  }

  getOutList(){
    return [
      this.url,
      this.code,
      this.employmentType,
      this.name,
      this.email,
      this.status
    ];
  }
}

class/Account.js

var ACCOUNT = {
  applicationName: {
    gws: 'Google Workspace',
    akerun: 'Akerun入退室管理システム',
  },
};

class Account{
  constructor(){
    this.id;
    this.member = {
      email: undefined,
      code: undefined,
    };
    this.name;
    this.application = {
      name: undefined,
      tenantName: undefined,
    };
    this.displayRole;
    this.status;
    this.organizationList = [];
    this.groupList = [];
  }

  setDataFromJson(member, json){
    this.id = json.databaseId;
    this.member = {
      email: member.getEmail(),
      code: member.getCode(),
    };
    this.name = (json.displayAccount.includes('gmail')) ? 'xxxxxx' : json.displayAccount;
    this.application = {
      name: json.application.name,
      tenantName: json.application.tenantName,
    };
    this.displayRole = json.displayRole;
    this.status = json.applicationAccountStatus.displayName;
    this.organizationList = (json.data.attributes.organizations === undefined) ? [] : json.data.attributes.organizations.map(o => o.name);

    const getGroupList = json => {
      const groupList = this.isSameApplicationName(ACCOUNT.applicationName.akerun)
        ? json.data.attributes.user_groups : json.data.attributes.groups;

      if(groupList === undefined) return [];
      return groupList.map(g => {
        if(this.isSameApplicationName(ACCOUNT.applicationName.gws)) return g.email;
        return g.name;
      });
    };
    this.groupList = getGroupList(json);
  }

  setDataFromSheet(row){
    this.id = row[SHEET.account.column.id - 1];
    this.member = {
      email: row[SHEET.account.column.member.email - 1],
      code: row[SHEET.account.column.member.code - 1],
    };
    this.name = row[SHEET.account.column.name - 1];
    this.application = {
      name: row[SHEET.account.column.application.name - 1],
      tenantName: row[SHEET.account.column.application.tenantName - 1],
    };
    this.displayRole = row[SHEET.account.column.displayRole - 1];
    this.status = row[SHEET.account.column.status - 1];
    this.organizationList = row[SHEET.account.column.organizationList - 1].split((', '));
    this.groupList = row[SHEET.account.column.groupList - 1].split((', '));
  }

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

  isSameApplicationName(applicationName){
    return this.application.name === applicationName;
  }

  isSameApplicationTenantName(tenantName){
    return this.application.tenantName === tenantName;
  }

  getDisplayRole(){
    return this.displayRole;
  }

  getApplicationName(){
    return this.application.name;
  }

  getOrganizationList(){
    return this.organizationList;
  }

  getGroupList(){
    return this.groupList;
  }

  getEmail(){
    return this.member.email;
  }

  getOutList(){
    return [
      this.id,
      this.member.email,
      this.member.code,
      this.name,
      this.application.name,
      this.application.tenantName,
      this.displayRole,
      this.status,
      this.organizationList.join(', '),
      this.groupList.join(', ')
    ];
  }
}

bundle.js

const TOKEN = PropertiesService.getScriptProperties().getProperty('token');

const URL_API = 'https://<tenant>.bundle.jp/api/v1';

function fetchBundle(query){
  const options = {
    'method': 'POST',
    'payload': JSON.stringify({query: query}),
    'headers': {
      'content-type': 'application/json',
      'Accept': 'application/json',
      'Authorization': `Bearer ${TOKEN}`,
    },
    muteHttpExceptions:true,
  };

  const res = UrlFetchApp.fetch(URL_API, options);
  return JSON.parse(res).data.team;
}

function getMemberList() {

  let memberList = [];

  let cursor = '';
  let json = {};

  do{
    const query = `
{
team {
  members(first:30, after:"${cursor}") {
    pageInfo {
      endCursor
      hasNextPage
    }
    nodes {
      displayName
      url
      primaryEmail
      status
      employmentType {
        name
      },
      code
    }
  }
}
}
    `;

    json = fetchBundle(query);
    memberList = memberList.concat(json.members.nodes.map(json => new Member(json)));
    cursor = json.members.pageInfo.endCursor;
    Utilities.sleep(1 * 1000);
  }while (json.members.pageInfo.hasNextPage);


  return memberList;
}

function getAccountListByMember(member){

  const query = `
{
  team {
    applicationAccounts(where: {keyword: "${member.getEmail()}"}, first:100){
      nodes{
        databaseId,
        application{
          name,
        	tenantName
      	},
        displayAccount,
        displayRole,
        applicationAccountStatus{displayName},
        data{
          attributes
        }
      }
    }
  }
}
  `;

  return fetchBundle(query).applicationAccounts.nodes.map(json => {
    const account = new Account();
    account.setDataFromJson(member, json);
    return account;
  });
}