반응형
안녕하세요! 오늘은 구글 스프레드시트에서 특정 시트의 변경사항을 자동으로 감지하고, 변경이 있을 때마다 백업을 생성하는 스크립트를 소개하려고 합니다. 이 스크립트를 사용하면 데이터 손실에 대한 걱정을 덜 수 있고, 이전 버전의 데이터를 손쉽게 복원할 수 있습니다.
개요
Google 스프레드시트를 사용할 때 중요한 데이터를 보호하기 위해 자동 백업 시스템이 필요합니다. 이번 글에서는 Google Apps Script를 활용하여 특정 시트의 데이터를 감지하고 변경 사항이 있을 경우 자동으로 백업하는 방법을 소개합니다.
스크립트 소개
아래 코드는 "1번시트"라는 시트의 변경사항을 감지하여 백업을 생성하고, 백업된 시트를 관리하는 기능을 제공합니다. 또한, 지정된 최대 개수를 초과하는 오래된 백업 시트를 자동으로 삭제합니다.
코드 개요
본 스크립트는 다음과 같은 기능을 수행합니다:
- 원본 시트(1번시트)의 데이터를 확인
- 이전 백업 데이터(백업시트)와 비교하여 변경 감지
- 변경 사항이 있을 경우 새로운 백업 시트 생성
- 기존 백업 시트 정리 (최대 100개 유지)
- 자동 실행을 위한 트리거 설정
javascript
function createSheetBackup() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("1번시트");
var backupSheet = ss.getSheetByName("백업시트");
if (!sourceSheet) {
Logger.log("시트를 찾을 수 없습니다.");
return;
}
var lastRow = sourceSheet.getLastRow();
// 백업시트가 없으면 생성
if (!backupSheet) {
backupSheet = ss.insertSheet("백업시트");
var initialRange = sourceSheet.getRange("B5:J" + lastRow);
initialRange.copyTo(backupSheet.getRange(initialRange.getA1Notation()));
backupSheet.hideSheet();
Logger.log("백업시트 최초 생성");
return;
}
// B5:J 범위의 데이터 변화 감지
var checkRange = sourceSheet.getRange("B5:J" + lastRow);
var currentValues = checkRange.getValues();
var previousValues = backupSheet.getRange("B5:J" + lastRow).getValues();
// 데이터 변경 확인 (데이터 타입 일치화 후 비교)
var hasChanged = false;
for (var i = 0; i < currentValues.length; i++) {
for (var j = 0; j < currentValues[i].length; j++) {
var current = String(currentValues[i][j]).trim();
var previous = String(previousValues[i][j]).trim();
if (current !== previous) {
hasChanged = true;
Logger.log("변경 감지 - 행: " + (i+5) + ", 열: " + (j+2));
Logger.log("이전 값: " + previous + ", 현재 값: " + current);
break;
}
}
if (hasChanged) break;
}
// 변경사항이 있을 경우에만 사본 생성
if (hasChanged) {
var timestamp = new Date().toLocaleString("ko-KR");
var newSheetName = "Backup_" + Utilities.formatDate(new Date(), "Asia/Seoul", "yyyy-MM-dd_HH:mm:ss");
try {
// 새 시트를 맨 뒤에 생성
var newSheet = null;
var allSheets = ss.getSheets();
var lastPosition = allSheets.length;
// 새 시트 생성 후 맨 뒤로 이동
newSheet = ss.insertSheet(newSheetName);
ss.moveActiveSheet(lastPosition + 1);
// B5:P 범위의 데이터와 서식 복사
var copyRange = sourceSheet.getRange("B5:P" + lastRow);
copyRange.copyTo(newSheet.getRange(copyRange.getA1Notation()), {contentsOnly: false});
// 열 너비 복사 (B부터 P까지)
for (var col = 2; col <= 16; col++) {
newSheet.setColumnWidth(col, sourceSheet.getColumnWidth(col));
}
// 백업 시간 정보 추가
newSheet.getRange("B1").setValue("백업 시간: " + timestamp);
// 현재 데이터를 백업시트에 저장 (비교용)
checkRange.copyTo(backupSheet.getRange(checkRange.getA1Notation()), {contentsOnly: true});
Logger.log("백업 생성 완료: " + newSheetName);
// 오래된 백업 정리
cleanupOldBackups();
} catch (error) {
Logger.log("오류 발생: " + error.toString());
}
} else {
Logger.log("변경사항 없음 - 백업 생성하지 않음");
}
}
// 오래된 백업 시트 정리 함수
function cleanupOldBackups() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var backupSheets = [];
for (var i = 0; i < sheets.length; i++) {
var sheetName = sheets[i].getName();
if (sheetName.startsWith("Backup_")) {
backupSheets.push({
sheet: sheets[i],
name: sheetName,
time: new Date(sheetName.substring(7))
});
}
}
backupSheets.sort(function(a, b) {
return b.time - a.time;
});
var maxBackups = 100;
if (backupSheets.length > maxBackups) {
for (var i = maxBackups; i < backupSheets.length; i++) {
ss.deleteSheet(backupSheets[i].sheet);
Logger.log("오래된 백업 삭제: " + backupSheets[i].name);
}
}
}
// 트리거 설정 함수
function createTimeDrivenTrigger() {
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger('createSheetBackup')
.timeBased()
.everyMinutes(1)
.create();
Logger.log("트리거 설정 완료");
}
반응형
주요 기능 설명
1. 변경사항 감지 및 백업 생성 (createSheetBackup 함수)
- 시트 접근 및 초기 설정:
- sourceSheet: 백업 대상이 되는 "1번시트"를 가져옵니다.
- backupSheet: 이전 상태를 저장할 "백업시트"를 가져옵니다.
- 시트가 없을 경우 로그를 남기고 실행을 중지합니다.
- 백업시트 초기 생성:
- "백업시트"가 없을 경우 생성하고, 원본 시트의 데이터("B5:J" 범위)를 복사합니다.
- 백업시트는 숨겨진 상태로 설정되어 사용자에게 보이지 않습니다.
- 데이터 변경 감지:
- 원본 시트와 백업시트의 "B5:J" 범위 데이터를 가져와 비교합니다.
- 문자열로 변환 후 공백을 제거하여 정확한 비교를 수행합니다.
- 변경사항이 감지되면 로그를 남기고 반복문을 종료합니다.
- 백업 시트 생성:
- 변경사항이 있을 경우, 현재 시간을 기반으로 한 새로운 백업 시트를 생성합니다.
- 원본 시트의 "B5:P" 범위 데이터를 서식과 함께 복사합니다.
- 열 너비도 원본과 동일하게 설정합니다.
- 시트의 "B1" 셀에 백업 시간을 기록합니다.
- 이전 데이터 업데이트:
- 백업시트에 현재 데이터를 복사하여 다음 실행 시 비교에 사용합니다.
- 백업 관리 함수 호출:
- cleanupOldBackups 함수를 호출하여 오래된 백업 시트를 정리합니다.
2. 오래된 백업 시트 정리 (cleanupOldBackups 함수)
- 백업 시트 목록 생성:
- 모든 시트를 순회하며 이름이 "Backup_"으로 시작하는 시트를 수집합니다.
- 시트 생성 시간을 추출하여 객체 배열에 저장합니다.
- 백업 시트 정렬 및 삭제:
- 생성 시간 기준으로 백업 시트를 내림차순으로 정렬합니다.
- 최대 백업 개수(maxBackups)를 초과하는 시트를 삭제합니다.
3. 트리거 설정 (createTimeDrivenTrigger 함수)
- 기존 트리거 삭제:
- 스크립트에 설정된 모든 기존 트리거를 삭제합니다.
- 새 트리거 생성:
- createSheetBackup 함수를 1분마다 실행하는 시간 기반 트리거를 생성합니다.
사용 방법
1. 스크립트 추가
- 구글 스프레드시트를 열고, 상단 메뉴에서 도구 > 스크립트 편집기를 클릭합니다.
- 새로운 스크립트 파일에 위의 코드를 복사하여 붙여넣습니다.
- 스크립트 파일에 적절한 이름을 지정합니다.
2. 트리거 설정
- 스크립트 편집기에서 createTimeDrivenTrigger 함수를 선택하고 실행합니다.
- 권한 요청이 나타나면 승인 절차를 진행합니다.
- 실행이 완료되면 로그에 "트리거 설정 완료" 메시지가 나타납니다.
3. 백업 시트 확인
- 이제 스크립트가 매 1분마다 실행되어 변경사항을 감지합니다.
- 원본 시트의 "B5:J" 범위에 변경사항이 있을 경우, 자동으로 백업 시트가 생성됩니다.
- 생성된 백업 시트는 "Backup_YYYY-MM-DD_HH:mm:ss" 형식의 이름을 가지며, 원본 데이터와 서식을 포함합니다.
커스터마이징
- 백업 대상 시트 변경:
- sourceSheet 변수의 "1번시트"를 원하는 시트 이름으로 변경합니다.
- 감지할 데이터 범위 변경:
- getRange("B5:J" + lastRow) 부분에서 열("B5:J")과 시작 행(5)을 조정하여 원하는 범위로 변경합니다.
- 백업 데이터 범위 및 서식 범위 변경:
- 백업 시 데이터 복사 범위를 변경하려면 copyRange 변수의 범위를 조정합니다.
- 백업 최대 개수 변경:
- 오래된 백업 시트를 관리하는 maxBackups 변수의 값을 변경하여 백업 시트의 최대 개수를 조절합니다.
- 트리거 주기 변경:
- createTimeDrivenTrigger 함수에서 everyMinutes(1) 부분을 수정하여 트리거 실행 주기를 변경할 수 있습니다.
- 예를 들어, 5분마다 실행하려면 everyMinutes(5)로 설정합니다.
주의사항
- 권한 설정: 스크립트가 정상적으로 동작하기 위해서는 구글 계정의 권한 승인이 필요합니다. 스크립트 첫 실행 시 나타나는 권한 요청을 승인해주세요.
- 데이터 양 관리: 백업 시트가 많아지면 스프레드시트의 용량이 커질 수 있습니다. maxBackups 값을 적절히 조절하여 용량을 관리하세요.
- 숨겨진 시트 보기: 백업시트는 숨겨져 있으므로 필요할 경우 보기 > 숨겨진 시트에서 확인할 수 있습니다.
결론
이상으로 구글 스프레드시트에서 자동으로 시트의 변경사항을 감지하고 백업을 생성하는 스크립트에 대해 알아보았습니다. 이 스크립트를 활용하면 중요한 데이터의 손실 위험을 줄이고, 변경 이력을 추적하여 업무 효율을 높일 수 있습니다. 필요에 맞게 커스터마이징하여 활용하시길 바랍니다.
감사합니다!
728x90
반응형
그리드형
댓글