Apps Script로 엑셀 데이터와 Google Groups 자동화하기 (4) 최종 + 리팩토링

최종 기능 구현

 

추가된 기능

• 성공 / 에러 처리 메시지 정교화
• 최신 업데이트 시간 추가
• 데이터 공백 없애는 처리 (공백 포함되면 에러가 났음)
• MEMBER 등 소문자로 입력해도 대문자로 처리 (소문자로 입력되면 에러가 났음)
• 문서 작성

 

문서 작성

 

구현 화면 1

 

그룹스 -> 엑셀로 데이터 가져오기

• 관리하는 그룹이 많았기 때문에 개별 그룹에 해당되는 메일 주소를 groupKey로 설정했다.
• 데이터 요청이 성공적으로 처리되면 해당 groupKey를 출력하여 어떤 그룹의 데이터를 가져왔는지 확인할 수 있도록 했다.

 

구현 화면 2

엑셀 -> 그룹스로 데이터 업데이트하기

• 예외처리 (엑셀에 수정 내용이 없을 때 출력)

 

구현 화면 3

엑셀 -> 그룹스로 데이터 업데이트하기

• 신규 등록했을 때 성공 메시지 출력
• 실패하는 경우 에러 response 메시지 출력 (유효하지 않는 값 등 무슨 에러인지 확인)
• 아래 오른쪽 그림은 그룹스에 회원이 등록된 모습

 

 

구현 화면 4

엑셀 -> 그룹스로 데이터 업데이트하기

• 기존 회원을 삭제했을 때 성공 메시지 출력
• 실패하는 경우 에러 response 메시지 출력 (유효하지 않는 값 등 무슨 에러인지 확인)
• 아래 오른쪽 그림은 그룹스에 회원이 삭제된 모습

 

구현 화면 5

엑셀 -> 그룹스로 데이터 업데이트하기

• 기존 회원의 정보를 수정했을 때 성공 메시지 출력
• 실패하는 경우 에러 response 메시지 출력 (유효하지 않는 값 등 무슨 에러인지 확인)
• 아래 오른쪽 그림은 그룹스에 'dd@naver.com' 회원의 '역할' 정보가 '회원' -> '관리자'로 변경된 모습

 

리팩토링 (var, 함수 선언식)

 

코드 작성하면서 어떤 레퍼런스를 봐도 다 함수 선언식, var로 변수 선언이 되어 있어서 당황스러웠다. apps script는 이번이 처음이었기 때문에 검증된 레퍼런스로 우선 기능 구현에 집중했다. 따라서, 구현이 완료된 지금 여유롭게 이것저것 조금씩 수정해가며 리팩토링을 해보았다.

 

Apps Script의 특징

 

1. 2020년 2월부터 Apps Script에서 ES6 문법이 사용이 가능해졌다.

 

한때 Apps Script에서 const를 사용하면 제대로 기능이 작동하지 않은 것으로 보인다. (아래 링크에서 발췌)

• 링크 포스팅의 문제점은 언제 작성된 글인지 포스팅한 날짜가 안 보인다는 것이다.
• (최신 정보 여부가 중요한 만큼, 개발 블로그 포스팅에는 정확한 날짜가 있어야 한다고 생각한다.)

https://ramblings.mcpher.com/gassnippets2/apps-script-const-scoping-problems/

Apps Script const scoping problems - Desktop Liberation

 

크롬과 같은 V8엔진이 Apps script에도 장착이 되면서 화살표 함수, const와 let 등으로 변수 선언이 가능해졌다.

• 따라서 내가 이해한 바로는 인터넷에 있는 레퍼런스들이 var을 쓰는 이유는 그저 레거시 코드였기 때문이다.
• var을 써야하는 이유가 없기 때문에 리팩토링을 해야 하는 것으로 결론이 난다.

 

https://www.benlcollins.com/apps-script/apps-script-v8-runtime/

Apps Script V8 Runtime Explained For Non-Professional Developers

https://ramblings.mcpher.com/apps-script/apps-script-v8/javascript-v8-variable-scopes/#Const_redclaration

JavaScript V8 variable scopes - Desktop Liberation

 

 

 

 

2. import를 하지 않아도 된다.

 

아래와 같이 utils.gs에 작성된 findAddedItems를 syncMyGroup.gs 파일에서 사용하고 있는데 import나 require를 쓰지 않았다.

• 즉, 파일은 편의상 분리되어 있지만 전역에서 다 같이 쓰이고 있다. 

 

 

리팩토링 코드

 

• 코드 내용은 크게 달라지지 않았기 때문에 생략한다.
• 추가된 부분만 주로 주석처리해두었다.
• 만약에 엑셀로 변경되는 데이터가 많은 경우에는 성공 / 실패 메시지 출력 로직을 변경하는 것이 좋다.
  (100개를 수정하면 메시지 100번 뜨는 상황이 생긴다.)

 

getGroupKey.gs

자주 쓰이는 groupKey 추출하기 로직을 함수로 빼두었다.

