[구글 스프레드시트] 원본을 유지하며 나만의 시트를 만들자 || 외부 시트 불러오기 - IMPORTRANGE, FILTER, QUERY 함수

이용 난이도 : ★★☆☆☆ / 유용함 : ★★★★☆

 

구글 스프레드시트에서는 문서 공유를 회사 직원들과, 혹은 원하는 사용자와 함께 공유가 가능합니다.

하지만 이로 인해 타인의 문서를 임의로 편집하거나, 수정하게 될 수도 있기 때문에 문서를 공유하게 될 때 일부 인원에게만 편집권한을 부여하고, 기본적으로는 뷰어권한만 허용하는 경우도 있게 될 것입니다.
하지만 반대로 뷰어권한만을 부여받는 상황에서는 해당 데이터를 이용함에 있어 매번 작업을 하려 하는 시트를 만들어 복사, 붙여넣기를 하거나, 사본을 만든 후 가공해야하는 경우가 생길 수도 있겠죠.

심지어는 (미래에 연관된 글을 작성드릴 예정이지만) 원본 시트가 외부DB의 데이터를 주기적으로 갱신하는 데이터라면,, 이를 응용해야 하는 입장에서는 당연하게도 데이터 최신화를 하는 것에 고민에 빠지게 될 것입니다.

이걸 사람 손으로 복사하고 있는 게 말이 안된다.

보기 권한밖에 없으니 그 스프레드시트 자체에서 새로운 시트를 생성하여 작업할 수도 없고, 내가 원하는 대로 새 필터보기를 만들어서 보려 해도 임시필터 보기로 적용되기 때문에 한번 작업 후 미래에 다시 접속하면 필터 조건을 다시 지정해줘야 할 것입니다.

이를 해결하기 위해 2개의 유용한 함수와 더 나아가서 이용할 수 있는 함수를 소개드립니다.

 

---

IMPORTRANGE

제일 먼저 개인적으로 스프레드시트를 입문(?)하게 되면서 제일 유용하게 썼던 함수였습니다.
일반적으로 스프레드시트에서 함수들을 자동완성하게 되면 문자들이 대문자로 변환되어 가끔 알아보기 어려운 것들도 있긴 하지만,,, 다행히도 간단한 조합으로 된 함수이름들도 있습니다.

사용되는 변수는 시트의 ID와, 시트의 범위 두가지로
텍스트 값들이 들어가기 때문에 "" 를 이용해 변수들을 입력해 주어야 합니다.

=IMPORTRANGE("Example_URL_ID","시트1!A:Z")

1. 불러오려는 데이터의 시트 ID를 적습니다.
   전체주소인 https://docs.google.com/spreadsheets/d/asdifjao~ 를 모두 적어주어도 기능적으로는 동일하게 작동하긴 하지만, importrange 함수를 다른 함수와 함께 쓰는 경우에는 이 url이 함수를 읽게 만드는 데에 방해가 되었던 적이 많아 개인적으로는 음영처리된 부분처럼 /d/이 부분만 /edit=1234 호출하여 사용하고 있습니다.
2. 시트이름과 범위를 지정해줍니다.
   이 때는 이미 ""처리가 되어있어서 굳이 '시트1'!A:Z 의 형식으로 적어주지 않아도 잘 불러와집니다.
3. 외부 문서를 연결하는 것이기 때문에 처음에 함수를 적으면 데이터 연결과 관련한 안내가 나오는데, "허용" 바로 눌러주시면 함수가 다시 작동하기 시작합니다.

다만 이 함수가 "외부URL"의 데이터를 불러오고, 동시에 "범위"에 대해 불러오는 함수이기 때문에 범위가 너무 크면 아예 IMPORTRANGE 함수 자체에서 에러를 표시하는 경우도 있습니다.

약 9,000개의 행 x 19개의 열을 불러오게 함수 작성 후 실패하는 내용들

