二代目めんツナかんかんIoTを作ってみた(ソラカメとAI画像解析で在庫管理)

/** =========================================
 * 在庫カウント受信 → 変化時のみメール通知（GAS + スプレッドシート）
 * 通知は Gmail（MailApp）で送信。
 *
 * ■シート構成（自動作成）
 *   - Current : A1=product_id, B1=count, C1=image_path, D1=updated_at（1行保持）※B1以外は白文字にしておく
 *   - History : 追記ログ（timestamp, product_id, old_count, new_count, diff, image_path, source_ip）
 *   - Settings: A1=notify_emails（カンマ区切り）, A2=shared_secret（トークン）
 *
 * ■GET 仕様（推奨）
 *   /exec?token=YOUR_SECRET&count=123&image_path=...（URLエンコード必須）
 *
 * ■POST 仕様（互換）
 *   { "input":  { "image_path": "..." }, "output": { "count": 123 } }
 * ========================================= */

const TZ = 'Asia/Tokyo';
const CURRENT_SHEET  = 'Current';
const HISTORY_SHEET  = 'History';
const SETTINGS_SHEET = 'Settings';
const DEFAULT_PRODUCT_ID = '****';

const SPREADSHEET_ID = '****************';

function fmt_(d) {
  return Utilities.formatDate(d, TZ, "yyyy-MM-dd'T'HH:mm:ssXXX");
}
function ss_() {
  return SpreadsheetApp.openById(SPREADSHEET_ID);
}

function ensureSheets_() {
  const ss = ss_();

  // Settings
  let settings = ss.getSheetByName(SETTINGS_SHEET);
  if (!settings) {
    settings = ss.insertSheet(SETTINGS_SHEET);
    settings.getRange('A1').setValue('notify_emails（例: [email protected],[email protected]）');
    settings.getRange('A2').setValue('YOUR_SECRET');
  }

  // Current
  let cur = ss.getSheetByName(CURRENT_SHEET);
  if (!cur) {
    cur = ss.insertSheet(CURRENT_SHEET);
    cur.getRange('A1:D1').setValues([['product_id','count','image_path','updated_at']]);
  }

  // History
  let hist = ss.getSheetByName(HISTORY_SHEET);
  if (!hist) {
    hist = ss.insertSheet(HISTORY_SHEET);
    hist.getRange(1,1,1,7).setValues([['timestamp','product_id','old_count','new_count','diff','image_path','source_ip']]);
  }
}

function handle_(countNumber, imagePath, sourceIp) {
  ensureSheets_();
  const now = new Date();
  const ss = ss_();

  const lock = LockService.getScriptLock();
  lock.waitLock(10000);

  try {
    const cur = ss.getSheetByName(CURRENT_SHEET);
    const row = cur.getRange('A1:D1').getValues()[0];
    let oldCount = Number(row[1]);
    if (!Number.isFinite(oldCount)) oldCount = NaN;

    const changed = !Number.isFinite(oldCount) || oldCount !== countNumber;

    if (changed) {
      const iso = fmt_(now);

      cur.getRange('A1').setValue(DEFAULT_PRODUCT_ID);
      cur.getRange('B1').setValue(countNumber);
      cur.getRange('C1').setValue(String(imagePath));
      cur.getRange('D1').setValue(iso);

      const hist = ss.getSheetByName(HISTORY_SHEET);
      hist.appendRow([
        iso,
        DEFAULT_PRODUCT_ID,
        Number.isFinite(oldCount) ? oldCount : '',
        countNumber,
        Number.isFinite(oldCount) ? (countNumber - oldCount) : '',
        String(imagePath),
        String(sourceIp || '')
      ]);

      notifyChange_(ss, {
        productId: DEFAULT_PRODUCT_ID,
        oldCount: Number.isFinite(oldCount) ? oldCount : null,
        newCount: countNumber,
        diff: Number.isFinite(oldCount) ? (countNumber - oldCount) : null,
        imagePath: String(imagePath),
        when: iso
      });

      return { status: 200, updated: true, product_id: DEFAULT_PRODUCT_ID, image_path: imagePath, count: countNumber, message: 'count changed and saved' };
    }

    return { status: 200, updated: false, product_id: DEFAULT_PRODUCT_ID, image_path: imagePath, count: countNumber, message: 'no change (count identical)' };

  } catch (err) {
    console.error(err);
    return { status: 500, message: 'internal error' };
  } finally {
    lock.releaseLock();
  }
}

