[Excel 파워쿼리] 공유 받은 구글 시트를 로컬에서 활용하기 (ft. 글또)

0. Introduction

요즘은 젊은 기업일수록 구글 스프레드 시트를 많이 사용하는 것 같다. 얼마 전에도 한 문서를 구글로 공유 받았다.

구글 시트는 동시 입력 작업을 하거나 최신 버전을 url로 공유할 수 있는 점이 편리하다.

 

하지만 나는 액셀만을 사용해온 직장인이라서 구글 시트는 영 불편해서 자주 들어가지 않게 되었다.

 

자주 업데이트 사항을 확인하면 좋을텐데 더 편하게 확인할 수는 없을까, 고민하다가 액셀 파워쿼리를 활용해서 구

글 문서를 로컬에 가져와서 내가 원하는 form으로 편집하고 새로고침해서 보는 방식으로 작업을 개선했다.

 

0.1 직면했던 문제

내가 겪었던 어려움은 온라인 커뮤니티 활동 중 생겼다. 

2주에 한 번씩 글을 작성해 제출하고 공유하는 글또라는 개발자 커뮤니티 활동을 하고 있다.

 

모든 참여자들의 제출글 목록이 Google Sheet로 공유되고 있다.

 

참여자가 400명 이상이고, 격주 단위로 글이 제출되고 있어서 많은 글들이 제출되고 있었다.

관심이 있는 참여자의 글이나 흥미로운 주제의 글들을 따로 필터링해서 보고 싶었지만, View Only 권한이라 정렬이나 필터를 할 수 없었다.

 

만약 Edit 권한이 있었다고 하더라도, 많은 사람들과 함께 보는 데이터를 내가 보고싶은 대로 바꿔서 볼 수는 없다.

가끔 공유한 시트를 누군가 임의 조작하거나 변경해서 곤란해본 경험이 있을 것이다.

이를 방지하기 위해서 View Only 권한만을 주는 것인데, 공유 받는 사람 입장에서는 아무런 조작도 할 수 없어 불편하다.

 

 

참여자들 글을 잘 챙겨읽겠다는 다짐과 달리 조회가 불편하니 잘 보지 않게 되었다.

 

그래서 평소에 사용이 익숙한 "Excel"과 Excel에서 제공하는 툴인 "PowerQuery"를 통해서 편의성을 올려보기로 했다.

 

0.2 Excel Power Query란?

많은 분들이 구글 시트나 액셀에는 익숙하겠지만, 파워쿼리는 생소하실 것 같아 간략히 소개한다.

 

(1) Power Query는 마이크로소프트 사의 액셀에서 기본 제공하는 툴이다.

따라서, Ms Office 라이선스가 있고, 액셀이 설치가 되어있다면 별도의 다운로드나 설치 없이 MS 액셀에서 바로 사용이 가능하다.

코딩이 필요 없어 일반 사용자들도 어렵지 않게 사용할 수 있다. 액셀 VBA처럼 VB coding이나 SQL을 알 필요가 없다.

파워쿼리의 기본 인터페이스를 사용해서 클릭하는 것만드로도 다양한 기능 사용이 가능하다.

 

(2) 외부 데이터 연결을 편리하게 할 수 있다.

외부 데이터를 연결해 가져와 사용할 수 있다. 액셀에 저장된 데이터 뿐 아니라 웹이나 API, 데이터베이스에 직접 연결해서 데이터를 가지고와 사용할 수 있다.

오늘 포스팅에서는, 웹(Google Sheets)에서 데이터를 가져와서 사용하는 방법을 알아본다.

 

(3) 가공 및 데이터 병합(Join)을 쉽게 할 수 있다.

외부 데이터를 가져와서 사용하는 경우 데이터를 클린징하거나 가공해서 사용하는 경우가 많다. 간단한 조작을 통해서 다음과 같은 일들을 쉽게 처리할 수 있다.

