구글 스프레드시트 실시간 연동(웹 크롤링 for JSON)

ME2DESIGNER.COM·2022년 3월 24일

사용목적

velog에서 REST방식에 Open API를 제공하지 않아, 글 목록 외부로 가져올 수 있는 방법이 없다. JSON 파일로 목록을 만들어 놓고 내부에서 처리할 수도 있지만, 상시 업데이트가 되는 부분이라 유지/보수 측면에서 JSON 파일을 직접 수정하는 방식은 적합하지 않다고 판다.

처음에는 Excel로 만들어 JSON 변환을 시도했는데, 생각 해보니 구글 스프레드시트 JSON으로 파싱하여 사용하는 것이 효율적이라 생각하여 작업을 진행하게됨.

구글 스프레드시트란?

구글에서 제공하는 스프레드시트 프로그램으로 MS Excel과 동일하지만 클라우드 형태로 별도의 프로그램 없이 웹브라우저를 이용하여 엑셀의 기능을 사용할 수 있다는 점에서 접근성과 생산성에 이점이 많다.

스프레드시트에서 해야하는 작업

데이터 추가

스프레드시트에서 관리하고자하는 목록을 직접 입력한다.
첫 번째 행은 key에 해당하는 이름 추가
두 번째 행 부터 value이므로 데이터를 나열한다.

URL에서 KEY 추출

아래와 같이 .../spreadsheets/d/내용 복사/edit#gid=0 URL에서 KEY 값을 추출한다.

JSON 추출 확인

추출한 KEY 값을 아래 URL 중간에 추가한다.
http://spreadsheets.google.com/tq?key=여기에 KEY를 추가&pub=1
예시 : http://spreadsheets.google.com/tq?key=1PbOKz2JGK93EX7BN1f-2T41DT5JiQ9nrSCHtHzD1U6g&pub=1
KEY 추가한 URL를 브라우저 입력시 JSON 파일이 다운로드 되는지 확인한다.
스프레드시트 5분 간격으로 업데이트 되므로, 문서 생성 후 5분 뒤 JSON 파일이 저장 가능

JSON.parse()

라이브러리 설치

NPM 또는 CDN 이용하여 구글 차트 js 로드 한다.

NPM

 npm i -D google-charts

CDN

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

데이터 파싱

구글 스프레드시트에서 복사한 KEY을 추가하면 아래 console.log() 내용을 확인 할 수 있다.

let myKey = "1PbOKz2JGK93EX7BN1f-2T41DT5JiQ9nrSCHtHzD1U6g"; // 스프레드시트 KEY

google.charts.load("current", { packages: ["corechart"] }).then(() => {
  let query = new google.visualization.Query(
    `http://spreadsheets.google.com/tq?key=${myKey}&pub=1`
  );

  query.send((response) => {
    if (response.isError()) {
      console.error(
        "Error in query: " +
          response.getMessage() +
          " " +
          response.getDetailedMessage()
      );
      return;
    }

    let dataTable = response.getDataTable().toJSON();
    let jsonData = JSON.parse(dataTable);
    let cols = jsonData.cols.map((col) => col.label);
    let rows = jsonData.rows.map((row) => {
      let newRow;

      row.c.forEach((obj, index) => {
        if (obj == null || obj == undefined) return; //빈값이 경우 정지
        obj[cols[index]] = "f" in obj ? obj["f"] : obj["v"];
        ["f", "v"].forEach((each) => delete obj[each]);
        newRow = { ...newRow, ...obj };
      });

      return newRow;
    });

    console.log(rows);
  });
});

작업결과물

리스트와 페이징 처리 작업 진행했는데, 페이징은 구현한 경험도 개념도 없어서 다른 사람이 만든 예제를 뜯어서 다시 만들었는데 기회가 생기면 코드리뷰를 통해 피드백을 받을 예정이다.
https://me2designer.com/#blog

HTML

<section id="blog">
  <div class="inner">
    <h3 class="tit_section">공부와 정보 공유 목적의 코딩블로그</h3>
    <p class="desc_section">
      더 좋은 엔지니어가 되기 위해 웹스크랩핑 정보를 체계적으로 정리하여 새로운 기술 습득력을 향상하고 있습니다.<br />일관된 스타일의 결과물, 단순하고 재사용 용의하도록 코드리뷰를 진행하고있습니다.
    </p>
    <table class="table table-hover-anchor">
      <colgroup>
        <col style="width:220px;" />
        <col style="width:*;" />
        <col style="width:200px;" />
      </colgroup>
      <tbody>
        <tr>
          <td class="cate text-left color-767676">카테고리</td>
          <td class="tit text-left">제목</td>
          <td class="date text-center color-767676">날짜</td>
        </tr>
      </tbody>
    </table>
    <div class="table-pagination">
      <span class="table-pagination-bullet"></span>
      <span class="table-pagination-prev">
        <img src="" data-images-path="/images/ico/chevron-left_regular.svg"/>
      </span>
      <span class="table-pagination-next">
        <img src="" data-images-path="/images/ico/chevron-right_regular.svg"/>
      </span>
    </div>
  </div>
