R&D 과제(RCMS) 연구비 관리 반자동화 시트

필자가 직접 과제를 관리하는 기업들뿐 아니라, 신규 문의로도 연구비 관리에 대한 문의는 많다.

보통 수행하는 과제가 하나라면, 매번 과제비 관리 사이트를 접속하여 확인하는 방법이 편할 수 있다.

또는 과제비를 담당하는 연구원이 매 과제비 사용건마다 엑셀 등으로 관리하는 방법도 있을 것이다.

다만 수행하는 과제가 세 개가 넘어간다면, 이런 방법은 매우 번잡해질 수 있다.

수행 과제가 많을 때는 과제별로 유기적인 과제비 사용이 필요할 때도 있기 때문에, 한 눈에 혹은 한 시트에서 과제비 사용 현황을 보는 것이 더욱 편할 것이다.

이는 과제에 참여하는 연구원 혹은 과제비 사용현황을 공유받고자 하는 사람(부서)들이 늘어날 수록 더욱 그렇다.

RCMS 등의 정부과제비 관리 사이트에서는 보안때문에 외부 프로그램과 연결이 쉽지 않다.

단순히 API 등을 받아 외부에서 연동시키는 것은 어렵다는 뜻이다.

가끔 그룹웨어나 ERP 등에서 연결이 가능하다면 홍보를 하기는 한다.

하지만 연구비 사용현황을 보는 정도라면 굳이 큰 비용을 들여 이를 도입하자는 의견을 내는 것도 비용상 문제가 있을 것이다.

필자는 직접 관리하는 과제들이 많다보니, 이를 한 눈에 볼 필요가 있다고 판단했다.

또 우리 기업 중에도 과제비 사용 현황을 이를 실시간으로 확인하고 싶다는 의견이 여럿 있었기도 하다.

그래서 최근에는 구글 워크스페이스를 이용해, 간단히 유사한 시스템을 만들었다.

필자가 수행하는 R&D 과제들은 주로 RCMS에서 연구비를 관리하는 것이 많아, RCMS 사이트를 기반으로 연구비 관리 ‘반자동화’ 시트를 만들었다.

1. 연구비 관리 시트 기획

앞서 언급했듯이 과제비 관리 사이트와 실시간으로 연동하는 것은 보안 때문에 문제가 있다.

그래서 필자가 간단하게 구성이 가능한 몇 가지만 집중했다.

• 무료로 구성되고 구성 자체가 매우 쉬울 것
• 최대한 사용자(연구원)이 귀찮지 않게 할 것
• 쉽게 타부서 또는 타 연구원과 공유할 수 있을 것

사용 방법은 아주 간단하다.

RCMS에 접속해서, 

연구비 사용내역을 그냥 전체 다 다운받아,

구글 드라이브 특정 폴더에 넣으면,

지정한 구글 시트에 사용내역이 모두 정리된다.

이 이후에는 엑셀에서 하듯이 표를 만들거나 그래프를 만들어 공유하면 되는 것이다.

사용한 프로그램이라고는 구글 시트와 구글 드라이브뿐이다.

구글 드라이브는 아무나 사용이 가능하고,

구글 시트는 누구에게나 공유가 쉽고, 엑셀과 인터페이스가 유사하여 쉽게 변형이 가능하기 때문이다.

2. 연구비 관리 시트 구성 방법

구글에 가입 후, 구글 드라이브에 접속하자.

왼쪽 상단의 ‘+신규’ 버튼을 누른 후 ‘구글 스프레드 시트’를 클릭하면, ‘제목 없는 스프레드시트’가 하나 열린다.

제목은 적당히 ‘정부과제 연구비 관리 시트’ 정도로 잡으면 되겠다.

메뉴의 ‘확장 프로그램’ -> ‘Apps Script’로 이동하자.

‘제목 없는 프로젝트’라는 이름의 스크립트가 실행 될 것이다.

이 스크립트와 앞서 실행한 ‘정부과제 연구비 관리 시트’는 짝이 되는 것이다.

역시 적당한 이름을 넣은 후, ‘Code.gs’에 아래 코드를 몽땅 복사해 넣자.

참고로 테스트는 ‘실행’ 버튼을 누르는 것으로 할 수 있다.

[코드]

// ▼▼▼ 실행할 때 꼭 'startMultiProcessing'을 선택하세요! ▼▼▼
function startMultiProcessing() {
  const SPREADSHEET_ID = '?????????????????'; 
  const PROCESSED_FOLDER_ID = '?????????????????'; 

  // ============================================================
  // [설정] 과제별 폴더 ID와 시트 매핑
  // ============================================================
  const PROJECT_CONFIG = [
    {
      name: "?????????????????",       
      folderId: "?????????????????",   
      sheetName: "?????????????????", 
      startRow: ?????????????????,
      dateCell: "?????????????????"
    },
    {
      name: "?????????????????",       
      folderId: "?????????????????",   
      sheetName: "?????????????????",
      startRow: ?????????????????,
      dateCell: "?????????????????"
    },
    {
      name: "?????????????????",       
      folderId: "?????????????????",   
      sheetName: "?????????????????",
      startRow: ?????????????????,
      dateCell: "?????????????????"
    },
    {
      name: "?????????????????",       
      folderId: "?????????????????",   
      sheetName: "?????????????????",
      startRow: ?????????????????,
      dateCell: "?????????????????"
    },
    {
      name: "?????????????????",       
      folderId: "?????????????????",   
      sheetName: "?????????????????",
      startRow: ?????????????????,
      dateCell: "?????????????????"
    }
  ];

  // 공통 설정
  const START_COL = 2; // B열
  const COLUMN_INDICES = [0, 1, 2, 3, 5, 6, 7, 32, 35, 46, 57]; 
  const CHECK_COLUMNS = [2, 7, 32]; 
  
  // ⭐️ [안전장치] 파일 생성 후 최소 2분이 지나야 처리 (업로드 중단 방지)
  const SAFETY_DELAY_MINUTES = 2; 

  // ============================================================

  const processedFolder = DriveApp.getFolderById(PROCESSED_FOLDER_ID);
  const masterSs = SpreadsheetApp.openById(SPREADSHEET_ID);

  Logger.log(`🚀 [NEW] 총 ${PROJECT_CONFIG.length}개의 과제 처리를 시작합니다.`);

  for (let i = 0; i < PROJECT_CONFIG.length; i++) {
    let config = PROJECT_CONFIG[i];
    Logger.log(`\n========================================`);
    Logger.log(`👉 [${i + 1}/${PROJECT_CONFIG.length}] 프로젝트: ${config.name}`);

    try {
      // 1. 폴더 확인
      let uploadFolder;
      try {
        uploadFolder = DriveApp.getFolderById(config.folderId);
      } catch(e) {
        Logger.log(`🚨 [오류] 폴더 ID가 올바르지 않습니다.`);
        continue;
      }

      // 2. 시트 확인
      let masterSheet = masterSs.getSheetByName(config.sheetName);
      if (!masterSheet) {
        Logger.log(`🚨 [오류] 시트 이름(${config.sheetName})이 없습니다.`);
        continue;
      }

      // ---------------------------------------------------------
      // [기능 추가] 해당 시트의 지정된 셀(O15)에 날짜 업데이트
      // ---------------------------------------------------------
      if (config.dateCell) {
        let now = new Date();
        let timeStamp = Utilities.formatDate(now, "GMT+9", "yyyy-MM-dd HH:mm:ss");
        masterSheet.getRange(config.dateCell).setValue("최종 업데이트: " + timeStamp);
      }
      // ---------------------------------------------------------

      // 3. 엑셀 파일 탐색 (안전장치 적용)
      let allFiles = uploadFolder.getFiles();
      let excelFiles = [];
      let nowTime = new Date().getTime(); // 현재 시간

      while (allFiles.hasNext()) {
        let f = allFiles.next();
        let fName = f.getName().toLowerCase();
        
        // 엑셀 파일인지 확인
        if (fName.endsWith(".xlsx") || fName.endsWith(".xls")) {
          
          // ⭐️ [안전장치 로직] 방금 올린 파일은 건너뛰기
          let lastUpdated = f.getLastUpdated().getTime();
          let diffMinutes = (nowTime - lastUpdated) / (1000 * 60);

          if (diffMinutes < SAFETY_DELAY_MINUTES) {
            Logger.log(`⏳ [대기] '${f.getName()}'은(는) 방금 업로드됨. (안전을 위해 다음 턴에 처리)`);
            continue; 
          }

          excelFiles.push(f);
        }
      }

      if (excelFiles.length === 0) {
        Logger.log(`⚠️ 처리할 엑셀 파일이 없습니다.`);
        continue;
      } else {
        Logger.log(`📄 발견된 안전한 파일: ${excelFiles.length}개`);
      }

      // 4. 타겟 행 계산
      let targetRow;
      let cellStart = masterSheet.getRange(config.startRow, START_COL);
      if (String(cellStart.getValue()).trim() === "") {
        targetRow = config.startRow;
      } else {
        let lastRow = masterSheet.getRange(masterSheet.getMaxRows(), START_COL)
                                 .getNextDataCell(SpreadsheetApp.Direction.UP)
                                 .getRow();
        targetRow = Math.max(config.startRow, lastRow + 1);
      }
      Logger.log(`   - 입력 시작 행: ${targetRow}행`);

      // 5. 중복 방지 키 로딩
      const existingKeys = new Set();
      const lastRowInSheet = masterSheet.getLastRow();
      if (lastRowInSheet >= config.startRow) {
        const sheetData = masterSheet.getRange(1, 1, lastRowInSheet, masterSheet.getLastColumn()).getValues();
        const mapping = CHECK_COLUMNS.map(colIdx => COLUMN_INDICES.indexOf(colIdx));
        for (let r = config.startRow - 1; r < sheetData.length; r++) {
          if (!sheetData[r]) continue;
          let key = mapping.map(pos => {
            let colIdx = (START_COL - 1) + pos;
            return (colIdx < sheetData[r].length) ? String(sheetData[r][colIdx]).trim() : "";
          }).join("_");
          if (key.replace(/_/g, "") !== "") existingKeys.add(key);
        }
      }

      // 6. 파일 처리
      for (let file of excelFiles) {
        let tempSheetId = convertExcelToGoogleSheet(file, config.folderId);
        let tempSheet = SpreadsheetApp.openById(tempSheetId).getSheets()[0];
        let tempData = tempSheet.getDataRange().getValues();
        let newData = [];

        for (let j = 1; j < tempData.length; j++) {
          let row = tempData[j];
          let hasContent = COLUMN_INDICES.some(idx => row[idx] && String(row[idx]).trim() !== "");
          if (!hasContent) continue;

          let rowKey = CHECK_COLUMNS.map(idx => String(row[idx]).trim()).join("_");
          if (!existingKeys.has(rowKey)) {
            let filteredRow = COLUMN_INDICES.map(idx => row[idx]);
            newData.push(filteredRow);
            existingKeys.add(rowKey);
          }
        }

        if (newData.length > 0) {
          masterSheet.getRange(targetRow, START_COL, newData.length, newData[0].length).setValues(newData);
          Logger.log(`      ✅ ${newData.length}건 저장 완료`);
          targetRow += newData.length;
        }

        DriveApp.getFileById(tempSheetId).setTrashed(true);
        let timestamp = Utilities.formatDate(new Date(), "GMT+9", "yyyyMMdd_HHmmss");
        let newName = `${file.getName().replace(/\.xlsx?$/, "")}_${config.sheetName}_${timestamp}.xlsx`;
        file.setName(newName);
        file.moveTo(processedFolder);
      }
            // ---------------------------------------------------------
      // [기능 수정] 데이터가 실제로 추가되었을 때만 날짜 갱신
      // ---------------------------------------------------------
      if (isDataUpdated && config.dateCell) {
        let now = new Date();
        let timeStamp = Utilities.formatDate(now, "GMT+9", "yyyy-MM-dd HH:mm:ss");
        masterSheet.getRange(config.dateCell).setValue("최종 업데이트: " + timeStamp);
        Logger.log(`🕒 [날짜 갱신] ${config.sheetName} (${config.dateCell})`);
      } else {
        Logger.log(`ℹ️ [날짜 유지] 새로운 데이터가 없어 업데이트 날짜를 변경하지 않습니다.`);
      }
      // ---------------------------------------------------------

    } catch (e) {
      Logger.log(`🚨 에러: ${e.toString()}`);
    }
  }
  Logger.log("🏁 작업 종료");
}

function convertExcelToGoogleSheet(file, folderId) {
  let blob = file.getBlob();
  let resource = { title: file.getName().replace(/\.xlsx?$/, ""), parents: [{id: folderId}] };
  return Drive.Files.insert(resource, blob, {convert: true}).id;
}

‘?????????????????‘로 표기된 부분은 아래 수정 방법 확인 후, 필요에 따라 수정하면 된다.

[수정 방법]

1) 과제별 설정

const PROJECT_CONFIG = [
  {
    name: "과제명(로그용)",       // 실행 로그에서 식별할 이름
    folderId: "폴더ID_입력",      // 엑셀 파일이 업로드될 구글 드라이브 폴더의 ID
    sheetName: "시트이름",        // 데이터가 입력될 시트 탭 이름 (정확해야 함)
    startRow: 16,                // 데이터 작성을 시작할 행 번호
    dateCell: "O15"              // 최종 업데이트 날짜를 찍을 셀 위치 (생략 가능)
  },
  // ... 콤마(,)로 구분하여 계속 추가 가능
];

2) 전체 공통 설정

// 1. 메인 시트 및 완료 폴더 ID
const SPREADSHEET_ID = '...';      // 데이터를 모을 스프레드시트의 ID
const PROCESSED_FOLDER_ID = '...'; // 처리가 끝난 파일을 보관할 폴더의 ID

// 2. 데이터 매핑 (엑셀의 몇 번째 열을 가져올지: A열=0, B열=1 ...) - '연구비 사용내역' 시트에서 어떤 열의 데이터를 가져올지
const COLUMN_INDICES = [0, 1, 2, 3, 5, 6, 7, 32, 35, 46, 57];

// 3. 중복 체크 기준 (이 열들의 값이 모두 같으면 중복으로 간주)
const CHECK_COLUMNS = [2, 7, 32];

// 4. 안전장치 설정 (분 단위)
const SAFETY_DELAY_MINUTES = 2;

// 5. 시트 내 연구비 사용 내역이 시작될 열
  const START_COL = 2; // B열

3. 연구비 관리 시트 사용 방법

사용법은 간단하다.

위에서 지정한 폴더에, 과제에 해당하는 ‘연구비 사용 내역’ 엑셀 파일을 넣고 잠시만 기다리면 구글 시트에 해당하는 사용 내역이 쭉 정리된다.

현재는 약 10분 단위로 체크하도록 자동화하였으나, 시간을 줄이거나 혹은 수동으로 사용 내역을 옮길 수도 있다.

위의 코드로 필자가 구성한 형태는 대략 이런 식이다.

상단에는 과제의 기본정보를 볼 수 있도록 표를 작성했고, 하단의 사용내역을 정리하여 한눈에 볼 수 있도록 예산표를 만들었다.

하단의 과제비 사용 내역이 RCMS로부터 다운받은 사용내역이 자동으로 정리되는 부분이다.

이후에는 엑셀에서 하듯이 표를 만들거나 그래프를 만들어 필요한 사람과 공유하면 된다.

엑셀에서와 같이 ‘=’ 함수로 여러 개의 과제를 한 개의 시트에 모을 수도 있으니, 이는 사용자의 입맛대로 하면 될 것이다.

좀 번거롭기는 하지만, 미리 언급했듯이 ‘반자동화’라는 것을 고려하자.

좀 더 응용한다면 매크로 등을 이용해 RCMS 로그인부터 시작해 ‘억지 자동화’를 만들 수도 있을 것이다.

다만 필자의 상황에서는 이정도면 충분한 것 같다는 판단이다.

최근 AI가 발전함에 따라 코딩에 ‘ㅋ’자도 모르던 필자도, 손쉽게 이런식의 관리가 가능해졌다.

단점은 어떤 문제가 발생할지 모른다는 것이다.

혹시 문제가 발생하면, 피드백을 주거나 스스로(?) 고쳐 쓰시는 것도 좋겠다;;

과제와 관련하여 또 재밌거나 혹은 유익한 것들이 만들어지면 공유하겠다.