Google Apps Script(구글 앱스크립트)로 BigQuery(빅쿼리) 자동화: 데이터를 자동으로 가져와 Google Sheets(스프레드 시트)에 기록하기

 

데이터를 자동화해 마케팅 업무를 더 효율적으로 관리하고 싶으신가요? BigQuery와 Google Apps Script를 활용하면 반복 작업에서 벗어나 중요한 일에 집중할 수 있습니다.

안녕하세요! 일하기 싫은 마케터, 섭입니다. 오늘은 제가 실패와 성공을 반복하며 터득한 Google Apps Script와 BigQuery를 활용한 데이터 자동화 과정을 공유합니다. 이 글에서는 어떻게 제가 데이터를 자동으로 Google Sheets에 기록하는 시스템을 구축했는지, 그리고 그 과정에서 겪었던 시행착오와 팁을 나눌게요.

---

목차

1. BigQuery 데이터 자동화란?
2. 시작하기 전에 필요한 준비물
3. Google Apps Script로 BigQuery 연동하기
4. 실제 코드와 설정 과정
5. 트리거를 활용한 자동화
6. 실패와 성공을 통해 얻은 교훈

---

1. BigQuery 데이터 자동화란?

✔️ 반복적인 데이터 수집을 자동으로 처리
✔️ BigQuery에서 쿼리 실행 후 결과를 Google Sheets에 기록
✔️ 데이터 분석 시간을 절약하고, 효율적인 마케팅 캠페인 설계에 집중

데이터로 일하기 위한 수많은 데이터 기입 작업들...

BigQuery는 대규모 데이터를 처리하기에 탁월합니다. 매일 기입해야 하는 수많은 귀찮은 일을 해결할 수 있습니다. 하지만 이를 활용하는 것도 매번 쿼리를 실행하고 결과를 수동으로 정리하는 것은 귀찮고 비효율적입니다. 저는 이 문제를 해결하기 위해 Google Apps Script를 사용해 BigQuery와 Google Sheets를 연동했습니다.

---

2. 시작하기 전에 필요한 준비물

✔️ 구글 서치 콘솔(Google Search Console) 에서 BigQuery로 내보내기

대량 데이터 내보내기가 활성화된 이후 부터 빅쿼리에 데이터가 쌓임.

• Google Search Console(https://search.google.com/search-console/)에 접속하여 대량 데이터 내보내기를 활성화하세요.
  [구글 서치 콘솔 > 설정 > 대량 데이터 내보내기] 경로를 따라가면 활성화할 수 있습니다.
  * 대량 데이터 내보내기가 설정된 이후 부터 빅쿼리에 데이터가 쌓이니 꼭 선행하여야 합니다!

✔️ Google Cloud Console(GCP)에서 BigQuery API 활성화

• Google Cloud Console에 접속하여 BigQuery API를 활성화하세요.
  [API 및 서비스 > 라이브러리 > BigQuery API] 경로를 따라가면 활성화할 수 있습니다.

✔️ Google Sheets 준비

• 데이터를 기록할 Google 스프레드시트를 생성합니다.
• 이 스프레드시트에서 데이터를 기록할 시트 이름을 설정하세요. 저는 ‘구글 브랜드 검색량’이라는 이름을 사용했습니다.

---

3. Google Apps Script로 BigQuery 연동하기

✔️ Google Apps Script 프로젝트 생성

1. Google Sheets 상단 메뉴에서 확장 프로그램 > Apps Script를 클릭합니다.
2. 새로운 프로젝트가 생성되면, 아래 코드를 입력합니다.

✔️ Apps Script에서 BigQuery 고급 서비스 활성화

1. Apps Script 편집기의 좌측 서비스 추가 버튼을 클릭.
2. BigQuery API를 검색하여 활성화합니다.

---

4. 실제 코드와 설정 과정

아래는 제가 작성한 Google Apps Script 코드입니다. 데이터를 BigQuery에서 가져와 Google Sheets에 기록하고, 기존 데이터와 중복되지 않은 데이터만 추가합니다.

function exportBigQueryToSheet() {
  const projectId = '****-masked-project-id****'; // BigQuery 프로젝트 ID (마스킹 처리됨)
  const query = `
    WITH latest_date AS (
        SELECT MAX(data_date) AS latest_data_date
        FROM (
            SELECT data_date FROM \`****-masked-project-id****.masked_mobile_table\`
            UNION ALL
            SELECT data_date FROM \`****-masked-project-id****.masked_web_table\`
        )
    ),
    mobile AS (
        SELECT 
            data_date,
            query AS keyword,
            SUM(impressions) AS mobile_impressions,
            SUM(clicks) AS mobile_clicks
        FROM 
            \`****-masked-project-id****.masked_mobile_table\`
        WHERE 
            query LIKE "%~~~%" OR query LIKE "%~~~%"
            AND data_date = (SELECT latest_data_date FROM latest_date)
        GROUP BY 
            data_date, query
    ),
    web AS (
        SELECT 
            data_date,
            query AS keyword,
            SUM(impressions) AS web_impressions,
            SUM(clicks) AS web_clicks
        FROM 
            \`****-masked-project-id****.masked_web_table\`
        WHERE 
            query LIKE "%~~~%" OR query LIKE "%~~~%"
            AND data_date = (SELECT latest_data_date FROM latest_date)
        GROUP BY 
            data_date, query
    )
    SELECT
        COALESCE(mobile.data_date, web.data_date) AS date,
        COALESCE(mobile.keyword, web.keyword) AS keyword,
        GREATEST(COALESCE(mobile.mobile_impressions, 0), COALESCE(web.web_impressions, 0)) AS impressions,
        GREATEST(COALESCE(mobile.mobile_clicks, 0), COALESCE(web.web_clicks, 0)) AS clicks
    FROM
        mobile
    FULL OUTER JOIN
        web
    ON
        mobile.data_date = web.data_date AND mobile.keyword = web.keyword
    ORDER BY
        date DESC, keyword;
  `;

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('구글 브랜드 검색량'); // 스프레드 시트에 타겟 시트 이름
  if (!sheet) {
    Logger.log('Sheet 이름이 올바르지 않습니다.');
    return;
  }

  const request = {
    query: query,
    useLegacySql: false, // 표준 SQL 사용
  };

  const queryResults = BigQuery.Jobs.query(request, projectId);
  if (!queryResults || !queryResults.rows) {
    Logger.log('쿼리 결과가 없습니다.');
    return;
  }

  const existingData = sheet.getDataRange().getValues();
  const existingKeys = existingData.slice(1).map(row => `${row[0]}_${row[1]}`);

  const newRows = [];
  queryResults.rows.forEach(row => {
    const date = row.f[0].v;
    const keyword = row.f[1].v;
    const impressions = row.f[2].v;
    const clicks = row.f[3].v;
    const key = `${date}_${keyword}`;

    if (!existingKeys.includes(key)) {
      newRows.push([date, keyword, impressions, clicks]); // 차례로 추가 되어지는 값
    }
  });

// 기존값을 남기고 값을 추가하기
  if (newRows.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, newRows.length, newRows[0].length).setValues(newRows);
  }
}

