Apps Script로 Google Sheets 기반의 상품 관리 시스템 만들기

 

💡 Google Sheets 기반의 상품 관리 시스템을 만들어본다.

 

 

 

 

얼마전에 간단한 상품등록 화면을 구현해보았다.

상품 등록, 수정, 삭제가 가능한 시스템으로 고도화 해본다.

Apps Script로 Google Sheets에서 상품 관리 sheet 만들기

 

 

 

사용자들이 자신의 Google Sheets에서 상품 정보들을 입력하고, 리스트를 확인만 할 수 있다.

관리자용 Google Sheets 에서 상품 정보 리스트를 확인하고 이력 관리까지 할 수 있는 간단한 시스템을 구현해본다.

 

 

일반 사용자들의 입력 화면

입력폼과, 상품리스트 화면이 있다.

상품리스트 화면은 IMPORTRANGE를 이용해 보기만 가능하다.

 

Apps Script로 Google Sheets의 마스터성 데이터 관리하기

 

관리자 용 Google Sheets

상품리스트, 수정history, 삭제history로 구성하였다.

 

 

1. 저장 기능 구현

1.1 신규 등록

key 값인 상품 ID가 리스트에 존재하지 않으면, 신규 row를 쌓는다.

 

 

 

function saveProduct() {
  // 데이터 입력 시트 
  const inputSheetName = "지사_입력폼";

  const sheet     = SpreadsheetApp.getActive().getSheetByName(inputSheetName);
  const dataRange = sheet.getRange("D4:D14");
  const values    = dataRange.getValues();

  // 입력폼 위치정보
  let inputArr = [
                  {"row":0,   "value":"상품 ID"}
                , {"row":2,   "value":"상품명"}
                , {"row":4,   "value":"상품구분"}
                , {"row":6,   "value":"브랜드"}
                , {"row":8,   "value":"상품금액"}
                , {"row":10,  "value":"상품설명"}
                ];

  var cnt = 0;
  
  let dataRow = new Array(1);
  dataRow[0]  = new Array(inputArr.length);

  // 입력 데이터 확인 및 가공
  for( i in values ) {
    for ( j in inputArr ) {
      if ( i == inputArr[j].row) {
        const inputValue = values[i][0];
        if ( inputValue == '' || inputValue.length < 1 ) {
          alertMessage("오류", inputArr[j].value  + "은(는) 필수입력 값입니다.");
          return; 
        }
        dataRow[0][cnt] = values[i];
        cnt++;
      }
    }
  }

  // 데이터 저장 시트 정보
  const dataSheetId     = "GOOGLE_SHEET_ID";
  const dataSheetName   = "상품리스트";
  const dataSheetSs     = SpreadsheetApp.openById(dataSheetId);
  const dataSheet       = dataSheetSs.getSheetByName(dataSheetName);

  const dataListLastRow = dataSheet.getLastRow() + 1;
  var   dataListRange   = dataSheet.getRange(`A${dataListLastRow}:I${dataListLastRow}`);
  const dataListValues  = dataSheet.getRange(`A2:I${dataListLastRow}`).getValues();

  var executeGubun = "저장";
  var createdDttm, updatedDttm;

  for ( k in dataListValues ) {
    // 수정시 로직 생성
    if ( dataRow[0][0] == dataListValues[k][0] ) {
      createdDttm = getDateTime(dataListValues[k][6]); // 등록일시
      updatedDttm = getDateTime();                     // 수정일시

      dataListRange   = dataSheet.getRange(`A${parseInt(k)+2}:I${parseInt(k)+2}`);
      const modifyValues = dataListRange.getValues();
      modifyValues[0][6] = getDateTime(modifyValues[0][6]);     // 등록일시
      modifyValues[0][7] = getDateTime(modifyValues[0][7]);     // 수정일시
      modifyValues[0].push(Session.getActiveUser().getEmail()); // 수정자

      // 수정 history 시트 정보
      const modifySheetName = "수정history";
      const modifySheet     = dataSheetSs.getSheetByName(modifySheetName);
      const modifyLastRow   = modifySheet.getLastRow() + 1;

      // 수정 history 저장
      modifySheet.getRange(`A${modifyLastRow}:J${modifyLastRow}`).setValues(modifyValues).setBorder(true, true, true, true, true, true);

      executeGubun = "수정";
      break;
    }
    createdDttm = getDateTime();   // 등록일시
    updatedDttm = '';              // 수정일시
  }

  dataRow[0][cnt++] = [createdDttm];                        // 등록일시
  dataRow[0][cnt++] = [updatedDttm];                        // 수정일시
  dataRow[0][cnt++] = [Session.getActiveUser().getEmail()]; // 작성자

  // 데이터 시트에 저장
  dataListRange.setValues(dataRow).setBorder(true, true, true, true, true, true);

  // 입력폼 초기화
  dataRange.clearContent();
  
  // 성공 메세지 
  alertMessage("성공", `데이터 ${executeGubun}을 성공하였습니다.`);
}

