데이터는 분석과 의사결정을 하는데 아주 중요한 역할을 합니다. 비즈니스 분석가이거나, 연구가 또는 빅데이터를 사용하는 사람이라면 효율적으로 온라인상에 존재하는 데이터를 스프레드시트에 가져와 분석하는 것이 아주 중요한 기술이죠.
구글 시트를 분석에 사용하고 있다면, 웹 사이트에 존재하는 데이터를 일일이 복사하여 붙여넣기 하는 건 아주 비효율 적이라는 것을 아실 것입니다. 그리고 대개는 포맷도 엉성하게 뒤틀어져 있을 것입니다.
다행히도 더 좋은 방법이 있습니다. 웹 사이트에서 데이터를 효율적으로 구글 시트에 가져오는 여러 방법이 존재합니다.
웹 사이트에 있는 데이터를 구글시트에 가져오는 방법은?
구글 시트에는 웹 사이트에 있는 데이터를 가지고 오는 여러 방법을 마련해주고 있습니다. IMPORT 함수들이나 구글 시트 플러그인을 설치하면 되죠.
IMPORT 함수 패밀리는 IMPORTDATA (import data), IMPORTHTML (import html), IMPORTXML (import xml) 같이 여러 가지가 있습니다. 가지고 오는 데이터에 따라서 이 중의 함수를 골라서 쓰게 되는데, 로그인으로 막혀있는 사이트의 데이터에는 사용할 수 없습니다.
로그인으로 막혀있는 사이트의 경우에는 구글 시트의 플러그인을 설치하여 데이터를 불러올 수 있죠. 그럼 다음의 네 가지 방법을 알아보겠습니다.
IMPORTDATA
IMPORTDATA 함수를 이용하여 온라인에 존재하는 CSV나 TSV파일을 불러올 수 있습니다. 이 방법이 가장 간단한 방법이죠.
다음은 공식입니다:
=IMPORTDATA(URL, delimiter, locale)URL - (필수) 파일이 존재하는 웹 주소
delimiter - (선택) 데이터를 나누는 구분자 값
locale - (선택) 파일을 읽을 때 사용되는 로케일 (언어 설정 코드)
로케일 (locale)이란? https://www.44bits.io/ko/keyword/locale
마지막의 두 파라미터는 선택사항이고 입력하지 않으면 함수가 알아서 결정합니다. 그래서 대부분은 입력을 안 하는 것이 좋습니다.
다음의 예제의 공식은 아래의 사이트에서 CSV 파일을 불러옵니다:
=IMPORTDATA("https://people.sc.fsu.edu/~jburkardt/data/csv/crash_catalonia.csv")
URL만 입력이 되어있습니다. 그래서 IMPORTDATA 함수가 나머지 파라미터를 알아서 분석하여 데이터를 가지고 왔습니다. 중요한 점은, 이 함수는 웹 페이지가 아닌, CSV와 TSV 파일만 불러 올 수 있다는 점입니다 (위의 예제의 주소같이 URL의 마지막 부분이 csv나 tsv로 끝남). 웹 사이트의 주소를 입력하면 오류나 예상치 못한 결과가 보이게 되죠.
IMPORTHTML
IMPORTHTML 함수는 웹사이트에 존재하는 표나 리스트를 가지고 오는 함수입니다. CSV나 TSV만 가지고 올수 있는 IMPORTDATA보다는 실제로 활용도가 더 높습니다.
아래는 이 함수의 공식입니다:
=IMPORTHTML(URL, query_type, index)URL - (필수) 웹 주소
query_type - (필수) table이나 list
index - (필수) 페이지의 상단에서부터 테이블 (표) 나 리스트인지의 순번
다음의 예제는 Wikipedia의 페이지에서 테이블을 불러오는 공식입니다:
=IMPORTHTML("https://en.wikipedia.org/wiki/Demographics_of_Germany", "table", 8)
https://en.wikipedia.org/wiki/Demographics_of_Germany의 웹페이지에서 8번째의 테이블을 불러오라는 뜻이죠.
IMPORTXML
IMPORTXML 함수는 웹사이트에 존재하는 XML파일을 불러오는 함수입니다.
공식은 다음과 같습니다:
=IMPORTXML(URL, xpath_query)URL - (필수) 웹 주소
xpath_query - (필수) 불러올 XPath 쿼리
다음의 예제는 XML에서 CATALOG노드 아래에 있는 PLANT 노드의 데이터들을 불러오는 공식입니다.
=IMPORTXML("https://www.w3schools.com/xml/plant_catalog.xml", "CATALOG/PLANT")
위의 주소를 브라우저에서 치면 다음과 같은 XML 파일이 보이게 됩니다
이 파일에서 CATALOG이라는 노드 아래에는 여러 개의 PLANT라는 노드가 보이죠. PLANT의 노드에는 COMMON, BOTANICAL, ZONE, LIGHT, PRICE, AVAILABILITY라는 칼럼 데이터가 존재합니다. 이런 PLANT의 정보를 가지고 오기 위해서 CATALOG/PLANT라는 XPATH 파라미터를 이용하였습니다.
TIP: 웹 페이지 주소는 길고 복잡할 수 있으므로 다른 셀에 웹 주소를 넣고 그 셀의 값을 IMPORT 함수의 공식에 입력하는 것이 깔끔하고 관리하기가 편합니다.
HTML 태그와 XPath 쿼리를 아시면 IMPORTXML을 더 다양하게 사용할 수 있습니다. 아래의 예제는 B2 셀의 URL 주소 값의 URL 페이지에 존재하는 모든 H2 태그를 가져오는 공식입니다:
=IMPORTXML(B2, "//*/h2")
플러그인 사용하기
IMPORT 함수들 뿐만 아니라 구글 시트는 다양한 서드파티 플러그인을 지원합니다. 로그인으로 막혀있는 페이지에 있는 데이터를 불러온다던지 불러온 데이터를 매번 자동으로 업데이트를 하는 IMPORT 함수만으로 할 수 없는 기능을 보완해 줍니다.
구글 플러그인 마켓 https://workspace.google.com/marketplace
다음의 몇 가지 플러그인은 웹페이지의 데이터를 불러오는데 도움을 주고 있습니다:
Coefficient
Coefficient는 코딩을 하지 않고 데이터를 불러올 수 있게 도와주는 플러그인입니다. 데이터가 실시간 업데이트되고 불러올 데이터를 지정 할 수도 있는 장점이 있죠. Coefficient는 Salesforce, HubSpot, Google Analytics, Redshift, Locker, Tableau, MySQL 등의 여러 비즈니스 시스템과 호환이 되고 있습니다.
Coupler.io
Coupler.io도 유용한 플러그인입니다. 여러 사이트에서 데이터를 불러올 뿐만 아니라 데이터 업데이트도 스케줄을 할수 있는 장점이 있습니다. Coupler.io는 Google Analytics, Mailchimp, HubSpot, Salesforce, Shopify, Xero, Airtable, Trello 등의 사이트와 호환됩니다.
Awesome Table
Awesome Table은 데이터를 불러오는 기능에다가 불러온 데이터를 커스터마이징하여 포맷을 할 수 있는 기능이 있습니다. 데이터 업데이트를 스케줄도 할수 있습니다. QuickBooks, Xero, HubSpot, Airtable, Notion, YouTube 등의 사이트와 호환되고 있습니다.
이런 플러그인을 이용함으로써 어려운 공식이나 코드를 쓰지 않고도 데이터를 불러올 수 있습니다. 그리고 데이터 커스터마이징이나 스케줄을 이용한 데이터 업데이트 등등으로 데이터 분석의 효율을 높일 수 있죠.
참고:
