【GAS公開】スプレッドシートで「商品別・月別」の月末在庫クロス表をワンクリック作成する方法！

皆さん、こんにちは！モアネ編集員のぽんです✨

在庫管理って、数字の確認と集計にすごく手間がかかりますよね。特に、過去の在庫推移を確認したいとき、「月ごとの月末在庫」を商品コード別にクロス表にする作業は、手作業だと気が遠くなります…。

ネクストエンジンから日々の入出庫履歴をCSVで出力しても、そこから月ごとの最終在庫を拾い出して、データのない月を補完して…なんてやっていると、あっという間に時間が過ぎてしまいます。

そこで今回は、Google Apps Script (GAS) を使って、この面倒な集計作業をワンクリックで自動化する仕組みを作ってみましたのでご紹介します！

※記事内容の操作にについてサポートセンターではご案内できません。
　あらかじめご了承ください。

1．どんな表が作れるの？
2．事前準備～カスタムデータ～
3．事前準備～Googleスプレッドシート～
1. データのインポートとシート名の設定
2. 【ワンクリック化】GASコードをコピペ
まとめ

1．どんな表が作れるの？

今回作成するクロス表は、以下の3つのルールに基づいています。

ルール
1．各月で、最も新しい入出庫日の「更新後在庫数」を月末在庫とする。
2．その商品で初めて入出庫履歴がある月より前の月は、在庫を 0 とする。
3．初回活動月以降で、入出庫履歴がない月は、前月の月末在庫数を引き継ぐ。

ネクストエンジンから出力した入出庫履歴データから、最終的にこのような「商品コード × 各月」のクロス表が、たった1回クリックするだけで完成します！

注意事項
1．過去に1度も入出庫履歴がない商品は出力されない。
2．データ量が多すぎる場合、スプレッドシートの処理が止まってしまう可能性あり

2．事前準備～カスタムデータ～

まずは、元となるデータをカスタムデータ作成アプリより出力するためのテンプレートを作成します。

カスタムデータ作成を開き、[作る]をクリック
以下の項目を出力項目に設定（分類名：項目名）
- 商品マスタ：商品コード
- 在庫入出庫履歴：入出庫日
- 在庫入出庫履歴：入庫or出庫
- 在庫入出庫履歴：更新後在庫数

(オプション)　並び順を設定　で以下の条件を追加
- 項目の指定 → [ 商品マスタ：商品コード]
  - 昇順/降順の指定 → 昇順
- 項目の指定 → [ 在庫入出庫履歴：入出庫日]
  - 並び替えの単位 → 年月日単位
  - 昇順/降順の指定 → 昇順

【2】パターン情報を設定にて以下の項目を設定し、[パターンを保存]をクリック
- 名称：任意
- 出力するファイル名：任意

[ダウンロード・アップロード]を開き、データをダウンロード
- 行数の制限：制限を授けない　を選択してください

※データ量が多すぎてダウンロードできない場合は、下記マニュアルを参考に出力期間などの条件を別途追加してください。

▼オリジナルパターンの作成｜4．出力(絞り込み)の条件を設定

3．事前準備～Googleスプレッドシート～

データのインポートとシート名の設定

新規のスプレッドシートを開き、名称を設定
カスタムデータより出力したデータをスプレッドシートにインポートします
- [ファイル]＞[インポート]＞[アップロード]
  - インポート場所：現在のシートを置換する
  - 区切り文字の種類：カンマ
インポートしたシートの名称を「入出庫履歴」に変更
新しいシートを追加し、シートの名称を「月末在庫クロス表」に変更

【ワンクリック化】GASコードをコピペ

いよいよ自動化のためのGASコードです。以下の手順で設定してください。

① GASエディタを開く

スプレッドシートのメニューから「拡張機能」＞「Apps Script」を選択します。
開いたエディタに、元々入っているコードをすべて削除し、以下のコードを貼り付けます。

/**
 * スプレッドシートを開いたときにカスタムメニューを作成します。
 * onOpen関数は、スプレッドシートがロードされると自動で実行されます。
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('在庫管理メニュー')
      .addItem('月末在庫クロス表を作成', 'createMonthlyStockCrossTab') // 実行関数を指定
      .addToUi();
}


/**
 * 入出庫履歴シートからデータを読み込み、月ごとの月末在庫クロス表を作成します。
 * ルール1: 各月で最終の在庫数を採用
 * ルール2: 初回活動前の月は0埋め
 * ルール3: 活動がない月は前月の在庫を引き継ぎ
 */
function createMonthlyStockCrossTab() {
  // --- 設定 ---
  const sheetName = '入出庫履歴';     // データのあるシート名 (必要に応じて修正)
  const outputSheetName = '月末在庫クロス表'; // 結果を出力するシート名
  const stockColumnIndex = 4;        // '更新後在庫数'がD列 (1-based index: 4)

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName(sheetName);

  if (!dataSheet) {
    Browser.msgBox('エラー', `シート「${sheetName}」が見つかりません。シート名を確認してください。`, Browser.Buttons.OK);
    return;
  }

  // --- データ読み込み（安全に最終行まで取得し、空行をスキップ） ---
  const lastRow = dataSheet.getMaxRows(); 
  const lastColumn = dataSheet.getLastColumn();
  
  if (lastRow <= 1) {
    Browser.msgBox('エラー', 'データが2行目以降に見つかりません。データが存在するか確認してください。', Browser.Buttons.OK);
    return;
  }
  
  // A2からシートの最大行までを取得
  const range = dataSheet.getRange(2, 1, lastRow - 1, lastColumn);
  const rawData = range.getValues();
  const data = []; 

  // --- 変数初期化 ---
  const stockMap = new Map();
  const productCodes = new Set();
  let minDate = new Date(9999, 11, 31); 
  let maxDate = new Date(0); 

  // --- 1. 有効なデータの抽出とソート準備 ---
  for (let i = 0; i < rawData.length; i++) {
    const row = rawData[i];
    const productCode = row[0];
    const dateValue = row[1];
    
    // A列とB列が有効な行のみを抽出
    if (!productCode || productCode.toString().trim() === '' || !(dateValue instanceof Date || typeof dateValue === 'string')) {
      continue;
    }
    
    const date = new Date(dateValue);
    
    const cleanedRow = [productCode, date, row[stockColumnIndex - 1]];
    data.push(cleanedRow);

    if (date < minDate) minDate = date;
    if (date > maxDate) maxDate = date;
    
    productCodes.add(productCode);
  }
  
  if (data.length === 0) {
     Browser.msgBox('エラー', '有効な入出庫履歴データが2行目以降に見つかりませんでした。A列とB列を確認してください。', Browser.Buttons.OK);
    return;
  }

  // 日付順にソート（昇順）
  const sortedData = data.sort((a, b) => a[1] - b[1]);

  // --- 2. 月末在庫の抽出 (ルール1) ---
  for (let i = 0; i < sortedData.length; i++) {
    const row = sortedData[i];
    const productCode = row[0];
    const date = row[1];
    const currentStock = parseInt(row[2]); 
    
    const yearMonth = Utilities.formatDate(date, ss.getSpreadsheetTimeZone(), 'yyyy-MM');

    if (!stockMap.has(productCode)) {
      stockMap.set(productCode, new Map());
    }

    // 日付順に処理することで、同じ月の最終在庫が記録される
    stockMap.get(productCode).set(yearMonth, currentStock);
  }


  // --- 3. 月列のヘッダーリスト作成 ---
  const headerMonths = [];
  let currentDate = new Date(minDate.getFullYear(), minDate.getMonth(), 1); 
  const maxMonth = new Date(maxDate.getFullYear(), maxDate.getMonth() + 1, 0); 

  while (currentDate <= maxMonth) {
    headerMonths.push(Utilities.formatDate(currentDate, ss.getSpreadsheetTimeZone(), 'yyyy-MM'));
    currentDate.setMonth(currentDate.getMonth() + 1);
  }

  // --- 4. クロス表の作成と在庫の適用 (ルール2, 3) ---
  const result = [];
  const sortedProductCodes = Array.from(productCodes).sort(); 

  const header = ['商品コード'].concat(headerMonths);
  result.push(header);

  for (const productCode of sortedProductCodes) {
    const row = [productCode];
    let foundFirstActivity = false;
    let previousMonthStock = 0;

    for (const month of headerMonths) {
      const eomStock = stockMap.get(productCode).get(month); 

      if (eomStock !== undefined) {
        // A. その月に入出庫があった場合 (ルール1)
        row.push(eomStock);
        foundFirstActivity = true;
        previousMonthStock = eomStock;
      } else if (!foundFirstActivity) {
        // B. 初回入出庫月より前の月の場合 (ルール2: 0埋め)
        row.push(0);
        previousMonthStock = 0;
      } else {
        // C. 初回入出庫月以降で、入出庫がなかった月の場合 (ルール3: 前月在庫引き継ぎ)
        row.push(previousMonthStock);
      }
    }
    result.push(row);
  }

  // --- 5. 結果の出力 ---
  let outputSheet = ss.getSheetByName(outputSheetName);
  if (outputSheet) {
    outputSheet.clear();
  } else {
    outputSheet = ss.insertSheet(outputSheetName);
  }

  outputSheet.getRange(1, 1, result.length, result[0].length).setValues(result);
  
  outputSheet.setFrozenRows(1);

  Browser.msgBox('完了', `「${outputSheetName}」シートに月末在庫のクロス表を作成しました！`, Browser.Buttons.OK);
}