コピペで動くスプレッドシート自動化コード集｜突合・重複・集計・名寄せ4本（1本目は無料公開）

「毎回おなじ手作業」を、Googleスプレッドシートの自動実行に置き換えるためのコード集です。

プログラミングの経験は要りません。コピーして貼り付けて、実行ボタンを押すだけで動くところまで整えてあります。

収録している4本

① 突合：2つの表を突き合わせ、不足・数量違い・過剰だけを抜き出す
② 重複チェック：名簿の重複を、全角半角やハイフンの違いごと見つける
③ 集計：明細から担当別・店舗別の集計表を自動で作る
④ 名寄せ：表記ゆれのある氏名・社名を同一人物としてまとめる

先に正直にお伝えします

この4本がやっていることの一部は、無料の関数でもできます。単純な重複を消すだけなら UNIQUE 関数で足りますし、片方向の照合だけなら COUNTIF でも書けます。それで足りる方は、わざわざこれを買う必要はありません。

このコード集が引き受けているのは、関数では書きづらくなる側です。全角と半角が混ざった氏名、9種類あるハイフン、複数条件の一致、元の行番号を残したままの一覧、数千行でも重くならない書き方。そこを毎回考え直さなくていい形にしてあります。

動かすのに必要なもの

・Googleアカウント（無料のもので構いません）
・Googleスプレッドシート
・所要時間の目安：1本あたり10分

追加のソフトも、外部サービスへの登録も要りません。処理はすべてご自身のGoogleアカウントの中で完結し、データが外部に送られることはありません。

まず1本目を、無料で全部お渡しします

買う前に「本当に動くのか」を確かめられないと不安だと思います。なので①の突合ツールは、この無料部分でコード全文をお渡しします。貼り付けて動かしてみて、良ければ残りをどうぞ。

① 2つの表を突き合わせて、食い違いだけを抜き出す（全文・無料）

スプレッドシートを開き、上部メニューの「拡張機能」→「Apps Script」を選び、最初から入っているコードを全部消してから、下をそのまま貼り付けて保存します。

/************************************************************************
 * 2シート自動突合ツール  v1.0
 * --------------------------------------------------------------------
 * 2つのシート（例：発注シートと納品シート）を、指定したキー列で
 * 突き合わせ、「食い違い」だけを自動で抽出します。
 *
 * ■ 検出できる差分
 *   ・不足        … Aにあって、Bにない（例：未納品）
 *   ・過剰        … Bにあって、Aにない（例：発注なし納品）
 *   ・数量不一致  … 両方にあるが、数量など指定した数値列が違う
 *
 * ■ 使い方（詳しくは「導入手順書.pdf」をご覧ください）
 *   1. スプレッドシートに「発注」「納品」「差分結果」の3シートを用意
 *   2. 下の【設定】をご自身のシートに合わせて変更
 *   3. メニュー「▶ 突合ツール」→「差分を抽出する」を実行
 *
 * ※ このコードは自由に改変してお使いいただけます。
 ************************************************************************/


/* ============================================================
 * 【設定】ここだけ、ご自身のシートに合わせて変更してください
 * ============================================================ */
const CONFIG = {
  // 突き合わせる2つのシート名
  SHEET_A: '発注',          // 基準にするシート（例：発注した数）
  SHEET_B: '納品',          // 比較するシート（例：実際に納品された数）

  // 結果を書き出すシート名（自動で中身がクリアされます）
  SHEET_RESULT: '差分結果',

  // 見出し行が何行目か（1行目が見出しなら 1）
  HEADER_ROW: 1,

  // 突き合わせのキーになる列の見出し（両シート共通の名前）
  KEY_HEADER: '商品コード',

  // 数量など、値を比較したい列の見出し（両シート共通の名前）
  //   → 数量を比較しない（存在チェックだけ）場合は '' にしてください
  VALUE_HEADER: '数量',

  // 結果に一緒に表示したい列の見出し（任意・複数可）
  //   例：['商品名'] を入れると、結果に商品名も出ます
  SHOW_HEADERS: ['商品名'],

  // ラベル（お好みで日本語を変えられます）
  LABEL_MISSING:   '不足（Aにあり／Bになし）',
  LABEL_EXTRA:     '過剰（Bにあり／Aになし）',
  LABEL_MISMATCH:  '数量不一致',

  // 実行後にメールで結果を送る場合はここに宛先を入れる（送らないなら ''）
  MAIL_TO: '',
  MAIL_SUBJECT: '[差分レポート] 突合結果',
};


/* ============================================================
 * ここから下は、原則さわらなくて大丈夫です
 * ============================================================ */

/** スプレッドシートを開いたときに、専用メニューを追加する */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('▶ 突合ツール')
    .addItem('差分を抽出する', 'runReconciliation')
    .addToUi();
}

/** メインの処理：2シートを突き合わせて差分結果シートに書き出す */
function runReconciliation() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const rowsA = readSheet_(ss, CONFIG.SHEET_A);
  const rowsB = readSheet_(ss, CONFIG.SHEET_B);

  // キー → 行データ のマップを作る
  const mapA = toMap_(rowsA);
  const mapB = toMap_(rowsB);

  const results = [];  // 差分の行をためていく

  // --- Aを基準に、不足 と 数量不一致 を探す ---
  Object.keys(mapA).forEach(function(key) {
    const a = mapA[key];
    if (!(key in mapB)) {
      // Bに無い → 不足
      results.push(makeRow_(CONFIG.LABEL_MISSING, key, a, null));
    } else if (CONFIG.VALUE_HEADER) {
      // 両方にある → 数値を比較
      const b = mapB[key];
      const va = toNumber_(a[CONFIG.VALUE_HEADER]);
      const vb = toNumber_(b[CONFIG.VALUE_HEADER]);
      if (va !== vb) {
        results.push(makeRow_(CONFIG.LABEL_MISMATCH, key, a, b));
      }
    }
  });

  // --- Bを基準に、過剰（Aに無いもの）を探す ---
  Object.keys(mapB).forEach(function(key) {
    if (!(key in mapA)) {
      results.push(makeRow_(CONFIG.LABEL_EXTRA, key, null, mapB[key]));
    }
  });

  writeResult_(ss, results);

  const summary = summarize_(results);
  SpreadsheetApp.getActiveSpreadsheet().toast(summary, '突合が完了しました', 8);

  if (CONFIG.MAIL_TO) {
    sendMail_(summary, results);
  }
}

