Apps Script로 엑셀 데이터와 Google Groups 자동화하기 (4) 엑셀 -> 그룹스 동기화시키기

엑셀 시트에서 항목(회원 목록 데이터)이 삭제되거나 새로 추가되거나 기존 항목이 수정 됐을 때,

그룹스 데이터에서도 해당 변경 내용이 동기화되는 기능을 구현했다.

 

 

 

util 함수들

 

바로 이전 포스팅에서 나온 getMyGroup 함수에서 엑셀로 데이터 값 집어넣는 부분이 빠진 것 밖에 달라진 점이 없다. 

이번에 나오는 함수들(엑셀 시트 데이터에서 그룹스로 데이터 업데이트)한테 적용시켜야 하는 함수이다.

 

1. 데이터 환경변수에 저장하기 - 의사 코드

 

* getMyGroup 과 6, 7번만 빼고 로직은 같다.

* 2, 3번은 리팩토링 해서 oauth.gs에 전역으로 빼둔 상태이다.

1. 액세스 토큰 발급
2. 액셀 시트에 작성되어 있는 그룹 대표 이메일 값 추출해오기 (위치는 H1)
3. 이메일 값이 이중 배열로 들어오기 때문에 문자열 값으로 바꿔서 groupKey 라는 변수에 넣어주기
4. groupKey를 params에 넣어서 GET 요청 보내기
5. 받은 response를 JSON.parse 하고 member에 해당되는 데이터(그룹 내 모든 회원 목록) 추출하기
6. 엑셀 시트 내 원하는 위치부터 값 입력시키기 
7. 값 입력 완료 후의 칸은 다 빈칸으로 만들기
   -> 만약 그 전에 있었던 데이터가 더 많아서, 받아온 데이터가 완전히 덮어지기 전에 입력이 완료됐을 때를 위함
8. 최신 데이터 환경 변수에 저장하기 
   -> 그룹스에 있는 데이터가 최신이니까 엑셀로 가져왔다고 가정
   -> 나중에 엑셀 데이터가 변경되었을 때 기준점이 됨
   -> 엑셀시트를 읽었을 때와 똑같은 형식으로 변경해서 저장해줌. (배열 내 배열 형식)

 

/** 그룹스 목록 변수로 저장만 하기 */
function envMyGroup() { 
  var service = getService_();
  if (!service.hasAccess()) {  
    Logger.log(service.getLastError()); 
    return;
  }

  var getUrl = `https://admin.googleapis.com/admin/directory/v1/groups/${groupKey}/members`;  
  var getOptions = {
  	muteHttpExceptions: true, 
    headers: { Authorization: 'Bearer ' + service.getAccessToken()}
    };

  var response = UrlFetchApp.fetch(getUrl, getOptions);
  var { members } = JSON.parse(response.getContentText());   /** groupKey(해당 group 이메일 주소)에 해당하는 멤버들 목록 배열로 반환 */

  var savedMembers = members.map(member => [member.email, member.type, member.role])
  PropertiesService.getScriptProperties().setProperties({'memberlist': JSON.stringify(savedMembers)});

}

 

2. 수정/ 추가/ 삭제 데이터 찾아내기 - 의사 코드

 

1. 환경변수에 저장된 기존 회원 목록 데이터 가져오기
2. 현재 엑셀 시트에 작성된 최신 회원 목록 데이터 가져오기
3. 새 항목이 추가되었는지 찾기
4. 기존 항목이 삭제되었는지 찾기
5. 삭제/추가되지 않은 기존 항목이 무엇이었는지 찾고, 그 항목 중에서 수정된 항목 찾기

 

코드

// 이전 배열 🟣 1.
var oldArray = JSON.parse(PropertiesService.getScriptProperties().getProperty('memberlist'));
Logger.log(oldArray)
// 새로운 배열 🟣 2.
var sheet = SpreadsheetApp.getActive().getSheetByName("google 그룹 members_template 2022 0228.csv");
var range = "C20:E"
var allSheetLists = sheet.getRange(range).getValues();
var newArray = allSheetLists.filter( x => x[0] !== '');


// 새 항목이 추가된 경우를 찾기 위한 함수 🟣 3.
function findAddedItems() {
  var addedItems = [];
  for (var i = 0; i < newArray.length; i++) {
    if (!oldArray.some(item => item[0] === newArray[i][0] )) {
      addedItems.push(newArray[i]);
    }
  }
  return addedItems;
}

// 기존 항목이 삭제된 경우를 찾기 위한 함수 🟣 4.
function findDeletedItems() {
  var deletedItems = [];
  for (var i = 0; i < oldArray.length; i++) {
    if (!newArray.some(item => item[0] === oldArray[i][0] )) {
      deletedItems.push(oldArray[i]);
    }
  }
  return deletedItems;
}

