Googleシートの長期連携：追加設定

設定前のサイトは従来方式で動作します。次の設定が完了したら、サイト側の長期連携を有効にします。秘密の値をチャットやGitHubに貼らないでください。

SupabaseのSQL Editorで、下の追加SQLを実行します。
Google Cloudの同じプロジェクトで、新しいウェブOAuthクライアントを作ります。既存ログイン用クライアントは変更しません。リダイレクトURI：https://xcxzkmzhrcqyrezdyuqz.supabase.co/functions/v1/google-sheets/callback
SupabaseのEdge Functions → Secretsに GOOGLE_SHEETS_CLIENT_ID、GOOGLE_SHEETS_CLIENT_SECRET、GOOGLE_SHEETS_ENCRYPTION_KEY（ランダム32バイトのBase64）、APP_ORIGIN=https://musica-uneitool.pages.dev を登録します。
Edge Functionをエディターで作成し、名前を google-sheets にします。下のFunction全文を index.ts に貼り付けてDeployします。Googleからの戻り先にJWTがないため、Functionの自動JWT検証をOFFにします。POST操作の認証と権限確認はコード内で行います。
Google Sheets API、本番のOAuth公開状態と審査を確認します。
管理者がサイト側の長期連携を有効にして公開後、各利用者がログイン中と同じGoogleアカウントで最初の1回だけ許可します。

追加SQL

-- Google credentials are ciphertext accessible only to the Edge Function.
begin;
create table if not exists public.app_google_sheet_connections (
 user_id uuid primary key references auth.users(id) on delete cascade,
 google_sub text not null,
 encrypted_refresh text not null check(length(encrypted_refresh) between 1 and 16384),
 updated_at timestamptz not null default now()
);
create table if not exists public.app_google_sheet_oauth_states (
 state_hash text primary key check(state_hash ~ '^[a-f0-9]{64}$'),
 user_id uuid not null unique references auth.users(id) on delete cascade,
 google_sub text not null,
 encrypted_verifier text not null,
 expires_at timestamptz not null
);
alter table public.app_google_sheet_connections enable row level security;
alter table public.app_google_sheet_oauth_states enable row level security;
revoke all on public.app_google_sheet_connections,public.app_google_sheet_oauth_states from public,anon,authenticated;
grant select,insert,update,delete on public.app_google_sheet_connections,public.app_google_sheet_oauth_states to service_role;

-- Return only the registered source; never accept a caller-supplied sheet URL.
create or replace function public.app_google_sheet_source(p_kind text,p_concert uuid default null) returns text
language plpgsql security definer set search_path=pg_catalog,public as $$
declare source text;
begin
 if public.app_gate_status() is not true then raise exception 'Account access required' using errcode='42501'; end if;
 if p_kind in ('responses','absences') then
  if public.app_role(p_concert) is null or public.app_role(p_concert) not in ('owner','editor') then
   raise exception 'Edit permission required' using errcode='42501';
  end if;
  select case when p_kind='absences' then payload->>'absenceSheetURL' else payload->>'responseSheetURL' end into source from public.app_concerts where id=p_concert;
 elsif p_kind='master' then
  if public.app_roster_admin() is not true then raise exception 'Roster admin required' using errcode='42501'; end if;
  select source_url into source from public.app_master_roster where singleton;
 else raise exception 'Invalid sheet kind' using errcode='22023';
 end if;
 if source is null or source='' then raise exception 'Sheet source required' using errcode='22023'; end if;
 return source;
end $$;
revoke all on function public.app_google_sheet_source(text,uuid) from public,anon;
grant execute on function public.app_google_sheet_source(text,uuid) to authenticated;
commit;

Edge Function（単一ファイル）

// @ts-nocheck
// Self-contained JavaScript handler, tested with fictional services.
const SCOPE = "https://www.googleapis.com/auth/spreadsheets.readonly";
const encoder = new TextEncoder();
const uuid = /^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}$/i;
class Failure extends Error {
  constructor(code, status = 400) {
    super(code);
    this.status = status;
  }
}
const b64 = (bytes) => btoa(String.fromCharCode(...bytes));
const unb64 = (text) => Uint8Array.from(atob(text), (c) => c.charCodeAt(0));
const url64 = (bytes) =>
  b64(bytes).replaceAll("+", "-").replaceAll("/", "_").replace(/=+$/, "");
