従業員マスターをスプレッドシートで代用する、部署単位のAdmina SaaS管理

/**
 * ディレクトリ台帳スプレッドシート → Adminaディレクトリ（Identity）同期スクリプト
 *
 * 使い方:
 * 1. 台帳スプレッドシートの「拡張機能 > Apps Script」を開き、このコードを貼り付ける
 *    ※スクリプトと API キーは、台帳を編集できる人全員が見られる。台帳の編集者は API キーを見られてもいい人だけにする
 * 2. プロジェクトの設定 > スクリプト プロパティに、次の2つを登録する
 *    ADMINA_API_KEY: Admina の API キー / ADMINA_ORGANIZATION_ID: 組織ID（Admina 管理画面の URL に含まれる数値）
 * 3. SHEET_NAME・列定義（COL）を自社の台帳に合わせて修正する
 * 4. 台帳を新しく作る場合は、setupRosterSheet を1回実行してヘッダー行・サンプル行・プルダウンを用意する
 *    （既存の台帳を使う場合は不要。サンプル行は実際の従業員に書き換えるか削除する）
 * 5. 手動で syncDirectoryFromSheet を実行して動作確認する
 * 6. createDailyTrigger を1回だけ実行し、毎日自動実行のトリガーを設定する
 * 7. 台帳を開き直すと「Admina」メニューが出る。急ぎのときは「Admina > 今すぐ反映」で同期できる
 *    （初めて実行する人は、Google の承認画面でスクリプトの実行を許可する）
 *
 * 運用ルール: 退職した人の行は削除せず、ステータスを「退職」にするか最終勤務日を入れてください。
 * 行を削除しても Admina 側は就業中のまま残ります（実行ログに警告を出します）。
 */

// ===== 設定（自社の環境に合わせて変更）=====
const SHEET_NAME = "従業員一覧" // 台帳シートの名前
const TIME_ZONE = "Asia/Tokyo" // 最終勤務日を判定するタイムゾーン

// 台帳の列定義（1行目はヘッダー行として読み飛ばす）
const COL = {
  EMAIL: 0,        // A列: メールアドレス（突合キー）
  LAST_NAME: 1,    // B列: 姓
  FIRST_NAME: 2,   // C列: 名
  STATUS: 3,       // D列: ステータス（就業中 / 休職中 / 退職）
  EMP_TYPE: 4,     // E列: 雇用形態（正社員 / 契約社員 / ...）
  DEPT: 5,         // F列: 部署名
  CONTRACT_END: 6, // G列: 最終勤務日（任意・日付セル or YYYY-MM-DD）。Admina の契約終了日に入る
}

// 台帳の日本語表記 → Admina APIのenum値
const STATUS_MAP = {
  "就業中": "active",
  "休職中": "on_leave",
  "退職": "retired",
}
const EMP_TYPE_MAP = {
  "正社員": "full_time_employee",
  "契約社員": "fixed_time_employee",
  "派遣社員": "temporary_employee",
  "パート・アルバイト": "part_time_employee",
  "業務委託": "contract_employee", // コラボレーターは外部ID専用で、自社ドメインの人には使えない
  "その他": "other",
}

/**
 * メイン処理: 台帳を読み、Adminaディレクトリへ upsert（新規作成 or 差分更新）する
 */
function syncDirectoryFromSheet() {
  // APIキーと組織IDはコードに直書きせず、スクリプトプロパティから取得する
  const props = PropertiesService.getScriptProperties()
  const apiKey = props.getProperty("ADMINA_API_KEY")
  const organizationId = props.getProperty("ADMINA_ORGANIZATION_ID")
  if (!apiKey) throw new Error("スクリプトプロパティ ADMINA_API_KEY が設定されていません")
  if (!organizationId) throw new Error("スクリプトプロパティ ADMINA_ORGANIZATION_ID が設定されていません")
  const api = { key: apiKey, base: `https://api.itmc.i.moneyforward.com/api/v1/organizations/${organizationId}` }

  // メニューからの実行と毎日のトリガーが重なると二重に新規作成されるので、同時には1つだけ動かす
  const lock = LockService.getScriptLock()
  if (!lock.tryLock(30 * 1000)) throw new Error("別の同期が実行中です。少し待ってから、もう一度実行してください")
  try {
    return syncWithLock_(api)
  } finally {
    lock.releaseLock()
  }
}

