블로그

나는 재무팀장이다.

새로 입사한 회사에서 흥미로운 광경을 목격했다.
창고 별로 수불부 파일이 있었는데, 각각의 파일에 원가표를 복사해 넣고 있었다.

“회사 기준 정보는 한 파일에 모아 두고, 불러다 사용하면 어떨까요?”
“아, 구글 시트는 그게 안되는 걸로 알고 있어요.”

아익후..

IMPORTRANGE 함수 하나면 된다.

구글 스프레드시트에는 IMPORTRANGE라는 함수가 있다.
다른 스프레드시트 파일의 데이터를 실시간으로 가져올 수 있는 기능이다.

사용법은 간단하다.
=IMPORTRANGE(“스프레드시트URL”, “시트명!범위”)

이런 이점이 있다.
– 기준정보 파일 하나만 관리하면 됨
– 수정사항이 자동으로 모든 파일에 반영됨
– 파일 복사할 필요 없음
– 버전 관리 걱정도 사라짐
– 여러 파일을 통합하기 쉽다.

믿지 않는 눈치다. 회의실에서 시연해줬다.

✅교육 전에 예시에 대한 상황을 설명했다.

영업 지점별 매출 내역이 각각 다른 파일에 있을 때 통합하기
→ 강남점과 압구정점 매출을 “취합”이라는 파일로 통합한다.

1️⃣ 데이터를 가져올 구글 시트 정보를 정리한다.

• 필요한 정보
=IMPORTRANGE(“스프레드시트URL”, “시트명!범위”)

• 확인한 정보

1. 스프레드시트URL : https://docs.google.com/spreadsheets/d/1dwldD9FbgqylQTi1Gf9YBdqTtIN7o-lENrjCT6lGAvg/edit?gid=0#gid=0
2. 시트명!범위 : 강남점!B5:G15

2️⃣ 취합할 구글 시트를 새로 만들고, 아래와 같이 수식을 입력한다.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dwldD9FbgqylQTi1Gf9YBdqTtIN7o-lENrjCT6lGAvg/edit?gid=0#gid=0","강남점!B5:G15")

수식을 입력하면, #REF! 에러가 생긴다.
원래 그런거다. 쫄지 않아도 된다.

수식이 입력된 셀을 선택하면, 아래와 같이 엑세스를 허용하라는 메시지가 나온다. “엑세스 허용”을 클릭한다.

데이터가 잘 불러와 지는 것을 확인할 수 있다.

같은 요령으로 압구정점 매출도 아래 수식으로 가져올 수 있다.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1eo1yddkAyc7Zm5CckQhd9RyZW_4K3qmTtmo4RA18hc0/edit?gid=1853046104#gid=1853046104","압구정점!B5:G15")

강남점과 압구정점 매출 통합하기

배열 수식으로 2개의 배열을 묶어주면 된다.

🔹데이터를 아래쪽으로 쌓는 방식 → 배열 사이에 “;”를 넣으면 행으로 추가된다.
={ 배열1; 배열2 }

🔹데이터를 오른쪽으로 붙이는 방식 → “배열 사이에 ,”를 넣으면 열이 추가된다.
={ 배열1, 배열2 }

강남점과 압구정점 매출이 같은 열구조이므로, 아래와 같이 아래쪽 즉 행 방향으로 쌓는 방식을 사용하면 된다.

={ IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dwldD9FbgqylQTi1Gf9YBdqTtIN7o-lENrjCT6lGAvg/","강남점!B5:G15");
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1eo1yddkAyc7Zm5CckQhd9RyZW_4K3qmTtmo4RA18hc0/","압구정점!B6:G15") }

압구정점은 제목 행을 제외하기 위해서 B5가 아닌 B6부터 가져왔다.

수식이 길면, URL에서 구글 스프레드 시트 ID만 표시해도 된다.

={ IMPORTRANGE("1dwldD9FbgqylQTi1Gf9YBdqTtIN7o-lENrjCT6lGAvg","강남점!B5:G15");
IMPORTRANGE("1eo1yddkAyc7Zm5CckQhd9RyZW_4K3qmTtmo4RA18hc0","압구정점!B6:G15") }

매장이 늘어나도 같은 방식으로 계속 추가 가능!!

심화학습 단계다. 초보자는 안봐도 된다.

1️⃣ 구글 시트 URL대신 칩을 사용해도 된다.

아래 이미지처럼, 구글 시트 URL위치에 칩이 들어가 있는 셀 주소를 넣으면 된다.

✅ 구글 시트를 복사한 URL을 입력한 셀을 더블 클릭하면 칩으로 변환할 수 있는 메뉴가 팝업는데, “칩”을 클릭하면 변환할 수 있다.

2️⃣ 가져올 데이터 소스가 “표” 객체로 되어 있다면 시트명과 영역으로 지정하지 않아도 표 이름 지정만으로 쉽게 데이터를 가져올 수 있다.

또한 데이터 범위가 확장되어도 표 영역이 자동으로 확장되므로, 셀 주소를 변경하지 않아도 된다.

강남점 매출은 t강남매출이라는 표로 입력했고, 압구정점 매출은 t압구정매출이라는 표로 만들었다. 강남매출은 제목줄이 필요해서 t강남매출[#ALL]로 표시했고, 압구정매출은 데이터만 필요해서 표 이름만 표기를 했다.

✅ 표 영역을 참조하는 방식은 MS 도움말을 참조하자.

https://support.google.com/docs/answer/15637642?sjid=2692537847315675731-NC

✅ “표” 객체로 만드는 방법은 아래와 같다.

변환할 영역을 선택하고, 메뉴에서 “서식” > “표로 변환”을 클릭하면 된다.

다음 시간에는 QUERY함수를 사용하여 필요한 데이터만 골라오는 방법에 대해서 알아 보자.

다음 시간이 기대된다면 댓글을 달아서 푸쉬하면 좀 더 빨리 볼 수 있다.

나는 경영전략실장이다.
매일 아침 현재 재고 보유 금액을 누군가가 내 책상에 올려놨음 좋겠다.

애들한테 시킬까했는데, 그러면 애들이 나를 싫어할 것 같아서.. 자동으로 하는 방법을 찾아봤다.

다행히 물류 업체 빵토스가 매일 아침 8시에 재고 현황을 엑셀 파일로 보내주기로 했다.

제미나이에게 아래처럼 물어보면, 필요한 코드를 바로 만들어 준다.
코드를 복사해서 앱스 스크립트에 붙여 넣기만 하면 된다.
(구글 앱스 스크립트 작성은 챗GPT보다 제미나이가 조금 낫다.)

넌 구글 스프레드시트 앱스 스크립트 개발 전문가야.
아래 조건에 맞는 코드를 작성해 줘.
코드는 최대한 간결하게 작성해 줘.
코드가 완성되면 오류가 있는지 다시 한번 살펴보고 오류가 없는 코드를 제시해 줘.

- * 목적
매일 아침 8시 10분에 지메일에 수신된 이메일에서 물류회사에서 보낸 메일을 검색해서 첨부된 엑셀 파일을 구글 스프레드 시트 데이터로 변환하는 작업
- * 찾을 메일
1. 메일 제목에 "일일 재고 현황"이 포함 되어 있음
2. 첨부파일이 있음

- * 엑셀 파일 변환 방법
1. Drive API를 사용할 것
2. 임시 구글 스프레드시트 파일을 만들고, 데이터를 가져온 다음에는 임시파일을 삭제할 것
- * 저장할 스프레드시트 정보
1. 타겟 구글 스프레드 시트 : https://docs.google.com/spreadsheets/d/1NnKb2U10vIjttlTuyD82bdted2IOhG_JcmmFBbdu___/
2. 구글 스프레드시트 시트명 : 현재재고

제미나이가 아래와 같이 답변해 줬다.

/**
 * Gmail 첨부 파일을 Google Drive API를 사용해 스프레드시트로 임시 변환 후,
 * 그 내용을 타겟 시트에 덮어쓰고 임시 파일을 삭제합니다.
 *
 * @requires Drive API Service (Apps Script Services에서 활성화 필요)
 */
function importInventoryDataUsingDriveConvert() {
  // --- 설정 변수 ---
  const SPREADSHEET_ID = '1NnKb2U10vIjttlTuyD82bdted2IOhG_JcmmFBbdu___'; // 타겟 구글 스프레드시트 ID
  const SHEET_NAME = '현재재고'; // 데이터를 넣을 시트 이름
  // Gmail 검색 쿼리 (3PL 업체에 맞춰 조정 필요)
  //const SEARCH_QUERY = 'subject:"일일 재고 현황" has:attachment is:unread';
  const SEARCH_QUERY = 'subject:"일일 재고 현황" has:attachment';
  
  // 임시 변환 파일을 저장할 폴더 ID (내 드라이브의 루트 폴더를 사용하려면 주석 처리하거나 'root' 사용)
  // 공유 드라이브를 사용한다면 해당 공유 드라이브 폴더 ID를 지정해야 합니다.
  const TEMP_FOLDER_ID = null; // null로 두면 Apps Script가 접근할 수 있는 기본 위치에 생성됨
  // -----------------
  
  let convertedFileId = null; // 임시 파일 ID를 저장할 변수

  try {
    const targetSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
    if (!targetSheet) {
      Logger.log(`오류: 시트 이름을 찾을 수 없습니다. (시트명: ${SHEET_NAME})`);
      return;
    }

    // 1. 조건에 맞는 메일과 첨부 파일 찾기
    const threads = GmailApp.search(SEARCH_QUERY, 0, 1);
    
    if (threads.length === 0) {
      Logger.log('조건에 맞는 새 메일을 찾을 수 없습니다.');
      return;
    }

    const message = threads[0].getMessages().pop();
    const attachments = message.getAttachments();
    
    // 재고 파일 (확장자가 .xls, .xlsx, .csv 인 파일을 우선 처리)
    const inventoryFile = attachments.find(att => 
      att.getName().match(/\.(xls|xlsx|csv)$/i)
    );
    
    if (!inventoryFile) {
      Logger.log('첨부 파일 중 Excel(.xls, .xlsx) 또는 CSV 파일을 찾을 수 없습니다.');
      return;
    }

    // 2. Drive API를 사용하여 첨부 파일(Blob)을 Google 스프레드시트로 임시 변환
    const blob = inventoryFile.copyBlob();
    const resource = {
      title: 'TEMP_CONVERTED_' + inventoryFile.getName() + '_' + Date.now(),
      mimeType: MimeType.GOOGLE_SHEETS, // "application/vnd.google-apps.spreadsheet"
      // TEMP_FOLDER_ID가 있으면 해당 폴더에 저장
      ...(TEMP_FOLDER_ID && { parents: [{ id: TEMP_FOLDER_ID }] }) 
    };
    
    // Drive.Files.insert를 사용하여 파일 업로드 및 변환 수행
    const converted = Drive.Files.insert(resource, blob, {
      convert: true, // 파일 형식 자동 변환
      supportsAllDrives: true // 공유 드라이브 지원
    });
    convertedFileId = converted.id; // 임시 파일 ID 저장

    // 3. 변환된 임시 스프레드시트에서 데이터 읽기
    const tempSS = SpreadsheetApp.openById(convertedFileId);
    const srcSheet = tempSS.getSheets()[0]; // 변환된 파일의 첫 번째 시트
    
    // 데이터 범위의 모든 값을 2차원 배열로 가져오기
    const values = srcSheet.getDataRange().getValues();

    // 4. 타겟 시트에 데이터 덮어쓰기 및 초기화
    targetSheet.clearContents(); // 기존 데이터 초기화

    if (values.length > 0 && values[0].length > 0) {
      targetSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
      Logger.log(`${targetSheet.getName()} 시트에 ${values.length}행의 재고 데이터 업로드를 완료했습니다.`);
    } else {
      Logger.log('변환된 파일에서 유효한 데이터를 읽을 수 없습니다.');
    }

    // 5. 메일을 읽음으로 표시 (중복 처리 방지)
    message.markRead();
    
  } catch (e) {
    Logger.log('스크립트 실행 중 오류가 발생했습니다: ' + e.toString());
  } finally {
    // 6. 임시 변환 파일 정리 (오류 발생 여부와 관계없이 실행)
    if (convertedFileId) {
      try {
        // 임시 파일 휴지통으로 이동 (Drive API 사용)
        Drive.Files.trash(convertedFileId, { supportsAllDrives: true });
        Logger.log(`임시 파일 (${convertedFileId})을 휴지통으로 이동했습니다.`);
      } catch (e) {
        // 휴지통 이동 실패 시, 파일 속성 업데이트로 폴백 (공유 드라이브 등)
        Drive.Files.update({ trashed: true }, convertedFileId, null, { supportsAllDrives: true });
        Logger.log(`임시 파일 (${convertedFileId})을 업데이트를 통해 휴지통으로 이동했습니다.`);
      }
    }
  }
}

아래부터는 위에서 복사한 코드를 사용해서 사용하는 방법이다.

천천히 따라하다 보면 어느 순간.. 오~~하는 순간이 온다.

1️⃣ 구글 시트 메뉴에서 “확장 프로그램 ” > “Apps Script”를 선택한다.

2️⃣ 샘플로 들어가 있는 코드는 제거해버리고, 위에서 복사한 코드를 붙여 넣은 다음 3가지 정보를 내 환경에 맞게 수정한다.

① SPREADSHEET_ID : 구글 시트 URL의 아이디 부분

② SHEET_NAME : 저장할 구글 시트의 시트명

③ SEARCH_QUERY : 검색할 메일 제목 설정

3️⃣ 위 이미지를 보면, 화면 왼쪽 패널에 “서비스” 옆의 +를 누르면 Drive API를 추가할 수 있는 창이 뜬다. 선택하고 “추가”버튼을 누른다.

4️⃣ 저장 버튼을 눌러 저장한다. 그리고 실행 버튼을 누른다.

5️⃣ 처음 실행할 때만 승인을 받으라고 뜬다.
원래 뜨는 것이니 쫄지 않아도 된다.

권한검토 → 계정 선택 → 경고창이 뜨는데.. 왼쪽 아래 “고급”을 눌러서 프로젝트로 이동을 누른다. → 엑세스 권한을 모두 부여하고 “계속”을 클릭!

6️⃣ 구글 시트에 정보가 잘 들어가는지 확인한다. 끝!

매일 8시에 자동으로 실행되게 설정해 보자.

⏹️ 자동 실행 트리거 설정

왼쪽 패널의 시계모양의 트리거 아이콘을 클릭한다.

오른쪽 아래에 있는 “트리거 추가”버튼을 눌러서 트리거를 추가한다.

시간 기반 → 일단위 → 오전8시~9시사이로 설정하고 저장한다.

이제 매일 아침 8시에 물류 회사가 재고 현황을 엑셀로 보내주면, 첨부된 엑셀을 구글 시트로 저장해 준다.

작업 결과를 메일로 확인해보고 싶다면, 제미나이에게 이렇게 요청하면 코드를 업데이트해준다.

작업이 완료되면 작업 완료 메일을 아래와 같이 보내주는 코드를 추가해 줘.
# 메일 내용 :
작업 성공 여부, 수신된 이메일 제목, 이메일 받은 시간, 데이터 저장된 구글 시트의 url
# 메일 수신인 :
abyul@naver.com, joowonoh@gmail.com

메일을 보내는 코드를 추가하지 않더라도, 실행된 결과는 화면 왼쪽 패널의 실행 아이콘을 클릭하면 볼 수 있다.

다음 시간에는 구글 시트로 변환한 재고 자료로 리포트를 만들어서 메일로 보고하는 것까지 해보자.

다음 시간이 기대된다면 댓글을 달아서 푸쉬하면 좀 더 빨리 볼 수 있다.