Apps Script로 엑셀 데이터와 Google Groups 자동화하기 (3) 액셀 시트로 데이터 가져오기

 

getMyGroup 함수를 만들어서 그룹스에 있는 멤버 목록 데이터를 액셀 시트로 가져오는 기능을 구현했다.

 

getMyGroup - 의사 코드

 

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

 

코드

getMyGroup.gs

/** 그룹스 목록 가져오기 (그룹스 데이터 -> 엑셀 데이터) */
function getMyGroup() { 

 // 🟣 1.
  var service = getService_();            
  if (!service.hasAccess()) {  
    Logger.log(service.getLastError()); 
    return;
    }
 
  // 🟣 2.
  var workingSheet = SpreadsheetApp.getActive();
  var range = "H1"  /** groupKey로 쓰이는 해당 그룹 메일 주소는 H1에 위치해야 합니다. */
  var groupKeyArray = workingSheet.getRange(range).getValues();

  // 🟣 3.
  if (groupKeyArray.length > 0 && Array.isArray(groupKeyArray[0]) && groupKeyArray[0].length > 0) {
    var extractedString = groupKeyArray[0][0];
    var groupKey = extractedString; 
  } 

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

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

  // 🟣 6.
  const startingRow = 20;

  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);
  })

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

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

 

response로는 아래와 같은 객체 데이터가 배열에 담겨서 온다.

 

리팩토링

2~3 groupKey를 추출하는 부분은 나중에 다른 함수에서도 중복되어 쓰이기 때문에 아예 oauth.gs 파일에 옮겨서 전역으로 빼두었다. 따로 import해오지 않아도 전역에서 사용이 가능하다.

 

oauth.gs

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

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

/** (환경설정에 해당되는 부분입니다. getService_ 함수명 뒤에 있는 언더바_ 부분 절대 지우지 마세요!) */
function getService_() {

var USER_EMAIL = PropertiesService.getScriptProperties().getProperty('user_email');
var PRIVATE_KEY = PropertiesService.getScriptProperties().getProperty('private_key').replace(/\\n/g, '\n');
var CLIENT_EMAIL = PropertiesService.getScriptProperties().getProperty('client_email');

  return OAuth2.createService('GoogleDrive:' + USER_EMAIL)
  .
  .
  .
  .
  .

 

 

참고

• https://developers.google.com/admin-sdk/directory/reference/rest/v1/members/get?hl=ko
• https://developers.google.com/admin-sdk/directory/v1/guides/manage-group-members?hl=ko#get_member
• https://developers.google.com/admin-sdk/directory/reference/rest/v1/groups/get?hl=ko
• https://www.autooffice.io/knowhow/write-data-to-specific-sheet

시트를 지정해서 데이터를 활용하기