function syncWithLock_(api) {

  const { members, emails, errors } = readRosterSheet_() // ① 台帳シートを読み込む（読めない行は errors へ）
  const identities = fetchAllIdentities_(api) // ② 既存Identityを全件取得（cursorでページング）
  const byEmail = new Map(
    identities
      .filter((i) => i.primaryEmail)
      .map((i) => [i.primaryEmail.toLowerCase(), i])
  )

  let created = 0
  let updated = 0
  let unchanged = 0
  const warnings = []

  for (const member of members) {
    const existing = byEmail.get(member.email)
    try {
      if (!existing) {
        const identity = createIdentity_(api, member) // ③-a 台帳にいてAdminaにいない → 新規作成
        warnIfNotManaged_(identity, warnings)
        created++
      } else if (needsUpdate_(existing, member)) {
        const identity = updateIdentity_(api, existing.id, member) // ③-b 差分あり → 更新
        warnIfNotManaged_(identity, warnings)
        updated++
      } else {
        warnIfNotManaged_(existing, warnings)
        unchanged++
      }
    } catch (e) {
      // 1件の失敗で全体を止めず、ログに溜めて最後にまとめて確認する
      errors.push(`${member.email}: ${explainError_(e.message)}`)
    }
  }

  // ④ 台帳から消えたのに Admina で就業中のまま残っている社内IDを洗い出す（自動では退職にしない）
  for (const identity of identities) {
    const email = (identity.primaryEmail || "").toLowerCase()
    if (identity.managementType === "managed" && identity.employeeStatus === "active" && !emails.has(email)) {
      warnings.push(`${email || identity.id}: 台帳に行がないのに Admina では就業中です。退職した人なら、台帳に行を戻してステータスを「退職」にしてください`)
    }
  }

  const summary = `同期完了: 新規 ${created}件 / 更新 ${updated}件 / 変更なし ${unchanged}件`
  console.log(summary)
  if (warnings.length > 0) {
    console.warn(`要確認 ${warnings.length}件\n${warnings.join("\n")}`)
  }
  if (errors.length > 0) {
    console.error(`失敗 ${errors.length}件\n${errors.join("\n")}`)
  }
  return { summary, warnings, errors }
}

/** ① 台帳シートを読み込んで従業員の配列に変換する */
function readRosterSheet_() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME)
  if (!sheet) throw new Error(`シート「${SHEET_NAME}」が見つかりません`)

  const today = Utilities.formatDate(new Date(), TIME_ZONE, "yyyy-MM-dd")
  const rows = sheet.getDataRange().getValues().slice(1) // ヘッダー行を除く
  const members = []
  const emails = new Set() // エラーの行も含めた、台帳にあるメールアドレス全件
  const errors = []

  rows.forEach((row, i) => {
    const rowNo = i + 2 // シート上の行番号（1行目はヘッダー）
    const email = String(row[COL.EMAIL]).trim().toLowerCase()
    if (!email) return // メールアドレスが空の行は対象外
    if (email === SAMPLE_EMAIL) {
      errors.push(`${rowNo}行目 ${email}: setupRosterSheet が入れたサンプル行です。実際の従業員に書き換えるか、行を削除してください`)
      return
    }
    emails.add(email)

    // 知らない表記を「就業中」扱いにすると退職検知が漏れるので、その行は反映せずに知らせる
    const statusLabel = String(row[COL.STATUS]).trim()
    const status = lookup_(STATUS_MAP, statusLabel)
    if (!status) {
      errors.push(`${rowNo}行目 ${email}: ステータス「${statusLabel}」は使えません（${Object.keys(STATUS_MAP).join(" / ")} のいずれか）`)
      return
    }
    const typeLabel = String(row[COL.EMP_TYPE]).trim()
    const employeeType = typeLabel ? lookup_(EMP_TYPE_MAP, typeLabel) : "unknown"
    if (!employeeType) {
      errors.push(`${rowNo}行目 ${email}: 雇用形態「${typeLabel}」は使えません（${Object.keys(EMP_TYPE_MAP).join(" / ")} のいずれか）`)
      return
    }
    const contractEndAt = toDateString_(row[COL.CONTRACT_END])
    if (contractEndAt && !/^\d{4}-\d{2}-\d{2}$/.test(contractEndAt)) {
      errors.push(`${rowNo}行目 ${email}: 最終勤務日「${row[COL.CONTRACT_END]}」は日付として読めません（日付セルか YYYY-MM-DD で入力）`)
      return
    }

    members.push({
      email,
      lastName: String(row[COL.LAST_NAME]).trim(),
      firstName: String(row[COL.FIRST_NAME]).trim(),
      // 最終勤務日を過ぎた人（翌日以降）は、ステータス列の値にかかわらず退職として反映する
      employeeStatus: contractEndAt && contractEndAt < today ? "retired" : status,
      employeeType,
      department: String(row[COL.DEPT]).trim(),
      contractEndAt,
    })
  })

  return { members, emails, errors }
}