이럴 땐 번거롭지만 어쩔수 없이 기존처럼 시트 복사를 이용하여 여러분의 시트로 복제 후 작업을 하신다거나, 해당 데이터 또한 외부 DB에 생성되어있는 데이터라면 Google Apps Script를 제작해서 작업하는게 정신건강에 좋은 결과를 만드실 수 있습니다.
(*사실 데이터가 엄청 많지 않은 경우에는 저는 아직도 셀마다 VLOOKUP과 함께 IMPORTRANGE를 쓰고 있긴 한데, 이렇게 쓰면 구글이 많이 아파한다는 걸 꿈에서 들은 적이 있긴 합니다.)

번외로 이 함수가 같은 스프레드시트 내에서 다른 시트를 불러올때도 적용이 가능하지만, 그럴땐 굳이 함수입력 없이 ={'Sheet'!A:E} 와 같이 배열로 불러와주면 복잡한 함수 입력이 필요 없어집니다.또한 범위와 범위 간에 ";"를 입력하여 여러개를 동시에 불러올 수도 있습니다. (*대신 함수에서처럼 괄호가 자동입력되지 않기 때문에 중괄호를 직접 닫아주셔야 합니다.)

 

FILTER

이제 IMPORTRANGE로 불러와진 함수가 보이면 이 데이터들을 원하는 조건으로 자연스럽게 정제(?)하고 싶어질 것입니다.
불러와진 IMPORTRANGE의 범위에서 필요없는 데이터를 지우려 해보고 Col이나 Row를 추가하려 시도해보셨다면 성공적으로 이 함수를 이해하기 위한 더 좋은 도움을 받으실 수 있을 겁니다.

IMPORTRANGE의 함수는 URL, 범위를 직접 지정하는 것이기 때문에 함수를 불러온 시트에서도 동일한 공간을 차지하게 됩니다.

지정했던 범위 내에서 데이터를 덮어쓰면 에러가 나오게 됩니다.

때문에 IMPORTRANGE를 한 번 호출하고 난 이후로는 (편집할 수 없을 뿐더러) 편집을 권하지 않는 것을 권장드립니다.

이후 불러와진 데이터를 가공 및 원하는 데이터만을 보기 위해서 제가 처음으로 알게 되었던 함수는 FILTER 함수였습니다.

=FILTER(Sheet!A:N,Sheet!A:A=TRUE,Sheet!B:B>=14,...)

