Webで稼働するシステムを製作中(2)

// ✅ メニュー表示 ===========================================================================
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('📋 データ転記メニュー')
    .addItem('🟢 CSVを取り込む', 'CSV_Import')
    .addItem('🟢 転記する', 'Transcription')
    .addItem("⬇ すべてのA列の最終行へ", "scrollToLastRowInColumnA_AllSheets_All")
    .addItem("📩 CSVメールを転送する", "forwardCSVEmailsOnce")
    .addSeparator()
    .addItem('🟢 列幅取得', 'getColumnWidths')
    .addItem('🟢 列幅設定', 'setColumnWidths')
    .addItem('🟢 文字置換', 'CharacterSubstitution')
    .addItem("👇 アクティブシートのA列の最終行へ", "scrollToLastRowInColumnA")
    .addSeparator()
    .addItem("❌ 特定スレッド非表示", "labelTargetThreads")
                                    // .addItem('🟢 サイドバーに曜日を表示", "showWeekdaySidebar')
    .addToUi();
}

// ✅ CSVのImport ===========================================================================
function CSV_Import() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const logSheet = ss.getSheetByName("取込log");
  const query = 'subject:"[OMRON]測定結果" has:attachment -label:"古いOMRONデータ"';
  const threads = GmailApp.search(query, 0, 50);

  if (!logSheet) {
    SpreadsheetApp.getUi().alert("❌ logシートが見つかりません");
    return;
  }

  if (logSheet.getLastRow() > 1) {
    logSheet.getRange(2, 1, logSheet.getLastRow() - 1, logSheet.getMaxColumns()).clearContent();
  }

  const headers = [["取込完了ログ", "tmp-body 午前 削除", "tmp-body 午後 削除", "tmp-blood 午前 削除", "tmp-blood 午後 削除", "tmp-activity 午前 削除", "tmp-activity 午後 削除"]];
  logSheet.getRange(1, 1, 1, headers[0].length).setValues(headers).setBackgroundRGB(0, 112, 192)
    .setFontColor("white").setFontWeight("bold");
  logSheet.setFrozenRows(1);
  logSheet.setColumnWidths(1, headers[0].length, 200);

  const results = {
    body: { am: [], pm: [], log: "", sheet: "tmp-body" },
    blood: { am: [], pm: [], log: "", sheet: "tmp-blood" },
    activity: { am: [], pm: [], log: "", sheet: "tmp-activity" }
  };

  threads.forEach(thread => {
    thread.getMessages().forEach(message => {
      const subject = message.getSubject();
      const sheetName = subject.includes("body") ? "tmp-body" :
                        subject.includes("blood") ? "tmp-blood" :
                        subject.includes("activity") ? "tmp-activity" : null;
      if (!sheetName) return;

      const sheet = ss.getSheetByName(sheetName);
      const attachments = message.getAttachments();
      if (!sheet || attachments.length === 0) return;

      let csv = attachments[0].getDataAsString("UTF-8");
      if (csv.includes("�") || csv.includes("謾") || csv.includes("諸")) {
        csv = attachments[0].getDataAsString("Shift_JIS");
      }

      const csvData = Utilities.parseCsv(csv);
      if (csvData.length <= 1) return;

      if (sheet.getLastRow() > 1) {
        sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getMaxColumns()).clearContent();
      }

      sheet.getRange(2, 1, csvData.length - 1, csvData[0].length).setValues(csvData.slice(1));
    });
  });

  for (const type in results) {
    const sheet = ss.getSheetByName(results[type].sheet);
    if (!sheet) continue;

    const data = sheet.getDataRange().getValues();
    const headers = data.shift();
    const grouped = {};

    data.forEach(row => {
      const date = new Date(row[0]);
      const key = Utilities.formatDate(date, "Asia/Tokyo", "yyyy-MM-dd") + (date.getHours() < 12 ? "_am" : "_pm");
      if (!grouped[key]) grouped[key] = [];
      grouped[key].push(row);
    });

    const filtered = [headers];
    for (const key in grouped) {
      const group = grouped[key];
      if (group.length > 1) {
        group.sort((a, b) => new Date(a[0]) - new Date(b[0]));
        const removed = group.slice(0, -1);
        filtered.push(group[group.length - 1]);
        removed.forEach(r => {
          const log = [${Utilities.formatDate(new Date(r[0]), "Asia/Tokyo", "yyyy/MM/dd(E) H:mm:ss")} 削除];
          results[type][key.endsWith("_am") ? "am" : "pm"].push(log);
        });
      } else {
        filtered.push(group[0]);
      }
    }

    sheet.clearContents();
    sheet.getRange(1, 1, filtered.length, filtered[0].length).setValues(filtered);
    results[type].log = ${results[type].sheet} 取込完了 ${filtered.length - 1}件;
  }

  // ログシート出力
  logSheet.insertRows(2);
  logSheet.getRange("A2").setValue(results.body.log);
  logSheet.getRange("A3").setValue(results.blood.log);
  logSheet.getRange("A4").setValue(results.activity.log);

  logSheet.getRange(2, 2, results.body.am.length || 1, 1).setValues(results.body.am.length ? results.body.am : [["該当なし"]]);
  logSheet.getRange(2, 3, results.body.pm.length || 1, 1).setValues(results.body.pm.length ? results.body.pm : [["該当なし"]]);
  logSheet.getRange(2, 4, results.blood.am.length || 1, 1).setValues(results.blood.am.length ? results.blood.am : [["該当なし"]]);
  logSheet.getRange(2, 5, results.blood.pm.length || 1, 1).setValues(results.blood.pm.length ? results.blood.pm : [["該当なし"]]);
  logSheet.getRange(2, 6, results.activity.am.length || 1, 1).setValues(results.activity.am.length ? results.activity.am : [["該当なし"]]);
  logSheet.getRange(2, 7, results.activity.pm.length || 1, 1).setValues(results.activity.pm.length ? results.activity.pm : [["該当なし"]]);
}

