Gmailの請求書/領収書を自動でGoogle Driveへ保存する方法（電帳法の考え方つき）

// ★ここを書き換え
const ROOT_FOLDER_ID = '【証憑管理のフォルダID】';
const LEDGER_SHEET   = '台帳';

function quickTestStandalone(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName(LEDGER_SHEET) || ss.insertSheet(LEDGER_SHEET);

  const tz = Session.getScriptTimeZone();
  const now = new Date();
  const y = Utilities.formatDate(now, tz, 'yyyy');
  const m = Utilities.formatDate(now, tz, 'MM');

  const root = DriveApp.getFolderById(ROOT_FOLDER_ID);
  function getOrCreate(parent, name){
    const it = parent.getFoldersByName(name);
    return it.hasNext() ? it.next() : parent.createFolder(name);
  }
  const yFolder = getOrCreate(root, y + '年');
  const mFolder = getOrCreate(yFolder, m + '月');

  const file = mFolder.createFile(Utilities.newBlob('test','text/plain','test.txt'))
                      .setName('test_' + Date.now() + '.txt');

  sh.appendRow([
    Utilities.formatDate(now, tz, 'yyyy-MM-dd'),
    'テスト','','',file.getName(),file.getId(),
    '証憑管理/' + y + '年/' + m + '月','quickTest'
  ]);
}
// 実行メニューで quickTestStandalone を選んで実行

// ===== 設定 =====
const ROOT_FOLDER_ID = '【証憑管理のフォルダID】'; // Driveの「証憑管理」フォルダ
const LABEL_INBOX    = 'Receipts_Inbox';
const LABEL_DONE     = 'Receipts_Done';
const LABEL_DUP      = 'Receipts_Duplicate'; // 任意
const LEDGER_SHEET   = '台帳';               // A:H を使用

// ===== メイン =====
function processReceipts() {
  const label = GmailApp.getUserLabelByName(LABEL_INBOX);
  if (!label) return;

  const threads = label.getThreads(0, 30); // 1回30スレッドまで
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName(LEDGER_SHEET) || ss.insertSheet(LEDGER_SHEET);

  threads.forEach(t => {
    const msgs = t.getMessages();
    msgs.forEach(m => saveMessage(m, sh));
    t.removeLabel(label);
    t.addLabel(getOrCreateLabel(LABEL_DONE));
  });
}

function saveMessage(msg, sh) {
  const date = msg.getDate();
  const y = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy');
  const m = Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM');
  const monthFolder = ensureMonthFolder(y, m);

  const attachments = msg.getAttachments({includeInlineImages:false});
  attachments.forEach(att => {
    const hash = toHex(Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, att.getBytes()));
    if (existsByHash_(hash, monthFolder)) {
      msg.addLabel(getOrCreateLabel(LABEL_DUP));
      return;
    }
    const file = monthFolder.createFile(att.copyBlob()).setName(att.getName());
    file.setDescription('hash=' + hash);

    // --- 台帳8列（例） ---
    // A:日付 B:取引先 C:金額 D:書類種別 E:ファイル名 F:ファイルID G:保存先パス H:備考
    const row = [
      Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd'),
      guessVendor_(msg), '', '', file.getName(), file.getId(),
      '証憑管理/' + y + '年/' + m + '月', 'msgId=' + msg.getId()
    ];
    sh.appendRow(row);
  });
}

// ====== 補助 ======
function ensureMonthFolder(y, m) {
  const root = DriveApp.getFolderById(ROOT_FOLDER_ID);
  const yName = y + '年', mName = m + '月';
  const yFolder = getOrCreateFolder_(root, yName);
  return getOrCreateFolder_(yFolder, mName);
}

function getOrCreateFolder_(parent, name) {
  const it = parent.getFoldersByName(name);
  return it.hasNext() ? it.next() : parent.createFolder(name);
}

function getOrCreateLabel(name){
  return GmailApp.getUserLabelByName(name) || GmailApp.createLabel(name);
}

function existsByHash_(hash, folder) {
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();
    const desc = f.getDescription() || '';
    if (desc.indexOf('hash=' + hash) !== -1) return true;
  }
  return false;
}

function toHex(bytes){
  return bytes.map(function(b){ b = (b & 0xff) + 0x100; return b.toString(16).slice(-2); }).join('');
}

// ざっくり取引先を推測（必要なら辞書化）
function guessVendor_(msg){
  const from = msg.getFrom() || '';
  const m = from.match(/<(.*?)>/);
  const addr = (m ? m[1] : from).toLowerCase();
  if (addr.indexOf('amazon') >= 0) return 'Amazon';
  if (addr.indexOf('rakuten') >= 0) return 'Rakuten';
  return '';
}