NARADESIGN

웹표준, 웹접근성, 유니버설디자인, HTML, CSS, UI, UX, UD


구글 스프레드시트 데이터를 웹 문서에 출력하기.

본문 건너 뛰기

최근에 회사 근처 식당을 무작위로 추천해 주는 웹 앱을 하나 만들었는데요. 점심 때 다녀왔던 식당을 정리해 두었다가 자바스크립트 파일에 손코딩으로 목록을 업데이트해서 동료들에게 공유했었습니다. 사용자로부터 “식당 목록이 더 많았으면 좋겠다. 추천 식당을 사용자가 직접 업데이트하면 좋겠다.”라는 피드백을 받았습니다. 그러려면 DB를 운용하고 백엔드 개발을 해야 하는데 제 전문 분야가 아니다 보니 손쉬운 방법을 찾게 됐어요. 평소에 즐겨 사용하던 구글 설문지구글 스프레드시트 API를 이용하면 가능할 수도 있겠다고 생각했는데 정말 가능하더라고요. 오늘은 구글 스프레드시트 데이터를 웹 페이지로 뽑아오는 간단한 방법을 공유합니다.

준비

Step 1. 구글 설문지와 스프레드시트에서 응답 보기 기능 사용.

모든 데이터를 본인이 직접 통제하기를 원한다면 이 과정은 건너 뛰어도 상관 없습니다. 하지만 사용자로부터 구글 스프레드시트에 데이터를 입력 받고 싶다면 구글 설문지를 이용하는 것이 좋습니다. 구글 설문지 생성 후 스프레드시트에서 응답 보기 기능을 이용하면 사용자 입력 데이터가 구글 스프레드시트에 쌓이게 됩니다. 중요한 것은 이렇게 생성한 스프레드시트의 권한을 “누구나 읽기 가능한 공유 상태“로 만드는 것입니다. 공유 상태의 시트 데이터만 외부에서 뽑아 낼 수 있습니다.

구글 설문지 응답보기 화면

이렇게 설문지로부터 생성한 스프레드시트에서 “공유 > 공유 가능한 링크 가져오기 > 링크가 있는 모든 사용자가 볼 수 있음” 항목에 체크하면 아래와 같이 공유 URL을 생성할 수 있습니다. 공유 후 추가로 권한을 설정하지 않는 이상 사용자는 이 시트를 볼 수 있지만 편집할 수는 없습니다.

https://docs.google.com/spreadsheets/d/1M…cY/edit?usp=sharing

공유 URL에서 글꼴을 두껍게 처리한 문자열이 이 시트의 키 값입니다. 시트마다 고유의 키 값이 있으므로 제가 예시로 제시한 키 값은 여러분에게 쓸모가 없을 것입니다.

1MRRvDioS4Q40H1dxiOPDUsRE-F1REzsbbkZVfH72ecY // 키 값 예시

키 값은 구글 스프레드시트에서 데이터를 꺼내오기 위한 첫 번째 단서입니다. 구글 스프레드시트를 웹 브라우저에서 열어 놓고 있으면 주소 표시줄에서 키 값을 항상 확인할 수 있기 때문에 따로 저장하거나 메모할 필요는 없습니다.

Step 2. 구글 스프레드 시트 데이터를 텍스트 형식으로 꺼내오기.

공유 상태의 구글 스프레드시트는 구글 API URL을 통해서 스프레드시트의 값을 텍스트로 꺼내 올 수 있습니다. 구글 설문지를 통해 생성한 스프레드시트가 아니라도 상관 없습니다. 공유 상태의 구글 스프레드시트라면 어떤 시트라도(심지어 다른 사람이 만든 시트라도) 값을 꺼내 올 수 있습니다. 아래 키 값을 포함하지 않은 API URL을 클릭하면 접근 거절 메시지가 포함된 텍스트 형식의 응답을 받을 수 있습니다. 접근 거절 이유는 키 값을 포함하지 않았기 때문입니다.

https://spreadsheets.google.com/tq
// 응답 결과
google.visualization.Query.setResponse({"version":"0.6","status":"error","errors":[{"reason":"access_denied","message":"Access denied","detailed_message":"Access denied"}]});

구글 스프레드시트 API URL에 내가 만든 스프레드시트의 키 값(1M…cY)을 파라메터 형식으로 추가해서 요청하면 원하는 응답을 받을 수 있습니다. 응답 결과는 json.txt 파일입니다. json.txt 파일에는 자바스크립트 코드와 내가 생성한 스프레드시트의 셀 값들이 객체에 담겨 압축 상태로 포함되어 있습니다.

https://spreadsheets.google.com/tq?key=1MRRvDioS4Q40H1dxiOPDUsRE-F1REzsbbkZVfH72ecY

// 응답 결과
/*O_o*/ google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"555123827","table":{"cols":[{"id":"A","label":"","type":"string"},{"id":"B","label":"","type":"string"},{"id":"C","label":"","type":"string"}],"rows":[{"c":[{"v":"타임스탬프"},{"v":"추천 식당 이름"},{"v":"추천 식당 좌표"}]},{"c":[null,{"v":"팔팔해물탕"},{"v":"http://naver.me/xhk9OG37"}]}, {...} ,{"c":[null,{"v":"박민자당진아구동태찜"},{"v":"http://naver.me/5mFCMgoM"}]}]}});