• 값 바꾸기
• 정렬
• 필터링
• 각종 연산
• 가공
• 데이터 병합
  • Inner Join
  • Outer Join
  • Cross Join 등 다양한 조인 기능 지원

그리고 파워쿼리를 통해 가공한 일련의 처리과정은 쿼리(코드)로 만들어주고, 이 코드를 연결한 데이터 업데이트와 함께 반복수행해줄 수 있다.

 

정리해 말하자면 데이터를 연결해놓고 파워쿼리로 한 번 가공을 해주면, 동일 소스에서 데이터를 가져올 때마다 내가 정의한 가공 과정을 자동으로 반복해준다.

 

0.3 What to Do (오늘 할 일)

오늘 작업 내용을 요약해 보았다. 구글 시트 Veiw Only로 공유된 파일을 액셀과 연결하고, 가공하는 방법을 알아본다.

 

다음 과정을 거치면 View Only로 공유된 구글 시트 파일을 내 로컬 액셀 파일에서 조회하고 가공 사용할 수 있다.

 

1. 구글 시트를 액셀로 가져와 동기화하기
    > importrage() 함수를 사용해서 내 구글 시트로 데이터 가져오기
    > 구글 시트를 CSV 형식으로 Web Publishing해 URL 생성
     > 생성한 URL을 사용해 액셀에서 데이터 불러들이기
2. 가져온 데이터를 파워쿼리에서 가공하기
    > 불필요한 열 제거, 순서 바꾸기
    > 정렬, 필터링
    > 사용자 지정 열 생성(함수 사용)
3. 다른 표와 병합하기(Table Join)
   > 병합할 표 파워쿼리로 불러와 Join하기
4. 새로고침으로 편하게 활용하기
   > 마무리 디자인 및 hyperlink 생성하기
   > 연결-가공을 거친 데이터를 Refresh해서 최신 버전 조회하기

 

 

위 과정을 단계별로 하나씩 소개할테니 차근 차근 따라해보며 자신의 케이스에 적용해보시기 바란다.

 

---

 

Part I. 구글 시트를 액셀로 가져와 동기화하기

 

1.1. 공유된 시트를 내 구글 시트 파일로 가져오기

먼저 공유 받은 구글 시트의 URL을 확인해야 한다.

구글 시트 주소창을 보면 docs.googl.com/spreadsheets/d/@@@@@@@/edit#gid=0 형식으로 주소가 나온다.

이 형식에서 오늘 사용할 부분은 @@@@@@@ 부분이다. 이 부분값은 구글 시트의 고유 주소키값으로, 다른 시트에서 해당 시트를 참조할 때 사용한다.

예시 캡쳐 상의 주소키값은 1Hek .........F4k 에 해당한다.

 

읽기 전용(View Only)으로 공유된 파일의 경우 웹으로 게시할 권한이 없다. 액셀 파워쿼리와 연결하기 위해서는 웹 게시가 된 URL이 필요하다. 

 

때문에 내 계정에서 새 구글 시트를 만든다. 그런 다음, 공유 받은 데이터를 가지고 오는 작업이 필요하다.

새 스프레드 시트를 만들어 준다.

 

이제 공유된 구글 시트의 데이터를 내 시트로 가져오자.

importrage()함수를 사용해서 내 구글 시트로 데이터를 가져온다.

예시에서는 'DATA'라는 시트의 A열부터 L열까지의 값을 가져오도록 작성했다.

[입력 수식 예시]

# importrange 함수
=importrange(“주소키값”, “‘시트명’!셀범위”)


# 'DATA'라는 시트의 A열에서부터 L열까지
=importrange(“1Hek....yF4”, “‘DATA’!A:L”)

 

이때 주의할 점은, 데이터가 계속 입력될 예정이라면 특정 행까지만 가져와서는 안 되고 모든 열을 다 가져와야 한다.

예1) =importrange(“1Hek....yF4”, “‘DATA’!A1:L150”) ➡️ BAD 🙅‍♀️

