～with ChatGPT～ GAS初心者が商品管理マスタを自動化したリアルな記録

Google Apps Scriptで複数ある商品設計書（スプレッドシート）を一括管理・更新する方法

こんにちは。製造小売業で食品の製品開発をしているminamiと申します。
商品設計書（仕様書）のスプレッドシートが増えていくと、「どこに何があるのか分からない」「更新漏れがある」などの悩みが出てきます。

「またこの設計書、開いて確認か…」
「このJANコードの商品、どこに載ってたっけ？」
「原材料の価格が変わったけど、どこを直せばいいのかわからない…」

惣菜の商品開発という仕事は、目に見えない「小さな手間」の集合体でした。
そのひとつが、Gシートで管理されている設計書の確認と更新作業です

本記事では、Google Apps Script を活用した「商品設計書」の自動化管理について、3つの機能に分けて紹介します。

（作成した機能）
・原材料検索
・商品マスタ（JAN）と設計書の紐付け
・設計書内の単価一括更新

事前準備：フォルダ作成

設計書（Google Driveのフォルダ名）

・設計書ファイル1.xlsx

・設計書ファイル2.xlsx

・設計書ファイル3.xlsx～

今回は複数のテストファイルをひとつのフォルダ（設計書）に投函し、参照しながら管理用スプレッドシートを作成していきます。商品設計書の内容は下記の通りです。

管理用スプレッドシート

・商品マスタシート

・原材料マスタシート

・検索シート

・ログシート

実践

① 原材料検索機能

原材料名でDrive内の設計書を検索します。
コード（searchMaterialsInSheets）
👉 補足：B1セルの検索ワードを元に、Drive配下のシートを横断検索

「検索」シートのB1セルに入力した原材料名をもとに、Drive配下の設計書を全て走査して、該当があればファイル名・アイテム名・リンクを取得します。

スプレッドシート（検索）はこのように作成しました。

今回は設計書フォルダの中から、B1セル「白飯」を使用するすべての商品を検索します。
コードはChatGPTの力を借りてこのように作成しました。
（ChatGPTへの依頼文↓）

function searchMaterialsInSheets() {
  const folderName = '設計書';
  const sheetName = '検索';
  const materialToSearch = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange("B1")
    .getValue();
  const outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  const folders = DriveApp.getFoldersByName(folderName);
  if (!folders.hasNext()) {
    SpreadsheetApp.getUi().alert("フォルダ「" + folderName + "」が見つかりません。");
    return;
  }
  const folder = folders.next();
  outputSheet.getRange("B3:D1000").clearContent();

  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  let result = [];

  while (files.hasNext()) {
    const file = files.next();
    const ss = SpreadsheetApp.openById(file.getId());
    const sheets = ss.getSheets();

    for (let sheet of sheets) {
      const values = sheet.getDataRange().getValues();
      let found = false;

      for (let row of values) {
        for (let cell of row) {
          if (cell && typeof cell === 'string' && cell.includes(materialToSearch)) {
            found = true;
            break;
          }
        }
        if (found) break;
      }

      if (found) {
        const itemName = sheet.getRange("C3").getValue();
        result.push([file.getName(), itemName, ss.getUrl()]);
        break;
      }
    }
  }

  if (result.length > 0) {
    outputSheet.getRange(3, 2, result.length, 3).setValues(result);
    SpreadsheetApp.getUi().alert("検索完了しました。");
  } else {
    SpreadsheetApp.getUi().alert("一致する設計書は見つかりませんでした。");
  }
}

フォルダ内の複数あるシートから「白飯」を使用しているものをリストアップしたところ、このような検索結果がでてきました。成功！

② 商品マスタ（JAN）に基づいた原材料費の抽出

コード（updateMaterialCostFromDesignSheets）
👉 補足：設計書のF3セルとJANを照合して、コスト（L9）を商品マスタに記入／上書き