/** よくある API エラーに、原因の手がかりを添える */
function explainError_(message) {
  // ドメインが未登録だと外部IDとして扱われ、正社員など社内ID専用の雇用形態が不正と判定される
  if (message.includes("identity_invalid_employee_type")) {
    return `${message}（メールアドレスのドメインが 設定 > 組織 > ドメイン に登録されているか確認してください）`
  }
  return message
}

/** 対応表にある表記ならAPIの値を、なければ null を返す */
function lookup_(map, label) {
  return Object.prototype.hasOwnProperty.call(map, label) ? map[label] : null
}

/** 社内IDとして登録されていなければ警告に積む（ドメイン未登録のとき外部IDなどに分類されるため） */
function warnIfNotManaged_(identity, warnings) {
  if (identity && identity.managementType && identity.managementType !== "managed") {
    warnings.push(`${identity.primaryEmail}: 社内IDではなく「${identity.managementType}」として登録されています。設定 > 組織 > ドメイン にメールアドレスのドメインが登録されているか確認してください`)
  }
}

/** ② Identity一覧APIをページングしながら全件取得する */
function fetchAllIdentities_(api) {
  const all = []
  let cursor = null
  do {
    const query = cursor ? `?limit=200&cursor=${encodeURIComponent(cursor)}` : "?limit=200"
    const res = request_(api, "get", `/identity${query}`)
    all.push(...res.items)
    cursor = res.meta.nextCursor // 次ページがなければ null になる
  } while (cursor)
  return all
}

/** 差分があるか判定する（無駄な更新リクエストを打たないため） */
function needsUpdate_(existing, member) {
  return (
    existing.employeeStatus !== member.employeeStatus ||
    existing.employeeType !== member.employeeType ||
    existing.lastName !== member.lastName ||
    existing.firstName !== member.firstName ||
    (existing.department?.name || "") !== member.department ||
    toDateString_(existing.lifecycle?.contractEndAt) !== member.contractEndAt
  )
}

/** ③-a 新しい従業員を作成する（作成したIdentityを返す） */
function createIdentity_(api, member) {
  return request_(api, "post", "/identity", toPayload_(member))
}

/** ③-b 既存の従業員を更新する（更新後のIdentityを返す） */
function updateIdentity_(api, identityId, member) {
  return request_(api, "put", `/identity/${identityId}`, toPayload_(member))
}

/** Admina APIに渡すリクエストボディを組み立てる */
function toPayload_(member) {
  return {
    employeeStatus: member.employeeStatus, // 必須: active / retired など
    employeeType: member.employeeType,     // 必須: full_time_employee など
    lastName: member.lastName,             // 必須
    firstName: member.firstName,           // 必須
    displayName: `${member.lastName} ${member.firstName}`,
    primaryEmail: member.email,
    department: { name: member.department || null }, // 空欄なら null で部署を消す（department ごと null だと変更されない）
    lifecycle: { contractEndAt: member.contractEndAt }, // 契約終了日（台帳の最終勤務日）
  }
}

/** HTTPリクエストの共通処理 */
function request_(api, method, path, payload) {
  const res = UrlFetchApp.fetch(api.base + path, {
    method,
    contentType: "application/json",
    headers: { Authorization: `Bearer ${api.key}` },
    payload: payload ? JSON.stringify(payload) : undefined,
    muteHttpExceptions: true, // エラー時も例外にせず、自分でハンドリングする
  })
  const code = res.getResponseCode()
  const body = res.getContentText()
  if (code >= 400) {
    throw new Error(`APIエラー ${code}: ${body.slice(0, 200)}`)
  }
  return body ? JSON.parse(body) : {}
}