예2) =importrange(“1Hek....yF4”, “‘DATA’!A:L”)       ➡️ GOOD 👍

 

함수를 입력하고 잠시 기다리면 지정한 영역의 데이터가 다 불러와진다.

데이터가 잘 들어왔는지 확인한다.

일부 데이터가 불러와지지 않았다면 (1) 주소키값을 잘못 입력했거나, (2) 범위를 잘못 지정한 것이다.

함수를 수정해보면서 잘 입력되도록 한다.

 

1.2. 가져온 시트를 Web으로 Publishing 하기

다음으로는 만들어진 구글 시트를 액셀로 가져오기 위한 주소를 생성해야 한다.

[파일] - [공유하기] - [웹 게시]를 선택해준다.

 

 

구글 시트를 CSV 형식으로 Web Publishing하고, 전체 URL을 복사한다.

CSV로 게시해야 액셀에서 불러들였을 때 가장 편리하니 꼭 CSV로 게시하자.전체 URL을 이용해 액셀에서 해당 데이터를 불러들여야 하니 전체 주소를 복사한다.

 

1.3. URL을 활용해 액셀로 데이터 가져오기

이제 로컬에서 새 액셀 파일을 만들어 연다.

[데이터] 메뉴에서 [웹] 버튼을 누르면 나오는 창에서 게시해서 얻은 URL을 입력후 [확인]을 누른다.

혹시 [데이터] 메뉴에서 [웹] 버튼이 보이지 않는다면, [데이터] - [데이터 가져오기] - [기타 원본에서] - [웹]을 누르면 된다.

 

주소 입력 후 [확인]을 누르면 구글에서 생성했던 시트 데이터 일부를 불러와 보여준다.

맞게 불러와지는지 확인해본 뒤에 [데이터 변환] 버튼을 누른다.

이때, 이 데이터를 바로 액셀에서 사용하고 싶다면 바로 [로드]를 눌러서 작업을 종료해도 된다.

 

하지만 나는 파워쿼리에서 필요 없는 열은 버리고, 정렬 순서 등도 새로 바꾸는 작업을 해주기 위해서 [데이터 변환]을 눌렀다.

[데이터 변환]을 누르면 바로 파워 쿼리 창으로 넘어간다.

 

---

 

Part II. 액셀 파워쿼리로 가공하기

이번 파트에서는 불러온 데이터를 파워쿼리로 가공하는 방법을 다룬다.

 

2.1 파워쿼리 화면 소개

기본적인 메뉴를 설명하면, 탭쪽에는 행/열 변환 기능을 제공하고 있다.

그 아래에 데이터 그리드가 있다. 열 제목의 화살표를 누르면 데이터 형식 변환, 정렬, 필터링을 할 수 있다.

그리고 화면 우측에는 내가 작업한 내역이 순차적으로 표시된다. 

셀을 더블클릭하면 바로 데이터를 수정할 수 있는 액셀과는 다르게, 파워 쿼리에서는 데이터를 직접 수정할 수 없다.

 

2.2 불필요한 열 제거 및 열 순서 바꾸기

보지 않을 데이터들은 제거해 버리자.

제거할 열 이름을 우클릭한 후 [제거]를 눌러주면 바로 열이 제거된다.

열 순서는 열 이름을 드래그 앤 드롭으로 바꿀 수 있다.

필요 없는 행들을 모두 제거한다.

 

2.3 정렬과 필터링

열 이름의 화살표(🔽) 버튼을 누르면 정렬, 필터링, 빈항목 제거 등을 할 수 있다.

나는 제출 내역 중 "pass"를 제외하고, 제출일자 최근 순으로 정렬을 새로 했다.

 

작업을 마칠 때 마다 오른쪽 [쿼리 설정]-[적용된 단계]에 내가 수행한 작업이 단계별로 쌓이는 걸 확인할 수 있다.

수식창(노란색 표시)에서는 해당 단계에 수행된 쿼리를 볼 수 있고, 여기에서 쿼리를 직접 수정할 수도 있다.

 

