コピペで作る月次レポートにサヨナラ—集計＋整形＋凍結保管を自動化する

// コード.gs
/**
 * 売上明細から指定月の月次レポートを自動生成する
 *   - サマリー（合計売上・取引件数・平均客単価）
 *   - カテゴリ別売上（金額・件数・構成比）
 *   - 商品トップ10（売上金額順）
 * レポートは「月次レポート_YYYY-MM」名のシートとして出力（履歴として残る）
 */

const SOURCE_SHEET_NAME = '売上明細';
const COL_DATE = '日付';
const COL_CATEGORY = 'カテゴリ';
const COL_PRODUCT = '商品名';
const COL_AMOUNT = '売上金額';

const REPORT_COLS = 5;
const COLOR_SECTION = '#cfe2f3';
const COLOR_TABLE_HEAD = '#f3f3f3';

function generateMonthlyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ui = SpreadsheetApp.getUi();

  const source = ss.getSheetByName(SOURCE_SHEET_NAME);
  if (!source) {
    ui.alert('「' + SOURCE_SHEET_NAME + '」シートが必要です。');
    return;
  }

  const data = source.getDataRange().getValues();
  if (data.length < 2) {
    ui.alert('「' + SOURCE_SHEET_NAME + '」にヘッダー+データ行が必要です。');
    return;
  }

  const header = data[0];
  const dateIdx = header.indexOf(COL_DATE);
  const categoryIdx = header.indexOf(COL_CATEGORY);
  const productIdx = header.indexOf(COL_PRODUCT);
  const amountIdx = header.indexOf(COL_AMOUNT);
  const missing = [];
  if (dateIdx === -1) missing.push(COL_DATE);
  if (categoryIdx === -1) missing.push(COL_CATEGORY);
  if (productIdx === -1) missing.push(COL_PRODUCT);
  if (amountIdx === -1) missing.push(COL_AMOUNT);
  if (missing.length > 0) {
    ui.alert('「' + SOURCE_SHEET_NAME + '」に必要な列がありません: ' + missing.join(' / '));
    return;
  }

  // 既定の対象月: データ内の最新月
  const allMonths = [];
  for (let i = 1; i < data.length; i++) {
    const m = toMonth_(data[i][dateIdx]);
    if (m) allMonths.push(m);
  }
  if (allMonths.length === 0) {
    ui.alert('日付列に有効な日付がありません。');
    return;
  }
  allMonths.sort();
  const defaultMonth = allMonths[allMonths.length - 1];

  const resp = ui.prompt(
    '月次レポート生成',
    '対象月を YYYY-MM 形式で入力してください。\n（空欄なら既定値「' + defaultMonth + '」を使います）',
    ui.ButtonSet.OK_CANCEL
  );
  if (resp.getSelectedButton() !== ui.Button.OK) return;
  const targetMonth = (resp.getResponseText() || '').trim() || defaultMonth;
  if (!/^\d{4}-\d{2}$/.test(targetMonth)) {
    ui.alert('月の形式は YYYY-MM です。例: 2026-04');
    return;
  }

  const monthRows = [];
  for (let i = 1; i < data.length; i++) {
    if (toMonth_(data[i][dateIdx]) === targetMonth) monthRows.push(data[i]);
  }
  if (monthRows.length === 0) {
    ui.alert('「' + targetMonth + '」のデータがありません。');
    return;
  }

  const totalAmount = monthRows.reduce(function(s, r) {
    return s + (Number(r[amountIdx]) || 0);
  }, 0);
  const transactionCount = monthRows.length;
  const avgPerTransaction = Math.round(totalAmount / transactionCount);

  const byCategory = {};
  monthRows.forEach(function(r) {
    const cat = String(r[categoryIdx]);
    if (!byCategory[cat]) byCategory[cat] = { amount: 0, count: 0 };
    byCategory[cat].amount += Number(r[amountIdx]) || 0;
    byCategory[cat].count += 1;
  });
  const categoryRows = Object.keys(byCategory).map(function(cat) {
    return [cat, byCategory[cat].amount, byCategory[cat].count, byCategory[cat].amount / totalAmount];
  }).sort(function(a, b) { return b[1] - a[1]; });

  const byProduct = {};
  monthRows.forEach(function(r) {
    const key = r[productIdx];
    if (!byProduct[key]) byProduct[key] = { name: r[productIdx], category: r[categoryIdx], amount: 0, count: 0 };
    byProduct[key].amount += Number(r[amountIdx]) || 0;
    byProduct[key].count += 1;
  });
  const productRanking = Object.keys(byProduct).map(function(k) { return byProduct[k]; })
    .sort(function(a, b) { return b.amount - a.amount; })
    .slice(0, 10)
    .map(function(p, i) { return [i + 1, p.name, p.category, p.amount, p.count]; });

  writeReport_(ss, targetMonth, totalAmount, transactionCount, avgPerTransaction, categoryRows, productRanking);

  SpreadsheetApp.flush();
  ui.alert(
    '月次レポートを生成しました。\n\n' +
    '対象月: ' + targetMonth + '\n' +
    '合計売上: ¥' + totalAmount.toLocaleString() + '\n' +
    '取引件数: ' + transactionCount + ' 件\n\n' +
    '「月次レポート_' + targetMonth + '」シートを確認してください。'
  );
}

function toMonth_(value) {
  if (value instanceof Date) {
    return Utilities.formatDate(value, Session.getScriptTimeZone(), 'yyyy-MM');
  }
  const s = String(value);
  return /^\d{4}-\d{2}/.test(s) ? s.substring(0, 7) : '';
}