// ✅ TMPからlogシートへ転記 ===========================================================================
// ✅ tmp-body シートの E1〜R列のデータを log シートの I1〜V列へ転記 ================================
function Transcription() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("tmp-body");
  const targetSheet = ss.getSheetByName("log");

  if (!sourceSheet || !targetSheet) {
    SpreadsheetApp.getUi().alert("❌ tmp-body または log シートが見つかりません。");
    return;
  }

  const lastRow = sourceSheet.getLastRow();
  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert("❗ 転記するデータが存在しません。");
    return;
  }

  const numRows = lastRow - 1;

  // ▼ A列・C列・D列のデータ取得
  const colA = sourceSheet.getRange(2, 1, numRows, 1).getValues(); // A列 = 測定日
  const colC = sourceSheet.getRange(2, 3, numRows, 1).getValues(); // C列 = 体重
  const colD = sourceSheet.getRange(2, 4, numRows, 1).getValues(); // D列 = 体脂肪%

  // ▼ logシートの A, B, D列 に転記
  for (let i = 0; i < numRows; i++) {
    targetSheet.getRange(i + 2, 1).setValue(colA[i][0]); // A列
    targetSheet.getRange(i + 2, 2).setValue(colC[i][0]); // B列
    targetSheet.getRange(i + 2, 4).setValue(colD[i][0]); // D列
  }

  // ▼ E1〜R1 の表題、E2〜R(最終行) のデータ取得
  const headerValues = sourceSheet.getRange(1, 5, 1, 14).getValues();      // E1:R1
  const bodyValues = sourceSheet.getRange(2, 5, numRows, 14).getValues();  // E2:R

  const allValues = [...headerValues, ...bodyValues];

  // ▼ logシートの I〜V列に転記
  targetSheet.getRange(1, 9, allValues.length, 14).setValues(allValues);

  // ▼ A列〜V列（1行目）の装飾
  const headerRange = targetSheet.getRange(1, 1, 1, 22);
  headerRange
    .setBackgroundRGB(66, 133, 244)
    .setFontColor("white")
    .setFontWeight("bold")
    .setHorizontalAlignment("center")
    .setVerticalAlignment("middle");

  // ▼ A列〜V列（2行目以降）の中央揃え
  const dataRange = targetSheet.getRange(2, 1, numRows, 22);
  dataRange
    .setHorizontalAlignment("center")
    .setVerticalAlignment("middle");

  // ▼ 1行目固定
  targetSheet.setFrozenRows(1);

  // ▼ A1〜V(最終行) に罫線を設定
  const borderRange = targetSheet.getRange(1, 1, allValues.length, 22);
  borderRange.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
}