/**
 * KST 시간 반환 
 */
function getDateTime(curr) {
  if ( curr == null ) {
    curr = new Date();
  }
  const utc           = curr.getTime() + (curr.getTimezoneOffset() * 60 * 1000);
  const KR_TIME_DIFF  = 9 * 60 * 60 * 1000;
  const today         = new Date(utc + (KR_TIME_DIFF));
  
  var dateConcat  = "-";
  var timeConcat  = ":";
  var allConcat   = "T";

  const date    = today.getFullYear() + dateConcat + lpad((today.getMonth()+1), 2, '0') + dateConcat + lpad(today.getDate(), 2, '0');
  const time    = lpad(today.getHours(), 2, '0') + timeConcat + lpad(today.getMinutes(), 2, '0') + timeConcat + lpad(today.getSeconds(), 2, '0');
  const result  = date + allConcat + time;
  return result;
}

/**
 * Alert message
 */
function alertMessage(tit, msg) {
  var ui = SpreadsheetApp.getUi();
  ui.alert(tit, msg, ui.ButtonSet.OK);
}

/**
 * lpad
 */
function lpad(str, padLen, padStr) {
  if ( padStr.length > padLen ) {
    console.log("오류 : 채우고자 하는 문자열이 요청 길이보다 큽니다");
    return str;
  }
  str     += "";
  padStr  += "";
  while (str.length < padLen) {
    str = padStr + str;
  }
  str = str.length >= padLen ? str.substring(0, padLen) : str;
  return str;
}

 

테스트를 위해 데이터를 더 입력해 보았다.

 

1.2 수정 기능

중복된 key 값의 데이터가 존재하면,

리스트에서 데이터를 수정한다.

 

수정 history sheet에 이력을 남기는 기능도 추가했다.

 

 

 

2. 조회 기능 구현

key 값인 상품ID를 입력해 데이터를 입력 폼에 뿌려준다.

데이터가 존재하지 않으면 alert 메세지를 뿌려준다.

 

function getProduct() {
  // 데이터 입력 시트 
  const inputSheetName  = "지사_입력폼";
  const sheet           = SpreadsheetApp.getActive().getSheetByName(inputSheetName);
  const dataRange       = sheet.getRange("D4");
  const searchValue     = dataRange.getValue();

  if ( searchValue == null || searchValue == "" ) {
    alertMessage("알림", "조회조건을 입력해주세요.");
    resertForm();
    return;
  }

  // 데이터 시트
  const dataSheetId     = "GOOGLE_SHEET_ID";
  const dataSheetName   = "상품리스트";
  const dataSheetSs     = SpreadsheetApp.openById(dataSheetId);
  const dataSheet       = dataSheetSs.getSheetByName(dataSheetName);

  const dataListLastRow = dataSheet.getLastRow() + 1;
  const dataListValues  = dataSheet.getRange(`A2:I${dataListLastRow}`).getValues();

  var resultFlag = false;

  for ( k in dataListValues ) {
    if ( searchValue == dataListValues[k][0] ) {
      sheet.getRange("D6").setValue(dataListValues[k][1]);
      sheet.getRange("D8").setValue(dataListValues[k][2]);
      sheet.getRange("D10").setValue(dataListValues[k][3]);
      sheet.getRange("D12").setValue(dataListValues[k][4]);
      sheet.getRange("D14").setValue(dataListValues[k][5]);
      resultFlag = true;
      break;
    }
  }

  if ( !resultFlag ) {
    alertMessage("알림", `"${searchValue}" 존재하는 데이터가 없습니다.`);
    resertForm();
  }
}

