구글 Form을 이용해서 구글 시트에 Data 모으기

구글 Form으로 설문조사를 해 보면 재미 있는 사실을 알 수 있습니다.

설문지를 작성해 놓으면 해당 문서의 URL을 가지고 여러 사람들이 응답을 기재하고

이 응답을 구글시트에 연동하여 자동으로 기재가 되게 만들 수 있습니다.

 

이런 설문은 응답자가 직접 답을 가재하고 [제출]을 하여 자료가 기록되는데,,,

혹시 이걸 VBA를 이용해서 자동화 할 수 있다면,,,

서버를 구축하지 않고도 데이터를 수집할 수 있지 않을까하여 그 방법을 소개합니다.

 

1. 구글 Form 만들기

구글 앱 중에서 [Forms]를 선택하고 [새 설문지 만들기]를 합니다.

2. 구글 Form에 수집할 항목 만들기

설문지 문항을 아래와 같이 하나씩 추가 해 줍니다.

샘플은 단답형으로 3개의 문항을 만들었습니다.

 

3. [응답] 탭에서 [Sheets에 연결]

[응답] 탭을 선택하고 [Sheets에 연결]눌러  [새 스프레트시트 만들기]를 선택하여 자동으로 시트가 생성되도록 합니다.

 

 

새로 생성된 시트에는 방금 만들었던 질문 3개와 맨 앞에 [타임스탬프]라는 열이 하나 추가되어 있습니다.

말 그대로 데이터가 수집된 시간이 실시간으로 기재 됩니다.

 

4. 설문응답용 URL

이제 오른쪽에 있는 [보내기]을 눌러 가운데  링크 아이콘을 선택하여 [링크] 주소를 [복사] 버튼으로 복사해 줍니다.

이 URL을 열면 설문지를 작성할 수 있게 됩니다.

 

 

5. 설문지 작성

복사해 둔 URL을 Chrome 주소창에 붙여 넣고 엔터를 치면 아래와 같이 응답할 수 있는 설문지가 나옵니다.

각 항목에 답을 기재해 봅니다.

 

6. HTML 개조

[제출] 버튼을 누르지 말고 [F12] 버튼으로 HTML 소스와 그 요소들을 확인합니다.

그 중에서 아래와 같이 <form ,,,> 태그를 찾고  속성중에서 "method="POST"를 찾습니다.

이 부분의 "POST"를 "GET"으로 변경합니다.

이제 [제출] 버튼을 눌러 결과를 확인합니다.

 

7. "GET" 방식으로 처리할 수 있는 URL 확인

[제출] 버튼을 누르고 나면 Chrome의 주소입력줄에 URL이 기재됩니다.

 

URL은 아래와 같은 형태로 되어 있는데, 물음표(?) 앞까지가 일반적인 URL이고 그 뒤로는 "필드명=값" 형식으로 여러 필드인 경우 "&"를 사용하여 연결하고 있습니다. 

총 3개의 질문을 작성했는데, 해당 질문은 "entry.12345678" 형식으로 되어 있는 것을 볼 수 있습니다.

이 질문 이후의 나머지 필드는 필요 없으므로 삭제해도 됩니다.

 

https://docs.google.com/forms/u/0/d/e/1FAIpQLSeEWXxqaFFyYwFOVqK6Hg-XcMe7wgERZK8owiSSC9enH0LvXQ/formResponse?

entry.310108796=%EA%B0%80%EB%82%98%EB%8B%A4

&entry.835259974=ABCD

&entry.179992367=1234

&dlut=1693662254092

&fvv=1

&partialResponse=%5Bnull%2Cnull%2C%22418635156462773754%22%5D

&pageHistory=0

&fbzx=418635156462773754

 

그럼 복사한 URL을 일부 수정하여 제대로 작동하는지 확인합니다.

아래와 같이 수정하여 주소창에 붙여 넣고 엔터를 친 다음, 결과 집계 시트에서 확인 해 봅니다.

(아래 주소는 어떻게 보이든 상관없이 공백이 없으며 줄바꿈도 없이 입력된 것입니다.)

 

https://docs.google.com/forms/u/0/d/e/1FAIpQLSeEWXxqaFFyYwFOVqK6Hg-XcMe7wgERZK8owiSSC9enH0LvXQ/formResponse?entry.310108796=abcdefg&entry.835259974=1234&entry.179992367=wxyz 

 

그럼 아래처럼 실시간으로 기록된 것을 확인할 수 있습니다.

 

8. URL을 이용한 자료 수집

이제 설문지를 만들고, 연결 시트를 만든 다음에 "GET" 방식으로 브라우저 주소창에 URL을 입력하여 자료를 기록하는 방법이 생겼습니다.

 

그럼 이걸 이용하여 사용자가 친숙한 Excel 양식과 매크로를 이용해서 자동화 된 자료 수집이 가능합니다.

 

아래 URL은 test용으로 유지할 예정이므로 URL중에서 각 질문의 응답 값을 수정하여 주소줄에 입력하시고,

연결된 시트에서 추가된 결과를 확인해 보시기 바랍니다.

 

https://docs.google.com/forms/u/0/d/e/1FAIpQLSeEWXxqaFFyYwFOVqK6Hg-XcMe7wgERZK8owiSSC9enH0LvXQ/formResponse?entry.310108796=첫번째&entry.835259974=두번째&entry.179992367=세번째

 

결과 집계 시트

https://docs.google.com/spreadsheets/d/1UYsLZqxwO6iP29sIqnxrQAxJ5GwrbLb7x2qLc-pntIM/edit?usp=sharing