엑셀에서 버튼 클릭 시 특정 데이터를 구글 스프레드시트에 자동 업로드하는 방법 (VBA + Google Sheets API 활용)

⸻
엑셀로 정리한 데이터를
🌐 구글 스프레드시트로 자동 업로드하고 싶으신가요?

예를 들어,
• 버튼 클릭 시 특정 범위(A1:F10)를 구글 시트에 실시간 전송
• 팀원들과 구글 시트에서 공유하고, 실시간 협업 가능

오늘은 엑셀 VBA + Google Sheets API를 이용해
✅ 버튼 클릭 →
✅ 특정 데이터 추출 →
✅ 구글 시트에 자동 전송
하는 방법을 단계별로 소개해드릴게요.

⸻

✅ 1. 사전 준비: Google Sheets API 활성화
1. https://console.cloud.google.com 접속
2. 새 프로젝트 생성 → ‘Google Sheets API’ 사용 설정
3. [사용자 인증 정보] 메뉴에서 서비스 계정 생성
4. 서비스 계정에 편집 권한 부여
5. JSON 키 다운로드 (엑셀에서 사용할 인증용)

🔑 이 JSON 파일은 매우 중요하므로 잘 보관하세요.

⸻

✅ 2. 구글 시트 공유 설정
1. 구글 시트를 하나 생성
2. JSON 키 파일에 있는 서비스 계정 이메일 주소를
해당 구글 시트에 ‘편집자’로 공유
(예: example@project-id.iam.gserviceaccount.com)

⸻

✅ 3. 엑셀에서 Google Sheets API 호출을 위한 도구 설정

VBA에서 HTTP 요청을 보내기 위해 Microsoft XML v6.0을 사용합니다.
1. Alt + F11 → VBA 편집기
2. [도구] → [참조] → Microsoft XML, v6.0 체크

⸻

✅ 4. VBA 코드 예제: 특정 범위 데이터를 구글 시트에 전송

※ 이 예제는 Google Apps Script(GAS) 서버에 웹앱으로 업로드된 중간 API를 통해 전송됩니다. 직접 Google Sheets API를 REST 호출하려면 OAuth2 처리 필요합니다.

✅ 1단계: 구글 앱스 스크립트 (GAS)로 API 준비
1. 구글 시트에서 [확장 프로그램] → [앱스 스크립트] 클릭
2. 아래 코드 입력:

function doPost(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("데이터"); // 시트 이름
  var data = JSON.parse(e.postData.contents);

  // 기존 데이터 삭제
  sheet.clearContents();

  for (var i = 0; i < data.length; i++) {
    sheet.getRange(i + 1, 1, 1, data[i].length).setValues([data[i]]);
  }

  return ContentService.createTextOutput("성공");
}

3. [배포] → [웹앱으로 배포] → 액세스: 모든 사용자 허용
→ URL 복사해두세요 (VBA에서 사용할 API 주소)

⸻

✅ 2단계: 엑셀 VBA 코드 – HTTP POST로 데이터 전송

Sub SendDataToGoogleSheet()
    Dim dataRange As Range
    Dim rowData As Variant
    Dim jsonData As String
    Dim i As Long, j As Long
    Dim http As Object

    ' 보낼 데이터 범위
    Set dataRange = ThisWorkbook.Sheets("보고서").Range("A1:F10")
    rowData = dataRange.Value

    ' JSON 문자열 구성
    jsonData = "["
    For i = 1 To UBound(rowData, 1)
        jsonData = jsonData & "["
        For j = 1 To UBound(rowData, 2)
            jsonData = jsonData & """" & Replace(rowData(i, j), """", "'") & """"
            If j < UBound(rowData, 2) Then jsonData = jsonData & ","
        Next j
        jsonData = jsonData & "]"
        If i < UBound(rowData, 1) Then jsonData = jsonData & ","
    Next i
    jsonData = jsonData & "]"

    ' HTTP 요청 보내기
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", "https://script.google.com/macros/s/웹앱URL/exec", False
    http.setRequestHeader "Content-Type", "application/json"
    http.Send jsonData

    MsgBox "데이터 전송 완료: " & http.responseText, vbInformation
End Sub

🔁 "https://script.google.com/macros/s/웹앱URL/exec" 부분에 실제 배포된 웹앱 주소 입력

⸻

✅ 5. 버튼 연결
1. [개발 도구] → [삽입] → 양식 버튼
2. 생성 후 SendDataToGoogleSheet 매크로 연결
3. 버튼 텍스트: “구글 시트 전송” 등 지정

⸻

💡 요약 정리

단계 설명
1단계 Google Sheets API / 서비스 계정 생성
2단계 구글 시트에 서비스 계정 공유
3단계 GAS로 웹앱 만들고 POST 처리
4단계 엑셀 VBA로 JSON 전송
5단계 버튼으로 실행 자동화


⸻

이 기능은
📤 리더에게 실시간 보고,
📥 양식 데이터 수집,
🤝 협업용 데이터 실시간 공유
에 매우 적합합니다.

⸻