/**
 * 입력 폼 초기화
 */
function resertForm() {
  const inputSheetName = "지사_입력폼";

  const sheet     = SpreadsheetApp.getActive().getSheetByName(inputSheetName);
  const dataRange = sheet.getRange("D4:D14");
  
  dataRange.clearContent();
}

 

 

3. 삭제 기능 구현

데이터를 조회하고 삭제를 진행한다.

삭제 처리 시 삭제history sheet에 log를 남겨준다.

 

상품리스트에서 데이터가 삭제되고, 삭제된 데이터가 삭제history 시트에 저장된다.

function deleteProduct() {
  // 데이터 입력 시트 
  const inputSheetName  = "지사_입력폼";
  const sheet           = SpreadsheetApp.getActive().getSheetByName(inputSheetName);
  const dataRange       = sheet.getRange("D4");
  const searchValue     = dataRange.getValue();

  if ( searchValue == null || searchValue == "" ) {
    alertMessage("알림", "삭제할 상품 ID가 없습니다.");
    return;
  }
  
  // 데이터 저장 시트 정보
  const dataSheetId     = "GOOGLE_SHEET_ID";
  const dataSheetName   = "상품리스트";
  const dataSheetSs     = SpreadsheetApp.openById(dataSheetId);
  const dataSheet       = dataSheetSs.getSheetByName(dataSheetName);
 
  const dataListLastRow = dataSheet.getLastRow() + 1;
  const dataListValues  = dataSheet.getRange(`A2:I${dataListLastRow}`).getValues();

  var resultFlag = false;

  for ( k in dataListValues ) {
    if ( searchValue == dataListValues[k][0] ) {
      const thisRow = parseInt(k)+2;

      const dataListRange = dataSheet.getRange(`A${thisRow}:I${thisRow}`);
      const deleteValues  = dataListRange.getValues();

      deleteValues[0][6] = getDateTime(deleteValues[0][6]);     // 등록일시
      deleteValues[0][7] = getDateTime(deleteValues[0][7]);     // 수정일시
      deleteValues[0].push(getDateTime());                      // 삭제일시
      deleteValues[0].push(Session.getActiveUser().getEmail()); // 삭제자

      // 삭제 history 시트 정보
      const deleteSheetName = "삭제history";
      const deleteSheet     = dataSheetSs.getSheetByName(deleteSheetName);
      const deleteLastRow   = deleteSheet.getLastRow() + 1;

      // 삭제 history 저장
      deleteSheet.getRange(`A${deleteLastRow}:K${deleteLastRow}`).setValues(deleteValues).setBorder(true, true, true, true, true, true);

      // 데이터 리스트에서 해당 row 삭제
      dataSheet.deleteRow(thisRow);

      resultFlag = true;
      break;
    }
  }

  if ( resultFlag ) {
    // 성공 메세지 
    alertMessage("성공", `데이터 삭제를 성공하였습니다.`);
    // 입력폼 초기화
    resertForm();
  }
}

 

 

 

 

 

여기까지가 간단한 상품관리 기능이고,

응용하면 발주 기능등을 추가하여 시스템의 구색을 갖출 수 있을것이다.

 

모든 작업은 사용자용 Google Sheets에서 이루어지고,

관리자용 Google Sheets는 DB 역할만 하게 된다.

 

update / delete 로그들도 한눈에 관리가 가능하다.