안녕하세요! 😊

이번에는 Excel VBA에서 GA4 Data API를 이용하여 데이터를 취득하는 도구를 작성합니다.

작성하게 된 경위는 ↓ 기사를 확인해 주세요!

※ 간단히 말하자면, 디지털 초보자라도 만들어서 사용할 수 있는 간편한 도구를 만들고 싶다는 이야기입니다.

• 📝 시작하며
• 💻 개발 환경
• 🗺️ 전체 흐름
• ☁️ Google Cloud 설정
• 📂 Excel VBA 준비
• 📄 JSON 파일 읽기
• 🔍 JSON 파싱하기
• 🪪 JWT 생성 준비
• 🎉 마무리

최종적으로는 다음과 같은 도구를 작성할 예정입니다.

LP의 URL·게재 기간을 입력
↓
GA4 Data API로부터 데이터 취득
...

이번에는 그 첫걸음으로,

• Google Cloud 설정
• 서비스 계정(Service Account) 생성
• JSON 키 취득
• VBA에서 JSON 파일 읽기
• JWT 생성 준비

까지 진행합니다.

• Windows11
• Microsoft Excel 365
• Excel VBA
• Google Cloud Console
• Google Analytics 4 (GA4)

이번에 작성할 도구의 흐름입니다.

Google Cloud
↓
Analytics Data API 활성화
...

Google Cloud Console에 접속하여, 새로운 프로젝트를 생성합니다.

프로젝트 이름은 임의로 정할 수 있지만, 이번에는

GA4_Report

라고 했습니다.

왼쪽 상단 메뉴에서

API 및 서비스
↓
라이브러리

를 열고,

Google Analytics Data API

를 검색하여 **「사용 설정」**을 클릭합니다.

왼쪽 상단 메뉴에서

IAM 및 관리
↓
서비스 계정

를 열고,

＋ 서비스 계정 만들기

를 클릭합니다.

이름은 예시로

GA4Excel

라고 했습니다.

서비스 계정을 열고,

키
↓
키 추가
...

를 클릭합니다.

키 유형은

JSON

을 선택하고 **「만들기」**를 누릅니다.

JSON 파일이 자동으로 다운로드됩니다.

GA4의

관리
↓
속성 액세스 관리

에서,

JSON 파일 내의

client_email

에 열람 권한을 부여합니다.

Visual Basic Editor를 엽니다.

Alt + F11

이어서

삽입
↓
표준 모듈

를 선택합니다.

도구
↓
참조 설정

에서

• Microsoft XML, v6.0
• Microsoft Scripting Runtime

에 체크를 합니다.

GitHub에서

JsonConverter.bas

를 다운로드하여,

파일
↓
파일 가져오기

를 통해 추가합니다.

JSON 파일을 데스크톱 등 임의의 장소에 저장합니다.

예

C:\Users\사용자명\Desktop\GA4\

VBA에서는 다음 코드로 JSON 파일을 읽어옵니다.

Sub JSON読込テスト()
Dim fso As Object
Dim ts As Object
...

이어서, JSON 문자열을 VBA에서 다룰 수 있는 형식으로 변환합니다.

Dim Json As Object
Set Json = JsonConverter.ParseJson(JsonText)

Dim ClientEmail As String
ClientEmail = Json("client_email")
MsgBox ClientEmail

Dim ProjectID As String
ProjectID = Json("project_id")
MsgBox ProjectID

Dim PrivateKey As String
PrivateKey = Json("private_key")
MsgBox Left(PrivateKey, 50)

비밀키는 매우 길기 때문에, 앞부분 50글자만 표시하여 확인합니다.

Google로 인증하기 위해서는 JWT (JSON Web Token)를 생성해야 합니다.

먼저 JWT에서 사용할 시간 정보를 취득합니다.

Dim IssuedAt As Long
Dim ExpireAt As Long
IssuedAt = DateDiff("s", "1970/1/1 09:00:00", Now)
...

제대로 취득되었는지 확인하려면

MsgBox IssuedAt

또는

MsgBox ExpireAt

를 실행합니다.

이번에는 GA4 Data API를 이용하기 위한 준비로서,

• Google Cloud 프로젝트 생성
• Analytics Data API 활성화
• 서비스 계정 (Service Account) 생성
• JSON 키 취득
• GA4에 권한 부여
• Excel VBA 준비
• JSON 파일 읽기
• client_email, project_id, private_key 취득
• JWT 생성 준비

까지를 구현했습니다.

다음 회차에서는 JWT를 생성하고, Google OAuth를 통해 액세스 토큰 (Access Token)을 취득하여, GA4 Data API로부터 데이터를 취득하는 처리를 구현해 나가겠습니다!