Zenn
🦆

PC棚卸入力を設計してみよう

に公開
inventory
idea

なぜ作成したのか

  • Slackをつかって棚卸入力を簡単に実装してみたい

質問（to ChatGPT）

IT企業の情シス担当者です。
PCの棚卸アプリの構築を計画しています。

予定している技術スタックは以下です

  • Slack
  • Spreadsheet
  • GAS
  • Appsheet

ステークホルダーは以下です

  • 情シス担当者
  • 一般ユーザー

処理の流れは以下です。

  • 情シス担当者はPCの情報を「デバイスリスト」に登録する
  • 情シス担当者はデバイスリストからデバイスを選択し、使用する一般ユーザーを紐づけ、「貸出台帳」に登録します。
  • 情シス担当者は棚卸実施期間になったらデバイスを貸与したユーザーに対して一斉にSlackで棚卸通知を行います。
    • 棚卸通知には、棚卸入力画面へのリンクと、「こちらから棚卸入力じっししてね」というガイドを表示する
  • デバイスを貸与された一般ユーザーはメンションされたメッセージのリンクから棚卸入力画面を起動する。
    • 棚卸入力画面には、でばいすりすとに登録されている機種、シリアル番号、OS,CPU、メモリ、ストレージサイズを表示し、相違がある個所を修正できるようにする。SAVEボタンをクリックしたら、入力情報を「棚卸履歴」に登録する
  • 情シス担当者は棚卸確定一覧から入力された棚卸入力情報を選択し、棚卸確定画面を表示する。
    • 棚卸確定画面では、入力された棚卸情報を表示し、対応するデバイス情報と比較して、差異があるデバイス情報側を更新し、保存する。
    • 棚卸確定が完了した棚卸情報はステータスを「確定済み」として識別できるようにする。

制限事項

  • GASのアクセスできるユーザーは社内ユーザーのみとする

この条件をもとにシーケンス図を作成してください。
次に処理に必要なデータ項目を精査し、Spreadsheetの行設計を行ってください。
次にGASの設計を行ってください。
最後にAppsheetの設計を行ってください。

回答

以下では、Mermaid シーケンス図 → スプレッドシート行設計 → GAS 設計 → AppSheet 設計 の順にまとめます。

1. シーケンス図（Mermaid ）

2. Spreadsheet 行設計（主要 3 シート）

デバイスリスト (devices) 型 / 例 備考
DeviceID (PK) TEXT / DEV-0001 手入力または自動採番
AssetTag TEXT / IT2025-15 台帳管理番号
Model TEXT / ThinkPad X1 機種名
SerialNo TEXT S/N
OS TEXT / Windows 11 Pro 現在値
CPU TEXT / i7-1355U ―
MemoryGB NUMBER / 16 GB
StorageGB NUMBER / 512 GB
AssignedUser TEXT (メール) 空欄＝未貸与
AssignedDate DATE ―
Status ENUM(InStock / InUse / Retired) 運用状態
LastInventory DATE 最終確定日
貸出台帳 (loans) 型 / 例 備考
LoanID (PK) TEXT / L-2025-04-001 自動採番
DeviceID (FK) TEXT ―
UserMail TEXT 貸与ユーザー
AssignDate DATE ―
DueDate DATE 任意
ReturnDate DATE 返却時に更新
Status ENUM(OnLoan / Returned) ―
棚卸履歴 (inventory) 型 / 例 備考
InvID (PK) TEXT / INV-2025-05-001 自動採番
DeviceID TEXT ―
UserMail TEXT 報告者（AppSheet USEREMAIL()）
ReportedOS … ReportedStorageGB TEXT / NUM 入力値
InventoryDate DATE NOW() デフォルト
DeltaFlag YES/NO GAS が差異検出で自動セット
Status ENUM(未確定 / 確定済み) IT 確定時に更新
ConfirmedBy / ConfirmedDate TEXT / DATE 監査証跡

設計意図
差分は DeltaFlag で可視化し、確定後は devices に上書き
Slack 通知ログが必要なら別シート slack_log を追加しても良い

3. GAS 設計（ファイル構成 & 主要関数）

ファイル名 役割 主要関数
config.gs 定数 & PropertyStore SPREADSHEET_ID, Slack Bot Token …
onEdit.gs 共有トリガ onEdit(e) → 貸出台帳 or 棚卸履歴 更新判定
loanHandler.gs 貸与関連 updateDeviceOnLoan(e)
inventoryHandler.gs 棚卸関連 markDelta(invRow), applyInventory(invRow)
slackNotify.gs Slack API postInventoryDM(userMail, link)
menu.gs UI メニュー createCustomMenu(), startInventoryBatch()

