구글 시트로 건물번호 자동 검색, 도로명주소와 지번주소 변환

티스토리 뷰

카테고리 없음

구글 시트로 건물번호 자동 검색, 도로명주소와 지번주소 변환

다음카페 2026. 8. 2. 00:24

구글 시트로 건물번호 자동 검색하고 도로명주소·지번주소 변환하는 방법

주소 목록을 관리하다 보면 도로명주소에서 건물번호를 분리하거나, 지번주소를 도로명주소로 변환해야 하는 경우가 많습니다. 주소가 몇 건이라면 직접 검색할 수 있지만 수십 건 이상이면 구글 시트와 Apps Script를 활용해 자동화하는 것이 효율적입니다.

구글 시트의 기본 함수만으로도 주소에 포함된 건물번호를 추출할 수 있으며, 공공 주소 검색 API를 연동하면 도로명주소와 지번주소, 우편번호, 건물관리번호 등을 자동으로 불러올 수 있습니다.

1. 도로명주소 건물번호와 지번의 차이

도로명주소의 건물번호는 도로명을 기준으로 건물에 부여된 번호입니다. 예를 들어 ‘서울특별시 중구 세종대로 110’에서 도로명은 세종대로이고 건물번호는 110입니다.

건물번호가 ‘110-2’처럼 표시된 경우 앞의 110은 건물본번이고 뒤의 2는 건물부번입니다. 부번이 없는 주소는 건물본번만 사용합니다.

지번주소는 토지에 부여된 번호를 기준으로 작성됩니다. 예를 들어 ‘서울특별시 중구 태평로1가 31’에서 31은 지번본번입니다. 산지번은 ‘산 10-2’처럼 산이라는 문자가 함께 표시될 수 있습니다.

도로명주소와 지번주소는 서로 기준이 다르기 때문에 단순히 숫자만 바꿔서는 변환할 수 없습니다. 정확한 변환을 위해서는 주소 검색 데이터나 주소 API를 사용해야 합니다.

2. 구글 시트에서 건물번호만 추출하는 방법

A2 셀에 도로명주소가 입력되어 있다고 가정하면 REGEXEXTRACT 함수를 사용해 마지막에 있는 건물번호를 추출할 수 있습니다.

=IFERROR(REGEXEXTRACT(A2,"(\d+(?:-\d+)?)\s*(?:,|$)"),"")

예를 들어 A2 셀에 ‘전라남도 여수시 시청로 1’이 입력되어 있다면 결과는 1로 표시됩니다. ‘서울특별시 강남구 테헤란로 123-4’라면 123-4가 추출됩니다.

주소 뒤에 건물명이나 상세주소가 붙어 있다면 건물번호 바로 뒤의 내용을 기준으로 추출해야 합니다. 주소 형식이 일정하지 않은 경우에는 먼저 괄호 안의 건물명이나 동·호수 정보를 제거하는 것이 좋습니다.

=TRIM(REGEXREPLACE(A2,"\([^)]*\)",""))

이 함수는 주소에 포함된 괄호와 괄호 안의 내용을 제거합니다. 정리된 주소를 별도의 열에 만든 다음 건물번호 추출 수식을 적용하면 오류를 줄일 수 있습니다.

3. 정규식을 이용한 본번·부번 분리

건물번호가 123-4처럼 본번과 부번으로 구성되어 있다면 두 값을 각각 다른 열에 분리할 수 있습니다. B2 셀에 건물번호가 있다고 가정하면 건물본번은 다음 수식으로 추출합니다.

=IFERROR(VALUE(REGEXEXTRACT(B2,"^\d+")),"")

건물부번은 하이픈 뒤의 숫자를 추출하면 됩니다.

=IFERROR(VALUE(REGEXEXTRACT(B2,"-(\d+)$")),0)

건물번호가 123이면 건물본번은 123, 건물부번은 0으로 표시됩니다. 건물번호가 123-4라면 본번은 123, 부번은 4로 나뉩니다.

한 개의 수식으로 두 열에 분리하려면 SPLIT 함수를 사용할 수 있습니다.

=SPLIT(B2,"-")

다만 부번이 없는 주소는 값이 하나만 생성되므로 데이터베이스 업로드용 자료라면 IFERROR 함수로 부번을 0으로 처리하는 것이 안전합니다.

4. 주소 검색 API 준비 방법

구글 시트에서 도로명주소와 지번주소를 정확하게 변환하려면 주소정보를 제공하는 공공 API의 인증키가 필요합니다. 주소 검색 서비스에서 개발자용 API 사용을 신청한 뒤 승인된 인증키를 발급받아야 합니다.

API 신청 시에는 구글 시트와 Apps Script를 이용해 사내 주소자료 또는 개인 주소자료를 정리한다는 용도로 입력할 수 있습니다. 인증키가 발급되면 외부에 공개하지 말고 Apps Script의 별도 변수나 스크립트 속성에 보관하는 것이 좋습니다.

주소 검색 결과에서는 일반적으로 다음과 같은 정보를 받을 수 있습니다.

· 전체 도로명주소

· 전체 지번주소

· 우편번호

· 시도와 시군구

· 읍면동과 법정동

· 도로명

· 건물본번과 건물부번

· 지번본번과 지번부번

· 건물관리번호

한 개의 주소 검색어에 여러 결과가 나올 수 있으므로 검색 결과의 첫 번째 항목을 무조건 사용하는 것보다 시군구와 건물명 등을 함께 비교해야 합니다.

5. Apps Script로 주소 자동 검색하기

구글 시트 상단의 확장 프로그램 메뉴에서 Apps Script를 선택하면 스크립트 편집기를 열 수 있습니다. 주소 검색 API를 호출하는 사용자 함수를 만들면 일반 시트 함수처럼 사용할 수 있습니다.

function ADDRESS_SEARCH(keyword, type) {
  if (!keyword) return "";

  var apiKey = "발급받은_인증키";
  var url = "주소검색_API_요청주소"
    + "?confmKey=" + encodeURIComponent(apiKey)
    + "&currentPage=1"
    + "&countPerPage=10"
    + "&keyword=" + encodeURIComponent(keyword)
    + "&resultType=json";

  try {
    var response = UrlFetchApp.fetch(url, {
      method: "get",
      muteHttpExceptions: true
    });

    var data = JSON.parse(response.getContentText());
    var list = data.results.juso;

    if (!list || list.length === 0) return "검색 결과 없음";

    var item = list[0];

    if (type === "도로명") return item.roadAddr;
    if (type === "지번") return item.jibunAddr;
    if (type === "우편번호") return item.zipNo;
    if (type === "건물본번") return item.buldMnnm;
    if (type === "건물부번") return item.buldSlno;
    if (type === "건물관리번호") return item.bdMgtSn;

    return item.roadAddr;
  } catch (e) {
    return "오류: " + e.message;
  }
}

위 코드에서 ‘발급받은_인증키’와 ‘주소검색_API_요청주소’ 부분은 실제로 사용하는 주소 API 정보에 맞게 입력해야 합니다.

스크립트를 저장한 후 처음 실행할 때는 외부 서비스 연결 권한을 승인해야 합니다. 권한 승인이 끝나면 구글 시트에서 사용자 함수를 사용할 수 있습니다.

6. 도로명주소와 지번주소 자동 변환

A2 셀에 지번주소가 입력되어 있다면 다음과 같이 도로명주소를 불러올 수 있습니다.

=ADDRESS_SEARCH(A2,"도로명")

도로명주소를 지번주소로 변환하려면 두 번째 인수를 지번으로 변경합니다.

=ADDRESS_SEARCH(A2,"지번")

우편번호와 건물번호도 각각 다른 열로 불러올 수 있습니다.

=ADDRESS_SEARCH(A2,"우편번호")

=ADDRESS_SEARCH(A2,"건물본번")

=ADDRESS_SEARCH(A2,"건물부번")