// ✅ セル選択時に曜日をポップアップ表示する処理 =================================================================
function onSelectionChange(e) {
  const sheet = e.range.getSheet();       // 選択したシート
  const cell = e.range;                   // 選択範囲

  // 対象シート名の確認（シート名が異なる場合は修正）
  if (sheet.getName() !== "log") return;

  // A列以外を無視
  if (cell.getColumn() !== 1) return;

  const value = cell.getValue();          // セルの値を取得

  // 値が日付でなければ終了
  if (!(value instanceof Date)) return;

  // 曜日を取得
  const days = ['日曜日', '月曜日', '火曜日', '水曜日', '木曜日', '金曜日', '土曜日'];
  const weekday = days[value.getDay()];

  // カレンダーのみ表示
  SpreadsheetApp.getUi().showSidebar(html);

  // メッセージボックスで表示
  // SpreadsheetApp.getUi().alert(📅 選択された日付「${value.toLocaleString()}」は「${weekday}」です);
}

// ========================================================================
// // ✅ サイドバーに曜日を表示する処理
// function showWeekdaySidebar() {
//   const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//   const cell = sheet.getActiveCell();
//   const value = cell.getValue();

//   // セルの値が日付でない場合は中止
//   if (!(value instanceof Date)) {
//     SpreadsheetApp.getUi().alert("❌ 選択したセルには日付が含まれていません。");
//     return;
//   }

//   // 曜日を取得
//   const weekdays = ["日曜日", "月曜日", "火曜日", "水曜日", "木曜日", "金曜日", "土曜日"];
//   const weekday = weekdays[value.getDay()];

//   // HTMLを生成（曜日のみ表示）
//   const html = HtmlService.createHtmlOutput(
//     <div style="font-family:sans-serif;padding:20px;">
//       <h2 style="color:#4285f4;">📅 曜日</h2>
//       <p style="font-size:24px;"><strong>${weekday}</strong></p>
//     </div>
//   ).setTitle("曜日表示");

//   // サイドバーに表示
//   SpreadsheetApp.getUi().showSidebar(html);
// }

// ========================================================================
// ✅ アクティブシートのA列の最終行に正確にスクロールする処理
function scrollToLastRowInColumnA() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const columnA = sheet.getRange("A:A").getValues(); // A列すべて取得
  let lastRow = 0;

  // 下から上に向かって最初に値がある行を見つける
  for (let i = columnA.length - 1; i >= 0; i--) {
    if (columnA[i][0] !== "") {
      lastRow = i + 1; // 配列は0始まりなので+1
      break;
    }
  }

  if (lastRow > 1) {
    sheet.setActiveRange(sheet.getRange("A" + lastRow));
  } else {
    SpreadsheetApp.getUi().alert("A列にデータが見つかりません。");
  }
}

// ✅ すべてのシートのA列の最終行に正確にスクロールする処理
function scrollToLastRowInColumnA_AllSheets_All() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  let noDataSheets = []; // データが見つからないシート名を格納

  for (const sheet of sheets) {
    const columnA = sheet.getRange("A:A").getValues();
    let lastRow = 0;

    // A列を下から上へ検索して最終行を特定
    for (let i = columnA.length - 1; i >= 0; i--) {
      if (columnA[i][0] !== "") {
        lastRow = i + 1;
        break;
      }
    }

    if (lastRow > 1) {
      SpreadsheetApp.setActiveSheet(sheet); // シートをアクティブに
      sheet.setActiveRange(sheet.getRange("A" + lastRow)); // 最終行を選択
    } else {
      noDataSheets.push(sheet.getName());
    }
  }

  // すべてのシートの処理が終了した後、必要があればアラート表示
  if (noDataSheets.length === sheets.length) {
    SpreadsheetApp.getUi().alert("すべてのシートのA列にデータが見つかりません。");
  } else if (noDataSheets.length > 0) {
    SpreadsheetApp.getUi().alert("以下のシートではA列にデータが見つかりませんでした：\n" + noDataSheets.join(", "));
  }
}