function writeReport_(ss, month, totalAmount, transactionCount, avgPerTransaction, categoryRows, productRanking) {
  const sheetName = '月次レポート_' + month;
  let sheet = ss.getSheetByName(sheetName);
  if (sheet) {
    sheet.clear();
    sheet.clearConditionalFormatRules();
  } else {
    sheet = ss.insertSheet(sheetName);
  }

  // 全セクションを1つの2次元配列にまとめて、最後に setValues で一括書き込み
  const rows = [];
  const fmt = []; // [行, 列, 列数, 種別] の指示リスト

  // タイトル
  rows.push(['月次レポート ' + month, '', '', '', '']);
  rows.push(['', '', '', '', '']);

  // サマリー
  fmt.push({ row: rows.length + 1, type: 'section' });
  rows.push(['■ サマリー', '', '', '', '']);
  fmt.push({ row: rows.length + 1, type: 'summaryAmount' });
  rows.push(['対象期間', month, '', '', '']);
  fmt.push({ row: rows.length + 1, type: 'summaryAmount' });
  rows.push(['合計売上', totalAmount, '', '', '']);
  fmt.push({ row: rows.length + 1, type: 'summaryNumber' });
  rows.push(['取引件数', transactionCount, '', '', '']);
  fmt.push({ row: rows.length + 1, type: 'summaryAmount' });
  rows.push(['平均客単価', avgPerTransaction, '', '', '']);
  rows.push(['', '', '', '', '']);

  // カテゴリ別
  fmt.push({ row: rows.length + 1, type: 'section' });
  rows.push(['■ カテゴリ別売上', '', '', '', '']);
  fmt.push({ row: rows.length + 1, type: 'tableHead' });
  rows.push(['カテゴリ', '売上金額', '件数', '構成比', '']);
  const categoryStartRow = rows.length + 1;
  categoryRows.forEach(function(r) { rows.push([r[0], r[1], r[2], r[3], '']); });
  const categoryEndRow = rows.length;
  rows.push(['', '', '', '', '']);

  // 商品トップ10
  fmt.push({ row: rows.length + 1, type: 'section' });
  rows.push(['■ 商品トップ10', '', '', '', '']);
  fmt.push({ row: rows.length + 1, type: 'tableHead' });
  rows.push(['順位', '商品名', 'カテゴリ', '売上金額', '件数']);
  const productStartRow = rows.length + 1;
  productRanking.forEach(function(r) { rows.push(r); });
  const productEndRow = rows.length;

  sheet.getRange(1, 1, rows.length, REPORT_COLS).setValues(rows);

  // 体裁
  sheet.getRange(1, 1).setFontSize(14).setFontWeight('bold');
  fmt.forEach(function(f) {
    if (f.type === 'section') {
      sheet.getRange(f.row, 1, 1, REPORT_COLS)
        .setFontWeight('bold')
        .setBackground(COLOR_SECTION);
    } else if (f.type === 'tableHead') {
      sheet.getRange(f.row, 1, 1, REPORT_COLS)
        .setFontWeight('bold')
        .setBackground(COLOR_TABLE_HEAD)
        .setHorizontalAlignment('center');
    } else if (f.type === 'summaryAmount') {
      sheet.getRange(f.row, 1).setFontWeight('bold');
    } else if (f.type === 'summaryNumber') {
      sheet.getRange(f.row, 1).setFontWeight('bold');
    }
  });

  // 数値フォーマット
  // サマリー: 合計売上(行3+B), 取引件数(B), 平均客単価(B)
  sheet.getRange(5, 2).setNumberFormat('¥#,##0'); // 合計売上
  sheet.getRange(7, 2).setNumberFormat('¥#,##0'); // 平均客単価
  // カテゴリ: 売上金額（B列）、構成比（D列）
  if (categoryEndRow >= categoryStartRow) {
    sheet.getRange(categoryStartRow, 2, categoryEndRow - categoryStartRow + 1, 1).setNumberFormat('¥#,##0');
    sheet.getRange(categoryStartRow, 4, categoryEndRow - categoryStartRow + 1, 1).setNumberFormat('0.0%');
  }
  // 商品トップ10: 売上金額（D列）
  if (productEndRow >= productStartRow) {
    sheet.getRange(productStartRow, 4, productEndRow - productStartRow + 1, 1).setNumberFormat('¥#,##0');
  }

  // 列幅
  sheet.setColumnWidth(1, 130);
  sheet.setColumnWidth(2, 140);
  sheet.setColumnWidth(3, 90);
  sheet.setColumnWidth(4, 110);
  sheet.setColumnWidth(5, 80);
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('自動ボタン')
    .addItem('月次レポート生成', 'generateMonthlyReport')
    .addToUi();
}

sheet.getRange(1, 1, rows.length, REPORT_COLS).setValues(rows);

const rows = [];
rows.push(['月次レポート ', '', '', '', '']);  // タイトル行
rows.push(['', '', '', '', '']);                       // 空行
rows.push(['■ サマリー', '', '', '', '']);
rows.push(['対象期間', month, '', '', '']);
rows.push(['合計売上', totalAmount, '', '', '']);
// ... カテゴリ別、商品トップ10と続く

✗ 遅い:  for (...) { sheet.getRange(行, 列).setValue(値); }   ← 30回通信
○ 速い:  sheet.getRange(1, 1, 30, 5).setValues(rows);         ← 1回通信