[업무기록] google appscript 활용한 구글 스프레드시트 자동화(이진 탐색 알고리즘)

 

 

최근 맡았던 업무 중에 쿼리문을 매주 한 번씩 주기적으로 돌린 다음, 구글 스프레드시트로 업데이트 하는 과정 전체를 자동화하는 일감이 있었다. 어제 최종 완료되었는데 사실 중간에 추가 요청이 들어와서 전체 코드를 갈아엎었다.

 

<최초 진행 과정>

(1) googleapiclient를 활용해 google bigquery와 연동하여 주피터 환경에서 데이터를 불러오기

(2) gspread를 활용해 구글 스프레드시트로 데이터 업데이트(매주 월요일에 최근 1주 데이터 업데이트)

(3) 해당 워크시트에서 google appscript를 활용해 일정 기간 동안의 데이터 삭제하는 기능 구현(버튼 클릭하면 스크립트 실행)

 

이렇게 해서 전달했는데 추가적인 요청사항이 생겨서 전체 과정을 아예 수정했다.

 

<최종 진행 과정>

(1) googleapiclient를 활용해 google bigquery와 연동하여 주피터 환경에서 데이터를 불러오기

(2) googleapiclient를 활용해 반기별로 새로운 워크시트 생성(해당 월이 1월 혹은 7월일 경우 '해당연도-상(하)반기'로 워크시트명 지정되도록 코드 작성)

(3) gspread 활용하여 매주 최근 1주일 데이터 업데이트 되도록 코드 작성

(google appscript 활용 X)

 

최종적으로는 google appscript를 사용하지 않았다. 다만, 최초 진행 과정에서 활용했던 appscript 코드를 기록해두려고 한다. 이유는 일단 처음 다뤄보는 것이었기에 익숙하지 않았지만 재미있었고, 결정적으로는 코드의 성능을 최적화하는 과정이 개인적으로 의미있었다고 생각했다. 총 3차례에 걸쳐 코드를 수정하였다.

 

 

---

첫 번째 시도

function delete_5Month() {
    var sht = SpreadsheetApp.getActive().getSheetByName("시트1");
    // console.log(sht.getLastRow())

    var arr = sht.getRange(2, 2, sht.getLastRow()).getValues();
    var currently_date = new Date(sht.getRange(2, 2).getValue());
    var currently_month = currently_date.getMonth() + 1
    var currently_year = currently_date.getYear()

    for (var i=arr.length; i>=2; i--) {
        var date = new Date(sht.getRange(i, 2).getValue());
        var month = date.getMonth() + 1
        var year = date.getYear()
        if (year == currently_year) {
            if (month < currently_month - 5) {
                sht.deleteRow(i);
            }
        } else if (year < currently_year) {
            sht.deleteRow(i);
        }
    }
}

 

구글 앱스크립트는 구글 스프레드시트 내에서 작성할 수 있는 자바스크립트 기반의 편집기라고 보면 된다. 자바스크립트는 예전에 프론트엔드 개발쪽을 공부할 때 조금 해뒀던 터라 큰 어려움 없이 (버벅거리기는 했지만...) 진행할 수 있었다.

 

앱스크립트로 스프레드시트내에 구현하려고 했던 기능은 <이번 달로부터 5개월 이전의 데이터는 삭제>였다. 위 코드를 보면 현재 시트의 전체 행의 개수를 카운트 한 다음에 마지막 행에서부터 거꾸로 위로 올라오면서 특정 조건에 부합하는 데이터들을 삭제하게끔 했다. 물론, 시트의 데이터들은 날짜를 기준으로 내림차순 정렬 상태였다.

 

다만, 문제는 속도였다. for문을 통해 한 줄씩 올라오다보니까 수 만 행에 달하는 데이터들을 도는 게 엄청난 시간 낭비였다. 알고리즘으로 따지면 순차 탐색 알고리즘이었다.

 

 

최종 수정 코드

알고리즘 공부를 열심히 했던 것은 아니었지만 문득 이진 탐색 알고리즘이 떠올랐다. 만약 내가 알고리즘 공부에 충실했다면 처음부터 이진 탐색 알고리즘으로 코드를 짰을텐데 아쉽다. 그래도 늦게나마 기억이 떠올라서 코드를 수정했다.

 

function delete_5month() {
    var sht = SpreadsheetApp.getActive().getSheetByName("시트1");
    var arr = sht.getRange(2, 2, sht.getLastRow()).getValues();
    const currently_date = new Date(arr[0]);
    var currently_month = currently_date.getMonth() + 1;
    var currently_year = currently_date.getYear();
    let startIndex = 0;
    let endIndex = arr.length - 1;

    while (startIndex < endIndex) {
        const middleIndex = startIndex + Math.floor((endIndex - startIndex) / 2);
        if (new Date(arr[middleIndex]) > new Date(new Date(arr[0]).setDate(new Date(arr[0]).getDate()-150))) {
            console.log('not delete')
            console.log(new Date(arr[middleIndex]), new Date(new Date(arr[0]).setDate(new Date(arr[0]).getDate()-150)))
            startIndex = middleIndex + 1;
        } 
        else if (new Date(arr[middleIndex]) <= new Date(new Date(arr[0]).setDate(new Date(arr[0]).getDate()-150))) {
            console.log('delete rows')
            console.log(new Date(arr[middleIndex]), new Date(new Date(arr[0]).setDate(new Date(arr[0]).getDate()-150)))
            sht.deleteRows(middleIndex, sht.getLastRow() - middleIndex + 1);
            endIndex = middleIndex - 1;
        }
    }
}

 

전체 행의 절반 지점의 행을 middleIndex로 정하고, middleIndex의 날짜가 현재로부터 5개월 이전인 시점보다 이후이면 'not delete'를 출력하고 데이터를 삭제하지 않는다. 그리고 startIndex가 middleIndex + 1이 되고 다시 while문을 돌린다. while문을 다시 돌리면서 middleIndex는 변경된 startIndex와 변경되지 않았던 endIndex의 절반 지점의 행으로 변경되고, 아까와 같은 방식으로 조건문을 돌린다. 만약, middleIndex의 날짜가 현재로부터 5개월 이전인 시점보다 이전이면 'delete rows'를 출력하고 데이터를 삭제한다. 이때, 삭제되는 데이터는 middleIndex로부터 마지막 행까지의 데이터다.(이렇게 하려면 코드 실행 전에 모든 데이터가 날짜 내림차순으로 정렬되어 있어야 한다)

 

그리고 endIndex는 middleIndex - 1이 되고, 다시 while문을 돌린다. 이 과정은 startIndex가 endIndex보다 작을 때 계속해서 진행된다.

 

결과적으로는 추가적인 요청이 들어와서 앱스크립트를 활용하지 않게 되었지만 속도를 위해 코드를 개선하는 과정이 즐거웠다.