// ========================================================================
// ✅ 改良済みコード（3ファイルを1通にまとめて送信）
function forwardCSVEmailsOnce() {
  const searchQuery = 'subject:"[OMRON]測定結果" newer_than:7d has:attachment';
  const recipient = "YOUR_EMAIL@example.com"; // ← 公開用にダミーアドレスへ変更

  // 一意の件名を作成（日時付き）
  const now = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMdd_HHmm");
  const newSubject = "一括CSV転送_" + now;

  const threads = GmailApp.search(searchQuery);

  if (threads.length === 0) {
    Logger.log("🔍 該当するスレッドが見つかりません。");
    return;
  }

  let allAttachments = [];
  let foundCount = 0;

  for (const thread of threads) {
    const messages = thread.getMessages();

    for (const msg of messages) {
      try {
        const attachments = msg.getAttachments();
        if (attachments.length > 0) {
          for (const file of attachments) {
            // ファイル名が指定ワードを含む場合のみ追加
            const name = file.getName();
            if (
              name.includes("BodyComposition") ||
              name.includes("BloodPressure") ||
              name.includes("Activity")
            ) {
              allAttachments.push(file);
              Logger.log(📎 追加: ${name});
              foundCount++;
            }
          }
        }
      } catch (e) {
        Logger.log(❌ 添付ファイル取得エラー: ${e.message});
      }
    }
  }

  if (allAttachments.length === 0) {
    Logger.log("⚠️ 添付ファイルが見つかりませんでした。");
    return;
  }

  // 転送メールの送信
  GmailApp.sendEmail(recipient, newSubject, "3種類のCSVファイルが一括送付されました。", {
    attachments: allAttachments
  });

  Logger.log(✅ 転送完了：${foundCount} 件の添付ファイルを送信しました。);
}

// ✅ 特定スレッド非表示 ===========================================================================
function labelTargetThreads() {
  const labelName = "非表示対象"; // 付けたいラベル名
  const label = GmailApp.createLabel(labelName); // 存在しなければ作成

  // 件名に一致するスレッドを検索
  const query = 'subject:"[OMRON]測定結果" OR subject:"主要３CSVダウンロード" OR subject:"があなたと共有されました"'
  const threads = GmailApp.search(query, 0, 50); // 最大50件

  if (threads.length === 0) {
    Logger.log("❌ 該当するスレッドが見つかりませんでした。");
    return;
  }

  // ラベルを付与
  threads.forEach(thread => {
    thread.addLabel(label);
  });

  Logger.log(✅ ${threads.length} 件のスレッドに「${labelName}」ラベルを付けました。);
}

// ✅ 文字の置換 ===========================================================================
function CharacterSubstitution() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("log");
  if (!sheet) {
    Logger.log("❌ シート「log」が見つかりません。");
    return;
  }

  // A1セルのチェックと変換（"測定日" → 日付）
  let a1Value = sheet.getRange("A1").getValue().toString().trim();
  a1Value = a1Value.replace(/^"|"$/g, ""); // 両端の " を除去
  Logger.log("🔍 A1セルの実際の値: [" + a1Value + "]");
  if (a1Value === "測定日") {
    sheet.getRange("A1").setValue("日付");
    Logger.log("✅ A1セルを「日付」に変換しました。");
  } else {
    Logger.log("⚠️ A1セルは『測定日』と一致しませんでした。");
  }

  // B1セルのチェックと変換（体重(kg) → 体重）
  let b1Value = sheet.getRange("B1").getValue().toString().trim();
  b1Value = b1Value.replace(/^"|"$/g, ""); // 万一"が含まれている場合に備える
  Logger.log("🔍 B1セルの実際の値: [" + b1Value + "]");
  if (b1Value === "体重(kg)") {
    sheet.getRange("B1").setValue("体重");
    Logger.log("✅ B1セルを「体重」に変換しました。");
  } else {
    Logger.log("⚠️ B1セルは『体重(kg)』と一致しませんでした。");
  }
}

// ✅ 列幅を取得 ===========================================================================
function getColumnWidths() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // アクティブなシートを取得
  const lastColumn = sheet.getLastColumn(); // データがある最後の列番号を取得

  let widths = [];
  for (let col = 1; col <= lastColumn; col++) {
    const width = sheet.getColumnWidth(col);
    widths.push(列${col}: 幅=${width}px);
  }

  Logger.log("列幅一覧:\n" + widths.join("\n"));
}

// ✅ 指定された列幅を設定する (シート名：log)===============================
function setColumnWidths() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 指定された列幅の配列（1列目から順）
  const widths = [
    133,70,180,70,
    70,100,100,100,
    85,100,95,68,
    85,134,134,134,94,
    148,148,148,33,69
  ];

  // 配列の内容に従って列幅を設定
  for (let col = 0; col < widths.length; col++) {
    sheet.setColumnWidth(col + 1, widths[col]); // 列番号は1から始まる
  }
}

// ✅✅✅ 図形とテキストボックスで作成する「擬似フローチャート」(シート名：フロー)===========================
function drawCompleteFlowchart() {
  formatFlowSheet(); // セル幅や行高など整える
  drawFlowchartInSheet(); // フローチャート描画
}

