スマホで共有！慶弔金管理を自作スプレッドシートで簡単に【テンプレ付】

function updatePeopleList() {
  // スプレッドシートのB列「対象者名」を取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('対象者リスト'); // 対象シート（必要に応じて変更）
  const names = sheet.getRange(2, 2, sheet.getLastRow()-1).getValues().flat(); // B2:B最終行

  // 連携されているGoogleフォームのURLを自動取得
  const form = FormApp.openByUrl(ss.getFormUrl());

  // Googleフォーム内の対象質問（名前を選ぶ質問、例えば「対象者名」）を特定
  const items = form.getItems(FormApp.ItemType.LIST); // プルダウン型
  items.forEach(function(item){
    if(item.getTitle().indexOf('対象者名') !== -1){
      const listItem = item.asListItem();
      listItem.setChoiceValues(names);
    }
  });
}

function addNewPeopleFromFormResponses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formSheet = ss.getSheetByName("フォームの回答");
  const listSheet = ss.getSheetByName("対象者リスト");

  // 対象者リスト既存名取得（スペース除去して比較用配列）
  const existingNamesRaw = listSheet.getRange(2, 2, listSheet.getLastRow() - 1, 1).getValues().flat();
  const existingNames = existingNamesRaw.map(name => normalizeName(name));

  // フォームの回答データ取得
  const lastRowForm = formSheet.getLastRow();
  if (lastRowForm < 2) return; // データなし

  const formData = formSheet.getRange(2, 3, lastRowForm - 1, 6).getValues();
  // formData[行][0]=C列(対象者名), [5]=H列(対象者がリストにない人)

  let listAppendData = []; // 対象者リストに追加する行（[名前, カナ(空) ]の配列）
  let updateFormRows = []; // フォーム側のC列更新指示 [行番号, 名前]

  for (let i = 0; i < formData.length; i++) {
    const currentName = formData[i][0]; // C列
    const newNameRaw = formData[i][5];   // H列

    if (!currentName && newNameRaw) {
      const normalized = normalizeName(newNameRaw);
      // 既存と突合（スペース除去比べ）
      if (!existingNames.includes(normalized)) {
        // [B列: 名前, C列: カナ（空）]
        listAppendData.push([newNameRaw, ""]);
        existingNames.push(normalized); // 追加候補も即時重複防止
      }
      updateFormRows.push([i + 2, newNameRaw]);
    }
  }

  // 名簿シート追記（A列には追加しない＝No.はシート関数任せ）
  if (listAppendData.length > 0) {
    listSheet.getRange(listSheet.getLastRow() + 1, 2, listAppendData.length, 2).setValues(listAppendData);
  }

  // フォームC列を更新
  updateFormRows.forEach(r => {
    formSheet.getRange(r[0], 3).setValue(r[1]);
  });

  SpreadsheetApp.flush();
}

// 名前の全角・半角スペースをすべて削除
function normalizeName(name) {
  if (!name) return "";
  return name.toString().replace(/\s/g, "").replace(/　/g, "");
}

function sendGiftReminder() {
  // 各種シートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formSheet = ss.getSheetByName("フォームの回答");
  const summarySheet = ss.getSheetByName("集計表");
  
  // メールアドレス取得（集計表F4）
  const mailAddress = summarySheet.getRange("F2").getValue();
  if (!mailAddress) {
    Logger.log("集計表シートのF2セルにメールアドレスが入力されていません。");
    return;
  }
  
  // データ最終行判定（B列で）
  const lastRow = formSheet.getLastRow();
  if (lastRow < 2) {
    Logger.log("フォームの回答シートにデータがありません。");
    return;
  }

  const rangeValues = formSheet.getRange(2, 2, lastRow - 1, 7).getValues(); // B2:Hxxx
  
  const today = new Date();
  let notifyRows = [];
  
  rangeValues.forEach(function(row, idx) {
    const dateVal = row[0]; // B列(いつもらった/わたしましたか?)
    const amount = Number(row[3]); // E列(もらった/わたした金額)
    const getOrGive = row[4]; // F列(もらったモノ/わたしたモノ)
    const status = row[6]; // H列(ステータス)
    
    // 日付が有効な値かつ日付オブジェクト
    if (!dateVal || !(dateVal instanceof Date)) return;
    // 金額が1以上か
    if (!(amount >= 1)) return;
    // F列が「もらったモノ」か
    if (getOrGive != "もらったモノ") return;
    // H列が未了、または空欄か
    if (status && status !== "未了") return;
    // 30日経過判定
    const daysPassed = Math.floor((today - dateVal) / (1000 * 60 * 60 * 24));
    if (daysPassed < 30) return;
    
    // 該当行データを整形して格納（例：行番号、日付、金額など…必要に応じ調整）
    notifyRows.push(
      `受付日: ${Utilities.formatDate(dateVal, Session.getScriptTimeZone(), "yyyy/MM/dd")}\n` + 
      `金額: ${amount} 円`
      // 必要に応じ他カラムも追記
    );
  });

  if (notifyRows.length > 0) {
    const subject = "お返しはできていますか？";
    let body = "下記のお祝い・お見舞い（もらった分）について、30日経過時点でお返しが未了（または未チェック）なものがあります：\n\n";
    body += notifyRows.join('\n-------------------\n');
    body += "\n\n慶弔金管理シートのご確認をお願いいたします。";
    MailApp.sendEmail(mailAddress, subject, body);
    Logger.log("該当分まとめてリマインドメール送信済み");
  } else {
    Logger.log("リマインドメール該当行なし");
  }
}

function setAllReceivedToCompleted() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formSheet = ss.getSheetByName('フォームの回答');

  const lastRow = formSheet.getLastRow();
  if (lastRow < 2) return; // データなし

  // B〜H列（タイトル行後）
  const data = formSheet.getRange(2, 2, lastRow-1, 7).getValues(); // B2:H

  let updateRows = [];
  for (let i=0; i<data.length; i++) {
    const getOrGive = data[i][4]; // F列＝そのまま（index=4、B=0〜）
    const status = data[i][6];    // H列（index=6）
    if (getOrGive === "もらったモノ" && (!status || status === "未了")) {
      updateRows.push(i+2); // 行番号(2始まり)
    }
  }
  // 一括更新
  if(updateRows.length>0){
    updateRows.forEach(rowNum => {
      formSheet.getRange(rowNum, 8).setValue("済"); // H列（=8列目）
    });
    SpreadsheetApp.flush();
  }
}