2.4 "사용자 지정 열"을 활용해서 Merge나 연산하기

"사용자 지정 열"은 수식 입력이 필요한 고급기능이다.

여러 열을 사용해서 새로운 열을 만들 때 사용하는 기능인데,  파워쿼리 제공 함수를 사용할 수 있고, 프로그래밍에 익숙한 사람이라면, if문을 사용해서 절차적인 처리도 할 수 있다.

 

나는 데이터의 description(작성자의 글 소개), tags(글 태그), title(글 제목) 열을 하나의 열에 모아서 검색용 열을 하나 만들기로 했다.

새 열 이름을 "검색"이라고 지정했다.

사용자 지정 열 수식에는 원하는 작업 수식을 입력해주었다.

[사용 가능한 열]에서 해당 열을 더블클릭하면 수식창에 추가된다.

[입력 수식 예시]

= [description] & "#(lf)" & [tags] & "#(lf)" & [title]

열 이름은 꺽쇠괄호 "[",  "]"로 표기해주어야 한다.

"#(lf)"는 개행 문자다. (Line Feed) 

 

수학 연산은 물론 스트링 처리(split, length count, textjoin 등) 도 가능한데, 자세한 함수들과 사용 방법은 마이크로소프트의 공식 문서를 참조하자.

https://learn.microsoft.com/ko-kr/powerquery-m/power-query-m-function-reference

파워 쿼리 M 함수 참조 - PowerQuery M

 

2.5 마무리 작업

열 생성이 완료된 것을 확인한다.

마무리 작업으로 표 제목을 바꿔주었고, 제출 일자 내림차순으로 정렬을 해주었다.

 

이제 작업을 저장하고 파워쿼리에서 나가면 된다.

[닫기 및 로드]를 누르면 작업이 저장되며 액셀로 되돌아가진다.

 

액셀 창에서 잠시 기다리면, 내가 방금 파워쿼리에서 만든 데이터 시트가 새로 생긴다.

여기까지 진행하면 사용을 위한 준비가 끝났다.

 

---

 

Part III. 다른 표와 병합하기(Table Join)

파워쿼리에서는 SQL의 JOIN과 같은 기능을 제공한다.

액셀 함수로 비유하자면 VLOOKUP이나 INDEX(MATCH())로 다른 시트의 데이터를 참조해오는 것과 비슷하다.

 

나는 수많은 제출글 중에서 커피챗을 나눴거나, 동종 분야에 있는 사람들의 데이터만 골라서 보고 싶어서 이 기능을 사용해 구현해보았다.

 

3.1 병합할 표 작성 및 파워쿼리로 로드하기

액셀에 새 시트를 추가하고, 관심 있는 작성자를 표로 만들었다. 

"관심작성자" 표를 "전체글" 표와 INNER JOIN 시켜서 전체 글 중에서 관심작성자글만 보여주는 표를 만들 예정이다.

 

3.2 표 병합하기

불러와진 데이터는 알아보기 쉽도록 제목을 바꿔준다. 

그리고 [홈] - [쿼리 병합]을 누른다.

 

 

병합 설정창이 뜨면, 다음과 같이 설정해준다.

JOIN Key 열(VLOOKUP할 열)을 설정하고, JOIN할 대상 표와 Key를 정의해준다. 

그리고 조인의 종류를 택한다. SQL JOIN 방식과 용어가 완전히 동일하다. (Left outer join, right outer join, full outer join, inner join, anti join 제공)

JOIN이 익숙하지 않으신 분들은 "왼쪽 외부"를 선택해서 진행해보고 원하는 값이 다 나오지 않으면 다시 되돌아가서 다른 조인 방식을 눌러보시기 바란다.

 

3.3 필요한 열 확장하기

확인을 누르면 없었던 열이 하나 생긴다. 병합했던 "제출글" 표가 하나의 열로 접혀서 들어있다.

 