위와 같이 필터링할 범위를 제일 먼저 호출하고, 그 후에 조건을 적어주는 형식입니다.
(*FILTER 함수 자체에서 IMPORTRANGE를 쓰는 방법도 있습니다. =FILTER(IMPORTRANGE(URL,범위),IMPORTRANGE(URL,범위)>=조건,IMPORTRANGE(URL,범위)<=조건.... 의 형식으로,,,, 다만 위에서 언급한 것처럼 동시에 많은 IMPORTRANGE를 사용하는게 시간이 조금 더 소요될 수 있습니다.)

예시로 외부에 공개되어 있는 샘플 시트를 불러오고, FILTER를 적용해보겠습니다.
(구글에서 발견된 샘플 시트를 활용하였습니다. [원문])

Ctrl + `(backquote) 를 누르면 위와 같이 함수로 되어있는 셀을 쉽게 구분할 수 있습니다.

먼저 데이터를 불러오기 위해 'Sheet'에 IMPORTRANGE를 이용해 원하는 범위의 데이터를 불러와주고,
하단에 Filter라는 탭을 새로 생성해주겠습니다.

그리고 이 중에서 출금액이 10,000원 이상인 데이터들만을 걸러내보게 된다면,

= FILTER ( 'Sheet'!A2:F , 'Sheet'!F2:F >= 10000 )

위와 같이 작성하여 데이터를 가공할 수 있게 됩니다.

 

몇가지 특이점(?)을 발견하신 분들도 있겠지만,

1. IMPORTRANGE는 A1에서 함수를 입력, FILTER에서는 A2에 함수를 입력함.
2. FILTER 함수에서는 열데이터로만 함수를 지정한 것이 아닌 A2:F / F2:F 의 형식으로 범위를 지정함.
3. Filter시트에서 G:N까지의 데이터는 불러와지지 않음.

3가지 항목이 IMPORTRANGE에서 이용했던 것과는 다르게 적용이 되어 있는 것을 알 수 있습니다.
세가지를 적용해놓은 이유에 대해서 조금씩 설명을 드리자면,

1. IMPORTRANGE는 말 그대로 "범위"에 대한 내용이기 때문에, 헤더행까지도 모두 불러올 수 있습니다.
   시트를 관리하는 유형에 따라 다를 수도 있지만, 헤더 행이 자주 변동되지 않는 경우에는 IMPORTRANGE에서도 헤더 행을 제외하고 A2:R, A2:AA 를 불러올 수 있습니다. (*혹은 저처럼 FILTER나 VLOOKUP에 바로 IMPORTRANGE를 쓰는 못된 예시를 적는 경우에도 이용할 수 있습니다.)
2. FILTER 함수는 별도로 헤더 행을 구분하지 않기 때문에, A:F로 설정하게 되면 헤더 행의 데이터도 조건 검사를 하게 됩니다.
   때문에 1행의 데이터는 값만 붙여넣기를 이용해 그대로 불러오고, 필터를 걸 조건의 범위를 별도로 설정하였습니다.
   + 범위 인수의 행 수와 조건 인수의 행 수는 같아야 합니다. (=FILTER(xx!A2:F,xx!A3:A<>TRUE) 와 같이 행의 크기가 차이나면 함수 실행 불가)
3. 2번과 연계되는 내용이기도 하며, 범위를 A2:F까지로 지정했기 때문에 G열 이후의 데이터는 불러와지지 않았습니다.

또한 여기서 조금 더 응용해보고 싶으시다면 FILTER 함수 외부에 SORT 함수를 써보시는 것도 추천드립니다 :)

FILTER에서 조회된 값을 출금액 별로 오름차순 적용을 한 예시를 보여드리며,
위의 함수는 FILTER까지 궁금해하셨던 분들이라면 충분히 궁금해 하시고 찾기 위해 노력하셨을 것으로 생각되어
함수만 알려드리고, 사용법은 조금 더 찾아보는 재미를 남겨놓기 위해(?) 사용법까지는 자세히 기재하지 않겠습니다.
(SORT 함수를 직접 찾아보시고, 제가 적어둔 예시를 본다면 유사한 인수를 사용하는 함수에서 도움이 되실 것 같습니다. 아마도)

 

QUERY

(* 이 함수는 저도 비교적 최근에 알게된 함수인지라, 정보가 많이 부족할 수 있습니다.)

FILTER와 SORT를 통해 데이터 정렬을 진행하던 중 벽에 부딪히는 시점이 다가왔었습니다.
FILTER에서 여러개의 조건을 걸어야 할 때 모든 조건은 AND로만 인식이 되어 OR함수와의 혼용이 불가능하였고, 데이터의 서식이 텍스트(string)일 때 텍스트 안에서 "특정 단어"를 포함하는 조건을 만들때 FIND나 SEARCH를 이용하여 함수가 상당히 길어지게 되는,, 번거로움이 생겼었습니다.

이로 인해 초기 함수 구성 단계에서도 조건 선언에 시간이 많이 소요될 뿐더러 함수별로 들어가는 인수에 대해서 수정이 필요할때 어느 부분에서 수정을 해야 하는지 한눈에 찾아내기가 정말 고통스러운 시간이 되기도 했었죠.

업무 환경에 있어서 개발자 분들과 소통하는 일들이 많다 보니 이런 고충에 대해서 도움을 받을 수 있을지에 대해서 자문도 구해보았지만, 시트 함수는 개발과는 또다른 영역이더라구요.

다행히도 한창 서버 구조에 대해서 어느정도 배워가면서 DB에 대해서 인터넷을 통해 조금씩 배워나가고 있는 와중에, _"서버에서는 쿼리로 조건을 구성해 데이터를 조회한다."_라는 내용을 알게 되었고, 또 이 쿼리가 무엇인지에 대해서 구글링을 하다보니, 스프레드시트 참조문서에 쿼리 관련 문서가 있음을 발견하여 시트에서 응용을 해볼 수 있는지 찾아보게 되었습니다.
참조문서에 있었으니 당연하게도 스프레드시트 함수에도 존재했기에 이를 응용할 수 있게 되었습니다.

"쿼리" 라는 것 자체에 대해서 자세히 알고 있지는 않지만, DB별로 쿼리를 구성하는 방법이 조금씩 다른 것으로 알고 있습니다. 다만 참조문서에도 나와있듯이 SQL 쿼리라는 것을 잘 알고 계신다면 쉽게 익히실 수 있을 것 같아요.

=QUERY('Data_Range'!A:AE,"select A,B,C,F,L,M where (A = xxx and C contains '교통') or (A = yyy and C contains '식비'),null||-1)

서버에서는 쿼리문에서 어떤 테이블에서 불러올 것인지(from) 에 대해서도 구성을 해줘야 하지만 스프레드시트에서는 범위를 먼저 선언하기 때문에 이 'from'을 굳이 적지 않아도 데이터 구성이 가능합니다.

1. 데이터 범위 설정은 다른 함수들과 마찬가지로 범위 지정을 해주면 됩니다.
   다만, 일부 범위 호출 함수와의 차이점은 (제가 이용하던 기준으로) 범위 지정시 A2:AE와 같이 헤더행을 제외하고 불러올 필요가 없는 함수입니다. 마지막 인수에서 헤더행을 포함할지/안할지를 지정할 수 있습니다.
2. 이제 쿼리문 구성만 잘 배우면 됩니다.
   문장의 형태가 조금 있기 때문에 처음 볼때는 "이게 뭔말이지..." 싶긴 하지만, 수학 공식처럼 규칙만 외워두면 이후부터는 자유도가 대단히 상승하게 됩니다.

select : 불러오고 싶은 열을 선택합니다.
모든 열을 불러오고 싶다면 예제에서의 A,B,C,F,L,M 부분을 *로만 입력해주시면 됩니다.

where : 조건을 적어줍니다.
위에서 언급되었던 OR 조건도 인식합니다. () 사이에 조건을 넣으면 조건들이 그룹화가 되고 and 와 or을 섞어서 사용할 수 있는 자유의 몸(?)이 됩니다. 참조문서에서 실행가능한 조건 언어들을 많이 볼 수 있습니다. like, not, date 등등..

예제의 조건을 해석해보면
"A열의 데이터가 xxx이면서, C열에서 '교통'이라는 단어를 포함" 하거나,
"A열의 데이터가 yyy면서, C열에서 '식비'라는 단어를 포함"하는 데이터들의 A,B,C,F,L,M열 데이터를 불러온다.
가 됩니다.

마지막으로 쿼리문을 닫았다면 헤더행의 갯수에 대해서 선택하게 됩니다. (*저는 작업시트에 헤더 갯수가 많은 경우가 없어 입력하지 않고 패스합니다.)

이제 상단 IMPORTRANGE에서 조건을 조금 더 자세하게 걸어보고 함수로 구성해봅니다.
2020년 3월 상반기에 식비 사용내역과 하반기에 제작비로 쓰인 내역들만 날짜 역순(내림차순)으로 정렬해서 보고 싶다.
라고 하면,

=QUERY(Sheet!A:N,"select * where (C <= date '2020-03-15' and L contains '식비') or (C > date '2020-03-15' and L contains '제작비') order by C desc",1)

 

만약 이 조건들을 FILTER 함수로 처리하려 했다면, (헤더 행 복사 후) =FILTER(Sheet!A2:N,Sheet!C2:C<="2020-03-15",FIND("식비",Sheet!L2:L)) 의 데이터와 =FILTER(Sheet!A2:N,Sheet!C2:C>"2020-03-15",FIND("제작비",Sheet!L2:L))두 개의 함수 각각 모아와서 C열 기준으로 재정렬하거나, SORT와 {}처리를 함께 이용하여 하나의 함수로 만드는 방법을 고민해볼 수도 있을 것입니다.

 

---

 

 

글로 설명하기도 하고, 함수마다 특징을 적어놓다 보니 다소 어색하고 이해가 어려울 수 있는 부분이 있을 것 같습니다.
한 번씩 발견될 시 소소하게 수정하고, 앞으로 더 깔끔한 글을 한번 써보려 노력해보도록 고민해봐야겠네요. 🤔
긴 글 읽어주셔서 감사합니다.

에디터에서 보이는 방식과 실제 글에서 보이는 모양도 조금씩 다르네요. 😅
천천히 적응해보는것으로,,,