async function digest(text) {
  return new Uint8Array(
    await crypto.subtle.digest("SHA-256", encoder.encode(text)),
  );
}
async function hash(text) {
  return [...(await digest(text))]
    .map((b) => b.toString(16).padStart(2, "0"))
    .join("");
}
function createHandler({
  env,
  fetcher = fetch,
  now = () => Date.now(),
}) {
  return async (request) => {
    let origin;
    let stage = "configuration",
      backendCode = null;
    try {
      origin = new URL(env("APP_ORIGIN")).origin;
    } catch {
      return new Response(
        '{"error":"setup_required","stage":"configuration"}',
        { status: 503 },
      );
    }
    const cors = {
      "Access-Control-Allow-Origin": origin,
      "Access-Control-Allow-Headers": "authorization,content-type,apikey",
      "Access-Control-Allow-Methods": "POST,OPTIONS",
      Vary: "Origin",
      "Cache-Control": "no-store",
    };
    const json = (data, status = 200) =>
      new Response(JSON.stringify(data), {
        status,
        headers: { ...cors, "Content-Type": "application/json" },
      });
    const redirect = (result) =>
      new Response(null, {
        status: 303,
        headers: {
          Location: origin + "/?google_sheets=" + result,
          "Cache-Control": "no-store",
          "Referrer-Policy": "no-referrer",
        },
      });
    const callback =
      request.method === "GET" &&
      new URL(request.url).pathname.endsWith("/callback");
    try {
      if (!callback && request.headers.get("Origin") !== origin)
        throw new Failure("origin_denied", 403);
      if (request.method === "OPTIONS")
        return new Response(null, { status: 204, headers: cors });
      if (!callback && request.method !== "POST")
        throw new Failure("method_denied", 405);
      const base = env("SUPABASE_URL"),
        service = env("SUPABASE_SERVICE_ROLE_KEY"),
        clientId = env("GOOGLE_SHEETS_CLIENT_ID"),
        clientSecret = env("GOOGLE_SHEETS_CLIENT_SECRET");
      if (
        !base ||
        !service ||
        !clientId ||
        !clientSecret ||
        !env("GOOGLE_SHEETS_ENCRYPTION_KEY")
      )
        throw new Failure("setup_required", 503);
      stage = "encryption_key";
      const rawKey = unb64(env("GOOGLE_SHEETS_ENCRYPTION_KEY").trim());
      if (rawKey.length !== 32) throw new Failure("setup_required", 503);
      const key = await crypto.subtle.importKey(
        "raw",
        rawKey,
        "AES-GCM",
        false,
        ["encrypt", "decrypt"],
      );
      async function seal(text, aad) {
        const iv = crypto.getRandomValues(new Uint8Array(12));
        const encrypted = await crypto.subtle.encrypt(
          { name: "AES-GCM", iv, additionalData: encoder.encode(aad) },
          key,
          encoder.encode(text),
        );
        return b64(iv) + "." + b64(new Uint8Array(encrypted));
      }
      async function open(text, aad) {
        const [iv, data] = text.split(".");
        return new TextDecoder().decode(
          await crypto.subtle.decrypt(
            {
              name: "AES-GCM",
              iv: unb64(iv),
              additionalData: encoder.encode(aad),
            },
            key,
            unb64(data),
          ),
        );
      }
      async function send(url, options = {}) {
        return fetcher(url, {
          ...options,
          redirect: "error",
          signal: AbortSignal.timeout(15000),
        });
      }
      async function rest(path, options = {}, bearer = service) {
        const route = path.split("?")[0];
        stage =
          route === "rpc/app_gate_status"
            ? "account_permission"
            : route === "rpc/app_google_sheet_source"
              ? "source_permission"
              : route === "app_google_sheet_connections"
                ? "connection_table"
                : route === "app_google_sheet_oauth_states"
                  ? "oauth_state_table"
                  : "database";
        backendCode = null;
        const response = await send(base + "/rest/v1/" + path, {
          ...options,
          headers: {
            apikey: service,
            Authorization: "Bearer " + bearer,
            "Content-Type": "application/json",
            ...options.headers,
          },
        });
        if (!response.ok) {
          const failure = await response.json().catch(() => ({}));
          if (
            typeof failure.code === "string" &&
            /^(?:[A-Z0-9]{5}|PGRST\d{3})$/.test(failure.code)
          )
            backendCode = failure.code;
          else backendCode = "HTTP_" + response.status;
          throw new Failure(
            response.status === 401 || response.status === 403
              ? "permission_denied"
              : "setup_required",
            response.status === 401 || response.status === 403 ? 403 : 503,
          );
        }
        // PostgREST return=minimal can return 201/200 with an empty body.
        const text = await response.text();
        return text.trim() ? JSON.parse(text) : null;
      }
      const callbackUrl = base + "/functions/v1/google-sheets/callback";
      async function token(params) {
        stage = "google_token";
        backendCode = null;
        const response = await send("https://oauth2.googleapis.com/token", {
          method: "POST",
          headers: { "Content-Type": "application/x-www-form-urlencoded" },
          body: new URLSearchParams({
            client_id: clientId,
            client_secret: clientSecret,
            ...params,
          }),
        });
        if (!response.ok) {
          const error = await response.json().catch(() => ({}));
          throw new Failure(
            error.error === "invalid_grant"
              ? "reconnect_required"
              : "google_unavailable",
            error.error === "invalid_grant" ? 409 : 502,
          );
        }
        const result = await response.json();
        if (typeof result.access_token !== "string")
          throw new Failure("google_unavailable", 502);
        return result;
      }
      if (callback) {
        const params = new URL(request.url).searchParams,
          state = params.get("state");
        if (!state || !/^[A-Za-z0-9_-]{43}$/.test(state))
          throw new Failure("invalid_state");
        // DELETE ... RETURNING claims a state atomically, before code exchange.
        const claimed = await rest(
          "app_google_sheet_oauth_states?state_hash=eq." +
            (await hash(state)) +
            "&expires_at=gt." +
            encodeURIComponent(new Date(now()).toISOString()),
          { method: "DELETE", headers: { Prefer: "return=representation" } },
        );
        if (claimed.length !== 1) throw new Failure("invalid_state");
        const flow = claimed[0];
        if (params.get("error")) return redirect("cancelled");
        const code = params.get("code");
        if (!code || code.length > 4096) throw new Failure("invalid_code");
        const tokens = await token({
          grant_type: "authorization_code",
          code,
          redirect_uri: callbackUrl,
          code_verifier: await open(
            flow.encrypted_verifier,
            flow.user_id + ":pkce",
          ),
        });
        if (!tokens.scope?.split(" ").includes(SCOPE) || !tokens.refresh_token)
          throw new Failure("scope_required");
        stage = "google_identity";
        const infoResponse = await send(
          "https://openidconnect.googleapis.com/v1/userinfo",
          { headers: { Authorization: "Bearer " + tokens.access_token } },
        );
        if (!infoResponse.ok) throw new Failure("google_unavailable", 502);
        const info = await infoResponse.json();
        if (info.sub !== flow.google_sub || info.email_verified !== true)
          throw new Failure("account_mismatch", 403);
        await rest("app_google_sheet_connections?on_conflict=user_id", {
          method: "POST",
          headers: { Prefer: "resolution=merge-duplicates,return=minimal" },
          body: JSON.stringify({
            user_id: flow.user_id,
            google_sub: flow.google_sub,
            encrypted_refresh: await seal(
              tokens.refresh_token,
              flow.user_id + ":refresh",
            ),
            updated_at: new Date(now()).toISOString(),
          }),
        });
        return redirect("connected");
      }
      const authorization = request.headers.get("Authorization");
      if (!authorization?.startsWith("Bearer "))
        throw new Failure("login_required", 401);
      const jwt = authorization.slice(7);
      stage = "authentication";
      const userResponse = await send(base + "/auth/v1/user", {
        headers: { apikey: service, Authorization: authorization },
      });
      if (!userResponse.ok) throw new Failure("login_required", 401);
      const user = await userResponse.json();
      if (!uuid.test(user.id)) throw new Failure("login_required", 401);
      const allowed = await rest(
        "rpc/app_gate_status",
        { method: "POST", body: "{}" },
        jwt,
      );
      if (allowed !== true) throw new Failure("permission_denied", 403);
      const googleSub = user.identities?.find((i) => i.provider === "google")
        ?.identity_data?.sub;
      if (typeof googleSub !== "string" || !googleSub)
        throw new Failure("google_account_required", 403);
      stage = "request";
      const text = await request.text();
      if (text.length > 4096) throw new Failure("invalid_request");
      let body;
      try {
        body = JSON.parse(text);
      } catch {
        throw new Failure("invalid_request");
      }
      if (!body || typeof body !== "object" || Array.isArray(body))
        throw new Failure("invalid_request");
      const filter = "user_id=eq." + user.id;
      if (body.action === "disconnect") {
        await rest("app_google_sheet_oauth_states?" + filter, {
          method: "DELETE",
        });
        await rest("app_google_sheet_connections?" + filter, {
          method: "DELETE",
        });
        return json({ connected: false });
      }
      if (body.action === "start") {
        const state = url64(crypto.getRandomValues(new Uint8Array(32))),
          verifier = url64(crypto.getRandomValues(new Uint8Array(32)));
        await rest("app_google_sheet_oauth_states?" + filter, {
          method: "DELETE",
        });
        await rest("app_google_sheet_oauth_states", {
          method: "POST",
          headers: { Prefer: "return=minimal" },
          body: JSON.stringify({
            state_hash: await hash(state),
            user_id: user.id,
            google_sub: googleSub,
            encrypted_verifier: await seal(verifier, user.id + ":pkce"),
            expires_at: new Date(now() + 600000).toISOString(),
          }),
        });
        const auth = new URL("https://accounts.google.com/o/oauth2/v2/auth");
        auth.search = new URLSearchParams({
          client_id: clientId,
          redirect_uri: callbackUrl,
          response_type: "code",
          scope: "openid email " + SCOPE,
          access_type: "offline",
          prompt: "consent",
          state,
          code_challenge: url64(await digest(verifier)),
          code_challenge_method: "S256",
          login_hint: user.email || "",
        }).toString();
        return json({ url: auth.href });
      }
      const connections = await rest(
        "app_google_sheet_connections?" +
          filter +
          "&select=google_sub,encrypted_refresh",
      );
      const connection = connections[0];
      if (body.action === "status")
        return json({ connected: connection?.google_sub === googleSub });
      if (body.action !== "read") throw new Failure("invalid_action");
      if (!connection || connection.google_sub !== googleSub)
        throw new Failure("reconnect_required", 409);
      if (
        !["responses", "absences", "master"].includes(body.kind) ||
        (body.kind !== "master" && !uuid.test(body.concertId))
      )
        throw new Failure("invalid_request");
      const source = await rest(
        "rpc/app_google_sheet_source",
        {
          method: "POST",
          body: JSON.stringify({
            p_kind: body.kind,
            p_concert: body.kind !== "master" ? body.concertId : null,
          }),
        },
        jwt,
      );
      const link = new URL(source),
        match = link.pathname.match(
          /^\/spreadsheets\/d\/([A-Za-z0-9_-]+)\/edit$/,
        );
      if (
        link.protocol !== "https:" ||
        link.hostname !== "docs.google.com" ||
        link.username ||
        link.password ||
        link.port ||
        !match
      )
        throw new Failure("invalid_source");
      const gid = new URLSearchParams(link.hash.slice(1)).get("gid");
      if (gid !== null && !/^\d+$/.test(gid))
        throw new Failure("invalid_source");
      const refresh = await open(
        connection.encrypted_refresh,
        user.id + ":refresh",
      );
      let tokens;
      try {
        tokens = await token({
          grant_type: "refresh_token",
          refresh_token: refresh,
        });
      } catch (error) {
        if (error.message === "reconnect_required")
          await rest("app_google_sheet_connections?" + filter, {
            method: "DELETE",
          });
        throw error;
      }
      if (tokens.refresh_token)
        await rest("app_google_sheet_connections?" + filter, {
          method: "PATCH",
          headers: { Prefer: "return=minimal" },
          body: JSON.stringify({
            encrypted_refresh: await seal(
              tokens.refresh_token,
              user.id + ":refresh",
            ),
            updated_at: new Date(now()).toISOString(),
          }),
        });
      async function sheet(path) {
        stage = "sheet_read";
        backendCode = null;
        const response = await send(
          "https://sheets.googleapis.com/v4/spreadsheets/" + match[1] + path,
          { headers: { Authorization: "Bearer " + tokens.access_token } },
        );
        if (!response.ok)
          throw new Failure(
            response.status === 401
              ? "reconnect_required"
              : response.status === 403 || response.status === 404
                ? "sheet_access_denied"
                : "google_unavailable",
            response.status === 401 ? 409 : 502,
          );
        const result = await response.text();
        if (encoder.encode(result).length > 5000000)
          throw new Failure("sheet_too_large", 413);
        return JSON.parse(result);
      }
      const metadata = await sheet("?fields=sheets(properties(sheetId,title))");
      const tab =
        gid === null
          ? metadata.sheets?.[0]
          : metadata.sheets?.find((s) => String(s.properties.sheetId) === gid);
      if (!tab) throw new Failure("tab_missing");
      const range =
        "'" +
        tab.properties.title.replaceAll("'", "''") +
        "'!" +
        (body.kind === "master" ? "A:F" : "A:AZ");
      const result = await sheet(
        "/values/" +
          encodeURIComponent(range) +
          "?valueRenderOption=FORMATTED_VALUE",
      );
      const values = result.values || [];
      if (!Array.isArray(values) || values.length > 10001)
        throw new Failure("sheet_too_large", 413);
      return json({ values });
    } catch (error) {
      if (callback)
        return redirect(
          error instanceof Failure && error.message === "account_mismatch"
            ? "account_mismatch"
            : "failed",
        );
      return json(
        {
          error: error instanceof Failure ? error.message : "setup_required",
          stage,
          ...(backendCode ? { backend_code: backendCode } : {}),
        },
        error instanceof Failure ? error.status : 503,
      );
    }
  };
}

Deno.serve(createHandler({ env: (name: string) => Deno.env.get(name) }));

運営ツールへ戻る