구글 시트와 외부 데이터 연동: 키워드 자료를 실시간으로 가져와 분석을 자동화하는 비법

웹사이트 운영에서 방문자의 만족도를 높이고 안정적인 성장을 이루기 위해서는, 방문자가 실제로 검색하는 키워드 자료를 빠르고 정확하게 확보하고 분석하는 것이 필수입니다. 하지만 외부 전문 도구(키워드 분석 툴 등)에서 자료를 수동으로 내려받아 구글 스프레드시트(Google Sheets)에 붙여 넣는 작업은 시간이 많이 걸리고 오류가 발생하기 쉽습니다.

여러분이 간절히 바라시는 목표 달성(승인)과 안정적인 웹사이트 운영을 위해서는, 이 번거로운 수작업을 완전히 제거하고 외부 키워드 도구와 구글 시트를 직접 연결하여 자료를 실시간으로 가져오는 시스템이 필요합니다. 구글 시트의 핵심 자동화 기능인 **Apps Script(앱스 스크립트)**를 활용하면, 코딩 지식 없이도 API(Application Programming Interface) 연동을 통해 자료 수집 및 분석을 완전히 자동화할 수 있습니다.

이 글은 Apps Script를 활용하여 외부 자료를 구글 시트로 가져오고, 이를 기반으로 키워드 분석 환경을 구축하는 방법을 단계별로 상세히 안내합니다.

1. 외부 자료 연동의 기반: Apps Script 환경 설정

외부 자료를 시트로 가져오기 위해서는 Apps Script 편집기에서 코드를 작성해야 합니다. 이 코드는 외부 서버에 자료를 요청하고 응답을 받아 시트에 기록하는 역할을 합니다.

1.1. 스크립트 편집기 열기

구글 시트 문서 상단 메뉴에서 확장 프로그램 > Apps Script를 선택합니다.
새 탭에 편집기가 열리면, 함수 이름을 기능에 맞게 변경합니다 (예: fetchKeywordData).

1.2. API 키 및 엔드포인트 준비

외부 자료를 가져오기 위해서는 해당 도구에서 제공하는 API 키와 자료를 요청할 주소(Endpoint URL)가 필요합니다. 이 정보들은 스크립트 코드 내에서 사용됩니다.

API 키: 보안을 위해 스크립트 코드 내에 직접 입력하거나, 별도의 시트 셀에 숨겨둡니다.
엔드포인트 URL: 자료를 요청할 웹 주소 (예: https://api.keywordtool.com/v1/data)

2. Apps Script 코딩: URLFetchApp을 이용한 자료 요청

구글 시트의 UrlFetchApp 클래스는 웹에서 자료를 가져오는 역할을 합니다. 이는 브라우저를 통해 웹사이트에 접속하는 것과 유사합니다.

2.1. 자료 요청 코드 작성 (GET 방식)

대부분의 키워드 도구는 GET 방식을 사용하여 자료를 요청합니다. 요청 시 API 키와 필요한 매개변수(키워드, 기간 등)를 URL에 포함합니다.

JavaScript

function fetchKeywordData() {
  // 1. 필요한 변수 설정
  var API_KEY = "YOUR_SECRET_API_KEY"; // 실제 API 키로 대체
  var keyword = "웹사이트 운영"; // 분석할 키워드
  var endpoint = "https://api.example.com/data?"; 
  
  // 2. 요청 URL 구성 (API 키 및 매개변수 포함)
  var url = endpoint + "key=" + API_KEY + "&query=" + keyword + "&country=KR";
  
  // 3. 외부 서버에 자료 요청 및 응답 받기
  var response = UrlFetchApp.fetch(url);
  var jsonText = response.getContentText(); // 응답을 텍스트로 받음
  
  // 4. JSON 형식 자료 파싱 (시트 사용을 위해 객체로 변환)
  var data = JSON.parse(jsonText); 
  
  // (다음 단계에서 이 data 객체를 시트에 기록)
}

2.2. JSON 자료 파싱 및 시트 기록

외부 서버에서 받은 자료는 보통 JSON(JavaScript Object Notation) 형식입니다. 이 JSON 자료에서 필요한 값(검색량, 난이도 등)만 추출하여 시트에 기록해야 합니다.

JavaScript

  // ... (JSON.parse(jsonText) 까지 실행된 후) ...

  // 5. 필요한 자료 추출 (API 구조에 따라 다름)
  var searchVolume = data.results[0].searchVolume; // 첫 번째 키워드의 검색량 가정
  var difficulty = data.results[0].difficulty;   // 난이도 가정
  
  // 6. 구글 시트에 자료 기록
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("키워드 분석");
  sheet.appendRow([keyword, searchVolume, difficulty, new Date()]); // 시트 맨 아래에 행 추가
}