// 기존 항목 내용이 수정된 경우를 찾기 위한 함수 🟣 5.
function findModifiedItems() {
  var modifiedItems = [];
  for (var i = 0; i < newArray.length; i++) {
    var oldItem = oldArray.find(item => item[0] === newArray[i][0]);

    if (oldItem) {
    var isModified = oldItem.some((ele, index) => ele !== newArray[i][index]);

      if (isModified) {
        modifiedItems.push(newArray[i]);
      }
    }
  }
  return modifiedItems;
}

 

syncMyGroup - 의사 코드

 

1. 액세스 토큰 발급
2. 엑셀에서 기존 데이터 기준으로 항목 추가, 삭제, 수정된 데이터 추출
3. 새 항목이 추가된 데이터는 POST 요청으로 그룹스에 해당 데이터 추가시키기 + 예외 / 성공 메시지 띄우기
4. 기존 항목이 삭제된 데이터는 DELETE 요청으로 그룹스에 해당 데이터 삭제시키기 + 예외 / 성공 메시지 띄우기
5. 기존 항목이 수정된 데이터는 PATCH 요청으로 그룹스에 해당 데이터 정보 수정시키기  + 예외 / 성공 메시지 띄우기
6. 수정 사항이 없는 경우도 처리해주기 

 

코드

/** 그룹스 목록 최신화시키기 (엑셀 데이터 -> 그룹스 데이터) */
function syncMyGroup () {

  // 🟣 1.
  var service = getService_();
   if (!service.hasAccess()) {  
    Logger.log(service.getLastError()); 
    return;
    }

  // 🟣 2.
  var addedMembers = findAddedItems();
  var deletedMembers = findDeletedItems();
  var modifiedMembers = findModifiedItems();

  // 🟣 3. 새로운 항목 처리 로직
  if (addedMembers.length > 0) {     
 
  var postUrl = `https://admin.googleapis.com/admin/directory/v1/groups/${groupKey}/members`;  
  
  addedMembers.map(member => {
    var  [email, type, role] = member;

    var requestBody = {
        email,
        role,
        type
      }

    var postOptions = {
      method : 'post',
      payload : JSON.stringify(requestBody),
      muteHttpExceptions: true, 
      headers: {
        Authorization: 'Bearer ' + service.getAccessToken(),
        'Content-type': 'application/json'
      }
    };

    var res = UrlFetchApp.fetch(postUrl, postOptions);
    Browser.msgBox(res);
    envMyGroup();

  })

  // 🟣 4. 삭제된 항목 처리 로직
  } else if (deletedMembers.length > 0) {    

  deletedMembers.map(member => {
      var  [email] = member;
      var deleteUrl = `https://admin.googleapis.com/admin/directory/v1/groups/${groupKey}/members/${email}`;  
      var deleteOptions = {
        method: 'delete',
        muteHttpExceptions: true, 
        headers: { 
          Authorization: 'Bearer ' + service.getAccessToken()
        }
      };

      var res = UrlFetchApp.fetch(deleteUrl, deleteOptions);
      Browser.msgBox(res);
      envMyGroup();
  })

  // 🟣 5. 수정된 항목 처리 로직
  } else if (modifiedMembers.length > 0) {     
  
  modifiedMembers.map(member => {
    var  [email, type, role] = member;
    var patchUrl = `https://admin.googleapis.com/admin/directory/v1/groups/${groupKey}/members/${email}`;  

    var requestBody = {
        email,
        role,
        type
      }

    var patchOptions = {
      method : 'patch',
      payload : JSON.stringify(requestBody),
      muteHttpExceptions: true, 
      headers: { 
        Authorization: 'Bearer ' + service.getAccessToken(), 
        'Content-type': 'application/json'
      }
    };

   var res = UrlFetchApp.fetch(patchUrl, patchOptions);
   Browser.msgBox(res);
    envMyGroup();

  })

  // 🟣 6.
  } else {
    Browser.msgBox('수정 사항이 없습니다.')
  }
}

 

참고

• https://developers.google.com/admin-sdk/directory/v1/guides/manage-group-members?hl=ko#get_member
  • GET) https://developers.google.com/admin-sdk/directory/reference/rest/v1/members/get?hl=ko
  • POST) https://developers.google.com/admin-sdk/directory/reference/rest/v1/members/insert?hl=ko
  • PATCH) https://developers.google.com/admin-sdk/directory/reference/rest/v1/groups/patch?hl=ko
  • DELETE) https://developers.google.com/admin-sdk/directory/reference/rest/v1/members/delete?hl=ko