// ✅ シートのフォーマット（列幅・行高・セル結合など）
function formatFlowSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName("フロー");

  if (!sheet) {
    sheet = ss.insertSheet("フロー");
  } else {
    sheet.clear();
  }

  // 必要な範囲を仮に初期化して列と行を確保
  sheet.getRange("A1:J15").setValue("");

  // 列幅設定
  const setWidths = {
    "A": 30, "B": 30, "C": 30, "D": 30, "E": 30, "F": 30,
    "G": 180,
    "H": 30, "I": 30, "J": 30
  };

  for (const col in setWidths) {
    const colNum = sheet.getRange(col + "1").getColumn();
    sheet.setColumnWidth(colNum, setWidths[col]);
  }

  // 行の高さ設定
  const rowHeights = {
    1: 20,
    3: 20,
    5: 20,
    7: 20,
    8: 40,
    9: 20,
    10: 20,
    11: 20,
    12: 40,
    13: 20,
    14: 20
  };

  for (const row in rowHeights) {
    sheet.setRowHeight(Number(row), rowHeights[row]);
  }

  // セル結合
  sheet.getRange("G10:G11").merge();
  sheet.getRange("B12:E12").merge();
  sheet.getRange("G13:G14").merge();

  // 罫線の設定
  // 下罫線：D10:F10, D13:F13, H10, H13
  sheet.getRange("D10:F10").setBorder(false, false, true, false, false, false);
  sheet.getRange("D13:F13").setBorder(false, false, true, false, false, false);
  sheet.getRange("H10").setBorder(false, false, true, false, false, false);
  sheet.getRange("H13").setBorder(false, false, true, false, false, false);

  // 左罫線：D11, D13, H11:H13
  sheet.getRange("D11").setBorder(false, false, false, true, false, false);
  sheet.getRange("D13").setBorder(false, false, false, true, false, false);
  sheet.getRange("H11:H13").setBorder(false, false, false, true, false, false);

  // 強制的に罫線を引く
 //const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("フロー");

    // H10 に下罫線
    const h10 = sheet.getRange("H10");
    h10.setValue(" ") // 半角スペースを入れる（必要に応じて削除可）
       .setBorder(false, false, true, false, false, false, "#000000", SpreadsheetApp.BorderStyle.SOLID);

    // H14 に上罫線
    const h14 = sheet.getRange("H14");
    h14.setValue(" ") // 半角スペースを入れる
       .setBorder(true, false, false, false, false, false, "#000000", SpreadsheetApp.BorderStyle.SOLID);
}

// ✅ 擬似フローチャートを描画
function drawFlowchartInSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("フロー");

  const texts = [
    { cell: "G2", value: "CSV_Import()" },
    { cell: "G4", value: "メールメッセージを取得" },
    { cell: "G6", value: "添付ファイルを取得" },
    { cell: "G8", value: "CSVデータを転記" },
    { cell: "G10", value: "重複データの判定" },
    { cell: "B12", value: "重複行の削除" },
    { cell: "G13", value: "終了" }
  ];

  texts.forEach(t => {
    const range = sheet.getRange(t.cell);
    range.setValue(t.value);
    range.setHorizontalAlignment("center").setVerticalAlignment("middle");
    range.setBorder(true, true, true, true, true, true);
    range.setFontWeight("bold");
    range.setFontSize(12);
    sheet.setRowHeight(range.getRow(), 40);

    // 列が B列（列番号2）の場合だけ30px、それ以外は180px
    const colNum = range.getColumn();
    sheet.setColumnWidth(colNum, colNum === 2 ? 30 : 180);
  });

  // 矢印を文字で表現
  sheet.getRange("G3").setValue("↓").setHorizontalAlignment("center");
  sheet.getRange("G5").setValue("↓").setHorizontalAlignment("center");
  sheet.getRange("G7").setValue("↓").setHorizontalAlignment("center");
  sheet.getRange("G9").setValue("↓").setHorizontalAlignment("center");
  sheet.getRange("G11").setValue("↓").setHorizontalAlignment("center");
  sheet.getRange("C11").setValue("はい").setHorizontalAlignment("center").setVerticalAlignment("middle");
  sheet.getRange("I12").setValue("い\nい\nえ").setWrap(true);
  sheet.getRange("G13").setValue("終了").setHorizontalAlignment("center");
}