処理フロー

  1. 初期化
    • createCustomMenu() を onOpen で呼び出し。スプレッドシート UI に「棚卸開始」メニューを表示。
  2. 貸与時 onEdit (loans 追加)
    • updateDeviceOnLoan() が devices シートに AssignedUser 等を書き込み。
  3. 棚卸開始
    • IT がメニュー実行 → startInventoryBatch()
    • 貸出台帳の OnLoan レコードを取得→ユーザー別に Slack DM (postInventoryDM())
  4. 棚卸履歴 onEdit
    • ユーザーが AppSheet で保存 → inventoryHandler.gs で差分チェック→ DeltaFlag
  5. 確定処理
    • AppSheet アクション「確定」→ applyInventory() を Google Apps Script API で呼出し。
    • デバイス情報を上書き、Status=確定済み に更新。

権限制御

  • スクリプト デプロイは「社内ドメインユーザーのみ」。
  • 外部 API キーは暗号化してプロパティに保存。

4. GAS 実装

以下は “デバイス棚卸アプリ” 用 Google Apps Script（GAS）一式 です。
そのままスプレッドシートに スクリプトエディタ > ファイル > 新規作成 でファイル分割して貼り付けてください。
★ TODO とコメントの箇所は自社環境に合わせて置き換えて下さい。

1. config.gs ― 定数／共通ユーティリティ

/**
*  ======== 共通定数・ユーティリティ ========
*  PropertyService に保存した値を優先。無い場合はここで定義。
*/
const CONF = (() => {
  const props = PropertiesService.getScriptProperties();
  return {
    // ★TODO: シート ID（ブック URL の `/d/` と `/edit` の間）
    SPREADSHEET_ID:  props.getProperty('SPREADSHEET_ID') || 'YOUR_SPREADSHEET_ID',
    // ★TODO: Slack Bot User OAuth Token (xoxb-****)
    SLACK_TOKEN:      props.getProperty('SLACK_TOKEN')     || 'xoxb-your-token',
    // シート名
    SHEET: {
      DEV:   'devices',
      LOAN:  'loans',
      INV:   'inventory'
    },
    // DM に含める AppSheet URL 先頭（末尾に DeviceID を付けて送信）
    APP_INV_FORM_PREFIX: props.getProperty('APP_INV_FORM_PREFIX') 
      || 'https://www.appsheet.com/start/APP_ID#view=Inventory_Form&DeviceID='
  };
})();

/**
* Spreadsheet オブジェクトを取得（キャッシュ）
*/
function getBook() {
  const cache = CacheService.getScriptCache();
  const key   = 'BOOK_' + CONF.SPREADSHEET_ID;
  const id    = cache.get(key);
  if (id) { return SpreadsheetApp.openById(id); }
  const ss = SpreadsheetApp.openById(CONF.SPREADSHEET_ID);
  cache.put(key, CONF.SPREADSHEET_ID, 300);
  return ss;
}

/**
* Slack API 呼び出し共通
*/
function callSlack(api, payload) {
  const url   = 'https://slack.com/api/' + api;
  const token = CONF.SLACK_TOKEN;
  const res = UrlFetchApp.fetch(url, {
    method: 'post',
    muteHttpExceptions: true,
    contentType: 'application/json; charset=utf-8',
    headers: { Authorization: 'Bearer ' + token },
    payload: JSON.stringify(payload)
  });
  const json = JSON.parse(res.getContentText());
  if (!json.ok) throw new Error('Slack API error: ' + JSON.stringify(json));
  return json;
}

2. menu.gs ― メニュー／バッチ起動

/**
* スプレッドシートを開いた時にカスタムメニューを追加
*/
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('棚卸')
      .addItem('棚卸開始（Slack DM 一斉送信）', 'startInventoryBatch')
      .addToUi();
}

/**
* ーーー棚卸開始ーーー
* loans シートの OnLoan レコードを抽出し、Slack へ DM を送信。
* 送信ログは console へ出力（必要なら slack_log シート追加を推奨）
*/
function startInventoryBatch() {
  const ss     = getBook();
  const loanSH = ss.getSheetByName(CONF.SHEET.LOAN);
  const devSH  = ss.getSheetByName(CONF.SHEET.DEV);

  const loan  = loanSH.getDataRange().getValues();
  const head  = loan.shift();                         // 1 行目 = 見出し
  const mIdx  = head.indexOf('UserMail');
  const dIdx  = head.indexOf('DeviceID');
  const sIdx  = head.indexOf('Status');

  const batch = {};

  // OnLoan エントリをメールごとにまとめる
  loan.forEach(row => {
    if (row[sIdx] !== 'OnLoan') return;
    const mail = row[mIdx];
    const dev  = row[dIdx];
    if (!batch[mail]) batch[mail] = [];
    batch[mail].push(dev);
  });

  const logs = [];
  Object.keys(batch).forEach(mail => {
    const linkList = batch[mail]
        .map(devId => `<${CONF.APP_INV_FORM_PREFIX}${encodeURIComponent(devId)}|${devId}>`)
        .join('\n• ');
    const text = `PC棚卸のご協力をお願いします :clipboard:\n以下のリンクから入力してください：\n• ${linkList}`;
    slackPostDM(mail, text);
    logs.push([new Date(), mail, batch[mail].join(',')]);
  });

  console.log('DM sent: ' + logs.length);
  SpreadsheetApp.flush();
}

3. slackNotify.gs ― Slack DM 送信

/**
* メールアドレスから Slack ユーザー ID を取得
* キャッシュ 6 時間
*/
function getSlackUserIdByEmail(mail) {
  const cache = CacheService.getScriptCache();
  const key   = 'SLACK_UID_' + mail;
  let uid     = cache.get(key);
  if (uid) return uid;

  const res = callSlack('users.lookupByEmail', { email: mail });
  uid = res.user && res.user.id;
  if (!uid) throw new Error('Slack user not found for ' + mail);
  cache.put(key, uid, 6 * 3600);
  return uid;
}

/**
* Slack DM を送信
*/
function slackPostDM(mail, message) {
  const uid = getSlackUserIdByEmail(mail);
  callSlack('chat.postMessage', {
    channel: uid,
    text: message
  });
}

4. loanHandler.gs ― 貸与イベント処理

/**
* loans シート更新時に devices シートへ反映
*/
function updateDeviceOnLoan(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== CONF.SHEET.LOAN) return;

  const row = sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];
  const header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  const deviceId = row[header.indexOf('DeviceID')];
  const userMail = row[header.indexOf('UserMail')];
  const status   = row[header.indexOf('Status')];

  const devSH = getBook().getSheetByName(CONF.SHEET.DEV);
  const devData = devSH.getRange(2, 1, devSH.getLastRow()-1, devSH.getLastColumn()).getValues();

  devData.forEach((dRow, i) => {
    if (dRow[0] === deviceId) {
      const rowIdx = i + 2;
      devSH.getRange(rowIdx, devSH.getRange(1,1,1,devSH.getLastColumn()).getValues()[0].indexOf('AssignedUser') + 1)
           .setValue(status === 'OnLoan' ? userMail : '');
      devSH.getRange(rowIdx, devSH.getRange(1,1,1,devSH.getLastColumn()).getValues()[0].indexOf('AssignedDate') + 1)
           .setValue(status === 'OnLoan' ? new Date() : '');
    }
  });
}

5. inventoryHandler.gs ― 棚卸差分検出／確定

/**
* inventory シート追加・変更時に差分チェック
*/
function markDelta(invRowObj) {
  const invSH = getBook().getSheetByName(CONF.SHEET.INV);
  const devSH = getBook().getSheetByName(CONF.SHEET.DEV);
  const headersInv = invSH.getDataRange().getValues()[0];
  const headersDev = devSH.getDataRange().getValues()[0];

  const deviceId = invRowObj['DeviceID'];
  const devIdx   = devSH.getRange(2,1,devSH.getLastRow()-1,1)
                        .getValues()
                        .flat()
                        .indexOf(deviceId);
  if (devIdx === -1) return;  // not found

  const devRow = devSH.getRange(devIdx+2, 1, 1, devSH.getLastColumn()).getValues()[0];
  const mapDev = {};
  headersDev.forEach((h,i)=>{ mapDev[h] = devRow[i]; });

  let diff = false;
  ['OS','CPU','MemoryGB','StorageGB'].forEach(col=>{
    const reported = invRowObj['Reported'+col];
    if (reported && reported != mapDev[col]) diff = true;
  });

  const rowIdxInv = invRowObj.__rowNum__;   // Apps Script 触発用
  invSH.getRange(rowIdxInv, headersInv.indexOf('DeltaFlag')+1).setValue(diff);
}