결과적으로 사용자가 구글 설문지에서 작성한 내용이 공유된 구글 스프레드시트에 담기고 그 값들을 텍스트 형태로 꺼내 올 수 있는 것입니다.

Step 3. 응답 결과를 JSON 형태로 변환 후 필요한 데이터만 뽑아내기.

jQuery의 $.ajax() 메서드를 이용해서 API URL을 호출한 다음 텍스트 형식의 응답을 JSON 포멧으로 변환하면 객체에서 내가 원하는 값을 꺼내 웹 페이지에 출력할 수 있습니다. 저는 처음에 다음과 같은 코드를 작성했습니다.

jQuery(function($){
    $.ajax({
        url: 'https://spreadsheets.google.com/tq?key=1MRRvDioS4Q40H1dxiOPDUsRE-F1REzsbbkZVfH72ecY'
    }).done(function ( data ) {
        console.log( data ); // 이후 소개하는 코드는 모두 이 곳에 들어갑니다.
    }).fail(function () {
        alert('아이쿠! 데이터 불러오기 실패. 아마도 jQuery CDN 또는 일시적인 구글 API 문제. ㅜㅜ;');
    });
});

실행 결과를 웹 브라우저 콘솔에서 확인하니 json.txt 파일에서 보았던 문자열들이 빼곡히 찍혀 있었습니다. 저는 이 문자열을 JSON 형태로 변환하기 위해 아래와 같이 콘솔 로그를 한 번 더 찍어 보았습니다. JSON.parse() 메서드는 자바스크립트 객체 형식 문자열을 사용 가능한 자바스크립트 객체로 파싱합니다.

console.log( JSON.parse(data) );
// 콘솔 로그 응답 결과
Uncaught SyntaxError: Unexpected token / in JSON at position

하지만 텍스트가 JSON 포멧이 아니었기 때문에 오류를 만납니다. 응답 결과의 첫 번째 라인 주석 부분에 슬래시(/)를 포함했기 때문에 파싱 오류를 뱉은 것입니다.

저는 응답 결과 /*O_o*/google.visualization.Query.setResponse({ … }); 로부터 첫 번째 여는 중괄호 ‘{‘와 마지막 닫는 중괄호 ‘}‘ 사이의 객체 문자열 {…}만 뽑아내서 JSON.parse() 메서드가 정상 동작하도록 만들어야만 했습니다. 결국 아래와 같이 substring() 메서드를 이용하여 첫 번째 소괄호 ‘(‘ 이후 문자열부터 마지막 소괄호와 세미콜론 ‘);‘ 문자열 사이의 값 {…} 을 뽑아낸 다음 JSON.parse() 메서드를 이용하여 자바스크립트 객체로 변환하고 객체 속에서 특정 객체(JSON.parse(…).table.rows)만 뽑아 list 라는 변수에 담았습니다.

var list = JSON.parse( data.substring( data.indexOf( '(' )+1, data.indexOf( ');' ) ) ).table.rows;

console.log( data.substring( data.indexOf( '(' )+1, data.indexOf( ');' ) ) );
// 응답 결과
{"version":"0.6","reqId":"0","status":"ok","sig":"555123827","table":{"cols":[{"id":"A","label":"","type":"string"},{"id":"B","label":"","type":"string"},{"id":"C","label":"","type":"string"}],"rows":[{"c":[{"v":"타임스탬프"},{"v":"추천 식당 이름"},{"v":"추천 식당 좌표"}]},{"c":[null,{"v":"팔팔해물탕"},{"v":"http://naver.me/xhk9OG37"}]}, {...} ,{"c":[null,{"v":"박민자당진아구동태찜"},{"v":"http://naver.me/5mFCMgoM"}]}]}}

결과

결과적으로 위 소개한 방법을 통해서 11st.diner 라는 간단한 식당 추천 웹 앱을 만들었고요. 소스 코드(MIT 라이선스)는 github에 있습니다.

클라이언트 개발자가 데이터베이스 구현 없이 신속하게 간단한 서비스를 만들 때 이런 방법을 고려하면 괜찮을 것 같습니다. 다만 클라이언트에서 DOM 레디 이후 AJAX로 콘텐츠를 요청하기 때문에 로딩 성능이 빠르지 않고 콘텐츠를 모두 받을 때 까지 화면에 로딩 메시지를 출력해야 한다는 점은 단점으로 꼽을 수 있을 것 같네요.

분류: 생활의 발견,자바스크립트 | 2017년 3월 31일, 21:12 | 정찬명 | 댓글: 0개 |
트랙백URI - http://naradesign.net/wp/2017/03/31/2320/trackback/

댓글 쓰기

전송된 글이 나타나지 않는다면 필터링 된 것입니다. dece24앳gmail.com 으로 메일 주세요.
(X)HTML 코드 사용이 가능하지만 소스 코드 출력을 원하시면 <꺽쇠>는 [괄호]로 변환하여 작성해 주세요.

필수 아님

필수 아님