Googleグループの追加・削除をGASで行う

はじめに

追加や削除依頼を受けてGoogleグループのメンバーを変更しているが、管理者のアカウントから実行しなければならず大変だった。
そこで変更用のspreadsheetを作成し、

1. 依頼があればそのspreadsheetのorderシートに記入する
2. GASが一定間隔でorderシートから情報を取得して、orderの通りに命令を実行する。
   • トリガー設定元は管理者アカウントにする
     に設定して作業時間を削減できた。

なお、たまにgoogleグループが新設された際はgroupシートを一括更新する処理を実行する。
※groupシートにあるグループでしか、orderシートに記入できないようにしている。

シート

orderシート

groupシート

code

main.gs

function changeGroup(){
  getOrderList().forEach(order => order.execute());
}

function refreshGroupSheet(){
  const groupList = getGroupList();
  refreshSheet(SHEET.group.name, groupList.map(g => g.getOutList()));
}

function refreshGroupMemberSheet(){
  const groupList = getGroupList();

  const outList = getGroupMemberList().map(groupName => {
    const group = groupList.find(group => group.isSameName(groupName));
    return [group.getMemberListText()];
  });
  setList(
    SHEET.groupMember,
    SHEET.groupMember.row.data,
    SHEET.groupMember.column.memberList,
    outList
  );
}

sheet.gs

const SHEET = {
  order: {
    name : 'order',
    row: {
      data: 2,
    },
    column: {
      type: 1,
      group: 2,
      mail: 3,
      status: 4,
    },
  },
  
  group: {
    name : 'group',
    row: {
      data: 2,
    },
    column: {
      name: 1,
    },
  },
};


function getOrderList(){
  return getSheetData(SHEET.order).map((row, index) => new Order(row, index));
}

function setList(sheetConfig, row, column, list){
  if(!list.length) return;
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetConfig.name);
  sheet.getRange(row, column, list.length, list[0].length).setValues(list);
}

function getSheetData(sheetConfig, spreadSheetId){
  let data = getSheetDataFull(sheetConfig, spreadSheetId);
  [...Array(sheetConfig.row.data - 1)].forEach(_ => data.shift());
  return data;
}

function getSheetDataFull(sheetConfig, spreadSheetId){
  const ss = (spreadSheetId !== undefined) ? SpreadsheetApp.openById(spreadSheetId) : SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(sheetConfig.name);
  return sheet.getDataRange().getValues();
}

function setText(sheetConfig, row, column, text){
  setList(sheetConfig, row, column, [[text]]);
}

function setList(sheetConfig, row, column, list){
  if(!list.length) return;
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetConfig.name);
  sheet.getRange(row, column, list.length, list[0].length).setValues(list);
}

function setListAnotherSheet(spreadSheetId, sheetConfig, row, column, list){
  if(!list.length) return;
  const sheet = SpreadsheetApp.openById(spreadSheetId).getSheetByName(sheetConfig.name);
  sheet.getRange(row, column, list.length, list[0].length).setValues(list);
}

function refreshSheet(sheetName, outList, startColumn, startRow, spreadSheetId){
  
  if(!outList[0].length) return;
  const sheet = getSheet(sheetName, spreadSheetId);

  startRow = startRow ? startRow : 2;
  startColumn = startColumn ? startColumn : 1;
  sheet.getRange(startRow, startColumn, sheet.getLastRow(), outList[0].length).clear();
  sheet.getRange(startRow, startColumn, outList.length, outList[0].length).setValues(outList);
}

function addSheet(sheetConfig, column, list){
  if(!list.length) return;
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetConfig.name);
  sheet.insertRows(sheetConfig.row.data, list.length);
  sheet.getRange(sheetConfig.row.data, column, list.length, list[0].length).setValues(list);
}

function addSheetLastRow(sheetConfig, list, column, spreadSheetId){
  if(!list.length) return;

  const sheet = getSheet(sheetConfig.name, spreadSheetId);

  sheet.getRange(
    sheet.getLastRow() + 1,
    column !== undefined ? column : 1,
    list.length,
    list[0].length
  ).setValues(list);
}

function getSheet(sheetName, spreadSheetId){
  const ss = (spreadSheetId !== undefined) ? SpreadsheetApp.openById(spreadSheetId) : SpreadsheetApp.getActive();
  return ss.getSheetByName(sheetName);
}

order.gs

const ORDER = {
  status: {
    yet: '未実行',
    done: '実行済',
  },
  type: {
    add: '追加',
    remove: '削除',
  },
};

class Order{
  constructor(row, index){
    this.rowIndex = index + SHEET.order.row.data;
    this.type = row[SHEET.order.column.type - 1];
    this.group = row[SHEET.order.column.group - 1];
    this.mail = row[SHEET.order.column.mail - 1];
    this.status = row[SHEET.order.column.status - 1];
  }
  
  execute(){

    if(this.status !== ORDER.status.yet) return;

    let result = '';
    
    try {
      switch(this.type){
        case ORDER.type.add:
          AdminDirectory.Members.insert({ email: this.mail, role: 'MEMBER'}, this.group);
          break;

        case ORDER.type.remove:
          AdminDirectory.Members.remove(this.group, this.mail);
          break;

        default:
          break;
      }
      result = '実行済';

    } catch (e) {
      result = `Error ${e}`;
    }

    setText(SHEET.order, this.rowIndex, SHEET.order.column.status, result);
  }
}

group.gs

class Group{
  constructor(group){
    this.id = group.id;
    this.name = group.name;
    this.email = group.email;
  }

  getOutList(){
    return [
      `https://admin.google.com/ac/groups/${this.id}`,
      this.name,
      this.email
    ];
  }

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

  getMemberList(){
    const memberList = AdminDirectory.Members.list(this.email).members;
    return Object.prototype.toString.call(memberList) === '[object Array]' ? memberList.map(m => m.email) : [];
  }

  getMemberListText(){
    return this.getMemberList().join(', ');
  }
}