5. 트리거를 활용한 자동화

✔️ 트리거 설정 방법

1. Apps Script에서 상단 메뉴의 트리거 아이콘(시계 모양)을 클릭.
2. 새 트리거 추가:
   • 실행할 함수: exportBigQueryToSheet
   • 이벤트 소스: 시간 기반
   • 주기: 일별, 시간별 등 필요에 따라 설정.

---

6. 실패와 성공을 통해 얻은 교훈

✔️ 실패: BigQuery API 활성화나 IAM 권한 설정을 깜빡해 실행되지 않는 문제가 발생.
✔️ 해결: GCP에서 BigQuery API 활성화 및 OAuth 동의 화면 설정 확인.

✔️ 성공: 트리거를 통해 매일 자동으로 데이터를 기록하게 되어 수작업을 완전히 제거.
✔️ 교훈: 자동화를 설정할 때 초반 설정에 시간을 충분히 투자하면 이후 효율은 기하급수적으로 증가한다!

---

BigQuery API 활성화

문제: GCP에서 BigQuery API가 활성화되지 않으면 Apps Script가 BigQuery와 통신할 수 없습니다.
해결 방법:

1. Google Cloud Console 접속:
   • Google Cloud Console에 로그인합니다.
   • 상단의 프로젝트 선택 메뉴에서 Apps Script와 연결된 프로젝트를 선택합니다.
2. API 활성화:
   • API 및 서비스 > 라이브러리로 이동합니다.
   • BigQuery API를 검색하여 클릭한 뒤 활성화 버튼을 누릅니다.
3. Apps Script에서 BigQuery API 활성화 확인:
   • Google Apps Script 편집기에서 서비스 추가를 클릭합니다.
   • BigQuery API를 검색해 추가합니다.

---

IAM 권한 구성

빅쿼리 관리자를 설정해줘야 자동으로 앱스크립트를 실행할 수 있음.

문제: Apps Script 실행 계정에 BigQuery 데이터를 쿼리할 권한이 없으면 "Access Denied" 오류가 발생합니다.
해결 방법:

1. IAM 설정 열기:
   • GCP에서 IAM & Admin > IAM으로 이동합니다.
   • 프로젝트와 연결된 서비스 계정을 확인합니다.
2. 권한 부여:
   • Apps Script에서 사용 중인 계정 또는 서비스 계정에 다음 권한을 추가합니다:
     • BigQuery 관리자
3. 권한 테스트:
   • 권한을 추가한 후 다시 Apps Script를 실행하여 BigQuery 데이터에 접근 가능한지 확인합니다.

 

---

7. 마무리하며

이 자동화 시스템은 데이터를 효율적으로 관리하고, 반복 작업에서 벗어나는 데 큰 도움을 주었습니다.
여러분도 Google Apps Script와 BigQuery를 활용해 마케팅 업무를 자동화해 보세요!
궁금한 점이 있다면 언제든 댓글로 남겨주세요. 😊