구글 스프레드시트로 Geocoding Bot 만들기🌏

# 배경

회사에서 혼자하는 프로젝트로 영업용 중개사 지도검색 페이지를 만들고 있다.
영업팀에서 작성해주신 스프레드시트 데이터를 DB에 넣고, 그 데이터를 지도에 표시해줘야 하는데,
내가 가지고 있는 데이터는 도로명 주소 밖에 없었다 !

처음엔 구글 스프레드시트에서 extension Geocoding by Awesome Table을 활용해서 위도와 경도를 구했다.
Awesome Table이라는 서비스를 이 때 처음 알았는데, 스프레드시트 데이터를 읽어서 무려 데이터에 맞는 웹사이트(!)를 만들어주는 서비스였다. 여기서 왠지모를 위기감을 느꼈다.ㅋㅋ

아무튼, extension을 사용해서 구한 위도와 경도로 지도에 마커를 나타냈는데, 요 녀석이 Google Map 기반이여서, 우리나라에서는 맞지 않는 부분이 생각보다 많았다.
(마커가 호수 위에.. 고속도로 위에...🫠)
그래서 우리나라 좌표에 특화되어 있는 Naver Map Geocoding API 를 이용하기로 했다.

이전에는 아주 손쉽게 스프레드시트에서 Run! 버튼만 클릭하면 알아서 익스텐션이 착착 데이터를 넣어주었는데.. 네이버 API를 이용하려하니 어떤 식으로 작업해야할지 고민이 들었다.

• 지도에 마커를 그릴 때 마다 API로 데이터를 변환하는 방법
• 변환된 데이터를 스프레드시트에 넣고 고걸 그대로 DB에 넣어 지금처럼 활용하는 방법

고민 결과 이렇게 두 가지 결론이 나왔는데,
현재 가지고 있는 10000 + 개의 데이터와 앞으로 추가될 데이터를 생각하면.. 마커를 그릴 때마다 API를 불러오는 건 미친짓이라는 생각이 들었다.(돈이 ..🤔)
그래서 스프레드시트 Apps Script를 이용해 가지고 있는 도로명 주소로 위도/경도를 구하는 봇을 만들기로 했다!

여기서 잠깐, Apps Script란 ?

구글 앱 전반에서 자바스크립트를 이용해 자동화를 할 수 있는 클라우드 기반 플랫폼이다.
스프레드시트 뿐만 아니라, 구글 드라이브, 구글 독스 등 다른 앱에서도 사용이 가능하다.

# 목표

스프레드시트에 이미 입력되어 있는 도로명 주소로 네이버 지도 API를 이용해 위도/경도를 구하자!

# 과정

1. 네이버 맵 API 연결을 위한 키를 가져오는 함수를 만들어준다.

   const globalVariables = () => {
     return values = {
       ClientId : "발급받은 clientId",
       ClientSecretKey : "발급받은 ClientSecretKey"
     }
   }

2. 스프레드시트에서 Apps Script로 데이터를 가져오는 함수를 만들어준다.

   // 해당하는 범위의 스프레드시트 데이터를 가져오는 func
   // range에는 시작하는 행과열, 끝나는 행과 열이 문자열로 들어간다.
   // 예 ) C1:C2 - C열 1행부터 C열 2행까지
   const getSheetData = (range) => {
   	const sheet = SpreadsheetApp.getActive();
   	const sheetData = sheet.getRange(range).activate().getValues();
   	return sheetData;
   }

3. 네이버 Geocoding API 를 이용해서 위도경도를 구하는 함수를 만들어준다.

   // 네이버 Geocoding api로 주소에 맞는 좌표를 가져오는 func
   const geocodingBot = (query) => {
     const g = globalVariables();
     const url = `https://naveropenapi.apigw.ntruss.com/map-geocode/v2/geocode?query=${query}`;
     const options = {
       'methos' : 'get',
       'contentType': 'application/json',
       'headers' : {
         "X-NCP-APIGW-API-KEY-ID": g.ClientId,
         "X-NCP-APIGW-API-KEY": g.ClientSecretKey,
       }
     }
     const response = UrlFetchApp.fetch(url, options);
     const jsonResponse = JSON.parse(response.getContentText());
     return jsonResponse;
   }

4. 받은 데이터를 스프레드시트에 입력하는 함수를 만들어준다.

   // 해당하는 범위에 새로운 데이터를 입력하는 func
   // 데이터를 가져오는 함수와 같은 형태의 range가 들어간다.
   const writeSheetData = (range, newValue) => {
     const sheet = SpreadsheetApp.getActive();
     const cell = sheet.getRange(range);
     cell.setValue(newValue)
   }

