1. 목적
구글 앱스 스크립트에서 JDBC 연결을 통해서 SQL 결과를 가져올 수 있습니다. 하지만 구글 시트에 결과값을 붙여넣기를 하기 위해서 2차원 배열로 변환하는 과정을 앱스 스크립트에서 진행하면 시간이 많이 걸립니다. 결과가 수 천행이 넘는다면 앱스 스크립트의 6분 런타임 제한에 걸려서 결과를 가져오고도 활용할 수 없을 때가 있습니다. 어떻게 하면 SQL 결과를 구글 시트에 빠르게 붙여넣기 할 수 있을까요? php 를 통해서 가능한 방법을 알아보겠습니다.
2. 앱스 스크립트에서 2차원 배열 변환의 어려움
[GAS] 오라클 DB에서 데이터 읽어오기 (JDBC) 포스트에서는 JDBC 연결을 통해서 SQL 결과를 받아서 2차원 배열로 변환하는 방법을 소개했습니다. 예제는 오라클DB 에서 데이터를 가져오는 것이었지만, mySQL DB에서 가져오는 것도 가능합니다.
포스트에서 언급한 SQL 결과를 2차원 배열로 변경하는 방법은 다음과 같습니다.
const numCols = results.getMetaData().getColumnCount();
let rows = [];
while (results.next()) {
let arr = [];
for (let col = 0; col < numCols; col++) {
arr.push(results.getString(col + 1));
}
rows.push(arr);
}
JavaScript
복사
while과 for문으로 반복해서 SQL 결과를 2차원 배열로 변환해주는데, 시간이 꽤 소요됩니다. 행 수가 많지 않은 경우에는 문제없이 2차원 배열을 생성하고, 구글 시트에 붙여넣을 수 있습니다. 하지만 행 수가 수 천 ~ 수 만 행의 경우에는 변환하는 중에 6분 런타임 제한에 걸리게 됩니다.
앱스 스크립트에서 변환하는 과정이 오래 걸린다면, 변환을 한 결과를 받아온다면 어떨까요? 그렇게 할 수 있는 방법이 있습니다. 바로 php 를 이용하는 것입니다. (예제에서는 php 를 사용하지만, 다른 언어로도 구현할 수 있습니다. )
3. php를 활용한 중계 서버
원리는 다음과 같습니다.
기존에는 앱스 스크립트에서 JDBC 연결로 DB에 직접 쿼리를 해서 결과를 가져오고, 2차원 배열로 변환을 진행합니다. 그리고 구글 시트에 붙여넣기를 합니다.
솔루션은 앱스 스크립트에서 php 중계 서버를 호출하고, php에서 DB에 쿼리를 하고, 결과를 2차원 배열로 변환해서 앱스 스크립트로 반환해주면, 즉시 구글 시트에 붙여넣기를 합니다.
php 파일은 다음과 같이 작성합니다. 예제에서는 SQL 이 정해져있는 경우이지만, 호출할 때마다 변경되는 경우에는 매개변수로 받으면 됩니다.
<?php
header('Content-Type: application/json; charset=utf-8');
$secret_key = 'autooffice';
if (!isset($_GET['key']) || $_GET['key'] !== $secret_key) {
http_response_code(401);
echo json_encode(['error' => 'Unauthorized']);
exit;
}
$host = 'host url';
$dbname = 'db';
$user = 'user';
$password = 'pwd';
$conn = new mysqli($host, $user, $password, $dbname);
$conn->set_charset('utf8');
if ($conn->connect_error) {
http_response_code(500);
echo json_encode(['error' => $conn->connect_error]);
exit;
}
$sql = "sql query";
$result = $conn->query($sql);
if (!$result) {
http_response_code(500);
echo json_encode(['error' => $conn->error]);
$conn->close();
exit;
}
// SQL 결과를 2차원 배열로 변환하는 부분
$data = [];
while ($row = $result->fetch_row()) {
$data[] = $row;
}
echo json_encode($data);
$conn->close();
?>
SQL
복사
코드에서 마지막에 2차원 배열로 변환하는 부분이 포인트입니다. 앱스 스크립트에서는 이 변환하는 부분에 오랜 시간이 소요되지만 php 의 경우에는 컴파일 및 언어구조 상 매우 빠르게 이루어집니다.
빠르게 변환한 2차원 배열을 json_encode 로 텍스트 변환해서 앱스 스크립트로 전달하게 됩니다.
앱스 스크립트는 다음과 같이 구성할 수 있습니다.
const response = UrlFetchApp.fetch('php서버주소/getSQLResult.php?key=autooffice');
const data = JSON.parse(response.getContentText());
SQL
복사
response 를 JSON.parse로 텍스트를 2차원 배열로 다시 변환해주는 작업은 순식간에 가능합니다. 이렇게 받은 배열을 구글 시트에 붙여넣기 해주면 됩니다.
4. 활용처
정기적으로 DB의 데이터를 구글 시트로 가져와서 활용해야 하는 다양한 경우에 활용할 수 있습니다. 특히 크기가 큰 SQL 결과를 가져와야 할 때 사용할 수 있습니다.
저희 고객사 중에서는 매일의 생산 및 투입 데이터를 배치로 가져올 때에 사용하시는 경우, 현재 운영중인 기기의 상태 정보를 매 시간 가져와서 구글 시트에 최신화해주는 경우 등에 사용하셨습니다.
본 포스트에서는 php 로 구현한 것을 소개했지만, 파이썬, Node.js 등으로도 구현 가능하며, 구글 클라우드 펑션(Google Cloud Function)으로 서버리스 환경으로 구현할 수도 있습니다.
.png&blockId=3b1b2d8a-486c-80ee-a547-e107ac21f2bd)