/** 日付セル・ISO文字列を YYYY-MM-DD に正規化する */
function toDateString_(value) {
  if (!value) return null
  if (value instanceof Date) {
    return Utilities.formatDate(value, TIME_ZONE, "yyyy-MM-dd")
  }
  return String(value).slice(0, 10) // "2026-09-30T00:00:00.000Z" → "2026-09-30"
}

const SAMPLE_EMAIL = "taro.yamada@example.com" // setupRosterSheet が入れるサンプル行（同期では反映しない）

/** 台帳を新しく作るときに1回だけ実行: ヘッダー行・サンプル行・入力規則を用意する */
function setupRosterSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME)
  if (sheet.getLastRow() > 0) {
    throw new Error(`シート「${SHEET_NAME}」には既にデータがあります。上書きしないよう、空のシートで実行してください`)
  }

  const header = { EMAIL: "メールアドレス", LAST_NAME: "姓", FIRST_NAME: "名", STATUS: "ステータス", EMP_TYPE: "雇用形態", DEPT: "部署", CONTRACT_END: "最終勤務日" }
  const sample = { EMAIL: SAMPLE_EMAIL, LAST_NAME: "山田", FIRST_NAME: "太郎", STATUS: "就業中", EMP_TYPE: "正社員", DEPT: "情報システム部", CONTRACT_END: "" }
  const width = Math.max(...Object.values(COL)) + 1
  const toRow = (values) => {
    const row = new Array(width).fill("")
    for (const [key, index] of Object.entries(COL)) row[index] = values[key]
    return row
  }
  sheet.getRange(1, 1, 2, width).setValues([toRow(header), toRow(sample)])
  sheet.getRange(1, 1, 1, width).setFontWeight("bold")
  sheet.setFrozenRows(1)

  // 表記ゆれの行は同期でエラーになるので、ステータス・雇用形態はプルダウン、最終勤務日は日付だけ入力できるようにする
  const rows = sheet.getMaxRows() - 1
  const listRule = (values) =>
    SpreadsheetApp.newDataValidation().requireValueInList(values, true).setAllowInvalid(false).build()
  sheet.getRange(2, COL.STATUS + 1, rows).setDataValidation(listRule(Object.keys(STATUS_MAP)))
  sheet.getRange(2, COL.EMP_TYPE + 1, rows).setDataValidation(listRule(Object.keys(EMP_TYPE_MAP)))
  sheet
    .getRange(2, COL.CONTRACT_END + 1, rows)
    .setDataValidation(SpreadsheetApp.newDataValidation().requireDate().setAllowInvalid(false).build())
    .setNumberFormat("yyyy-mm-dd")
}

/** 台帳を開いたときに「Admina」メニューを出す */
function onOpen() {
  SpreadsheetApp.getUi().createMenu("Admina").addItem("今すぐ反映", "syncFromMenu").addToUi()
}

/** メニューから同期し、結果を画面に出す（台帳の編集者は実行ログを見に行かなくて済む） */
function syncFromMenu() {
  const ui = SpreadsheetApp.getUi()
  try {
    const { summary, warnings, errors } = syncDirectoryFromSheet()
    const details = [
      ...(errors.length > 0 ? [`失敗 ${errors.length}件`, ...errors] : []),
      ...(warnings.length > 0 ? [`要確認 ${warnings.length}件`, ...warnings] : []),
    ]
    ui.alert([summary, ...details].join("\n"))
  } catch (e) {
    ui.alert(`同期できませんでした: ${e.message}`)
  }
}

/** 初回に1回だけ実行: 毎日1回・0時台に同期する時間主導型トリガーを作る */
function createDailyTrigger() {
  // 既存の同名トリガーがあれば消してから作り直す（重複防止）
  ScriptApp.getProjectTriggers()
    .filter((t) => t.getHandlerFunction() === "syncDirectoryFromSheet")
    .forEach((t) => ScriptApp.deleteTrigger(t))

  ScriptApp.newTrigger("syncDirectoryFromSheet")
    .timeBased()
    .everyDays(1)
    .atHour(0) // 毎日0時台に実行
    .create()
}