구글 스프레드시트로 RE100 대시보드 만들기 (2. 데이터 수집)

지난 1편에서는 구글 스프레드시트 기반으로 RE100 현황을 확인할 수 있는 대시보드를 기능 위주로 살펴보았다.

이번 글에서는 이번 대시보드 제작 과정에서 첫 단계에 해당하는  "데이터 수집" 단계에 대해서 알아볼 예정이다.

 

구글시트에서 웹 데이터 수집방법

구글 스프레드시트에서 웹페이지 데이터를 수집하는 방법으로는 크게 2가지가 있다.

 

이번 대시보드에서는 1주일에 한번씩 RE100 웹페이지를 크롤링해서 새롭게 추가된 기업이 있는지 체크하고, 있는 경우 데이터를 대시보드에 자동으로 반영한다.

이처럼 자동 스케쥴링 기능을 위해서 (자동으로 1주일에 한번씩 데이터를 수집) 데이터 수집을 위해서 앱 스크립트를 이용하였다.

 

* 앱 스크립트는 확장 프로그램 → Apps Script를 클릭하게 되면 새 창이 뜬다. 

 

앱 스크립트 화면

 

준비 1) Cheerio 설치하기

 

웹 데이터 수집 전에 Cheerio라는 라이브러리를 설치해야 한다.

웹 데이터를 수집한다는 것은 일반적으로 html로 이루어진 데이터 전체를 가져와서, 이 중에서 내가 필요한 부분 일부분만 뽑아내는 것이다.

 

RE100 홈페에지의 기업 리스트는 얼핏 보면 왼쪽 같은 화면을 하고 있지만, 크롬에서 F12를 누르면 오른쪽 같은 코드를 확인할 수 있다. 그래서 데이터를 수집할 때는 오른쪽의 html 전체를 가져와서, 이 중에 대시보드에서 필요로 하는 부분만 선별하는 작업이 필요하다.

 

html 데이터를 선별하고 추출하기 위한 (전문적인 용어로는 파싱,Parsing) 자바스크립트 라이브러리가 Cheerio이다.

파이썬에서도 html를 파싱하기 위해서 beautifulsoup4같은 라이브러리가 있는데, 완전히 동일한 기능을 해주는 라이브러리다.

 

앱 스크립트에서는 아래의 방법으로 Cheerio 라이브러리를 추가하면 된다.

1. 라이브러리 오른쪽의 십자가 아이콘 클릭
2,. 스크립트 ID에 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0 입력 후 조회 및 확인

 

준비 2) 웹페이지 수집 대상의 태그 유형(CSS Selector) 파악하기

위에서 html에 대해서 간단히 언급했는데, 데이터 수집을 위한 코드를 작성하기 전에 먼저 웹페이지에 들어가서 내가 가져오고자 하는 데이터의 특성을 파악해야 한다.

 

크롬 브라우져에서 RE100 웹페이지 (https://www.there100.org/re100-members?items_per_page=All)에 접속한 후 F12를 누르면, 개발자 도구 창이 나타난다.

이 상태에서 내거 수집하고자 하는 부분에 우측 클릭 → 검사를 누른다.

이 페이지에서 수집하고자 하는 부분은 총 5개다.

① 기업 명 (Name)

② 참여 연도(Joining Year)

③ 목표 연도 (Target Year)

④ 산업 (Industry)

⑤ 국가 (Headquarters)

 

기업 명에 마우스를 가져다가 우측 클릭을 하고, 검사를 클릭한다.

그러면 아래와 같은 문자가 바로 위에 나타난다.

div.re100-members-table-title-text

 

이 문자를 그대로 복사한 후, 개발자 도구에서 Ctrl+f를 누른 후 입력한다. 그리고 엔터 키를 클릭해본다.

한칸씩 내려가면서 각 기업의 이름이 선택되는 것을 볼 수 있다.

 

이 처럼 이 웹페이지에서는 기업의 이름이  "div.re100-members-table-title-text"라는 유형으로 저장되어 있다.

(div라는 태그인데 그 중에서도 클래스가 re100-members-table-title-text인 태그)

 

같은 방법을 다른 항목에 대해서도 확인해보면 각 데이터가 저장되어 있는 태그 유형을 확인할 수 있다.

 

 

준비 3) 2차 배열과 스프레드시트 데이터 입력의 관계

데이터 수집을 위해서 1) Cheerio 설치 와 2) 데이터 태그 유형 파악까지 완료했으니,

마지막으로 2차 배열과 스프레드시트 데이터 입력의 관계에 대해서 소개한다.

 

데이터 수집 코드를 작성할 경우, 웹페이지에서 수집된 데이터는 보통 배열(Array(자바스크립트) 또는 List(파이썬)) 형태로 호출이 된다.

이렇게 배열 형태의 데이터를 다시 스프레드시트로 입력해줘야 하는 작업이 필요한데, 구글 스프레드시트에서는 아래 코드를 이용하면 2차원 배열을 시트에 매우 빠르고 수월하게 입력할 수 있다.

(2차원 배열 : 배열 안에 배열이 또 있는 배열)

 

function writeData(){
  var data = [
    ["a","b","c"],
    ["d","e","f"]
  ]

  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data")
  sh.getRange(2,2,data.length, data[0].length).setValues(data);
  // 2행, 2열부터 입력하고, 입력 행 개수는 data.length, 입력 열 개수는 data[0].length
}

위 코드에서는

1) data라는 2차원 배열을 먼저 정의하고,

2) "data"라는 이름을 지닌 스프레드시트를 sh라는 변수로 선언해서,

3) sh라는 시트의 2번행, 2번열부터 data의 데이터를 그대로 입력하라는 코드이다.