/**
* AppSheet アクションから呼ばれる確定関数
*   applyInventory("INV-2025-05-001")
*/
function applyInventory(invID) {
  const ss   = getBook();
  const invSH = ss.getSheetByName(CONF.SHEET.INV);
  const devSH = ss.getSheetByName(CONF.SHEET.DEV);

  const invData = invSH.getDataRange().getValues();
  const hInv    = invData.shift();
  const idxInv  = hInv.indexOf('InvID');
  const m       = invData.findIndex(r => r[idxInv] === invID);
  if (m === -1) throw new Error('inventory not found');

  const rowInv  = invData[m];
  const mapInv  = {};
  hInv.forEach((h,i)=>{ mapInv[h]=rowInv[i]; });

  // デバイス行
  const devId   = mapInv['DeviceID'];
  const devData = devSH.getDataRange().getValues();
  const hDev    = devData.shift();
  const idxDev  = devData.findIndex(r => r[0] === devId);
  if (idxDev === -1) throw new Error('device not found');

  // 差分を devices に反映
  ['OS','CPU','MemoryGB','StorageGB'].forEach(col=>{
    const repVal = mapInv['Reported'+col];
    if (repVal) {
      devSH.getRange(idxDev+2, hDev.indexOf(col)+1).setValue(repVal);
    }
  });
  devSH.getRange(idxDev+2, hDev.indexOf('LastInventory')+1).setValue(new Date());

  // inventory 行を確定済みに更新
  invSH.getRange(m+2, hInv.indexOf('Status')+1).setValue('確定済み');
  invSH.getRange(m+2, hInv.indexOf('ConfirmedBy')+1).setValue(Session.getActiveUser().getEmail());
  invSH.getRange(m+2, hInv.indexOf('ConfirmedDate')+1).setValue(new Date());
}

6. onEdit.gs ― 単一 onEdit ルーター

/**
* すべてのシート編集トリガ
*/
function onEdit(e) {
  const s = e.range.getSheet().getName();
  if (s === CONF.SHEET.LOAN) {
    updateDeviceOnLoan(e);
  } else if (s === CONF.SHEET.INV && e.value) {
    // 新規行（AppSheet Save）は often e.oldValue === undefined
    const row = e.range.getSheet().getRange(e.range.getRow(), 1, 1, e.range.getSheet().getLastColumn()).getValues()[0];
    const header = e.range.getSheet().getRange(1,1,1,e.range.getSheet().getLastColumn()).getValues()[0];
    const obj = { __rowNum__: e.range.getRow() };
    header.forEach((h,i)=>{ obj[h] = row[i]; });
    markDelta(obj);
  }
}

実装手順チェックリスト

  1. スクリプトプロパティ設定
    • SPREADSHEET_ID : ブック ID
    • SLACK_TOKEN : Bot Token (xoxb-…)
    • APP_INV_FORM_PREFIX : AppSheet 棚卸フォーム URL の先頭
  2. シート名 は devices / loans / inventory で一致させる
  3. スクリプトを保存 → トリガーを設定
    • onOpen : スプレッドシートを開いたとき
    • onEdit : スプレッドシートが編集されたとき
  4. Slack App の権限
    • users:read.email, chat:write, chat:write.public など
    • Bot をワークスペースへインストール
  5. AppSheet
    • アクション「Confirm Inventory」→ Apps Script の applyInventory(invID) を呼ぶ
    • フォームリンクは APP_INV_FORM_PREFIX + DeviceID で DM へ
  6. ドメイン制限
    • スクリプト共有を「社内ユーザーのみ」に設定
    • AppSheet アプリもドメイン限定デプロイ

4. AppSheet 設計

要素 内容
データソース 上記 3 シート (＋任意の slack_log)
Security Filters devices: OR(IN([Status], {"InUse","InStock"}), USERROLE()="admin")
loans: [UserMail] = USEREMAIL() OR USERROLE()="admin"
inventory: [UserMail] = USEREMAIL() OR USERROLE()="admin"
ユーザーロール admin ＝情シス担当者（メールドメイン条件で判定）
ビュー - My Devices (Deck) : 自分に紐付く端末一覧
- Inventory Form (Form) : UX: Show_if=CONTEXT("ViewType")="Detail"
- IT Dashboard (admin) : 未確定一覧＋確定ボタン
- Devices (Table, admin) : デバイス管理
アクション - Start Inventory : 行を inventory に作成 (Pre-filled) → 自動 Form へ
- Confirm Inventory : CALL Webhook → GAS applyInventory()
Bots / Automation - After Inventory Add → Slack Confirm DM (optional)
- Scheduled (Monthly) → Run GAS startInventoryBatch() など
UX Tips - Inventory Form の各フィールドに Initial value = [devices].[field] を設定し差分入力だけ求める
- DeltaFlag が TRUE の行を赤色ハイライト
オフライン & 同期 棚卸入力はモバイルでも可能。Sync on Save 推奨。

所感

  • 今日はお眠なのでお休み中に実験してみる
GitHubで編集を提案

Discussion