다른 스프레드시트에서 데이터 가져오기, IMPORTRANGE 함수

안녕하세요.

작년부터 새 학기를 앞두고 구글 설문으로 만든 학생 기초 자료 조사와 설문 결과를 확인할 수 있는 구글 스프레드시트를 학교 선생님들께 공유하고 있습니다. 

근무하는 학교에서도 공유중인데 작년에는 학년별로 구글 설문과 스프레드시트를 각각 만들어 사용했습니다. 그러다 올해 문득 하나의 구글 설문을 가지고 전체 학급 담임 선생님이 활용할 수 있는 방법을 없을까?라는 생각을 하면서 방법을 찾던 중 제가 수행평가 점수 정리에 사용하고 있던 IMPORTRANGE 함수를 사용하면 되겠다라는 생각이 들었고, 이를 소개하고자 합니다.

 

목표: 하나의 구글 설문 결과를 20학급의 담임 교사가 동시에 볼 수 있게 하자!

 

- 문제 상황: 하나의 구글 시트에 20학급의 텝은 너무 많아😭

스프레드시트 탭 현황

설문 결과 확인(상담 자료) 구글 스프레드시트에 학교 전체 학급의 텝을 추가해도 되지만, 문제는 현재 7학급만으로도 화면이 꽉 차게 되어 13학급을 추가할 경우 뒤에 있는 담임 선생님이 자료를 찾아보기가 매우 불편해지는 상황이다. 

그렇다면 아래 그림처럼 하나의 구글 설문을 3개의 구글 스프레드시트와 연결할 수는 없을까?

결론부터 말하면 구글 설문은 하나의 스프레드시트와 연결되기 때문에 설문 자체를 3개의 구글 스프레드시트와 연결하는 방법은 없다. 

 

처음에 상상했던 그림

 

- 문제 해결: IMPORTRANGE 함수를 이용해 다른 스프레드시트에서 데이터 가져오기

구글 설문은 하나의 스프레드시트와 연결된다. 

위 그림으로 보자면 구글 설문은 3학년 스프레드시트만 연결되기 때문에 1,2학년 스프레드시트에서는 구글 설문 결과를 볼 수 없다. 하지만 IMPORTRANGE 함수를 이용하면 3학년에 들어온 구글 설문 결과를 1,2학년 구글 스프레드시트로 가져올 수 있어 하나의 구글 설문을 가지고 1~3학년 전체의 학생 기초 자료 조사 및 공유가 가능하다. 

 

1. 구글 스프레드시트 사본 만들기

구글 설문 결과를 IMPORTRANGE 함수를 통해  받을 스프레드시트(상담자료)를 '파일 - 사본 만들기'를 통해 만들어준다. 그리고 제일 첫번탭에 마우스 오른쪽 버튼을 클릭하고 '설문지 연결 해제'를 선택한다. 이는 구글 설문과 연결된 스프레드시트를 복제한 경우 구글 설문도 같이 복제가 되기 때문에 IMPORTRANGE 값과 설문 결과가 충돌하는 일이 없게 하기 위해 필요하다. 

왼쪽은 구글 설문과 연결된 스프레드시트, 오른쪽은 구글 설문과 연결되지 않은 스프레드시트

 

2. 데이터를 가져올 스프레드시트에 IMPORTRANGE 함수 입력하기

IMPORTRANGE 함수 수식

필요한 데이터만 가져올 수도 있지만 지금은 같은 내용의 사본을 만드는 것이기 때문에 A2셀에 위와 같은 내용의 함수를 입력한다.

 

2-1. =(IMPORTRANGE("원본 데이터 URL 또는 ID", "데이터를 가져올 탭 이름과 범위")

- 구글 시트 주소(URL): 데이터를 가져올 스프레드시트 주소창에 있는 주소(URL)를 복사한다.

원본 데이터 주소창의 구글 시트 주소

- 구글 시트 ID: 위 처럼 주소(URL)을 복사해도 되지만 아래 빨간 박스 안에 있는 스프레드시트 ID만 복사할 수도 있다.

스프레드시트의 ID만 복사해도 가능하다.

이때 주의할 점은 URL이나 ID를 꼭 큰 따옴표(" ") 속에 넣어주어야 한다.

 

2-2. =(IMPORTRANGE("원본 데이터 URL 또는 ID", "데이터를 가져올 탭 이름과 범위")

URL이나 ID를 입력한 후 쉼표(,)로 구분한 후 가져올 데이터가 있는 탭의 이름과 데이터의 범위를 입력해준다.

아래 그림을 예로 설명하면 구글 설문 결과가 모이는 탭인 '설문지 응답 시트1'에서 A2부터 마지막 응답 결과가 있는 AQ까지로 입력한다.

데이터를 가져올 탭 이름과 범위 입력

다른 탭의 데이터를 가져올 경우 다른 이름으로, 그리고 원하는 데이터 셀의 범위를 입력한다.

 

3.  스프레드시트 연결 및 권한 부여

IMPORTRANGE 함수를 입력하면 처음에 오류가 나타난다. 이때 마우스를 가져간 후 액세스 허용을 눌러주면 모든 작업이 끝난다. 이제 원본 스프레드시트에 데이터가 추가되거나 변동이 생기면 연동된 스프레드시트에 자동으로 반영된다.

스프레드시트 연결하기

 

4.  소소한 팁

- 만약 3학년 자료를 원본 데이터로하고 2학년 자료에 위 함수를 입력했다면 이제 1학년 자료는 2학년 자료를 사본 만들기 한번만 하면 된다. 그리고 각 학년계 선생님께 주소를 공유하면 끝!

- 원본 데이터를 다른 사람이 수정할 수 없도록 원본 데이터 시트를 마우스 오른쪽 버튼으로 클릭 후 시트 보호를 선택하여 나만 수정할 수 있도록 한다. 이렇게 하면 다른 사람의 실수로 데이터가 지워지는 일은 없을 것이다.

 

5.  다른 활용 예

평소 학생의 수행평가 점수를 관리 할 때 아래 그림과 같이 수행평가 영역당 하나의 스프레드시트를 만들어 관리하고, 각 영역의 점수를 IMPORTRANGE 함수를 활용하여 수행평가 결과라는 하나의 구글 스프레드시트에 모아 관리하는 편이다. 이렇게 하면 학기 말에 수행평가 점수를 입력할 때 나이스에서 엑셀 파일을 다운 받은 후 결과 값을 복사-붙여넣기하면 수행평가 결과 입력을 손쉽게 끝낼 수 있다.

(현재 개인적으로 MS 오피스를 사용중이기 때문에 가능하며 경기도 교육청은 한셀을 사용 중이기 때문에 불가능한 작업이다.)

IMPORTRANGE 함수를 활용한 수행평가 점수 관리

 

복잡해 보이고, 어려워 보이는 것이 사실이지만 먼저 겁먹지 말고 한번씩 사용해보면 좋겠습니다.