/** シートを読み込み、[{見出し:値, ...}, ...] の配列にして返す */
function readSheet_(ss, sheetName) {
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    throw new Error('「' + sheetName + '」という名前のシートが見つかりません。設定のシート名をご確認ください。');
  }
  const values = sheet.getDataRange().getValues();
  const headerIdx = CONFIG.HEADER_ROW - 1;
  const headers = values[headerIdx].map(function(h) { return String(h).trim(); });

  const rows = [];
  for (let r = headerIdx + 1; r < values.length; r++) {
    const rowArr = values[r];
    // 空行はスキップ
    if (rowArr.every(function(c) { return c === '' || c === null; })) continue;
    const obj = {};
    headers.forEach(function(h, c) { obj[h] = rowArr[c]; });
    rows.push(obj);
  }
  return rows;
}

/** 行の配列を キー→行 のマップにする */
function toMap_(rows) {
  const map = {};
  rows.forEach(function(row) {
    const key = String(row[CONFIG.KEY_HEADER]).trim();
    if (key === '' || key === 'undefined') return;
    map[key] = row;   // キー重複時は後の行で上書き
  });
  return map;
}

/** 結果1行分を組み立てる */
function makeRow_(label, key, a, b) {
  const extra = CONFIG.SHOW_HEADERS.map(function(h) {
    if (a && a[h] !== undefined && a[h] !== '') return a[h];
    if (b && b[h] !== undefined) return b[h];
    return '';
  });
  const va = (a && CONFIG.VALUE_HEADER) ? a[CONFIG.VALUE_HEADER] : '';
  const vb = (b && CONFIG.VALUE_HEADER) ? b[CONFIG.VALUE_HEADER] : '';
  const diff = (CONFIG.VALUE_HEADER && a && b)
    ? (toNumber_(vb) - toNumber_(va))
    : '';
  return [label, key].concat(extra).concat([va, vb, diff]);
}

/** 結果シートに書き出す */
function writeResult_(ss, results) {
  let sheet = ss.getSheetByName(CONFIG.SHEET_RESULT);
  if (!sheet) sheet = ss.insertSheet(CONFIG.SHEET_RESULT);
  sheet.clearContents();

  const header = ['分類', CONFIG.KEY_HEADER]
    .concat(CONFIG.SHOW_HEADERS)
    .concat([CONFIG.SHEET_A, CONFIG.SHEET_B, '差(' + CONFIG.SHEET_B + '−' + CONFIG.SHEET_A + ')']);

  const out = [header].concat(results.length ? results : [['差分はありませんでした'].concat(header.slice(1).map(function(){return '';}))]);
  sheet.getRange(1, 1, out.length, header.length).setValues(out);

  // 見出しを太字＆背景色に
  sheet.getRange(1, 1, 1, header.length)
    .setFontWeight('bold')
    .setBackground('#f1f3f4');
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, header.length);
}

/** 件数の要約テキストを作る */
function summarize_(results) {
  let missing = 0, extra = 0, mismatch = 0;
  results.forEach(function(r) {
    if (r[0] === CONFIG.LABEL_MISSING)  missing++;
    if (r[0] === CONFIG.LABEL_EXTRA)    extra++;
    if (r[0] === CONFIG.LABEL_MISMATCH) mismatch++;
  });
  return CONFIG.LABEL_MISSING + '：' + missing + '件 / '
       + CONFIG.LABEL_MISMATCH + '：' + mismatch + '件 / '
       + CONFIG.LABEL_EXTRA + '：' + extra + '件';
}

/** 結果をメールで送る */
function sendMail_(summary, results) {
  const lines = results.map(function(r) { return r.join('\t'); }).join('\n');
  const body = '突合を実行しました。\n\n'
    + '実行日時：' + Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm') + '\n'
    + summary + '\n\n'
    + (results.length ? '--- 差分の明細 ---\n' + lines : '差分はありませんでした。') + '\n\n'
    + '※このメールは自動送信されています。';
  MailApp.sendEmail(CONFIG.MAIL_TO, CONFIG.MAIL_SUBJECT, body);
}

/** 文字列でも数値でも、数値に変換（カンマ・空白は無視） */
function toNumber_(v) {
  if (typeof v === 'number') return v;
  const n = parseFloat(String(v).replace(/[,\s]/g, ''));
  return isNaN(n) ? 0 : n;
}

/**
 * 毎日・毎週など自動実行したい場合は、この関数をトリガーに設定します。
 * （導入手順書の「自動実行の設定」をご覧ください）
 */
function scheduledRun() {
  runReconciliation();
}

保存したらスプレッドシートに戻り、再読み込みしてください。メニューに専用の項目が追加され、そこから実行できます。

動きましたか。もし動いたなら、残りの3本も同じ手順で動きます。

② 名簿の重複を、表記ゆれごと見つける