이번 게시글에서 Google Sheets와 AWS를 활용하여 데이터를 효율적으로 관리하고 자동화하는 방법을 자세히 설명하겠습니다. 우선 Google Sheets는 직관적이고 사용하기 쉬운 인터페이스로 데이터 관리를 용이하게 하며, Google Apps Script를 통해 다양한 기능을 확장할 수 있습니다. AWS S3와 RDS를 활용하면 대용량 데이터 저장과 데이터베이스 관리가 가능해져 보다 전문적인 데이터 처리 및 분석이 가능합니다.
이 게시물에서는 Google Sheets와 AWS를 연동하여 텍스트와 이미지를 CRUD (생성, 읽기, 업데이트, 삭제) 작업을 수행하는 방법을 단계별로 설명하고자 합니다. 또한, Google Sheets에서 텍스트와 이미지를 추출하고 이를 AWS S3와 RDS에 업로드 및 관리하는 구체적인 방법도 함께 설명합니다. 이 가이드를 통해 Google Sheets와 AWS를 활용하여 데이터 관리 및 자동화 작업을 효과적으로 수행할 수 있게 될 것입니다. 시작해보겠습니다.
1. AWS 설정 정보
먼저 AWS RDS와 S3 설정 정보를 살펴보겠습니다. 아래 코드는 데이터베이스 및 S3에 접근하기 위한 설정 정보입니다.
주의: 민감한 정보는 모두 주석으로 표시했습니다.
// AWS RDS 설정
var address = '/* 데이터베이스 주소 */';
var db = '/* 데이터베이스 이름 */';
var user = '/* 사용자 이름 */';
var userPwd = '/* 비밀번호 */';
var port = 3306;
var dbUrl = 'jdbc:mysql://' + address + ':' + port + '/' + db;
// AWS S3 설정
var AWS_ACCESS_KEY = '/* AWS 액세스 키 */';
var AWS_SECRET_KEY = '/* AWS 비밀 키 */';
var S3_BUCKET = '/* S3 버킷 이름 */';
var S3_REGION = '/* S3 리전 */';- AWS RDS 설정: 데이터베이스의 주소, 이름, 사용자 이름, 비밀번호, 포트 번호를 설정합니다.
- AWS S3 설정: AWS S3의 액세스 키, 비밀 키, 버킷 이름, 리전을 설정합니다.
2. Google Sheets 구성
Google Sheets는 다음과 같이 구성되어 있습니다:
- A열: 포스트 ID
- B열: 생성 날짜
- C열: 수정 날짜
- D열: 상태 메시지
- E열: 제목
- F열: 저자
- G열: 발행 날짜
- H열: 내용
- I열: 공개 여부 (Boolean)
- J열 이후: 이미지 URL
각 행은 하나의 포스트를 나타냅니다. 또한 각 열은 포스트의 속성을 나타내며, 포스트 ID, 생성 날짜, 수정 날짜, 상태 메시지, 제목, 저자, 발행 날짜, 내용, 공개 여부, 이미지 URL로 구성됩니다.
3. Google Sheets에서 텍스트와 이미지 추출 방법
Google Sheets에서 텍스트와 이미지를 추출하는 방법을 설명합니다.
function extractTextAndImagesFromSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
var extractedData = [];
for (var i = 1; i < data.length; i++) {
var rowData = {
id: data[i][0] ? parseInt(data[i][0]) : null,
createdAt: data[i][1],
updatedAt: data[i][2],
status: data[i][3],
title: data[i][4],
author: data[i][5],
publishDate: data[i][6] ? Utilities.formatDate(new Date(data[i][6]), Session.getScriptTimeZone(), 'yyyy-MM-dd') : null,
content: data[i][7],
isPublic: data[i][8] ? true : false,
imageUrls: []
};
for (var col = 9; col < data[i].length; col++) {
var imageFormula = sheet.getRange(i + 1, col + 1).getFormula();
if (imageFormula.startsWith('=IMAGE("') && imageFormula.endsWith('")')) {
var imageUrl = imageFormula.slice(8, -2);
if (imageUrl) {
rowData.imageUrls.push(imageUrl);
}
}
}
extractedData.push(rowData);
}
return extractedData;
}extractTextAndImagesFromSheet함수는 Google Sheets에서 데이터를 추출하여 배열로 반환합니다.- 각 행의 데이터를 객체로 변환하고, 텍스트와 이미지 URL을 분리하여 추출합니다.
- 텍스트 데이터는 열 인덱스에 따라 추출되고, 이미지 URL은 셀의
=IMAGE("url")형식을 분석하여 추출됩니다.
4. 데이터베이스 연결 설정 함수
데이터베이스에 연결하기 위한 함수를 정의합니다.
function getConnection() {
try {
return Jdbc.getConnection(dbUrl, user, userPwd);
} catch (e) {
Logger.log('DB 연결 실패: ' + e.message);
throw e;
}
}getConnection함수는 MySQL 데이터베이스에 연결하는 방법을 제공합니다.- 데이터베이스 URL, 사용자 이름, 비밀번호를 사용하여 연결을 시도하며, 연결에 실패할 경우 오류 메시지를 기록하고 예외를 발생시킵니다.
5. 텍스트 CRUD 기능 구현
(1) 텍스트 생성
새로운 포스트를 삽입하는 함수를 정의합니다.
function insertNewPost(conn, title, author, publishDate, content, isPublic) {
var stmt = conn.prepareStatement(
'INSERT INTO post (title, author, publish_date, content, is_public, created_at, updated_at, status) VALUES (?, ?, ?, ?, ?, NOW(), NOW(), ?)',
Jdbc.Statement.RETURN_GENERATED_KEYS
);
stmt.setString(1, title);
stmt.setString(2, author);
stmt.setString(3, publishDate);
stmt.setString(4, content);
stmt.setBoolean(5, isPublic);
stmt.setString(6, '포스트 입력 완료');
stmt.executeUpdate();
var generatedKeys = stmt.getGeneratedKeys();
var postId;
if (generatedKeys.next()) {
postId = generatedKeys.getInt(1);
}
stmt.close();
return postId;
}insertNewPost함수는 새로운 포스트를 데이터베이스에 삽입합니다.- 제목, 저자, 발행 날짜, 내용, 공개 여부 등의 정보를 저장합니다.
- 포스트가 성공적으로 삽입된 후 생성된 포스트 ID를 반환합니다.
(2) 텍스트 읽기
포스트를 읽어오는 함수를 정의합니다.
function getPostById(conn, id) {
var stmt = conn.prepareStatement('SELECT * FROM post WHERE id = ?');
stmt.setInt(1, id);
var rs = stmt.executeQuery();
var post = null;
if (rs.next()) {
post = {
id: rs.getInt('id'),
title: rs.getString('title'),
author: rs.getString('author'),
publishDate: rs.getString('publish_date'),
content: rs.getString('content'),
isPublic: rs.getBoolean('is_public'),
createdAt: rs.getTimestamp('created_at'),
updatedAt: rs.getTimestamp('updated_at'),
status: rs.getString('status')
};
}
rs.close();
stmt.close();
return post;
}getPostById함수는 특정 ID를 가진 포스트를 데이터베이스에서 조회합니다.- 포스트의 모든 속성을 가져와 반환합니다.
(3) 텍스트 업데이트
기존 포스트를 업데이트하는 함수를 정의합니다.
function updatePost(conn, id, title, author, publishDate, content, isPublic) {
var stmt = conn.prepareStatement(
'UPDATE post SET title = ?, author = ?, publish_date = ?, content = ?, is_public = ?, updated_at = NOW(), status = ? WHERE id = ?'
);
stmt.setString(1, title);
stmt.setString(2, author);
stmt.setString(3, publishDate);
stmt.setString(4, content);
stmt.setBoolean(5, isPublic);
stmt.setString(6, '포스트 수정 완료');
stmt.setInt(7, id);
stmt.executeUpdate();
stmt.close();
}updatePost함수는 기존 포스트의 정보를 수정합니다.- 포스트의 ID를 기반으로 제목, 저자, 발행 날짜, 내용, 공개 여부 등의 정보를 업데이트합니다.
(4) 텍스트 삭제
포스트를 삭제하는 함수를 정의합니다.
function deletePost(conn, id) {
var stmt = conn.prepareStatement('DELETE FROM post WHERE id = ?');
stmt.setInt(1, id);
stmt.executeUpdate();
stmt.close();
}deletePost함수는 특정 포스트를 데이터베이스에서 삭제합니다.- 포스트의 ID를 사용하여 해당
- 포스트를 데이터베이스에서 삭제합니다.
6. 이미지 CRUD 기능 구현
(1) 이미지 생성
이미지를 S3에 업로드하고 데이터베이스에 삽입하는 함수입니다.
function insertImageUrlToDatabase(conn, postId, imageUrl) {
var stmt = conn.prepareStatement(
'INSERT INTO images (post_id, image_url, created_at) VALUES (?, ?, NOW())'
);
stmt.setInt(1, postId);
stmt.setString(2, imageUrl);
stmt.execute();
stmt.close();
}insertImageUrlToDatabase함수는 이미지 URL을 데이터베이스에 삽입합니다.- 포스트 ID와 이미지 URL을 저장합니다.
(2) 이미지 읽기
포스트에 연결된 모든 이미지를 읽어오는 함수를 정의합니다.
function getImagesByPostId(conn, postId) {
var stmt = conn.prepareStatement('SELECT image_url FROM images WHERE post_id = ?');
stmt.setInt(1, postId);
var rs = stmt.executeQuery();
var images = [];
while (rs.next()) {
images.push(rs.getString('image_url'));
}
rs.close();
stmt.close();
return images;
}getImagesByPostId함수는 특정 포스트에 연결된 모든 이미지 URL을 데이터베이스에서 조회합니다.- 포스트 ID를 사용하여 해당 포스트에 연결된 모든 이미지를 반환합니다.
(3) 이미지 업데이트
기존 이미지를 업데이트하는 경우는 별로 없으므로, 필요 시 새로운 이미지를 삽입하고 기존 이미지를 삭제합니다.
function updateImagesForPost(conn, postId, newImageUrls) {
var currentImageUrls = getImagesByPostId(conn, postId);
var imagesToDelete = currentImageUrls.filter(url => !newImageUrls.includes(url));
var imagesToAdd = newImageUrls.filter(url => !currentImageUrls.includes(url));
imagesToDelete.forEach(url => {
deleteImageFromS3AndDB(conn, postId, url);
});
imagesToAdd.forEach(url => {
insertImageUrlToDatabase(conn, postId, url);
});
}updateImagesForPost함수는 기존 이미지 URL을 새로운 이미지 URL로 업데이트합니다.- 필요 시 새로운 이미지를 삽입하고, 기존 이미지를 삭제합니다.
(4) 이미지 삭제
이미지를 S3와 데이터베이스에서 삭제하는 함수를 세부적으로 분리하여 정의합니다.
이미지 삭제 메인 함수
function deleteImageFromS3AndDB(conn, postId, imageUrl) {
deleteImageFromS3(imageUrl); // S3에서 이미지 삭제
deleteImageUrlFromDatabase(conn, postId, imageUrl); // 데이터베이스에서 이미지 URL 삭제
}
S3에서 이미지 삭제 함수
function deleteImageFromS3(imageUrl) {
var now = new Date();
var amzDate = Utilities.formatDate(now, 'GMT', "yyyyMMdd'T'HHmmss'Z'");
var dateStamp = Utilities.formatDate(now, 'GMT', 'yyyyMMdd');
var host = S3_BUCKET + '.s3.' + S3_REGION + '.amazonaws.com';
var contentSha256 = 'UNSIGNED-PAYLOAD';
var fileName = imageUrl.split('/').pop();
var deleteUrl = '/' + encodeURIComponent(fileName);
var canonicalRequest = [
'DELETE',
deleteUrl,
'',
'host:' + host,
'x-amz-content-sha256:' + contentSha256,
'x-amz-date:' + amzDate,
'',
'host;x-amz-content-sha256;x-amz-date',
contentSha256
].join('\n');
var hashedCanonicalRequest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, canonicalRequest).map(function(byte) {
return ('0' + (byte & 0xFF).toString(16)).slice(-2);
}).join('');
var stringToSign = [
'AWS4-HMAC-SHA256',
amzDate,
dateStamp + '/' + S3_REGION + '/s3/aws4_request',
hashedCanonicalRequest
].join('\n');
var signingKey = getSignatureKey(AWS_SECRET_KEY, dateStamp, S3_REGION, 's3');
var signature = Utilities.computeHmacSha256Signature(Utilities.newBlob(stringToSign).getBytes(), signingKey).map(function(byte) {
return ('0' + (byte & 0xFF).toString(16)).slice(-2);
}).join('');
var authHeader = 'AWS4-HMAC-SHA256 Credential=' + AWS_ACCESS_KEY + '/' + dateStamp + '/' + S3_REGION + '/s3/aws4_request, ' +
'SignedHeaders=host;x-amz-content-sha256;x-amz-date, Signature=' + signature;
var deleteOptions = {
method: 'delete',
headers: {
'Authorization': authHeader,
'x-amz-date': amzDate,
'x-amz-content-sha256': contentSha256
},
muteHttpExceptions: true
};
var response = UrlFetchApp.fetch('https://' + host + deleteUrl, deleteOptions);
if (response.getResponseCode() !== 204 && response.getResponseCode() !== 200) {
throw new Error('Failed to delete image from S3: ' + response.getContentText());
}
}
데이터베이스에서 이미지 URL 삭제 함수
function deleteImageUrlFromDatabase(conn, postId, imageUrl) {
var stmt = conn.prepareStatement('DELETE FROM images WHERE post_id = ? AND image_url = ?');
stmt.setInt(1, postId);
stmt.setString(2, imageUrl);
stmt.executeUpdate();
stmt.close();
}7. 데이터 업로드 및 업데이트
Google Sheets의 데이터를 AWS RDS와 S3에 업로드하고 업데이트하는 함수입니다.
function uploadAndUpdateToAWS() {
var conn = getConnection();
try {
var extractedData = extractTextAndImagesFromSheet();
extractedData.forEach(function(rowData) {
var id = rowData.id;
var title = rowData.title;
var author = rowData.author;
var publishDate = rowData.publishDate;
var content = rowData.content;
var isPublic = rowData.isPublic;
var currentImageUrls = rowData.imageUrls;
if (title && author && content && publishDate) {
if (id) {
updatePost(conn, id, title, author, publishDate, content, isPublic);
updateImagesForPost(conn, id, currentImageUrls);
} else {
var newPostId = insertNewPost(conn, title, author, publishDate, content, isPublic);
insertImagesForPost(conn, newPostId, currentImageUrls);
}
}
});
} catch (err) {
Logger.log('Error: ' + err.message);
throw err;
} finally {
conn.close();
}
}
function insertImagesForPost(conn, postId, currentImageUrls) {
currentImageUrls.forEach(function(imageUrl) {
insertImageUrlToDatabase(conn, postId, imageUrl);
});
}uploadAndUpdateToAWS함수는 Google Sheets의 데이터를 추출하여 AWS RDS와 S3에 업로드하고 업데이트합니다.- 각 행의 데이터를 읽고, 텍스트와 이미지를 처리하여 데이터베이스와 S3에 반영합니다.
8. 이미지 업로드 HTML 및 자바스크립트
이미지 업로드를 위한 HTML 및 자바스크립트 코드입니다.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: Arial, sans-serif;
display: flex;
flex-direction: column;
align-items: center;
justify-content: space-between;
padding: 20px;
height: 80vh;
}
#dropZone {
border: 2px dashed #ccc;
padding: 20px;
margin: 20px 0;
width: 100%;
text-align: center;
}
#dropZone.dragover {
border-color: #000;
}
#fileName {
margin: 20px 0;
}
#preview {
max-width: 60%;
height: auto;
margin: 20px 0;
}
#progress {
margin: 20px 0;
display: none;
}
button {
padding: 10px 20px;
font-size: 16px;
background-color: #4CAF50;
color: white;
border: none;
cursor: pointer;
align-self: center;
margin-top: 20px;
}
button:disabled {
background-color: #ccc;
cursor: not-allowed;
}
button:hover:enabled {
background-color: #45a049;
}
</style>
</head>
<body>
<h2>이미지 업로드</h2>
<div id="dropZone">여기에 이미지를 드래그 앤 드롭 하거나 클릭하여 선택하세요.</div>
<input
type="file" id="fileUpload" accept="image/*" style="display:none;" />
<div id="fileName"></div>
<img id="preview" />
<div id="progress">업로드 중...</div>
<button id="uploadButton" onclick="uploadFile()">업로드</button>
<script>
const dropZone = document.getElementById('dropZone');
const fileInput = document.getElementById('fileUpload');
const fileNameDisplay = document.getElementById('fileName');
const preview = document.getElementById('preview');
const progressDisplay = document.getElementById('progress');
const uploadButton = document.getElementById('uploadButton');
dropZone.addEventListener('click', () => fileInput.click());
dropZone.addEventListener('dragover', (e) => {
e.preventDefault();
dropZone.classList.add('dragover');
});
dropZone.addEventListener('dragleave', () => dropZone.classList.remove('dragover'));
dropZone.addEventListener('drop', (e) => {
e.preventDefault();
dropZone.classList.remove('dragover');
const files = e.dataTransfer.files;
if (files.length) {
fileInput.files = files;
displayFileName();
}
});
fileInput.addEventListener('change', displayFileName);
function displayFileName() {
const file = fileInput.files[0];
if (file) {
fileNameDisplay.textContent = `선택된 파일: ${file.name}`;
const reader = new FileReader();
reader.onload = function(e) {
preview.src = e.target.result;
};
reader.readAsDataURL(file);
} else {
fileNameDisplay.textContent = '';
preview.src = '';
}
}
function uploadFile() {
const file = fileInput.files[0];
if (!file) {
alert('파일을 선택하세요.');
return;
}
uploadButton.disabled = true;
progressDisplay.style.display = 'block';
const reader = new FileReader();
reader.onload = function(e) {
google.script.run
.withSuccessHandler(function(response) {
google.script.host.close();
uploadButton.disabled = false;
progressDisplay.style.display = 'none';
if (response.success) {
google.script.run.insertImageIntoCell(response.url);
alert('이미지 업로드가 성공되었습니다!');
} else {
alert('이미지 업로드가 실패되었습니다. -> ' + response.message);
}
})
.withFailureHandler(function(error) {
uploadButton.disabled = false;
progressDisplay.style.display = 'none';
alert('에러: ' + error.message);
})
.uploadImageToS3(e.target.result, file.name);
};
reader.readAsDataURL(file);
}
</script>
</body>
</html>- HTML과 자바스크립트를 사용하여 사용자가 이미지를 쉽게 업로드할 수 있도록 합니다.
- 사용자는 드래그 앤 드롭 또는 클릭을 통해 이미지를 선택하고, 업로드 버튼을 클릭하여 이미지를 업로드할 수 있습니다.
- 업로드된 이미지는 AWS S3에 저장되고, 해당 URL이 Google Sheets에 삽입됩니다.
결론
위 코드를 통해 Google Sheets의 텍스트와 이미지를 AWS S3와 RDS에 업로드하고 관리할 수 있습니다. 각 기능을 함수로 분리하여 코드의 가독성과 유지보수성을 높였습니다. 필요한 경우 각 함수에 대한 추가 설명과 디버깅 방법을 주석으로 추가하여 코드의 이해를 돕도록 했습니다. 이를 통해 텍스트와 이미지의 CRUD 기능을 손쉽게 관리할 수 있게 되었습니다.
'AWS > AWS 프로젝트' 카테고리의 다른 글
| AWS Elemental MediaTailor를 이용한 스트리밍 서비스 구축 (0) | 2024.06.04 |
|---|