Google Apps Script 사용기

서포터즈 활동을 하던 중 일거리가 들어왔다.

요구사항은 구글 폼으로 접수받은 학생들의 정보를 바탕으로 매칭해주는 간단한 일이었다.
학생들의 숙박 정보를 제공해야하기 때문에 필요한 기능은 다음과 같다.

1. 룸메이트 정보를 사전 등록한 학생들의 데이터 유효성을 검사한다.
2. 사전 등록한 학생들의 데이터가 옳바르다면, 각 짝들 끼리 같은 그룹으로 묶어준다.
3. 사전 등록 하지 않은 학생들은 성별을 고려하여 랜덤하게 같은 그룹으로 묶어준다.

이렇게 크게 세 가지의 기능이 필요했습니다.

문론 눈으로 보며 손으로 계산하며 작성할 수도 있었지만..
수많은 학생들을 일일이 검사하긴 귀찮았고, 캡스톤 등의 개발에 지쳐있어 재밌어 보였습니다. 🙄

그러던 도중 Google Apps Script 라는 기능을 찾을 수 있었습니다.
이는 구글 드라이브 내 포함되어있는 파일들을 쉽게 불러와 javascript를 통한 데이터 처리가 가능합니다.

사용방법

구글 독스의 도구바에는 확장프로그램 내 Apps Script의 기능이 있습니다.
이동하게 된다면

다음과 같은 화면을 확인할 수 있습니다. 일반적인 IDE와 유사하기에 자세한 설명은 하지 않겠습니다.
함수 기반으로 작동하기 때문에 디버그 오른쪽에서 실행할 함수를 선택해야합니다.

//드라이브 내 파일 가져오기
const sheet = SpreadsheetApp.getActiveSheet();
const rawData = sheet.getDataRange().getValues();

기본적인 사용 코드는 다음과 같습니다.
위의 문법을 통하여 파일 내 데이터를 이 차원 배열로 가져올 수 있었습니다.

  //새로운 컬럼 부여
  const newColumn = sheet.getRange(1, lastCol + 1, groupRow.length, 1);
  newColumn.setValues(groupRow.map(function(value) {
    return [value];
  }));

데이터 처리 후 위와 같이 저장할 수 있고, 새로운 컬럼을 만들어 저장하는 방식 이었습니다.
이 때에 groupRow[] 에는 값이 들어가있고 첫 번째 요소에는 Column의 타이틀이 포함되어야 합니다.

code

전체 코드는 다음과 같습니다.

//드라이브 내 파일 가져오기
const sheet = SpreadsheetApp.getActiveSheet();
const rawData = sheet.getDataRange().getValues();
//데이터 무작위 정렬
const data = shuffleArray(rawData);

//배열 랜덤 섞기
function shuffleArray(array) {
  for (let i = array.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [array[i], array[j]] = [array[j], array[i]];
  }
  return array;
}

//룸메이트 정보가 유효한지 확인
function checkRoomMate() {
//룸메이트를 구하는 유저
  const users = [];
  data.map(p => {
    if(p[8] === '지정 배정 (*동성)')
      //학번과 성별로 
      users.push({idx: p[3], sex: p[5]});
  })

  //룸메이트 조건에 맞지않은 유저 체크
  const mate = data.filter(p => {
    if(p[8] === '지정 배정 (*동성)' && !users.find(user => user.idx === p[10] && user.sex === p[12])) 
      return true;
    return false;
  })

  if(mate.length === 0) {
    console.log('룸메이트 정보가 유효합니다.');
    return true;
  } else {
    console.log('룸메이트 정보가 맞지 않는 유저가 존재합니다.');
    console.log(mate);
    return false;
  }
}

//룸메이트가 확정된 
function setRoomNumber() {
  if(!checkRoomMate()) {
    throw '룸메이트 정보가 맞지 않습니다.';
  }
  let num = 1;
  const lastCol = sheet.getLastColumn();
  const groupTitle = ["방 배정 정보"];
  const groupData = Array(data.length);

  data.map((p, i) => groupData[i] = {idx: p[0] == 'index' ? 99 : p[0], value: undefined})

  data.map((p, i) => {
    data.map((e, j) => {
      if((groupData[i].value === undefined && groupData[j].value === undefined) && p[3] !== e[3]) {
        if((p[8] === '지정 배정 (*동성)' && e[8] === '지정 배정 (*동성)') && p[3] === e[10]) {
          groupData[i].value = `방 ${Math.ceil(num)}`;
          groupData[j].value = `방 ${Math.ceil(num)}`;
          num += 1;
        } else if((p[8] === '랜덤 배정' && e[8] === '랜덤 배정') && p[5] === e[5]) {
          groupData[i].value = `방 ${Math.ceil(num)}`;
          groupData[j].value = `방 ${Math.ceil(num)}`;
          num += 1;
        }
      }
    })
  })
  //배정 완료된 데이터
  const sortedGroupData = groupData.sort((a, b) => {
    return a.idx - b.idx;
  }).map(obj => obj.value);

  //타이틀 요소 제거
  sortedGroupData.pop();
  const groupRow = groupTitle.concat(sortedGroupData);
  //새로운 컬럼 부여
  const newColumn = sheet.getRange(1, lastCol + 1, groupRow.length, 1);
  newColumn.setValues(groupRow.map(function(value) {
    return [value];
  }));

}

실제 데이터 처리를 해보니 재밌었습니다. 이를 처리하기 위해 DB Query 문법 등을 사용할 수도 있었지만, 익숙한 javascript가 더욱 작업하기 쉬웠네요.

다만 아쉬운것은 시간복잡도는 엉망일거에요 ㅎ....

ref: Google Apps Script