GASの業務ロジックをローカルでテストしたい ── GASの制約をOSSで越える

function decideApprovalRoute() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("経費申請履歴");
  return sheet;
}
decideApprovalRoute();

ReferenceError: SpreadsheetApp is not defined
    at decideApprovalRoute (/demo.js:2:17)

npm install -D vitest @ciderjs/vitest-plugin-gas-mock

import { defineConfig } from "vitest/config";
import { mockGas } from "@ciderjs/vitest-plugin-gas-mock";

export default defineConfig({
  plugins: [mockGas()],
  test: {
    globals: true, // グローバルへの注入に必要
    clearMocks: true, // テスト間で呼び出し履歴を引きずらないようにする
  },
});

import { mockChain } from "@ciderjs/vitest-plugin-gas-mock";

mockChain("SpreadsheetApp.getActive.getSheetByName.getDataRange.getValues", [
  ["u001", "2026-8", 12000],
]);

export function decideApprovalRoute(request: ExpenseRequest): ApprovalRoute {
  // ...
}

npm install -D rolldown rolldown-plugin-remove-export

import { defineConfig } from 'rolldown';
import { removeExportPlugin } from 'rolldown-plugin-remove-export';

export default defineConfig({
  input: "src/index.ts", // すべてのGAS関数がエクスポートされているファイル
  output: {
    file: 'dist/out.js',
  },
  plugins: [removeExportPlugin("out.js")],
});

type ApprovalRoute = "auto" | "manager" | "manager_via_deputy" | "executive";

type ExpenseRequest = {
  requesterId: string;
  amount: number;
  category: "交通費" | "交際費" | "備品" | "その他";
  date: Date;
};

const AUTO_APPROVE_LIMIT = 10000;
const EXECUTIVE_APPROVAL_LIMIT = 100000;
const MONTHLY_BUDGET_CAP = 50000;
const ENTERTAINMENT_MANAGER_REVIEW_LIMIT = 30000;

export function decideApprovalRoute(request: ExpenseRequest): ApprovalRoute {
  if (request.amount > EXECUTIVE_APPROVAL_LIMIT) {
    return "executive";
  }

  if (request.category === "交際費" && request.amount > ENTERTAINMENT_MANAGER_REVIEW_LIMIT) {
    return isManagerOnLeave(request.date) ? "manager_via_deputy" : "manager";
  }

  const monthlyTotal = getMonthlySpending(request.requesterId, request.date);
  if (request.amount <= AUTO_APPROVE_LIMIT && monthlyTotal + request.amount <= MONTHLY_BUDGET_CAP) {
    return "auto";
  }

  return isManagerOnLeave(request.date) ? "manager_via_deputy" : "manager";
}

function getMonthlySpending(requesterId: string, date: Date): number {
  const sheet = SpreadsheetApp.getActive().getSheetByName("経費申請履歴");
  if (!sheet) return 0;

  // getValues() の実際の戻り値は any[][] なので、扱いやすいようにここでキャストしている
  const values = sheet.getDataRange().getValues() as [string, string, number][];
  const targetMonth = `${date.getFullYear()}-${date.getMonth() + 1}`;

  return values
    .filter(([id, ym]) => id === requesterId && ym === targetMonth)
    .reduce((sum, [, , amount]) => sum + amount, 0);
}

function isManagerOnLeave(date: Date): boolean {
  const events = CalendarApp.getDefaultCalendar().getEventsForDay(date);
  return events.some((event) => event.getTitle() === "上長休暇");
}

import { beforeEach, describe, expect, test } from "vitest";
import { mockChain } from "@ciderjs/vitest-plugin-gas-mock";
import { decideApprovalRoute } from "../src/expenseApproval";

describe("decideApprovalRoute", () => {
  beforeEach(() => {
    // デフォルトでは「履歴なし・上長は休暇していない」状態にしておく
    mockChain("SpreadsheetApp.getActive.getSheetByName.getDataRange.getValues", [[]]);
    mockChain("CalendarApp.getDefaultCalendar.getEventsForDay", []);
  });

  test("上限額を超える場合は決裁ルートになる", () => {
    const route = decideApprovalRoute({
      requesterId: "u001",
      amount: 150000,
      category: "備品",
      date: new Date(2026, 7, 20),
    });
    expect(route).toBe("executive");
  });

  test("交際費が閾値を超え、上長が出社している場合は上長承認になる", () => {
    const route = decideApprovalRoute({
      requesterId: "u001",
      amount: 40000,
      category: "交際費",
      date: new Date(2026, 7, 20),
    });
    expect(route).toBe("manager");
  });

  test("交際費が閾値を超え、上長が休暇中の場合は代理承認になる", () => {
    mockChain("CalendarApp.getDefaultCalendar.getEventsForDay", [{ getTitle: () => "上長休暇" }]);

    const route = decideApprovalRoute({
      requesterId: "u001",
      amount: 40000,
      category: "交際費",
      date: new Date(2026, 7, 20),
    });
    expect(route).toBe("manager_via_deputy");
  });

  test("少額かつ月次予算内なら自動承認になる", () => {
    const route = decideApprovalRoute({
      requesterId: "u001",
      amount: 8000,
      category: "交通費",
      date: new Date(2026, 7, 20),
    });
    expect(route).toBe("auto");
  });

  test("少額でも当月の累計が予算上限を超えるなら自動承認にならない", () => {
    mockChain("SpreadsheetApp.getActive.getSheetByName.getDataRange.getValues", [
      ["u001", "2026-8", 45000],
    ]);

    const route = decideApprovalRoute({
      requesterId: "u001",
      amount: 8000,
      category: "交通費",
      date: new Date(2026, 7, 20),
    });
    expect(route).toBe("manager");
  });

  test("履歴シートが存在しない場合は当月累計0として扱う", () => {
    mockChain("SpreadsheetApp.getActive.getSheetByName", null);

    const route = decideApprovalRoute({
      requesterId: "u001",
      amount: 8000,
      category: "交通費",
      date: new Date(2026, 7, 20),
    });
    expect(route).toBe("auto");
  });
});