↰↱ 확장 버튼을 누르면 "제출글" 표에서 불러와서 병합할 열을 체크할 수 있다.

필요한 열만 체크하고, [원래 열 이름을 접두사로 사용]은 체크 해제한다. 이 옵션을 체크하면, "제출글.회차", "제출글.team" 이런 식으로 표이름이 접두사로 붙은 이름이 생성된다.

 

[닫기 및 로드]를 눌러 로드해주면 완료된다.

 

관심있는 작성자들이 작성한 글만 모아진 표가 들어있는 시트가 새로 생성된 걸 확인할 수 있다.

 

---

 

Part IV. 새로고침으로 편하게 활용하기

파워쿼리에서 작업은 마쳤는데 아쉬운 점이 몇 가지 보이니 액셀에서 마지막 터치를 해주었다.

 

4.1 디자인 바꾸기

일단 표가 너무 못생겼으니까 조금 다듬어준다.

[테이블 디자인]-[표 스타일] 에서 원하는 스타일을 선택한다.

[테이블 디자인] 메뉴가 활성화 되지 않은 상태라면, 표 영역 안의 아무 셀(캡쳐에서는 A1:F2 중 하나)이나 클릭해보면 메뉴가 활성화 된다.

 

4.2 비활성화된 하이퍼링크 처리

문제가 하나 더 있는데, 파워쿼리로 생성한 표의 URL의 하이퍼링크가 사라져있다.

해당 셀에 들어갔다가 나오면 하이퍼링크가 다시 생성되지만, 모든 행을 그렇게 처리해줄 수도 없는데다가 파워쿼리 데이터를 새로 고칠 때마다 하이퍼링크는 계속 사라진다.

 

이 문제는 액셀의 hyperlink() 함수로 해결할 수 있다.

파워쿼리로 만든 표 맨 앞이나 맨 뒤에 열을 추가하고, 아래와 같이 수식을 입력해준다.

[입력 수식 예시]

=HYPERLINK(제출글[@[content_url]],"LINK🌐")

 

"표"로 지정된 영역은 기존 액셀 함수와 다르게 범위와 셀값을 입력한다.

회차에 해당하는 B열을 액셀에서는 "B:B"로 지정했다면, 표로 지정된 영역에서는 "[회차]"로 입력해야 하고,

"B2"에 해당하는 곳은 "[@[회차]]" 형식으로 입력한다. 

타이핑하기보다는 클릭해서 입력하기를 권한다.

 

 

그리고 hyperlink() 함수를 사용했을 때 #VALUE! 에러가 나는 경우가 있다. 이것은  hyperlink() 함수가 256 bytes 이상의 값을 처리하지 못해 생기는 에러다.

안타깝지만 딱히 해결방법이 없어서 원래 링크를 복사해서 사용할 수밖에 없다.

 

4.3 새로고침으로 데이터 다시 불러오기

모든 작업이 완료되었다.

파워쿼리는 데이터 연결을 해주더라도 실시간으로 변경사항을 반영해주지 않는다.

사용자가 새로고침을 해주어야 원본 데이터를 새로 읽어와주기 때문에 파일을 열면 새로고침을 꼭 해주어야 한다.

 

잘 작동하는지 이틀 뒤에 새로고침 테스트를 해봤다.

총 31건의 신규 데이터를 확인할 수 있었다. 31명의 글또 동기분들이 글을 작성해주신 걸 확인할 수 있었다.

 

5. Outro

파워쿼리는 동일한 format으로 입력되는 데이터에 대해서 동일한 가공처리를 해야 할 때 아주 유용한 툴이다. 

나는 웹에서 긁어온 데이터를 처리한다거나 업무상 반복적으로 해야하는 가공에 파워쿼리를 적극적으로 활용하며 업무 시간 단축 효과를 보고 있다.

액셀에 기본 탑재된 툴인데다가 별도의 코딩 없이도 사용할 수 있다보니 사무직분분에게도 강력 추천하고 싶다.