function doGet(e){
  const ss = ss_();
  const settings = ss.getSheetByName(SETTINGS_SHEET);
  const expected = (settings && settings.getRange('A2').getValue() || '').toString().trim();
  const token = (e.parameter && e.parameter.token) || '';
  if (expected && token !== expected) {
    return json_({ status: 401, message: 'unauthorized' });
  }

  const countStr   = (e.parameter && e.parameter.count);
  const imagePath  = (e.parameter && e.parameter.image_path) || '';
  const sourceIp   = (e.parameter && e.parameter.source_ip) || '';

  const countNumber = Number(countStr);
  if (!Number.isFinite(countNumber) || countNumber < 0) {
    return json_({ status: 400, message: 'count must be a non-negative number' });
  }

  const result = handle_(countNumber, imagePath, sourceIp);
  return json_(result);
}

function doPost(e) {
  ensureSheets_();
  const ss = ss_();
  const settings = ss.getSheetByName(SETTINGS_SHEET);
  const expected = (settings && settings.getRange('A2').getValue() || '').toString().trim();
  const token = (e.parameter && e.parameter.token) || '';
  if (expected && token !== expected) {
    return json_({ status: 401, message: 'unauthorized' });
  }

  if (!e.postData || !e.postData.contents) {
    return json_({ status: 400, message: 'empty body' });
  }
  let body;
  try {
    body = JSON.parse(e.postData.contents);
  } catch (err) {
    return json_({ status: 400, message: 'invalid json' });
  }

  const imagePath = (body && body.input && body.input.image_path) || '';
  const rawCount  = (body && body.output && body.output.count);
  const countNumber = Number(rawCount);
  if (!Number.isFinite(countNumber) || countNumber < 0) {
    return json_({ status: 400, message: 'output.count must be >= 0' });
  }

  const sourceIp = (e.parameter && e.parameter.source_ip) || '';
  const result = handle_(countNumber, imagePath, sourceIp);
  return json_(result);
}

function notifyChange_(ss, p) {
  const settings = ss.getSheetByName(SETTINGS_SHEET);
  const emailsRaw = (settings && settings.getRange('A1').getValue() || '').toString();
  const recipients = emailsRaw.split(',').map(function(s){ return s.trim(); }).filter(function(s){ return !!s; });
  if (recipients.length === 0) return;

  var subject;
  if (p.diff === null) {
    subject = '在庫カウント変更: (初回保存)（現在 ' + p.newCount + '）';
  } else {
    subject = '在庫カウント変更: ' + (p.diff > 0 ? '+' + p.diff : String(p.diff)) + '（現在 ' + p.newCount + '）';
  }

  var lines = [
    'product_id: ' + p.productId,
    p.imagePath ? ('image_path: ' + p.imagePath) : '',
    '旧: ' + (p.oldCount !== null ? p.oldCount : '(初回/不明)'),
    '新: ' + p.newCount,
    '差分: ' + (p.diff !== null ? (p.diff > 0 ? '+' + p.diff : String(p.diff)) : '(初回/不明)'),
    '時刻: ' + p.when + ' (' + TZ + ')'
  ].filter(function(s){ return !!s; });

  var html = lines.join('<br>');
  sendViaGmail_(recipients, subject, html);
}

function sendViaGmail_(toList, subject, htmlBody) {
  if (!toList || toList.length === 0) return;
  MailApp.sendEmail({
    to: toList.join(','),
    subject: subject,
    htmlBody: htmlBody
  });
}

function json_(obj) {
  var out = ContentService.createTextOutput(JSON.stringify(obj));
  out.setMimeType(ContentService.MimeType.JSON);
  return out;
}