주소 관리용 구글 시트는 다음과 같이 구성하면 편리합니다.

· A열: 원본 주소

· B열: 도로명주소

· C열: 지번주소

· D열: 우편번호

· E열: 건물본번

· F열: 건물부번

· G열: 건물관리번호

원본 주소를 A열에 입력하면 나머지 열이 자동으로 조회되도록 수식을 설정할 수 있습니다.

7. 여러 주소를 한꺼번에 처리하는 방법

주소가 많을 때 사용자 함수를 모든 셀에 직접 입력하면 API 호출 횟수가 급격하게 늘어날 수 있습니다. 특히 시트를 열거나 수정할 때마다 함수가 다시 계산되면 호출 제한에 걸릴 가능성이 있습니다.

주소가 수십 건 정도라면 첫 번째 행에 수식을 입력한 뒤 아래쪽으로 복사할 수 있습니다. 조회가 완료되면 결과 범위를 복사하고 값만 붙여넣기를 실행해 결과를 고정하는 것이 좋습니다.

수백 건 이상의 주소를 처리할 때는 셀마다 사용자 함수를 호출하는 방식보다 Apps Script에서 A열의 주소를 한꺼번에 읽고 결과를 배열로 저장하는 방식이 효율적입니다.

function convertAddressList() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();

  if (lastRow < 2) return;

  var addresses = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
  var results = [];

  for (var i = 0; i < addresses.length; i++) {
    var address = addresses[i][0];

    if (!address) {
      results.push(["", "", "", "", ""]);
      continue;
    }

    var road = ADDRESS_SEARCH(address, "도로명");
    var jibun = ADDRESS_SEARCH(address, "지번");
    var zip = ADDRESS_SEARCH(address, "우편번호");
    var mainNo = ADDRESS_SEARCH(address, "건물본번");
    var subNo = ADDRESS_SEARCH(address, "건물부번");

    results.push([road, jibun, zip, mainNo, subNo]);

    Utilities.sleep(200);
  }

  sheet.getRange(2, 2, results.length, 5).setValues(results);
}

호출 사이에 짧은 대기시간을 두면 서버에 요청이 한꺼번에 몰리는 것을 줄일 수 있습니다. 다만 데이터가 매우 많으면 일일 호출 한도나 실행시간 제한을 고려해 여러 번 나누어 처리해야 합니다.

8. 오류가 발생하는 주소와 주의사항

주소 검색이 되지 않는 가장 흔한 원인은 오타와 불필요한 상세주소입니다. 아파트 동·호수, 상호명, 전화번호, 괄호 안의 건물명 등이 포함되면 검색 정확도가 떨어질 수 있습니다.

검색 전에는 다음과 같은 항목을 정리하는 것이 좋습니다.

· 시도와 시군구 명칭 확인

· 도로명 또는 법정동 오타 수정

· 아파트 동·호수 제거

· 괄호 안의 건물명 제거

· 불필요한 공백과 특수문자 제거

· 산지번의 ‘산’ 표기 유지

한 개의 검색어에서 여러 주소가 나오면 첫 번째 결과가 실제 주소와 다를 수 있습니다. 특히 동일한 건물명이나 도로명이 여러 지역에 있을 때는 시도와 시군구를 포함한 전체 주소를 입력해야 합니다.

주소에는 개인의 거주지와 사업장 정보가 포함될 수 있으므로 공유 시트의 접근 권한을 제한해야 합니다. 주민등록번호, 전화번호 등 다른 개인정보와 주소를 함께 관리할 때는 외부 공개 설정 여부를 반드시 확인해야 합니다.

구글 시트에서 건물번호만 필요하다면 REGEXEXTRACT와 SPLIT 함수로 간단하게 처리할 수 있습니다. 도로명주소와 지번주소를 정확하게 변환하고 우편번호, 건물관리번호까지 자동으로 가져오려면 주소 검색 API와 Apps Script를 함께 사용하는 것이 효과적입니다.

최근에 올라온 글

TAG more

바로가기링크

티스토리 뷰