設計書スプレッドシートの中にあるJANコード（F3）と商品マスタのJAN（B列）を照合し、L9の単価情報をF列に書き込み＆ログ出力します。

ChatGPTへの依頼内容

サンプル：商品マスタシート

サンプル：商品設計書（原材料費参照先）

こちらが作成されたコードになります

function updateMaterialCostFromDesignSheets() {
  const folderName = '設計書';
  const masterSheetName = '商品マスタ';
  const logSheetName = 'ログ2';

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName(masterSheetName);
  const logSheet = getOrCreateSheet(logSheetName);

  const lastRow = masterSheet.getLastRow();
  const janList = masterSheet.getRange(2, 2, lastRow - 1).getValues();
  const currentValues = masterSheet.getRange(2, 6, lastRow - 1).getValues();

  const costResults = currentValues.map(v => [v[0]]);
  const logEntries = [];

  const folderIter = DriveApp.getFoldersByName(folderName);
  const folder = folderIter.next();
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);

  while (files.hasNext()) {
    const file = files.next();
    const ssFile = SpreadsheetApp.openById(file.getId());
    const sheets = ssFile.getSheets();

    for (const sheet of sheets) {
      const janInSheet = sheet.getRange("F3").getValue();
      if (!janInSheet) continue;

      for (let i = 0; i < janList.length; i++) {
        const jan = janList[i][0];
        if (jan && jan.toString().trim() === janInSheet.toString().trim()) {
          const newCost = sheet.getRange("L9").getValue();
          const oldCost = costResults[i][0];

          if (oldCost === '' || oldCost === null) {
            costResults[i][0] = newCost;
            logEntries.push([new Date(), jan, '', newCost, '新規']);
          } else if (oldCost != newCost) {
            costResults[i][0] = newCost;
            logEntries.push([new Date(), jan, oldCost, newCost, '上書き']);
          }
        }
      }
    }
  }

  masterSheet.getRange(2, 6, costResults.length, 1).setValues(costResults);
  if (logEntries.length > 0) {
    logSheet.getRange(logSheet.getLastRow() + 1, 1, logEntries.length, 5).setValues(logEntries);
  }

  SpreadsheetApp.getUi().alert(`商品マスタを更新しました（${logEntries.length} 件）。`);
}

反映されたシートです。大成功！

③ 設計書内の単価一括更新

コード（updateMaterialCostsInDesignSheets）
設計書にある原材料名（C列）と、原材料マスタのデータ（E列=原材料名、J列=単価）を照合して、F列（原単価）を自動で入力・更新します。

ChatGPTへの依頼内容

（サンプル：商品設計書）

サンプル：マスタシート
商品設計書C列を同じ原材料名を探し、J列の単価を「商品設計書」のF列へ書き込む

可能とのことで、下記コードを作成してくれました。

function updateMaterialCostsInDesignSheets() {
  const folderName = '設計書';
  const materialSheetName = '原材料マスタ';
  const logSheetName = 'ログ';

  const activeSS = SpreadsheetApp.getActiveSpreadsheet();
  const materialSheet = activeSS.getSheetByName(materialSheetName);
  const logSheet = getOrCreateSheet(logSheetName);

  const materialData = materialSheet.getRange(2, 5, materialSheet.getLastRow() - 1, 6).getValues();
  const materialMap = {};
  materialData.forEach(row => {
    const name = normalizeString(row[0]);
    const cost = row[5];
    if (name) materialMap[name] = cost;
  });

  const folder = getFolderByName(folderName);
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  const logEntries = [];

  while (files.hasNext()) {
    const file = files.next();
    const ss = SpreadsheetApp.openById(file.getId());
    const sheets = ss.getSheets();

    for (const sheet of sheets) {
      const lastRow = sheet.getLastRow();
      if (lastRow < 5) continue;

      const cRange = sheet.getRange(5, 3, lastRow - 4, 1);
      const fRange = sheet.getRange(5, 6, lastRow - 4, 1);
      const cValues = cRange.getValues();
      const fValues = fRange.getValues();

      const mergedCRanges = cRange.getMergedRanges();
      const mergedFRanges = fRange.getMergedRanges();
      mergedCRanges.forEach(range => range.breakApart());
      mergedFRanges.forEach(range => range.breakApart());

      for (let i = 0; i < cValues.length; i++) {
        const rawMaterial = cValues[i][0];
        const normMaterial = normalizeString(rawMaterial);
        const masterCost = materialMap[normMaterial];

        if (masterCost !== undefined) {
          const currentCost = fValues[i][0];
          const rowIndex = i + 5;

          if (currentCost === '' || currentCost === null) {
            sheet.getRange(rowIndex, 6).setValue(masterCost);
            logEntries.push([new Date(), file.getName(), sheet.getName(), rawMaterial, '', masterCost, '新規']);
          } else if (currentCost !== masterCost) {
            sheet.getRange(rowIndex, 6).setValue(masterCost);
            logEntries.push([new Date(), file.getName(), sheet.getName(), rawMaterial, currentCost, masterCost, '上書き']);
          }
        }
      }

      mergedCRanges.forEach(range => range.merge());
      mergedFRanges.forEach(range => range.merge());
    }
  }

  if (logEntries.length > 0) {
    logSheet.getRange(logSheet.getLastRow() + 1, 1, logEntries.length, 7).setValues(logEntries);
  }

  SpreadsheetApp.getUi().alert(`原単価を更新しました（${logEntries.length} 件）。`);
}

シートにはこのように自動入力されました

「ログ」シートにも記録を残します。

さて、ここで３つの機能をいれ、コードの選択が分かりづらくなったため、メニュー機能を追加しました。

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('設計書メニュー')
    .addItem('① 原材料検索（検索シート）', 'searchMaterialsInSheets')
    .addItem('② 商品マスタを更新', 'updateMaterialCostFromDesignSheets')
    .addItem('③ 設計書の原単価を一括更新', 'updateMaterialCostsInDesignSheets')
    .addToUi();
}

これで拡張機能を開かずに管理用スプレッドシートから更新ができます

おまけ：ヘルパー関数

// フォルダ取得
function getFolderByName(name) {
  const folders = DriveApp.getFoldersByName(name);
  if (!folders.hasNext()) {
    throw new Error(`フォルダ「${name}」が見つかりません。`);
  }
  return folders.next();
}

// ログシート作成／取得
function getOrCreateSheet(name) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(name);
  if (!sheet) {
    sheet = ss.insertSheet(name);
    sheet.appendRow(['日時', 'ファイル名', 'シート名', '原材料名', '旧単価', '新単価', '処理']);
  }
  return sheet;
}

// 表記ゆれ吸収用 正規化関数
function normalizeString(str) {
  if (!str) return '';
  return str
    .toString()
    .replace(/\s+/g, '') // スペース削除
    .replace(/[Ａ-Ｚａ-ｚ０-９]/g, s => String.fromCharCode(s.charCodeAt(0) - 0xFEE0)) // 全角→半角
    .toLowerCase();

おわりに

ここまで読んでいただき、ありがとうございました。
同じような業務で悩んでいる方の参考になれば嬉しいです。

🛠️ このスクリプトを形にするにあたっては、ChatGPT（by OpenAI）とあれこれ相談しながら進めました。
制作期間は3日。初心者の私にしては、なかなかの出来だと思っています
ゆとりができたので、職場のみんなにも使ってもらいました

ChatGPTでちょっと困ったことといえば、細かい指示ばかり出していたせいか、口調がとてもそっけなくなってしまった点でしょうか

今まで何日もかけていた作業が、ボタンひとつで片付くようになったのは感動的です
浮いた時間を使って、みんなに喜んでもらえるような「おいしい商品」を、開発室でたくさん試作していきたいと思います！！