하나의 리스트가 하나의 행으로 그대로 입력된다고 이해할 수 있다.

 

2차월 배열 → 스프레드시트로 데이터를 쓰는 것도 수월한 만큼 스프레드시트의 데이터를 2차원 배열로 읽어오는 방법도 간편하다. 아래 코드는 B2:C4에 저장된 데이터를 그대로 2차원 배열 [ ["a","b","c"], ["d","e","f"]] 로 가져와서, data라는 변수에 저장한다.

function readData(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data")
  var data = sh.getRange("B2:C4").getValues()
}

 

이번에 웹 페이지에서 총 351개의 행이 있고, 각 행마다 가져올 데이터는 5가지이다.

그래서 코드를 작성할 경우 저장되는 배열은 351행 * 5열 형태의 2차원 배열일 것이다. 

data = [

['기업명1','참여연도1','목표연도1','산업1','국가1'],

['기업명2','참여연도2','목표연도2','산업2','국가2'],

['기업명3','참여연도3','목표연도3','산업3','국가3'],

['기업명4','참여연도4','목표연도4','산업4','국가4'],

.... 

]

형태가 된다.

 

 

 

데이터 수집 코드 작성하기

 

웹페이지에서 기업 데이터를 2차원 배열로 가져오는 코드 (완성본)은 다음과 같다.

이제 getCurrent 라는 함수를 사용하면 RE100 기업 데이터를 호출할 수 있다.

function getCurrent() {
  var url = `https://www.there100.org/re100-members?items_per_page=All`
  var res = UrlFetchApp.fetch(url).getContentText();
  var $ = Cheerio.load(res);


  var memberNames = []
  $('div.re100-members-table-title-wrap').each(function() {
      memberNames.push($(this).text().trim());
  });
  
  var joinYears = []
  $('td.views-field-field-joining-year').each(function() {
      joinYears.push($(this).text().trim());
  });

  var targetYears = []
  $('td.views-field-field-target-year').each(function() {
      targetYears.push($(this).text().trim());
  }); 

  var industrys = []
  $('td.views-field-field-industry').each(function() {
      industrys.push($(this).text().trim());
  }); 

  var countrys = []
  $('td.views-field-field-headquarters').each(function() {
      countrys.push($(this).text().trim());
  }); 


  data = []
  var today = new Date()
  for (var memberIdx=0; memberIdx<memberNames.length; memberIdx++) {
  
    var row = [
      memberNames[memberIdx],
      joinYears[memberIdx],
      targetYears[memberIdx],
      industrys[memberIdx],
      countrys[memberIdx],
      today 
    ]
    data.push(row);
  } 
  return data

}

 

a) 웹페이지에 요청을 보내서, 요청의 결과값인 html 를 가져오기 + Cheerio 셋팅하기

  // 우리가 받아올 URL을 변수로 설정함
  var url = `https://www.there100.org/re100-members?items_per_page=All`

  // UrlFetchApp.fetch()를 통해서 해당 URL에 요청을 보내고, getContentText()함수를 통해서 그 요청의 결과값을 호출받는다
  // 호출받은 html를 res라는 변수에 저장해둔다.
  const res = UrlFetchApp.fetch(url).getContentText();

  // html를 파싱하고 원하는 부분만 가져올 수 있게 하는 Cheerio라는 라이브러리를 사용해서 res를 불러온다.
  // 이제 $ 라는 변수를 이용하면 html 파싱을 수월하게 할 수 있다.
  const $ = Cheerio.load(res);

b) 각 항목(기업명, 참여연도 등)의 태그유형을 통해서 데이터를 가져와서 배열에 저장하기

  // $안에 태그 조건(CSS Selector)을 기입하면, 그 태그 조건에 맞는 html element들만 다 모여서 하나의 배열이 된다.
  //  each라는 함수를 사용해서 배열의 각 element에 대해서 text만 뽑아내는 작업을 한다.
  var memberNames = []
  $('div.re100-members-table-title-wrap').each(function() {
  
  // 각 html element의 text만 뽑아내고 (text()), 만약 앞뒤로 빈칸이 있는 경우 제거하고 (trim())
  // memberName이라는 배열에 담아둔다.
      memberNames.push($(this).text().trim());
  });

 

c) 2차원 배열로 변환해주는 작업

// 2차원 배열 형태로 변형해주기.
// 업데이트일 데이터토 추가해주기
  data = []
  var today = new Date()
  
  for (var memberIdx=0; memberIdx<memberNames.length; memberIdx++) {
  
    let row = [
      memberNames[memberIdx],
      joinYears[memberIdx],
      targetYears[memberIdx],
      industrys[memberIdx],
      countrys[memberIdx],
      today 
    ]
    data.push(row);
  } 
  return data

 

스프레드시트에 데이터를 쓰기

위와 같이 작성한 함수로 데이터를 가져올 수 있으면, 다음 코드로 데이터를 "rawdata"라는 시트에 쓸 수 있다.

 

function writeData(){
  var data = getCurrent()

  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("rawdata")
  // 2행, 2열부터 data.length(351개)만큼의 행 개수, data[0].length (6개) 만큼의 열 개수인 범위를 선택해서 데이터를 쓰기
  sh.getRange(2,2,data.length, data[0].length).setValues(data);
}

앱스크립트 에디터에서 바로 실행을 클릭하면 데이터가 시트에 쓰여진 것을 볼 수 있다.

 

1행에 제목만 추가해둔다.

 

 

 

3편 [데이터 업데이트 자동화 : 트리거 설정] 에서는 트리거 설정을 통해서 웹페이지의 데이터를 주기적으로 확인해서 추가 항목이 있는 경우에만 시트에 업데이트하는 방법을 살펴볼 예정이다.