3. 분석 자동화: 시트 자료를 활용한 동적 요청

단일 키워드가 아닌, 구글 시트의 목록에 있는 수백 개의 키워드를 자동으로 순회하며 API 요청을 보내는 시스템을 구축합니다.

3.1. 키워드 목록 읽어오기

‘키워드 목록’ 시트의 A열에 있는 모든 키워드를 스크립트로 읽어옵니다.

JavaScript

function bulkFetchKeywords() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var listSheet = ss.getSheetByName("키워드 목록");
  
  // A열의 키워드 목록을 2차원 배열로 가져옴
  var keywordRange = listSheet.getRange("A2:A" + listSheet.getLastRow()); 
  var keywords = keywordRange.getValues(); 
  
  // 7. 키워드 배열을 순회하며 API 요청 반복
  keywords.forEach(function(row) {
    var keyword = row[0]; // 배열의 첫 번째 값(키워드) 추출
    if (keyword) {
      // (여기에 fetchKeywordData 함수의 요청 로직을 넣고 실행)
    }
  });
}

3.2. ARRAYFORMULA와 QUERY를 이용한 분석 환경 구축

API를 통해 가져온 자료(검색량, 난이도)를 기반으로, ARRAYFORMULA와 QUERY 함수를 사용하여 키워드 잠재력 분석을 자동화합니다.

잠재력 점수 계산: 난이도 대비 검색량을 반영한 점수 계산 공식(예: 검색량 * (100 - 난이도) / 100)을 ARRAYFORMULA로 자동 계산합니다.
QUERY 활용: 계산된 잠재력 점수를 기준으로 상위 10개 키워드를 추출하거나, 난이도는 낮고 검색량은 높은 키워드만 필터링합니다.

4. 실시간 업데이트 예약: Apps Script 트리거 활용

스크립트가 작성되었다면, 이 코드가 수동 실행 없이 자동으로 작동하도록 **트리거(Trigger)**를 설정하여 자료 업데이트를 예약합니다.

4.1. 시간 기반 트리거 설정

매일 새벽 특정 시간(예: 오전 4시)에 자동으로 API 자료를 갱신하도록 설정합니다.

스크립트 편집기 왼쪽 메뉴에서 시계 아이콘 (트리거)을 클릭합니다.
트리거 추가 > 실행할 함수에 bulkFetchKeywords를 선택합니다.
이벤트 소스를 시간 기반 트리거로 설정하고, ‘일 타이머’를 지정합니다.

결과: 지정된 시간에 스크립트가 자동으로 실행되어 외부 키워드 서버에 요청을 보내고, 구글 시트에 최신 자료를 기록합니다.

5. 시스템 안정화 및 유지보수 팁

5.1. API 호출 제한(Rate Limit) 관리

대부분의 외부 API는 시간당 또는 분당 호출 횟수(Rate Limit)를 제한합니다. 많은 키워드를 한 번에 요청하면 제한에 걸릴 수 있습니다.

해결책: Utilities.sleep(밀리초) 함수를 사용하여, API 요청 사이에 의도적으로 몇 초간의 지연 시간(Delay)을 줍니다. 이는 제한을 피하는 데 도움이 됩니다.

5.2. 오류 처리 (try…catch)

API 요청 실패, 네트워크 문제, 또는 잘못된 JSON 형식 등의 오류가 발생할 경우 스크립트가 멈추는 것을 방지합니다.

try…catch 활용: UrlFetchApp.fetch(url) 부분을 try { ... } catch (e) { ... } 구문으로 감싸서, 오류 발생 시 사용자에게 알림을 보내거나 오류를 기록하고 다음 키워드로 넘어가도록 합니다.

5.3. API 키 보안

API 키는 절대 외부에 노출되어서는 안 됩니다.

보안 대책: API 키를 스크립트 코드 내에 직접 하드코딩하는 대신, 구글 시트의 별도 시트에 숨겨두고 스크립트가 그 셀 값을 읽어오도록 합니다. 이후 해당 시트는 시트 보호 기능을 통해 잠급니다.

결론: 자동화된 자료 수집으로 시간 확보

구글 시트와 외부 API 연동 시스템은 키워드 자료 수집에 소요되던 시간을 완전히 제거합니다. Apps Script의 UrlFetchApp과 트리거 기능을 결합하여, 외부 전문 도구의 자료를 자동으로 가져오고, ARRAYFORMULA와 QUERY를 통해 분석을 자동화하십시오.

구글 시트와 외부 데이터 연동: 키워드 자료를 실시간으로 가져와 분석을 자동화하는 비법