[Apps script] 문자발송 내역 업데이트

[문제점 사진으로 정리]

하단 엑스표시 추가 개선함(= 자동화함)

 

[문제점 정리]

Description
1	(기존에는) 앱시트를 활용하여 문자 내역 업로드  오래 걸림
2	전일자, 당일자 시트 관리하기의  번거로움 존재
3	자동으로 중복 제거 / 문자 대상 선별
4	(최종) 개선후-배송지연현황시트 업로드 단계 자동화 완료  총 7단계->3단계로 업무 자동화

 

 

[추가 문제점 개선]

Description
1	배송지연 기간 2024-10-29 형식을  2024.10.29형식으로 자동 변환 (기존에는 수기로 진행)
2	해당 건 적용 완료 + (해당 작업이 트리거가 되어) 자동으로  문자 내역 업로드 진행 시작

 

 

[해결방안]

Description
1	확장프로그램 - Apps script 클릭
2	Appas script에 코드 작성
3	트리거 설정  *자동으로 하기 위함

 

 

[추가 개선]

 

Description
1	제품별 코드별로 정리하여 진행
2	각 제품 코드별로 정리 및 문자 내용에 색 구분  -> 빠르게 문자 보내기 위함

 

 

[문자 내용을 업데이트하는 코드]

// 이 함수는 '배송지연현황' 시트에서 수정이 발생할 때 자동으로 호출됩니다.
function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  
  // '배송지연현황' 시트가 수정될 때만 실행
  if (sheet.getName() === '배송지연현황') {
    generateMessageSheet();  // '배송지연현황' 시트가 수정되면 이 함수가 호출됩니다.
  }
}

// 이 함수는 '문자발송내역' 시트에 데이터를 추가하고 색상을 설정합니다.
function generateMessageSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const deleteSheet = ss.getSheetByName('시트1');
  const outputSheet = ss.getSheetByName('시트2');
  const delaySheet = ss.getSheetByName('시트3');

  // 데이터 가져오기
  const deleteData = deleteSheet.getDataRange().getValues();
  const delayData = delaySheet.getDataRange().getValues();
  const outputData = outputSheet.getRange(2, 1, outputSheet.getLastRow() - 1, outputSheet.getLastColumn()).getValues();

  // 중복 방지용 Set 생성 (중복 검사는 B, D, F 열 기준)
  const existingDataSet = new Set(outputData.map(row => `${row[1]}_${row[3]}_${row[5]}`));

  // 배송지연현황 데이터를 딕셔너리로 저장
  const delayMap = new Map(delayData.map(row => [row[0], row[2]]));
  
  // 배송지연현황의 A열 값 Set으로 저장
  const delaySet = new Set(delayData.map(row => row[0]));

  // D열 기준으로 데이터 그룹화
  const groupedData = {};

  for (let i = 1; i < deleteData.length; i++) {
    const row = deleteData[i];
    const dValue = row[5]; // D열
    const key = `${row[1]}_${dValue}_${delayMap.get(dValue) || ''}`;

    // 이미 있는 데이터는 스킵
    if (existingDataSet.has(key) || !delaySet.has(row[5])) continue; // 배송지연현황의 A열에 없으면 스킵

    if (!groupedData[dValue]) {
      groupedData[dValue] = [];
    }

    groupedData[dValue].push([
      row[0], // A열
      row[1], // B열
      row[3], // C열
      dValue, // D열
      row[8], // E열
      delayMap.get(dValue) || '', // F열
      '', // G열은 나중에 생성
      row[11], // H열
      row[12], // I열
      row[16], // J열
      '' // K열: 빈 체크박스
    ]);
  }

  // 새로운 데이터 및 색상 배열 준비
  const newData = [];
  const colors = [
    '#FFC0CB', '#FFB6C1', '#FF69B4', '#FF1493', '#DB7093', // 분홍 계열
    '#DDA0DD', '#9370DB', '#8A2BE2', '#6A5ACD', '#7B68EE'  // 보라 계열
  ];
  const colorMap = {};

  // 새로운 데이터 생성 및 색상 설정
  for (const dValue in groupedData) {
    const group = groupedData[dValue];
    const color = colors[Object.keys(colorMap).length % colors.length];

    // 색상 저장
    if (!colorMap[dValue]) colorMap[dValue] = color;

    // G열 메시지 생성
    for (const row of group) {
      row[6] = row[5] === '취소요청' 
? `안녕하세요 고객님, ~입니다. ${row[2]} 제품이 품절로 출고가 ~~~감사합니다.[수신불가]` 
        : `안녕하세요 고객님, ~입니다. ${row[2]} 제품이 재고부족으로 ${row[5]}일에 배송될 예정입니다.~~ 감사합니다.[수신불가]`;
      
      newData.push(row);
    }
  }

  // 새로운 데이터를 '문자발송내역' 시트에 추가
  if (newData.length > 0) {
    const startRow = outputSheet.getLastRow() + 1;
    outputSheet.getRange(startRow, 1, newData.length, newData[0].length).setValues(newData);

    // G열 색상 설정
    const backgrounds = newData.map(row => [colorMap[row[3]]]); // D열 기준으로 색상 설정

    // G열에 색상 일괄 적용
    outputSheet.getRange(startRow, 7, newData.length).setBackgrounds(backgrounds);
  }
}