구글시트 입력 자동화(팀/환불사유/은행명)

회사에서 구글시트를 많이 쓴다 

구글 시트는 앱스스크립트로 자동화 작업이 가능하기 때문에 

메인 업무를 진행하며 틈틈이 간단한 기능을 도입하였다 

 

[공통 도입방법]

- 구글 시트 열기 

- 앱스스크립트 열기 

- 아래의 각 코드 넣기 

- 트리거 각각 설정(보통 스프레드시트-수정 시로 진행) 

 

1. 팀

- 이름을 설정해 논 후, 해당 이름이 시트에 입력되면 자동으로 팀이 입력되게 함 

function updateBColumnBasedOnC() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = spreadsheet.getSheets();
  const sheetNamePattern = /^\d{4}년 고객환불내역$/; // 0000년 고객환불내역 형식의 시트를 찾기 위한 정규 표현식

  sheets.forEach(sheet => {
    if (sheetNamePattern.test(sheet.getName())) {
      const cRange = sheet.getRange('C2:C' + sheet.getLastRow()); // C열의 범위를 가져옵니다.
      const cValues = cRange.getValues(); // C열의 값을 가져옵니다.

      cValues.forEach((row, index) => {
        let cValue = row[0]; // 현재 C열의 값
        let bValue = ''; // B열에 입력할 값

        if (cValue === '홍길동' || cValue === '올라프') {
          bValue = '00팀';
        } else if (cValue) {
          bValue = '0000팀';
        } else {
          bValue = ''; // C열이 공란인 경우 B열도 공란으로 설정합니다.
        }

        // B열의 값을 설정합니다.
        sheet.getRange(index + 2, 2).setValue(bValue);
      });
    }
  });
}

 

 

2. 환불 사유 

- 환불비가 배송비에 해당하면 3000원의 배수라서 

3000원의 배수면 자동으로 '배송비 환불'로 나오게 함 

- 기타의 경우는 너무 다양해서 우선 빈칸으로 나오게 함 

function updateRefunds() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetNames = spreadsheet.getSheets().map(sheet => sheet.getName());
  const yearRegex = /^\d{4}년 고객환불내역$/;
  
  sheetNames.forEach(sheetName => {
    if (yearRegex.test(sheetName)) {
      const sheet = spreadsheet.getSheetByName(sheetName);
      const range = sheet.getRange('K:K');
      const values = range.getValues();
      const fColumn = sheet.getRange('F:F').getValues();

      for (let i = 0; i < values.length; i++) {
        const amount = values[i][0];
        const fCell = sheet.getRange(i + 1, 6);
        
        if (amount === '') {
          // K열의 데이터가 삭제된 경우 F열의 데이터도 삭제
          fCell.setValue('');
        } else if (amount % 3000 === 0) {
          // K열의 데이터가 3000원의 배수인 경우
          if (fColumn[i][0] === '' || fColumn[i][0] === '배송비 환불') {
            fCell.setValue('배송비 환불');
          }
        }
      }
    }
  });
}

 

 

3. 은행명 

- 은행별로 규칙찾아서 계좌번호 입력 시 은행 자동으로 뜨게 함

- 신한은행/우리은행 외에는 규칙성 찾기가 어려워서 우선 2개 선 도입 진행 

function processEditedRange(e) {
  var sheet = e.source.getActiveSheet();
  var sheetName = sheet.getName();
  
  // 시트 이름 패턴 확인
  if (!/^20\d{2}년 고객환불내역$/.test(sheetName)) {
    return; // '0000년 고객환불내역'과 같은 패턴이 아니면 종료
  }
  
  var editedRange = e.range;
  var editedSheet = editedRange.getSheet();
  var editedRow = editedRange.getRow();
  var editedColumn = editedRange.getColumn();
  
  var numRows = editedRange.getNumRows();
  var numCols = editedRange.getNumColumns();
  
  // 편집된 범위 내 각 셀에 대해 반복
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var row = editedRow + i - 1;
      var column = editedColumn + j - 1;
      
      var value = editedSheet.getRange(row, column).getValue();
      var hCell = editedSheet.getRange(row, 8); // 같은 행의 H열 셀
      
      if (column == 9) { // I열이 편집된 경우
        if (!value) {
          hCell.setValue(''); // I열 데이터가 삭제된 경우 H열 내용 삭제
        } else if (/^110/.test(value) && value.length == 12) {
          hCell.setValue('신한은행'); // I열 데이터가 110으로 시작하고 길이가 12인 경우
        } else if (/^1002/.test(value)) {
          hCell.setValue('우리은행'); // I열 데이터가 1002로 시작하는 경우
        } else {
          hCell.setValue(''); // 조건에 맞지 않는 경우 H열 내용 삭제
        }
      }
    }
  }
}

(참고)은행별 적금 계좌번호 패턴