</section>

CSS

#blog {
  padding-bottom: 110px;
  .tit_section {
    padding: 120px 0 30px;
    font-size: 41px;
    text-align: center;
    letter-spacing: -0.03em;
  }
  .desc_section {
    padding-bottom: 90px;
    font-size: 20px;
    color: #767676;
    text-align: center;
  }
}

#blog .table tr[data-disable="true"] {
  td {
    color: #cacaca !important;
  }
  td.tit {
    cursor: default;
    &::after {
      content: " ※ 현재 열람이 불가합니다.";
      display: inline-block;
      font-size: 0.8em;
      color: #ff0032 !important;
      opacity: 0;
      transform: translateX(30px);
    }
  }
  &:hover td.tit::after {
    opacity: 1;
    transform: translateX(10px);
    transition: 0.4s #{$G-easeHover};
  }
}

JS

const $wrap = $("#blog");
let $tbody = $wrap.find(".table tbody");
let $tr_copied = $tbody.find("tr").detach();
let $paging = $wrap.find(".table-pagination");
let $bullet_copied = $paging.find(".table-pagination-bullet").detach();

const runTableDate = totalList => {
  let default_options = {
    totalData: totalList.length, //총 데이터 수
    dataPerPage: 4, //한 페이지에 나타낼 글 수
    pageCount: 4, //페이징에 나타낼 페이지 수
    currentPage: 1, //현재 페이지
  };

  // 글목록 호출
  totalList.sort(() => Math.random() - 0.5); // array 랜덤 섞기
  let splitList = totalList.arrDivision(default_options.dataPerPage);

  const loadList = index => {
    $tbody.find("tr").remove();

    // clone() - 글목록
    splitList[--index].forEach(each => {
      let $tr_clone = $tr_copied.clone();

      $tr_clone.find(".cate").text(each.tag);
      $tr_clone.find(".tit").text(each.title);
      $tr_clone.find(".date").text(each.date);
      $tr_clone.on("click",(e) => {(
        window.open("about:blank").location.href = each.url)
      )};
      $tr_clone.appendTo($tbody);
    });
  };
  loadList(default_options.currentPage);

  // 페이징 호출
  const loadPaging = opt => {
    $paging.find(".table-pagination-bullet").remove();

    opt.totalPage = Math.ceil(opt.totalData / opt.dataPerPage); // 총 페이지 수
    opt.currentGroup = Math.ceil(opt.currentPage / opt.pageCount); // 현재 페이지 그룹

    let lastNum =
      opt.totalPage < opt.pageCount * opt.currentGroup
        ? opt.totalPage
        : opt.pageCount * opt.currentGroup;
    let firstNum = opt.currentGroup * opt.pageCount - (opt.pageCount - 1);

    // clone() - 페이징
    for (let i = firstNum; i <= lastNum; i++) {
      let $bullet_clone = $bullet_copied.clone();
      $bullet_clone.text(i);

      if (opt.currentPage === i) $bullet_clone.addClass("on");

      // Click
      $bullet_clone.on("click", (e) => {
        let pageNum = Number($(e.target).text());
        opt.currentPage = pageNum;

        // reset
        loadList(opt.currentPage);
        loadPaging(opt);
      });

      $bullet_clone.insertBefore(".table-pagination-next");
    }

    // $btnPrev
    let $btnPrev = $paging.find(".table-pagination-prev");
    let prevNum = firstNum - 1;

    if (opt.currentGroup === 1)
      $btnPrev
        .addClass("disable")
        .off("click")
        .on("click", (e) => e.preventDefault());
    else {
      $btnPrev
        .removeClass("disable")
        .off("click")
        .on("click", (e) => {
          opt.currentPage = prevNum;

          // reset
          loadList(opt.currentPage);
          loadPaging(opt);
        });
    }

    // $btnNext
    let $btnNext = $paging.find(".table-pagination-next");
    let nextNum = lastNum + 1;

    if (lastNum >= opt.totalPage)
      $btnNext
        .addClass("disable")
        .off("click")
        .on("click", (e) => e.preventDefault());
    else {
      $btnNext
        .removeClass("disable")
        .off("click")
        .on("click", (e) => {
          opt.currentPage = nextNum;

          // reset
          loadList(opt.currentPage);
          loadPaging(opt);
        });
    }
  };
  loadPaging(default_options);
};