5. 만들어 둔 함수들을 모두 활용해 실행해주는 봇 함수를 만들어준다. 완성!

   // Geocoding Bot 실행 (시작지점, 끝지점을 입력한다) 
   const RunGeocodingBot = (start, end) => {
     Logger.log("Geocoding Bot 작업 시작!")
   	// 주소가 C열에 있기 때문에 행만 입력해서 가져올 수 있게 해준다.
     const address = getSheetData(`C${start}:C${end}`);
     for(let i = 0; i < address.length; i++){
       const jsonResponse = geocodingBot(address[i])
   
       // 좌표 못찾을 때 로그
       // 처리를 안해주면 에러가 뜬다.
       if(!jsonResponse.addresses[0]){
         Logger.log(`${i+start}행 - ${address[i]} 의 좌표를 찾을 수 없어요 :(`)
       }
       else {
       const newLat = jsonResponse.addresses[0].y;
       const newLng = jsonResponse.addresses[0].x;
       writeSheetData(`D${i+start}`, newLat);
       writeSheetData(`E${i+start}`, newLng);
       }
   
       // 5% 진전될 때마다 표시해준다
       const completeRatio = (i / address.length) * 100
       if(completeRatio % 5 === 0){
       Logger.log(`작업 ${completeRatio}% 진행중 ..`)
       }
     }
     Logger.log("Geocoding Bot 작업 완료!")
   }
   

6. 전체 코드

   const globalVariables = () => {
     return values = {
       ClientId : "발급받은 clientId",
       ClientSecretKey : "발급받은 ClientSecretKey"
     }
   }
   // 네이버 Geocoding api로 주소에 맞는 좌표를 가져오는 func
   const geocodingBot = (query) => {
     const g = globalVariables();
     const url = `https://naveropenapi.apigw.ntruss.com/map-geocode/v2/geocode?query=${query}`;
     const options = {
       'methos' : 'get',
       'contentType': 'application/json',
       'headers' : {
         "X-NCP-APIGW-API-KEY-ID": g.ClientId,
         "X-NCP-APIGW-API-KEY": g.ClientSecretKey,
       }
     }
     const response = UrlFetchApp.fetch(url, options);
     const jsonResponse = JSON.parse(response.getContentText());
     return jsonResponse;
   }
   // 해당하는 범위의 스프레드시트 데이터를 가져오는 func
   const getSheetData = (range) => {
     const sheet = SpreadsheetApp.getActive();
     const sheetData = sheet.getRange(range).activate().getValues();
     return sheetData;
   }
   // 해당하는 범위에 새로운 데이터를 입력하는 func
   const writeSheetData = (range, newValue) => {
     const sheet = SpreadsheetApp.getActive();
     const cell = sheet.getRange(range);
     cell.setValue(newValue)
   }
   // Geocoding Bot 실행 (시작지점, 끝지점을 입력한다) 
   const RunGeocodingBot = (start, end) => {
     Logger.log("Geocoding Bot 작업 시작!")
     const address = getSheetData(`C${start}:C${end}`);
     for(let i = 0; i < address.length; i++){
       const jsonResponse = geocodingBot(address[i])
   
       // 좌표 못찾을 때 로그
       if(!jsonResponse.addresses[0]){
         Logger.log(`${i+start}행 - ${address[i]} 의 좌표를 찾을 수 없어요 :(`)
       }
       else {
       const newLat = jsonResponse.addresses[0].y;
       const newLng = jsonResponse.addresses[0].x;
       writeSheetData(`D${i+start}`, newLat);
       writeSheetData(`E${i+start}`, newLng);
       }
   
       // 5% 진전될 때마다 표시해준다
       const completeRatio = (i / address.length) * 100
       if(completeRatio % 5 === 0){
       Logger.log(`작업 ${completeRatio}% 진행중 ..`)
       }
     }
     Logger.log("Geocoding Bot 작업 완료!")
   }
   // * 실행방법 *
   // 1. start 자리에 좌표를 구하기 시작할 행의 숫자
   //    end 자리에 좌표를 구하는 것을 끝낼 행의 숫자를 입력합니다.
   //    예 ) 2행 ~ 10행까지의 좌표를 구하고 싶다 -> RunGeocodingBot(2, 10)
   //    좌표는 해당 데이터의 lat, lng 열에 자동으로 저장됩니다.
   // 2. 입력 후 위 실행버튼을 누른 뒤, 작업이 완료될 때까지 기다립니다. (1000개 기준 약 10분 소요)
   // 3. 작업이 완료되면 스프레드시트로 돌아가 잘 입력이 되었는지 확인합니다.
   // 4. 이 때, 좌표를 구할 수 없는 경우는 실행 로그에 행과 주소가 표시됩니다.
   // 5. 좌표를 구할 수 없는 경우 주소를 더 정확히 수정하거나, 구글 맵 & 네이버 맵을 통해 직접 찾아야 합니다.
   RunGeocodingBot(2, 10);
   

   
   

# 결과

Apps Script라는게 있는지도 몰랐지만, 이제 제대로 활용할 줄 알게 되었다.
이 녀석은 언제든 업무 자동화에 활용할 수 있을 것 같고, 특히 나에게 가장 익숙한 자바스크립트라서 사용하기에 부담이 없다 !
회사에서 기획팀 분들이 스프레드시트를 자주 이용하시던데, 심심할 때 뭔가 자동화해드릴 게 없나 살펴봐야겠다.