소개
직업과도 관련이 많아 에듀테크 스터디에 처음 참여하게 되었는데, 스터디장님께서 앱스 스크립트로 자동화하는 사례를 소개해주셨어요. 그 이야기를 들으며 제가 평소에 관심이 많았던 부동산 관련 작업에 꼭 적용해보고 싶다는 생각이 들었습니다.
저는 현재 몇몇 분들과 함께 부동산 스터디를 하고 있는데, 지역마다 다세대 빌라들을 분석하며 임장도 다닙니다. 그런데 임장 전에 반드시 해야 할 중요한 작업이 바로 그 지역 빌라들의 실거래가를 기반으로 한 다양한 비교 분석을 통해 투자할만한 가치가 있는지 판단해보는 거에요.
그러기 위해 관심있는 지역의 국토부 실거래가 데이터를 엑셀로 다운로드하고, 필터링하고, 정렬하고, 직접 일일이 계산하면서 비교해야 했습니다.
그런 상황에서 앱스 스크립트를 배우고 나니, 늘 하던 노가다 작업을 자동화할 수 있겠다는 생각이 들어 시도해보았습니다.
사용 도구
구글 스프레드 시트 : 국토부 실거래가 공개 시스템에서 관심있는 지역의 공동주택 실거래가 자료를 다운로드 합니다. => 구글 스프레드 시트로 열기
Google Apps Script : 시트의 링크를 입력 받아 데이터 필터링, 정리 및 매매가율, 전세가, 평단가 등 자동 계산 처리
ChatGPT o3-mini-high : 코드 작성 및 수정
진행 방법
국토부 실거래가 공개시스템(https://rt.molit.go.kr/)에서 관심 지역 연립/다세대 작년 실거래가 정보(엑셀)를 다운로드 합니다. 구글 스프레드 시트로 옮기고 편집 권한을 주어 공유합니다.
ChatGPT에게 앱스 스크립트 코드 요청을 합니다.
에듀테크 스터디에서 배운 것을 최대한 활용해서 프롬프트를 작성해 봤습니다.
스압 주의에요!
편집가능한 공유된 구글시트 링크를 사용자로부터 입력 받아서 [1단계 편집] 버튼을 누르면 1차적으로 시트를 편집해주고, [2단계 계산]버튼을 누르면 셀에 입력된 값들을 이용하여 계산하여 주는 html을 만들어주는 구글 앱스스크립트 코드를 만들어줘. 구글 시트의 내용은 어떤 지역의 다세대 주택의 국토부 실거래가야. 주소나 전용면적(㎡), 거래일자, 거래가격 등이 있는 부동산 관련 정보야. [사용자로부터 입력 받는 항목] 1. 편집 가능한 상태로 공유된 구글시트 url을 입력 받음. 2. 비교 기준이 되는 다세대 주택 한 곳의 '번지','건물명','전용면적(㎡)','대지면적','거래금액(만원)','층','건축년도'를 각 항목별로 입력 받음. 7개의 항목 -> 앞으로 이것을 '비교기준주택'이라 칭하겠음. [1단계 편집] 버튼을 사용자가 클릭 시 처리해야 할 프로세스 0. 현재의 시트 원본을 편집하기 전 시트 전체를 복사하여 원본을 백업 받는다. 백업 원본 시트의 이름을 '원본'으로 정하고, 오른쪽 시트에 둔다. 1. 셀의 값이 [시군구],[번지],[본번],[부번] 등의 값이 나와있는 행을 기준으로 1번 행부터 이 기준행 바로 전까지 행을 삭제 (대부분 1번 행부터 12번 행까지 삭제, 예외가 있을 수 있으니 확인요망) 2. 다음 열을 삭제 - [NO], [본번], [부번], [계약년월], [계약일], [매수자], [매도자], [도로명], [해제사유발생일], [거래유형], [중개사소재지], [등기일자], [주택유형] 1. 1행 열 끝에 다음 7개의 열 추가 -> [공시지가], [매매가율], [전세가], [갭], [전용평수], [평단가], [기타] 2. 1번 행에 대해 [보기]-[고정]-[행1개] 처리하여 고정시킴 3. 1번 행을 기준으로 필터 만들기 4. 1번 행 밑에 행을 삽입하여 사용자로터 입력받은 비교기준주택의 7가지 항목 정보를 열의 값에 맞춰 입력 후, 행의 색을 주황색으로 색칠. 이때 A열인 [시군구]열에 '★비교기준주택'이라고 입력. 5. [전용면적(㎡)] 열을 기준으로 오름차순 정렬 6. 비교기준주택의 전용면적(㎡)을 기준으로 -5 ~ +5에 해당하는 전용면적(㎡) 셀들을 주황색으로 칠함. 7. [건축년도] 열를 기준으로 오름차순 정렬 8. 비교기준주택의 건축년도를 기준으로 -5 ~ +5년에 해당하는 셀들을 주황색으로 칠함 9. [전용면적(㎡)]열과 [건축년도]열이 둘 다 주황색으로 칠해진 행만을 남기고 나머지 행은 삭제 10. 삭제 후 D열인 전용면적(㎡)열을 기준으로 오름차순으로 정렬 11. 1단계 편집이 완료되면 버튼 아래에 '시트 편집이 완료되었습니다. 공시지가를 입력해주세요.'라고 안내문구를 보여준다. [코드 스타일 요청] - 한글 주석 포함 - 상수는 상단에 별도 정의 - 모듈화된 함수 구조 - 각 기능별 분리된 함수 - API 키 등 민감정보는 상수로 분리 - 에러 처리 및 로깅 포함 - 중복 실행 방지 로직 [셀 속성 요구사항] - A 시군구열은 글자가 다 보이도록 넓이를 딱 맞춰서 넓혀준다. - 공시지가(I)열부터 평단가(N)열까지는 숫자표시로 ,를 넣어준다. - 글자 크기는 모두 11로 한다. - 공지지가, 매매가율, 전세가, 갭, 평단가는 소수점이 없이 일의자리까지 표시한다. - A열인 시군구열과 O열인 기타열을 제외한 모든 열은 텍스트 오른쪽 정렬을 한다.그랬더니 Code.gs, ui.html 코드를 줍니다.
// Code.gs // ================================ // 상수 및 전역 변수 정의 // ================================ const TARGET_SHEET_NAME = "Sheet1"; // 작업 대상 시트명 (필요에 따라 변경) const ORANGE_COLOR = "#FFA500"; // 주황색 (하이라이트용) const MIN_ORANGE_COUNT = 20; // 하이라이트 셀 최소 개수 기준 (20개 미만이면 범위 확대) // ================================ // 웹앱 진입점 (HTML UI 호출) // ================================ function doGet() { return HtmlService.createHtmlOutputFromFile("ui"); } // ================================ // [1단계 편집] 처리 함수 // ================================ function editSheet(sheetUrl, baselineData) { var lock = LockService.getScriptLock(); try { if (!lock || !lock.tryLock(30000)) { throw new Error("다른 작업이 실행 중입니다. 잠시 후 다시 시도해주세요."); } // 편집 가능한 구글시트 열기 var ss = SpreadsheetApp.openByUrl(sheetUrl); var sheet = ss.getActiveSheet(); // ───────────────────────────── // 0. 백업 단계 : 현재 시트 전체를 복사하여 백업. 백업 시트 이름을 "원본"으로 지정하고, 오른쪽(맨 끝)으로 이동. var backupName = "원본"; var existingBackup = ss.getSheetByName(backupName); if (existingBackup) { ss.deleteSheet(existingBackup); } var backupSheet = sheet.copyTo(ss); backupSheet.setName(backupName); ss.setActiveSheet(backupSheet); ss.moveActiveSheet(ss.getNumSheets()); // 작업 진행을 위해 원래 편집할 시트로 다시 활성화 ss.setActiveSheet(sheet); // ───────────────────────────── // 1. 헤더 행 찾기 : "시군구", "번지", "본번", "부번" 중 하나라도 포함되면 해당 행을 헤더로 간주 var headerRowIndex = findHeaderRow(sheet); if (headerRowIndex <= 1) { throw new Error("헤더 행을 찾지 못했습니다."); } // 헤더행 바로 전까지 삭제 (예: 1행부터 headerRowIndex-1행까지) sheet.deleteRows(1, headerRowIndex - 1); // 2. 불필요한 열 삭제 var columnsToDelete = ["NO", "본번", "부번", "계약년월", "계약일", "매수자", "매도자", "도로명", "해제사유발생일", "거래유형", "중개사소재지", "등기일자", "주택유형"]; deleteColumnsByHeaders(sheet, columnsToDelete); // 3. 헤더 행 끝에 새 열 7개 추가 var newHeaders = ["공시지가", "매매가율", "전세가", "갭", "전용평수", "평단가", "기타"]; appendNewColumns(sheet, newHeaders); // 4. 헤더 행 고정 (행 1 고정) sheet.setFrozenRows(1); // 5. 헤더 행에 필터 생성 sheet.getRange(1, 1, 1, sheet.getLastColumn()).createFilter(); // 6. 비교기준주택 데이터를 삽입하기 전에, // A열(시군구열)에 "★비교기준주택" 텍스트는 비교 기준 데이터를 삽입할 때 처리하도록 함. // 헤더 바로 아래(새 행을 삽입하여) 비교기준주택 정보를 입력하고, A열은 "★비교기준주택"으로 설정. insertBaselineRow(sheet, baselineData); // 7. "전용면적(㎡)" 열 기준 오름차순 정렬 sortByColumn(sheet, "전용면적(㎡)", true); // 8. 비교기준 전용면적 기준 ±5 범위 내 셀에 주황색 배경 적용 // (만약 해당 셀의 수가 20개보다 적으면, ±8 범위로 확대 적용) highlightRange(sheet, "전용면적(㎡)", parseFloat(baselineData.jeonyongmyeonsuk), 5, 8); // 9. "건축년도" 열 기준 오름차순 정렬 sortByColumn(sheet, "건축년도", true); // 10. 비교기준 건축년도 기준 ±5 범위 내 셀에 주황색 배경 적용 // (만약 해당 셀의 수가 20개보다 적으면, ±6 범위로 확대 적용) highlightRange(sheet, "건축년도", parseInt(baselineData.geonchukyeon), 5, 6); // 11~13. "전용면적(㎡)"와 "건축년도" 두 열 모두 주황색으로 칠해진 행만 남기도록 (검사 대상: 행 2부터 마지막 행까지) filterRowsByColor(sheet, ["전용면적(㎡)", "건축년도"], ORANGE_COLOR); return "시트 편집이 완료되었습니다. 공시지가를 입력해주세요."; } catch (e) { Logger.log(e); throw new Error("1단계 편집 중 오류: " + e.message); } finally { if (lock) { lock.releaseLock(); } } }// ui.html <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>부동산 시트 가공 및 계산 도구</title> <style> /* 기본 바탕 및 폰트 설정 */ body { font-family: 'Noto Sans KR', sans-serif; background: #f7f9fc; margin: 0; padding: 20px; } h2 { text-align: center; color: #333; } .container { max-width: 600px; background: #fff; margin: 20px auto; padding: 20px; border-radius: 8px; box-shadow: 0 2px 8px rgba(0,0,0,0.15); } label { display: block; margin-bottom: 5px; font-weight: bold; color: #555; } input { width: 100%; padding: 10px; margin-bottom: 15px; border: 1px solid #ddd; border-radius: 4px; font-size: 14px; } .button-container { text-align: center; margin-top: 15px; } button { background-color: #007bff; color: #fff; border: none; padding: 12px 20px; margin: 5px; border-radius: 4px; cursor: pointer; font-size: 14px; transition: background-color 0.3s; } button:hover { background-color: #0056b3; } #status { margin-top: 20px; text-align: center; font-weight: bold; color: #333; } </style> </head> <body> <div class="container"> <h2>부동산 시트 가공 및 계산 도구</h2> <label for="sheetUrl">구글시트 URL (편집 가능 공유 상태)</label> <input type="text" id="sheetUrl" placeholder="https://docs.google.com/spreadsheets/..." /> <h3>비교기준주택 정보 입력</h3> <label for="bunji">번지</label> <input type="text" id="bunji" placeholder="예: 123" /> <label for="gunmul">건물명</label> <input type="text" id="gunmul" placeholder="예: 한빛아파트" /> <label for="jeonyongmyeonsuk">전용면적(㎡)</label> <input type="text" id="jeonyongmyeonsuk" placeholder="예: 66.71" /> <label for="dajimen">대지권면적(㎡)</label> <input type="text" id="dajimen" placeholder="예: 85.23" /> <label for="georaegeum">거래금액(만원)</label> <input type="text" id="georaegeum" placeholder="예: 35000000" /> <label for="cheung">층</label> <input type="text" id="cheung" placeholder="예: 5" /> <label for="geonchukyeon">건축년도</label> <input type="text" id="geonchukyeon" placeholder="예: 2015" /> <div class="button-container"> <button onclick="runEdit()">1단계 편집</button> <button onclick="runCalculate()">2단계 계산</button> </div> <div id="status"></div> </div> <script> // 비교기준주택 데이터 취합 function getBaselineData() { return { bunji: document.getElementById("bunji").value, gunmul: document.getElementById("gunmul").value, jeonyongmyeonsuk: document.getElementById("jeonyongmyeonsuk").value, dajimen: document.getElementById("dajimen").value, georaegeum: document.getElementById("georaegeum").value, cheung: document.getElementById("cheung").value, geonchukyeon: document.getElementById("geonchukyeon").value }; } // [1단계 편집] 버튼 클릭 시 function runEdit() { var sheetUrl = document.getElementById("sheetUrl").value; var baselineData = getBaselineData(); document.getElementById("status").innerText = "1단계 편집 진행 중..."; google.script.run.withSuccessHandler(function(msg) { document.getElementById("status").innerText = msg; }).withFailureHandler(function(err) { document.getElementById("status").innerText = "오류: " + err.message; }).editSheet(sheetUrl, baselineData); } // [2단계 계산] 버튼 클릭 시 function runCalculate() { var sheetUrl = document.getElementById("sheetUrl").value; var baselineData = getBaselineData(); document.getElementById("status").innerText = "2단계 계산 진행 중..."; google.script.run.withSuccessHandler(function(msg) { document.getElementById("status").innerText = msg; }).withFailureHandler(function(err) { document.getElementById("status").innerText = "오류: " + err.message; }).calculateSheet(sheetUrl, baselineData); } </script> </body> </html>웹앱 배포
스크립트 편집기에서 [배포]-[새 배포] : 웹 앱으로, 모든 사용자 권한으로 배포합니다.
앱스스크립트 웹 앱 주소를 복사해서 크롬 주소창에 입력하니 아래와 같은 페이지가 나옵니다.
실거래가 원본 데이터가 들어가있는 구글 시트의 공유 url을 맨 위에 넣고, 내가 찜해둔 빌라의 정보를 입력하면 구글 시트에 새로운 행을 추가하여 찜해둔 빌라의 정보를 추가합니다.
[1단계 편집] [2단계 계산] 2개의 파란 버튼을 클릭하게 되면 제가 했던 노가다 작업들을 자동으로 해줍니다.
더 해보고 싶은 것
이번 자동화 도구를 통해 임장 전 사전 데이터 분석의 효율을 크게 높일 수 있었지만, 여전히 아쉬운 부분이 하나 있습니다. 바로 임장 중 수집하는 현장 정보의 정리와 관리입니다.
분석을 통해 추려낸 빌라들을 실제로 방문하면, 주차 공간은 충분한지, 건물 관리 상태는 어떤지, 뷰가 어떤지 등 현장에서만 확인할 수 있는 중요한 정보들을 지금까지는 종이나 휴대폰 메모장에 적어두고, 집에 돌아와서 다시 정리하곤 했는데요,
AppSheet와 구글 시트를 연동해, 임장 중 바로 스마트폰으로 현장 정보를 입력하고,
그 내용이 자동으로 구글 시트에 정리되도록 하는 모바일 임장 기록 시스템을 만들어보고 싶습니다.
결과와 배운 점
처음 자동화된 시트 결과를 봤을 때, 그동안 수작업으로 하던 기억들이 스쳐 지나가며 좀 짜릿하더라고요. '이제 이거 하나면 임장 전 데이터 준비가 훨씬 빨라지겠구나~' 하는 생각과, 같이 부동산 공부하는 분들과 공유해서 함께 써보고 싶은 마음에 살짝 설레입니다.^^
이번 스터디가 제 첫 참여라 낯설고 두리번거리기만 했지만,
그럼에도 불구하고 "자동화는 선택이 아니라 필수다"라는 것을 온몸으로 느꼈습니다.
반복되던 노력을 덜어주고, 더 중요한 분석에 집중할 수 있도록 도와주는 이 경험이 정말 강력했습니다.
도움 받은 글 (옵션)
https://www.gpters.org/research/post/lecture-business-automation-solve-x84TxJaw9LmOHXX
https://www.gpters.org/research/post/classes-more-fun-efficiently-i5rh1IToY9PnnFk