문서화프로젝트3: 데이터베이스

부제 : Javascript로 Google Spreadsheet 내용 불러오기

• 문서화프로젝트1: 왜 HTML인가? 무엇을 할 수 있는가? (표, 그림, 수식 연동 방법)
• 문서화프로젝트2: 작업환경 설정하기, 무료저장공간 얽기 (Git, IIS, VsCode)
• 문서화프로젝트3: 데이터베이스 만들기 (구글스프레스드시트)

 

목차

▷ 서론  - 왜 데이터베이스인가? ▷ 본론  - 스크립트 작성  - 예제파일

 

왜 데이터베이스인가?

 Javascript의 단점이라면, 페이지를 새로고침하면 처음부터 다시 진행해야한다는 점과 여러명의 사용자가 실시간으로 화면을 공유할 수 없다는 점일 것이다. 데이터베이스 서버를 구축한다는 것은 상당히 번거로운 일이다. 데이터베이스라면 우리는 엑셀에 익숙해 있다. 엑셀에서 웹 크롤링하는 기술은 잘 알려져 있다. 다만 외부에서 엑셀로 업로드하는데 한계가 있다. MS에서 Access라는 옵션을 선보인 적 있지만, 뜬금없는 Visual Basic과 애매한 SQL문법때문에 웬지 모를 거부감이 있다. 게다가 인터페이스는 windows95를 연상케한다.

	데이터 삽입 (외부 → 내부)	데이터 참조(내부 → 외부)
엑셀	방법이 없음	데이터형식 함수, 데이터가져오기, 웹 크롤링
구글 스프레드시트	설문지 Apps script Google 개발자:API	웹 게시 자바스크립트+쿼리

 

포스팅에 앞서 테스트 문서를 작성하면서 최초의 포스팅이 2014년부터 시작되었다는 것을 확인 할 수 있었다. 문제는 내가 무엇을 원하는지, 무엇을 하고자 하는지에 대한 정보를 찾기 힘들다는 것이었다. 내가 하려는 것은 외부에 공개하는 html문서에 포함된 Javascript를 통해서 이미 작성한 구글스프레드시트의 내용을 조작해서 보여 줄 것이다. 파워쉘로 테스트 한 포스팅도 있었다. CLI기반은 공유하기도 애매하고, 엑셀조차 전문성이 요구되기 때문에 최대한 심플하게 모바일에서 보여질만한 페이지를 만들어야 한다고 생각했다.

※ 시작하기 전에, 웹 서버를 반드시 거쳐야 한다. 그렇지 않으면 동작하지 않는다. 왜 안되는지 몰라서 많은 시간을 낭비할 수 있다. 테스트 서버는 "프로젝트2:환경설정"을 참조하자.

 

연말에 상품으로 구입한 상품들...

우선 결과물을 보자면,

3열은 경품이 이미 지급되었을 상황을 가정해서 작성되었다. 영상에 나오지만, 기본적으로 스프레드가 변경되면 자동으로 저장을 해주므로 바로바로 반영이 되는데, 영상 중간에 보면 저장버튼을 누르지 않았을 때, 일부만 반영이 되는 모습을 볼 수 있다.

 

스크립트 작성

컨셉을 보드로 그려보자면,

구글서칭을 하다보니 API키를 만들고, google script도 작성을 했었는데, 우선은 (지난 포스팅에서 준비한) 웹 서버만 준비되어있다면, 별다른 설치 없이 사용할 수 있다는 것이 장점이다. 스프레드시트에 자유롭게 테이블을 만든다. 그리고 공유할 때, 주의할 점은 링크를 반드시 전체 공개로 생성해야 한다는 점이다.

다음 예제 코드에서 수정 할 부분은 2군데인데, sheetId와 sheetName을 변경해야 한다.

const sheetId = '1QtMO1DqmyNIz6nP2QsnvF349I1im1EbIemDbCN6N_zw';
const base = `https://docs.google.com/spreadsheets/d/${sheetId}/gviz/tq?`;
const sheetName = '연말모임상품리스트(2022)';

