구글 form의 응답값을 구글 spreadsheet에 자동으로 저장하기

1. 프로젝트 설명

기존에는 강의 시간표를 짤 때, 강사한테 모두 연락해서 카톡을 통해 가능한 시간을 전달받고, Spreadsheet에 직접 입력했다. 하지만 이 방법은 상당히 비효율적이다. 그래서 강사별로 본인이 가능한 모든 시간을 시간표에서 한 눈에 볼 수 있는 방법을 고민하던 도중 프로그램을 개발하게 되었다. 

 

 

2. 개발 순서

개발은 다음과 같은 순서로 진행되었고, 간단했다. 

 

1. 강사들이 강의가 가능한 시간을 스스로 체크할 수 있는 구글 Form과 응답값을 을 만든다. 

2. Form을 통해 제출된 응답들을 csv파일로 다운로드받는다. 

3. 파이썬으로 csv 파일들을 읽고, 요일과 시간별로 리스트를 만든다. 행별로 한개의 리스트가 만들어지고, 요일별로 한개의 리스트가 만들어지기 때문에 한 행당 이중리스트 한개가 만들어진다. 전체적으로 보면 이런 그림이다.

    

	월	화	수	목	금
10:00	[강의명(강사), 강의명(강사)]	[강의명(강사), 강의명(강사)]			[강의명(강사), 강의명(강사)]
11:00		[강의명(강사), 강의명(강사)]			[강의명(강사), 강의명(강사)]
12:00				[강의명(강사), 강의명(강사)]	[강의명(강사), 강의명(강사)]
13:00	[강의명(강사), 강의명(강사)]		[강의명(강사), 강의명(강사)]	[강의명(강사), 강의명(강사)]	[강의명(강사), 강의명(강사)]
...

 4. Spreadsheet에서 제공하는 Google API를 이용해서 위의 리스트 내의 값들을 Spreadsheet에 넣는다. 표의 행을 그대로 시트에 추가하는 방법을 사용했다. 

 

 

 

3. 세부 설명

1. 구글 Form 만들기 

강사에게 일일이 연락하던 기존의 비효율적인 방법 대신, 강사들이 시간을 스스로 체크할 수 있는 구글 Form을 만들었다. 

문항은 강의/강사명과 요일별 가능 시간으로 총 6문항이다. 

Google Form 예시

 

 

 

2. 구글 form의 응답 결과를 csv 파일로 저장하기

응답 결과로 들어가면 아래 사진과 같이 응답을 csv 파일로 다운로드 받을 수 있다.

csv 파일로 다운받기

 

 

다운받은 csv 파일을 열어보면 다음과 같이 문항별로 응답 결과가 저장되어있다. 

 

 

 

 

3. 구글 form의 응답 결과를 csv 파일로 저장하기

나는 Google API 중에서 gspread를 사용했다. 

 

먼저 필요한 모듈을 import 한다. 

# 필요한 모듈 import
import csv
import gspread
from oauth2client.service_account import ServiceAccountCredentials

 

Google API를 사용하기 위한 코드이다. 

spreadsheet_url에는 내가 쓸 시트의 url을 적는다. 

# Google API permission
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
json_file_name = 'cred.json'
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file_name, scope)
gc = gspread.authorize(credentials)
spreadsheet_url = '내 스프레드시트 url'

 

문서와 시트를 각각 doc, worksheet 변수에 불러오고, 미리 로컬에 저장되어있던 csv 파일을 읽어와서 list에 저장한다. 

# 문서 불러오기
doc = gc.open_by_url(spreadsheet_url)
# 시트 불러오기
worksheet = doc.worksheet('시트1')


# csv파일 읽어와서 list에 저장
f = open('응답.csv','r')
rdr = csv.reader(f)

lines = []

for line in rdr:
    lines.append(line)

 

 

수업은 10:00부터 17:30까지 진행되기 때문에 딕셔너리를 사용해서 각각의 시간에 임의로 인덱스넘버를 지정해주었다. 

맨 마지막 'X'는 해당 요일에 불가능이라고 응답했을 경우이다. 

time = {'10:00':'2', '10:30':'3', '11:00':'4', '11:30':'5', '12:00':'6', '12:30':'7', 
        '13:00':'8', '13:30':'9', '14:00':'10', '14:30':'11', '15:00':'12', '15:30':'13', 
        '16:00':'14', '16:30':'15', '17:00':'16', '17:30':'17', 'X':'22'}      
    
row = 0
col = 0


blank = ';'

 

 

spreadsheet에는 한 행씩 통째로 업로드할 것이기 때문에 sheet라는 이차원배열을 선언했다. 

24행 5열인데, 10:00부터 17:30까지 수업에 불가능일 경우 한가지를 포함하면 17행이 필요하지만 일부러 넉넉하게 잡았다. 

5열은 월~금 5일을 뜻한다.

sheets = [[' ' for col in range(5)] for row in range(24)] #시트에 넣기 위한 이차원배열 선언

 

 

미리 읽은 csv파일을 저장해놓은 'lines'라는 리스트를 읽으며 각각의 강사가 응답한 '본인이 수업가능한 시간'에 맞게 이차원배열 sheets에 강사의 이름을 대입한다.

for i in range(1, len(lines)):
    print(lines[i])
    for j in range(len(lines[i])):
        if('불가능' in lines[i][j]):
            lines[i][j] = 'X'
    
    
    for j in range(2, len(lines[i])):  #월~금 도는 for문
        tmp = lines[i][j].split(";")
        
        
        for k in tmp:
            row = int(time[k]) - 2
            col = j-2
            sheets[row][col] = sheets[row][col] + blank + lines[i][1]

 

 

이차원배열 내에 강사의 이름이 모두 위치하면 이제 이것을 시트로 보낸다. 

하지만 시트로 업로드 하기 전에, 리스트를 역순으로 배치해준다. 안그러면 값이 뒤부터 거꾸로 들어간다.

sheets.reverse()

 

 

시트에 값을 추가한다. 

# 시트에 추가하기
for i in sheets:
    worksheet.insert_row(i, 2, 2)

 

 

 

 

 

4. 결과

아래 보이는 것처럼 강의명과 강사명이 정확하게 들어간 것을 확인할 수 있다.