const getGroupKey = () => {

const groupKeySheet = SpreadsheetApp.getActive();
const groupKeyrange = "H1"  /** groupKey로 쓰이는 해당 그룹 메일 주소는 H1에 위치해야 합니다. */
const groupKeyArray = groupKeySheet.getRange(groupKeyrange).getValues();

if (groupKeyArray.length > 0 && Array.isArray(groupKeyArray[0]) && groupKeyArray[0].length > 0) {
  const extractedString = groupKeyArray[0][0];
  const groupKey = extractedString;
  return groupKey;
} 

  return null;
}

 

syncMyGroup.gs

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

  const service = getService_();
   if (!service.hasAccess()) {  
    Logger.log(service.getLastError()); 
    return;
    }

  const groupKey = getGroupKey();
  
  const addedMembers = findAddedItems();
  const deletedMembers = findDeletedItems();
  const modifiedMembers = findModifiedItems();

  if (addedMembers.length === 0 && deletedMembers.length === 0 && modifiedMembers.length === 0 ) {
    Browser.msgBox('수정 사항이 없습니다.')   // 🟣 예외 처리 early exit 패턴으로 변경 
    return;
  }

  if (addedMembers.length > 0) {    
 
  const postUrl = `https://admin.googleapis.com/admin/directory/v1/groups/${groupKey}/members`;  
  
  addedMembers.map(member => {
    const [email, type, role] = member;

    const requestBody = {
        email : email.replace(/ /g,""),
        role: role.toUpperCase().replace(/ /g,""), // 🟣 공백 없애고, 소문자 -> 대문자로 변경
        type: type.toUpperCase().replace(/ /g,"")
      }

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

    const response = UrlFetchApp.fetch(postUrl, postOptions);
    const { email: postEmail } = JSON.parse(response); 

    if(postEmail) {
      Browser.msgBox(`${postEmail} 회원을 신규 등록했습니다.`); // 🟣 성공했을 때 메시지
    } else {
      Browser.msgBox(response); // 🟣 실패했을 때 메시지
    }
  })

  } else if (deletedMembers.length > 0) {      

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

      const response = UrlFetchApp.fetch(deleteUrl, deleteOptions);
      const messageData = response.getContentText();
     
     if(messageData === '') {
      Browser.msgBox(`${email} 회원이 삭제 되었습니다.`); // 🟣 성공했을 때 메시지 
     } else {
      Browser.msgBox(response); // 🟣 실패했을 때 메시지
     }
    // envMyGroup();
  })

  } else if (modifiedMembers.length > 0) {     
  
  modifiedMembers.map(member => {
    const [email, type, role] = member;
    const patchUrl = `https://admin.googleapis.com/admin/directory/v1/groups/${groupKey}/members/${email}`;  

    const requestBody = {
        email : email.replace(/ /g,""),
        role: role.toUpperCase().replace(/ /g,""),
        type: type.toUpperCase().replace(/ /g,"")
      }

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

    const response = UrlFetchApp.fetch(patchUrl, patchOptions);
    const { role: patchRole } = JSON.parse(response); 

    if(patchRole) {
      Browser.msgBox(`${email} 회원의 역할을 ${patchRole}로 수정했습니다.`); // 🟣 성공했을 때 메시지
    } else { 
      Browser.msgBox(response); // 🟣 실패했을 때 메시지
    }

  })
  }  
    envMyGroup();
    
    const formattedDate = formatDate(new Date()); // 🟣 업데이트 날짜 추출해서 L1 시트에 값 입력
    const workingSheet = SpreadsheetApp.getActive();
    workingSheet.getRange('L1').setValue(formattedDate);
}

 

getMyGroup.gs

• 위에서 수정된 코드와 비슷하다

const groupKey = getGroupKey();

/** 그룹스 목록 가져오기 (그룹스 데이터 -> 엑셀 데이터) */
const getMyGroup = () => { 
  const service = getService_();
  if (!service.hasAccess()) {  
    Logger.log(service.getLastError()); 
    return;
    }

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

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

  Browser.msgBox(`${groupKey}에 해당되는 데이터를 가져왔습니다.`);

  const workingSheet = SpreadsheetApp.getActive();
  const startingRow = 2;

  members.map((member, index) => {
    const workingRow = startingRow + index;
    workingSheet.getRange(`B${workingRow}`).setValue(groupKey);
    workingSheet.getRange(`C${workingRow}`).setValue(member.email);
    workingSheet.getRange(`D${workingRow}`).setValue(member.type);
    workingSheet.getRange(`E${workingRow}`).setValue(member.role);
  })

  const clearFromThisRow = startingRow + members.length;
  const range = `A${clearFromThisRow}:E`
  workingSheet.getRange(range).setValue('');

  const savedMembers = members.map(member => [
    member.email.replace(/ /g,""), 
    member.type.toUpperCase().replace(/ /g,""), 
    member.role.toUpperCase().replace(/ /g,"")
    ])
  PropertiesService.getScriptProperties().setProperties({'memberlist': JSON.stringify(savedMembers)});

  const formattedDate = formatDate(new Date());
  workingSheet.getRange('P1').setValue(formattedDate);
  }


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

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

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

  const savedMembers = members.map(member => [ 
    member.email.replace(/ /g,""), 
    member.type.toUpperCase().replace(/ /g,""), 
    member.role.toUpperCase().replace(/ /g,"")
    ])
  PropertiesService.getScriptProperties().setProperties({'memberlist': JSON.stringify(savedMembers)});

}