不在回答：日付別取り込みの設定

下のSQLをSupabaseのSQL Editorで実行します。既存の名簿・公演内容は変更しません。
Google Sheetsの設定ページから最新のEdge Function全文をコピーし、既存のgoogle-sheetsのCodeを置き換えてDeployします。Secretsは従来のものを使用します。
Cloudflareの最新デプロイ後、サイトの「別シートの不在回答を取り込む」で回答先タブを登録します。

フォームのグリッドは時間帯を行、2026/11/7など年を含む日付を列にします。同じ開催日の本番・リハに共通で反映し、下の参加者一覧で時間を調整できます。

-- Separate absence sheet; dated absence data is shared by same-date sessions.
begin;
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;

create or replace function public.app_validate_absence_payload() returns trigger
language plpgsql security definer set search_path=pg_catalog,public as $$
declare member jsonb; day record; span jsonb; parsed_date date;
begin
 if new.payload ? 'absenceSheetURL' and (jsonb_typeof(new.payload->'absenceSheetURL') is distinct from 'string' or length(new.payload->>'absenceSheetURL')>1000 or new.payload->>'absenceSheetURL' !~ '^https://docs\.google\.com/spreadsheets/d/[A-Za-z0-9_-]+/edit(#gid=[0-9]+)?$') then
  raise exception 'Invalid absence sheet URL' using errcode='22023';
 end if;
 for member in select value from jsonb_array_elements(new.payload->'members') loop
  if member ? 'absenceFormNote' and (jsonb_typeof(member->'absenceFormNote') is distinct from 'string' or length(member->>'absenceFormNote')>1000) then raise exception 'Invalid absence form note' using errcode='22023'; end if;
  if member ? 'absenceByDate' then
   if jsonb_typeof(member->'absenceByDate') is distinct from 'object' then raise exception 'Invalid absence dates' using errcode='22023'; end if;
   if (select count(*) from jsonb_object_keys(member->'absenceByDate'))>50 then raise exception 'Too many absence dates' using errcode='22023'; end if;
   for day in select key,value from jsonb_each(member->'absenceByDate') loop
    if day.key !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' then raise exception 'Invalid absence date' using errcode='22023'; end if;
    begin parsed_date=day.key::date; exception when others then raise exception 'Invalid absence date' using errcode='22023'; end;
    if parsed_date::text<>day.key or jsonb_typeof(day.value) is distinct from 'object' or jsonb_typeof(day.value->'ranges') is distinct from 'array' then raise exception 'Invalid absence record' using errcode='22023'; end if;
    if jsonb_array_length(day.value->'ranges')>48 then raise exception 'Too many absence ranges' using errcode='22023'; end if;
    if day.value ? 'note' and (jsonb_typeof(day.value->'note') is distinct from 'string' or length(day.value->>'note')>1000) then raise exception 'Invalid absence note' using errcode='22023'; end if;
    for span in select value from jsonb_array_elements(day.value->'ranges') loop
     if jsonb_typeof(span) is distinct from 'object' or jsonb_typeof(span->'start') is distinct from 'number' or jsonb_typeof(span->'end') is distinct from 'number' or coalesce(span->>'start','') !~ '^[0-9]{1,4}$' or coalesce(span->>'end','') !~ '^[0-9]{1,4}$' then raise exception 'Invalid absence interval' using errcode='22023'; end if;
     if (span->>'start')::integer >= (span->>'end')::integer or (span->>'end')::integer>1440 then raise exception 'Invalid absence interval' using errcode='22023'; end if;
    end loop;
   end loop;
  end if;
 end loop;
 return new;
end $$;
revoke all on function public.app_validate_absence_payload() from public,anon,authenticated;
drop trigger if exists app_absence_payload_validation on public.app_concerts;
create trigger app_absence_payload_validation before insert or update of payload on public.app_concerts for each row execute function public.app_validate_absence_payload();
commit;

運営ツールへ戻る