const data = []

document.addEventListener('DOMContentLoaded', init)
const output = document.querySelector('.output')
function init() {
    update("Select *");
}

function update(query){
    const eq = encodeURIComponent(query);
    const url = `${base}&sheet=${sheetName}&tq=${eq}`
    fetch(url)
    .then(res => res.text())
    .then(rep => {
        //Remove additional text and extract only JSON:
        const jsonData = JSON.parse(rep.substring(47).slice(0, -2));
        console.log(rep)
        const colz = [];
        const tr = document.createElement('tr');

        // 첫 줄 추출
        jsonData.table.cols.forEach((heading) => {
            if (heading.label) {
                let column = heading.label;
                colz.push(column);
                const th = document.createElement('th');
                th.innerText = column;
                tr.appendChild(th);
            }
        })

        // 첫 줄 출력
        output.appendChild(tr);

        // 데이터 추출
        jsonData.table.rows.forEach((rowData) => {
            const row = {};
            colz.forEach((ele, ind) => {
                row[ele] = (rowData.c[ind] != null) ? rowData.c[ind].v : '';
            })
            data.push(row);
        })
        
        processRows(data);
    })
}

// 데이터 출력
function processRows(json) {
    json.forEach((row) => {
        const tr = document.createElement('tr');
        const keys = Object.keys(row);
    
        keys.forEach((col) => {
            const td = document.createElement('td');
            td.textContent = row[col];
            tr.appendChild(td);
        })
        output.appendChild(tr);
    })
}

총 4개의 파일이 있는데(슬라이드2를 참조), 발췌한 파일은 giftlist.js이다. Query에서 각 열은 첫 째 열이 아니고, A, B, C... 열을 기준으로 한다. 전체 파일 참조는 git(https://github.com/iseohyun/html/tree/main/examples/giftlist)을 참조하자. 예를 들어, 

Select A, B where C=true

위 명령은 'No'열과 '상품명'열만 발췌를 하는데, 'YN'이 true인 조건만 반환하게 된다. URL로 요청을 하기 때문에, 쿼리를 인코딩 해줘야 하고, 수동으로 작성할 때는 빈 칸 대신 +를 넣어도 된다. Javascript가 급한대로 배워서 쓰다보니, 슬라이드 3처럼 구현을 해야하지만, 해결하지 못하고 일단 여기까지 하는걸로... 해결해주신다면 소정의 감사한 마음을 갖겠습니다. ㅠㅠ

• sheetId : 문서 URL에서 발췌
• sheetName : 스프레드시트 파일명

sheetId, 주소창에서 선택한 부분을 발췌

참고한 페이지:
https://dabid.tistory.com/41(국문:코드참조)
https://asbnotebook.com/fetch-google-spread-sheet-data-using-javascript/(영문:코드참조)
https://velog.io/@whdnjsdyd111/CSS-%EB%B2%84%ED%8A%BC-%EC%9D%B4%EC%81%98%EA%B2%8C-%EA%BE%B8%EB%AF%B8%EA%B8%B0(국문:버튼디자인)

 

데이터베이스를 javascript에서 다루기 쉽도록 select.js에서 변경하였다.

// 데이터 추출
function getList(json) {
    var list = [];
    json.forEach((row) => {
        const keys = Object.keys(row);
        var st= [];
        keys.forEach((c) => {st.push(row[c])})

        list.push(`(${st[0]})<br><br>${st[1]}`);
    })

    var ran = parseInt(Math.random()*list.length);
    document.getElementById('text').innerHTML = list[ran];
}

 

파일 참조

코드:
https://github.com/iseohyun/html/tree/main/examples/giftlist

스프레드시트:
https://docs.google.com/spreadsheets/d/1QtMO1DqmyNIz6nP2QsnvF349I1im1EbIemDbCN6N_zw/edit?usp=sharing 

연말모임상품리스트(2022)

 

결과물:
https://iseohyun.github.io/html/examples/giftlist/giftlist.html