ChatGPT API 없이 엑셀 반복 업무 자동화하기 (feat. Apps script)

function accumulateOnlinePerformanceData() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('분석해야 하는 시트'); // '분석해야 하는 시트'로 이름 변경
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('누적 데이터를 저장할 시트'); // '누적 데이터를 저장할 시트'로 이름 변경
  var sourceRange = sourceSheet.getDataRange(); // '분석해야 하는 시트'의 전체 데이터 범위를 가져옵니다.
  var sourceValues = sourceRange.getValues(); // 데이터 값을 배열로 가져옵니다.

  var uniqueCheck = {}; // 중복 체크를 위한 객체
  var filteredValues = []; // 조건에 따라 필터링하고 정제할 데이터를 저장할 새 배열

  // 데이터를 순회하며 조건에 맞는지 확인
  for (var i = 0; i < sourceValues.length; i++) {
    var row = sourceValues[i];
    var includeOnline = (["경로1", "경로2"].some(path => row[9].includes(path)) || ["상세경로1", "상세경로2"].some(detailPath => row[10].includes(detailPath))) && !["제외1", "제외2"].some(exclude => row[10].includes(exclude));
    var includeEtc = (row[9] === "제외" && row[10] === "제외") && (["포함1", "포함2"].some(include => row[23].includes(include)) || ["제외1", "제외2"].includes(row[23]));
    var isEmptyC = row[1] === "제외"; // 수정: B열 값이 "제외"인 경우를 체크
    var isTestD = row[2].includes("제외"); // 수정: C열에 "제외" 포함 여부를 체크

    if (!uniqueCheck.hasOwnProperty(row[1])) {
      uniqueCheck[row[1]] = true; // 중복이 아닌 경우 객체에 추가
    } else {
      continue; // 중복인 경우 다음 반복으로 넘어감
    }

    if ((includeOnline || includeEtc) && !isEmptyC && !isTestD) {
      filteredValues.push(row);
    }
  }

  // '누적 데이터를 저장할 시트'에 데이터를 누적하여 추가
  if (filteredValues.length > 0) {
    var lastRow = targetSheet.getLastRow(); // '누적 데이터를 저장할 시트'의 마지막 행 번호를 가져옵니다.
    targetSheet.getRange(lastRow + 1, 1, filteredValues.length, filteredValues[0].length).setValues(filteredValues); // 마지막 데이터 바로 아래에 필터링된 데이터를 추가합니다.
  }
}

function onEdit(e) {
  // 특정 시트나 셀의 변화를 감지하려면 e 객체를 사용
  var range = e.range;
  var sheet = range.getSheet();
  if(sheet.getName() === '원본 데이터') { // '원본 데이터' 시트에서만 반응
    accumulateOnlinePerformanceData(); // 필터링 및 누적 함수 실행
  }
}

// 특정 조건에 맞는 데이터를 필터링하는 함수
function filterData(values) {
  var filteredValues = [];
  if (!values || values.length === 0) return filteredValues;

  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    if (!row) continue;

    var row9AsString = row[9] ? String(row[9]) : "";
    var row10AsString = row[10] ? String(row[10]) : "";
    var row23AsString = row[23] ? String(row[23]) : "";
    var row3AsString = row[3] ? String(row[3]) : "";
    var row2AsString = row[2] ? String(row[2]) : "";

    // 변경된 조건 확인
    var includeOnline = (row9AsString.includes("경로") || row10AsString.includes("상세경로")) && !row10AsString.includes("제외");
    var includeEtc = (row9AsString === "포함" && row10AsString === "포함") && ["포함1", "포함2", "포함3", "포함4"].includes(row23AsString);

    var excludeWordsInD = ['제외1', '제외2', '제외3', '제외4'];
    var isExcludedInD = excludeWordsInD.some(word => row3AsString.includes(word));
    var isCColumnEmpty = row2AsString === "" || row2AsString.trim().length === 0;

    if ((includeOnline || includeEtc) && !isExcludedInD && !isCColumnEmpty) {
      filteredValues.push(row);
    }
  }
  return filteredValues;
}

// 중복 데이터를 제거하는 함수
function removeDuplicates(values) {
  var uniqueCheck = {};
  var uniqueValues = [];
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    if (!uniqueCheck.hasOwnProperty(row[1])) {
      uniqueCheck[row[1]] = true;
      uniqueValues.push(row);
    }
  }
  return uniqueValues;
}

// 필터링 및 중복 제거된 데이터를 시트에 집계하는 함수
function accumulateDataInSheet(filteredValues, targetSheet) {
  if (filteredValues.length > 0) {
    var lastRow = targetSheet.getLastRow();
    targetSheet.getRange(lastRow + 1, 1, filteredValues.length, filteredValues[0].length).setValues(filteredValues);
  }
}

// 중복 데이터 제거
function removeDuplicatesInOnlineScore() {
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('누적 데이터를 저장할 시트');
  var dataRange = targetSheet.getDataRange();
  var dataValues = dataRange.getValues();

  if (dataValues.length > 1) {
    var header = dataValues.splice(0, 1);
    var uniqueData = removeDuplicates(dataValues);

    targetSheet.clear();
    targetSheet.appendRow(header[0]);
    if (uniqueData.length > 0) {
      targetSheet.getRange(2, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
    }
  }
}

// '분석해야 하는 시트'에서 데이터를 필터링하고, '누적 데이터를 저장할 시트'에 데이터를 집계하는 전체 프로세스를 실행하는 함수
function accumulateOnlinePerformanceData() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('분석해야 하는 시트');
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('누적 데이터를 저장할 시트');
  var sourceValues = sourceSheet.getDataRange().getValues();

  var filteredValues = filterData(sourceValues);
  var uniqueValues = removeDuplicates(filteredValues);
  accumulateDataInSheet(uniqueValues, targetSheet);

  removeDuplicatesInOnlineScore();
}

// 스프레드시트가 편집될 때마다 호출되는 함수
function onEdit(e) {
  var editedSheetName = e.source.getActiveSheet().getName();
  if (editedSheetName === '분석해야 하는 시트') {
    accumulateOnlinePerformanceData();
  }
}