작업을 하다보면 데이터베이스 테이블의 데이터를 구글 스프레드 시트로 뽑아내는 작업을 자주 하곤 한다.
그런데 매번 시트에 최신 데이터를 반영해야하는 경우라면 어떨까? 매우 불편하다.
구글 스프레드시트는 스크립트를 추가하여 DB를 연결해 쉽게 연동된 데이터를 보여줄 수 있다.
방법을 알아보자.
1. 새 스프레드 시트를 열고, Apps Script 선택
새 스프레드 시트를 열고, 확장 프로그램>Apps Script 를 선택한다.
(서치했을 땐 도구>스크립트 편집기 를 통해 접근하라고 하는 내용도 있다. 업데이트 되었는지 나는 위 처럼 접근했다. 둘다 참고!)
2. 연동 스크립트 코드 입력
Apps Script를 누르면 Code.gs 라는 파일이 생성되고 기본 코드가 작성되어있다.
모두 지워주고 아래 코드를 넣어주자.
(필요한 코드는 응용해서 수정!)
var connectionName = 'IP:DB Port'; // 접속할 DB 서버의 IP와 Port
var user = ''; // DB 유저 ID
var userPwd = ''; // DB 유저 PW
var db = ''; // 접속할 DB명(schema명)
var instanceUrl = 'jdbc:DBMS종류://' + connectionName;
var dbUrl = instanceUrl + '/' + db;
var sheet = SpreadsheetApp.getActiveSheet(); // SpreadSheet 객체 생성
function exportDatabase() {
var conn = Jdbc.getConnection(dbUrl, user, userPwd); // DB 연결
var stmt = conn.createStatement();
stmt.setMaxRows(1000);
var results = stmt.executeQuery("SELECT column1, column2, column3 `desc` from test_table"); // 쿼리
var i=2; // 2번째 row부터 채움
while(results.next()) {
// getRange(Integer row, Integer Column)
column1 = sheet.getRange(i, 1); // A2
column2 = sheet.getRange(i, 2); // B2
column3 = sheet.getRange(i, 3); // C2
desc = sheet.getRange(i, 4); // D2
column1.setValue(results.getString("title")); // 현재 row의 column1 컬럼 값
column2.setValue(results.getString("content"));
column3.setValue(results.getString("author"));
desc.setValue(results.getString("desc"));
i++;
}
/* 연결 해제 */
results.close();
stmt.close();
conn.close();
}
코드 설명은 생략! (대충 자바와 자바스크립트를 안다면 이해할 수 있는 내용이라 pass..)
[참고] Mysql(MariaDB) 연동 시 몇가지 꿀팁!
1. 컬럼명으로 desc 를 사용할 경우 select 문에 넣을 때 에러가 난다.
`desc` 이렇게 백틱 문자로 묶으면 해결 가능하다.
2. mariaDB 대신 mysql 을 DBMS 명으로 사용하자
개발코드에서는 database url 로 mariadb 를 사용했는데 여기서는 에러난다.
mysql 으로 바꾸면 잘 연결된다. (어차피 db의 사상이 같아서 상관없나보다..)
3. 코드 저장 및 실행
왼쪽 저장모양 아이콘을 누르면 저장되고, 실행 버튼이 활성화된다.
저장 후 실행을 돌려서 에러가 발생하진 않았는지 코드 상태를 체크한다.
4. 배포
1) 배포>새 배포 선택
2) 팝업 창에서 [웹 앱] 유형 선택
3) 설명, 인증, 권한 설정 작성
4) [배포] 클릭
엑세스 승인 요청이 뜨고 흐름대로 진행하면 배포가 정상 완료된다.
초기 생성했던 스프레드시트를 확인해보고 데이터가 정상 반영되어있으면 완료!
간단하게 데이터베이스와 구글 스프레드시트를 연동하는 방법을 알아보았다.
처음엔 조금 귀찮을 수 있어도 생각보다 요긴하게 쓰일 수 있으니 참고해서 사용해보자 :)
참고
[MySQL] MySQL 데이터를 구글 스프레드 시트로 불러오기