ryogrid · April 1, 2025 23:58 · ryogrid · Mar 30, 2025 · ryogrid · Apr 1, 2025
diff --git a/tracking-bug-report-status.gs b/tracking-bug-report-status.gs
 // --- Configuration ---
 // --- ここから設定 ---

 // 1. Your Google Cloud Project API Key for Vertex AI (Gemini API)
 //    Google Cloudプロジェクトで生成したVertex AI API（Gemini API）のAPIキー
 const API_KEY = ''; // <-- ここにAPIキーを貼り付けてください (Paste your API key here)

 // 2. Your Google Cloud Project ID
 //    Google CloudプロジェクトID
 const PROJECT_ID = ''; // <-- ここにプロジェクトIDを貼り付けてください (Paste your project ID here)

 // 3. The Gmail label to search for bug reports
 //    検索対象のGmailラベル名
 const GMAIL_LABEL = 'pgsql-bugs'; // 例 (Example)

 // 4. The name of the sheet tab in your Google Spreadsheet
 //    記録先のGoogleスプレッドシートのシート名
 const SHEET_NAME = 'statuses'; // 例 (Example)
 //    !! 注意: このシートには 'Thread ID', 'Subject', 'Report Date', 'Status', 'Last Checked', 'Last Message Date', 'Gmail Link' のヘッダーが必要です。
 //    !! Note: This sheet requires headers: 'Thread ID', 'Subject', 'Report Date', 'Status', 'Last Checked', 'Last Message Date', 'Gmail Link'
 const REPORT_DATE_COLUMN_NAME = 'Report Date'; // ソートに使用する列のヘッダー名

 // 5. Gemini API Endpoint and Model
 //    Gemini APIのエンドポイントとモデル名
 const GEMINI_API_ENDPOINT = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash-thinking-exp-1219:generateContent?key=${API_KEY}`;

 // 6. Prompt for Gemini API to determine the bug status
 //    対応状況を判定するためのGemini APIへのプロンプト
 const GEMINI_PROMPT_TEMPLATE = `
 Analyze the content of the following email thread and determine the current status of the PostgreSQL bug report.

 Points to analyze:
 - Does the email subject contain a pattern like "BUG #[number]"?
 - Does the body contain keywords indicating a fix, such as "fixed", "patch", "committed", "commit [ID]", "applied", or "merged"?
 - Does it contain keywords indicating the issue will not be addressed, such as "will not fix", "not a bug", "invalid", or "wontfix"?
 - Are developers participating in the discussion and indicating concrete actions (e.g., investigation, patch review, fix proposals)?
  - Examples of developers:
    - Peter Eisentraut
    - Andres Freund
    - Magnus Hagander
    - Jonathan Katz
    - Tom Lane
    - Bruce Momjian
    - Dave Page
    - Laurenz Albe
    - Ashutosh Bapat
    - Oleg Bartunov
    - Christoph Berg
    - Nathan Bossart
    - Joe Conway
    - Dave Cramer
    - Jeff Davis
    - Bertrand Drouvot
    - Andrew Dunstan
    - Vik Fearing
    - Stephen Frost
    - Etsuro Fujita
    - Peter Geoghegan
    - Andrew Gierth
    - Devrim Gﾃｼndﾃｼz
    - Richard Guo
    - Daniel Gustafsson
    - Robert Haas
    - Stacey Haysler
    - ﾃ〕varo Herrera
    - Kyotaro Horiguchi
    - Tatsuo Ishii
    - Petr Jelinek
    - Stefan Kaltenbrunner
    - Amit Kapila
    - Alexander Korotkov
    - Alexander Lakhin
    - Amit Langote
    - Guillaume Lelarge
    - Heikki Linnakangas
    - Anastasia Lubennikova
    - Fujii Masao
    - Noah Misch
    - Thomas Munro
    - John Naylor
    - Michael Paquier
    - Melanie Plageman
    - Paul Ramsey
    - Dean Rasheed
    - Julien Rouhaud
    - David Rowley
    - Masahiko Sawada
    - Teodor Sigaev
    - Steve Singe
    - Pavel Stehule
    - Tomas Vondra
    - Mark Wong
 - Is there a patch (mention of a .patch file or an attachment), and is there a review or discussion taking place?
 - Has there been no new email for a certain period (e.g., more than a week)? (Except immediately after the report)
 - If someone other than the reporter (mainly developers) states that the behavior is consistent with the expectation, mark the status as "Not a Bug".
 - If someone other than the reporter (mainly developers) states that the behavior is consistent with the documentation, mark the status as "Not a Bug".
 - Has someone other than the reporter (mainly developers) stated that it is a bug or that the behavior is not in line with the specification?
 - If someone other than the reporter (mainly developers) stated that it is a bug or not in line with the specification, and also stated that it will not be fixed for some reason, mark the status as "Postponing Fix".
 - Assume that the reporter considers the content to be a bug or defect when analyzing.
 - If someone other than the reporter (mainly developers) mentions other project (not postgresql), mark the status as "Inappropriate Report".
 - If only one developer other than the reporter has responded, mark the status as "Not a Bug" unless the developer has requested additional information or action to further refine the issue, or has described it as a bug or incorrect behavior.
 - 
 Only select "Under Discussion (Investigating)" as the status when you are sure it is appropriate. In most cases where you would select "Under Discussion (Investigating)", "Not a Bug" is more likely to be appropriate.

 Taking the above points into account, select only one of the most appropriate statuses from the options below.

 Status options:
 - New Report
 - Inappropriate Report
 - Under Discussion (Investigating)
 - Not a Bug
 - Bug
 - Bug (Patch Proposed/Under Review)
 - Bug (Fixed)
 - Bug (Postponing Fix)


 Email thread content:
 ---
 {EMAIL_CONTENT}
 ---

 Please respond with only the status in the following format:
 Status: [Selected Status]
 `;

 //// 7. Delay between Gemini API calls in milliseconds (to respect rate limits)
 ////    Gemini API呼び出し間の遅延時間（ミリ秒）。10 RPM = 6秒/リクエスト
 //const API_CALL_DELAY_MS = 6000; // 6 seconds

 // 8. Number of threads to initially fetch from Gmail (adjust based on typical volume)
 //    Gmailから最初に取得するスレッド数（通常の量に応じて調整）
 const GMAIL_FETCH_LIMIT = 150;

 // 9. Maximum number of Gemini API calls allowed per script execution.
 //    1回のスクリプト実行で許可されるGemini API呼び出しの最大回数。
 const MAX_API_CALLS_PER_RUN = 12;

 // 10. Re-check interval in hours for existing threads.
 //     既存スレッドのステータス再チェック間隔（時間）。
 const RECHECK_INTERVAL_HOURS = 12;

 const MONTHS_TO_KEEP = 2;

 // --- 設定ここまで ---
 // --- End of Configuration ---

 /**
 * Main function to be triggered periodically.
 * Fetches emails, analyzes status, updates the spreadsheet, sorts by Report Date, and deletes old records.
 * 定期実行されるメイン関数。メールを取得し、状況を分析し、スプレッドシートを更新し、報告日でソートし、古い記録を削除します。
 */
 function main() {
  Logger.log('Starting bug tracking process...');
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) {
    Logger.log(`Error: Sheet "${SHEET_NAME}" not found.`);
    SpreadsheetApp.getUi().alert(`Error: Sheet "${SHEET_NAME}" not found. Please create it or update the SHEET_NAME variable in the script.`);
    return;
  }

  // Check for API Key placeholder
  if (API_KEY === 'YOUR_API_KEY' || !API_KEY) {
     Logger.log(`Error: API_KEY is not set. Please add your Gemini API key to the script.`);
     SpreadsheetApp.getUi().alert(`Error: API_KEY is not set. Please add your Gemini API key to the script.`);
     return;
  }

  let existingData;
  try {
      existingData = getExistingSheetData(sheet);
      Logger.log(`Found ${Object.keys(existingData).length} existing threads in the sheet.`);
  } catch (e) {
      Logger.log(`Error reading sheet data: ${e}. Aborting.`);
      return;
  }

  // --- Fetch Threads from Gmail ---
  const searchLabel = `label:${GMAIL_LABEL}`;
  let threads;
  try {
      threads = GmailApp.search(searchLabel, 0, GMAIL_FETCH_LIMIT);
      Logger.log(`Fetched ${threads.length} threads with label "${GMAIL_LABEL}".`);
  } catch (e) {
      Logger.log(`Error searching Gmail: ${e}`);
      return;
  }

  const threadsToUpdate = []; // Store threads that need full processing (API call)
  const updatesToPerform = []; // Store direct sheet updates (no API call needed)

  // --- 日付計算 ---
  const now = new Date(); // 現在時刻を取得
  const twelveHoursAgo = new Date(now.getTime() - RECHECK_INTERVAL_HOURS * 60 * 60 * 1000);
  const fiveDaysAgo = new Date(now.getTime() - 10 * 24 * 60 * 60 * 1000);
  // --- 追加: 新規スレッド追加と古い記録削除の基準となる日付 ---
  const recordKeepThresholdDate = new Date(now);
  recordKeepThresholdDate.setMonth(recordKeepThresholdDate.getMonth() - MONTHS_TO_KEEP);
  recordKeepThresholdDate.setHours(0, 0, 0, 0); // 日付の比較基準
  Logger.log(`Record keep threshold date (new threads must be newer than this): ${recordKeepThresholdDate.toISOString()}`);
  // --- 追加ここまで ---


  // --- Identify Threads to Process/Update ---
  const processedThisRun = new Set(); // Keep track of thread IDs handled in this run
  for (const thread of threads) {
    const threadId = thread.getId();

    // Avoid processing the same thread twice if fetched multiple times
    if (processedThisRun.has(threadId)) {
        continue;
    }

    const messages = thread.getMessages();
    if (!messages || messages.length === 0) {
        Logger.log(`Skipping thread ${threadId} as it has no messages.`);
        processedThisRun.add(threadId);
        continue;
    }
    const firstMessageDate = messages[0].getDate(); // Get first message date
    const lastMessageDate = thread.getLastMessageDate();

    if (!firstMessageDate) {
        Logger.log(`Skipping thread ${threadId} as it has no valid first message date.`);
        processedThisRun.add(threadId);
        continue;
    }

    // 返信なし＆5日以上経過のチェック用変数
    const isSingleMessage = messages.length === 1;
    const isOldAndUnreplied = isSingleMessage && firstMessageDate < fiveDaysAgo;


    if (!existingData[threadId]) {
      // --- 変更: 新規スレッドの日付チェックを追加 ---
      if (firstMessageDate < recordKeepThresholdDate) {
         Logger.log(`Thread ${threadId} is new but its report date (${firstMessageDate.toISOString()}) is older than ${MONTHS_TO_KEEP} months threshold. Skipping addition.`);
         processedThisRun.add(threadId); // 処理済みとしてマーク (追加しない)
         continue; // 次のスレッドへ
      }
      // --- 変更ここまで ---

      // --- 既存の新規スレッド処理 (日付チェックを通過した場合) ---
      if (isOldAndUnreplied) {
         // 新規スレッドで「対応不要」の場合
          const rowDataForIgnore = {
              'Thread ID': threadId,
              'Subject': thread.getFirstMessageSubject(),
              'Report Date': firstMessageDate,
              'Status': 'Inappropriate Report', // 新しいステータス
              'Last Checked': new Date(),
              'Last Message Date': lastMessageDate, // 最初のメールの日付と同じ
              'Gmail Link': `https://mail.google.com/mail/u/0/#label/${GMAIL_LABEL.replace(/ /g, '+')}/${threadId}`
          };
          addNewRow(sheet, rowDataForIgnore); // API呼び出しなしで直接追加
          Logger.log(`Thread ${threadId} is new, unreplied, and older than 10 days. Added directly as 'Inappropriate Report'.`);
          processedThisRun.add(threadId);
      } else {
          // 新規スレッドで「対応不要」でない場合（通常処理）
          threadsToUpdate.push({ thread: thread, isNew: true, reportDate: firstMessageDate });
          Logger.log(`Thread ${threadId} is new and within ${MONTHS_TO_KEEP} months. Added to processing list for analysis.`);
          processedThisRun.add(threadId);
      }
      // --- 既存の新規スレッド処理ここまで ---

    } else {
      // --- 既存スレッドの処理 (変更なし) ---
      const sheetData = existingData[threadId];
      const sheetLastMessageDate = sheetData.lastMessageDate ? new Date(sheetData.lastMessageDate) : null;
      const sheetLastCheckedDate = sheetData.lastCheckedDate ? new Date(sheetData.lastCheckedDate) : null;
      const sheetReportDate = sheetData.reportDate ? new Date(sheetData.reportDate) : firstMessageDate; // Fallback
      const currentStatus = sheetData.status; // 既存ステータスを取得

      const hasNewMessages = !sheetLastMessageDate || lastMessageDate.getTime() > sheetLastMessageDate.getTime();
      const isDueForCheck = !sheetLastCheckedDate || sheetLastCheckedDate < twelveHoursAgo;

      // 既存スレッドで「対応不要」の条件チェック
      if (isOldAndUnreplied && currentStatus !== 'Inappropriate Report') {
         updatesToPerform.push({
              action: 'updateIgnoredStatus',
              row: sheetData.row,
              threadId: threadId,
              lastCheckedDate: new Date(),
              lastMessageDate: lastMessageDate
         });
         Logger.log(`Thread ${threadId} is existing, unreplied, and older than 10 days. Queued for 'Inappropriate Report' status update.`);
      } else if (!isOldAndUnreplied) { // 「対応不要」でない場合のみ通常の更新ロジックへ
        if (hasNewMessages) {
          if (isDueForCheck) {
            threadsToUpdate.push({ thread: thread, isNew: false, row: sheetData.row, reportDate: sheetReportDate });
            Logger.log(`Thread ${threadId} has new messages and is due for check. Added to processing list.`);
          } else {
            updatesToPerform.push({
                action: 'updateLastMessageDate',
                row: sheetData.row,
                threadId: threadId,
                lastMessageDate: lastMessageDate
            });
          }
        } else { // No new messages
          if (isDueForCheck) {
             updatesToPerform.push({
                action: 'updateLastChecked',
                row: sheetData.row,
                threadId: threadId,
                lastCheckedDate: new Date()
             });
          }
        }
      } else if (isOldAndUnreplied && currentStatus === 'Inappropriate Report') {
          // 既にIgnoredなら何もしない
          // Logger.log(`Thread ${threadId} is already marked as 'Inappropriate Report'. Skipping.`);
      }
       processedThisRun.add(threadId); // Mark as handled for this run
      // --- 既存スレッドの処理ここまで ---
    }
  } // end of for loop over threads

  // --- Perform direct sheet updates (no API calls) ---
  Logger.log(`Performing ${updatesToPerform.length} direct sheet updates...`);
  for (const update of updatesToPerform) {
      try {
          if (update.action === 'updateLastMessageDate') {
              updateSheetRow(sheet, update.row, { 'Last Message Date': update.lastMessageDate });
          } else if (update.action === 'updateLastChecked') {
              updateSheetRow(sheet, update.row, { 'Last Checked': update.lastCheckedDate });
          } else if (update.action === 'updateIgnoredStatus') {
              updateSheetRow(sheet, update.row, {
                  'Status': 'Inappropriate Report',
                  'Last Checked': update.lastCheckedDate,
                  'Last Message Date': update.lastMessageDate // 更新
              });
              Logger.log(`Updated status to 'Inappropriate Report' for thread ${update.threadId} (row ${update.row})`);
          }
      } catch(e) {
          Logger.log(`Error performing direct update for thread ${update.threadId} (row ${update.row}): ${e}`);
      }
  }
  Logger.log('Direct sheet updates finished.');


  // --- Process threads requiring API analysis ---
  // (このループ内のロジックに変更はありません)
  Logger.log(`Processing ${threadsToUpdate.length} threads requiring analysis...`);
  let apiCallCount = 0;

  for (let i = 0; i < threadsToUpdate.length; i++) {
    if (apiCallCount >= MAX_API_CALLS_PER_RUN) {
        Logger.log(`Reached maximum API call limit (${MAX_API_CALLS_PER_RUN}) for this run. Stopping analysis.`);
        break;
    }

    const item = threadsToUpdate[i];
    const thread = item.thread;
    const threadId = thread.getId();
    const messages = thread.getMessages();

    if (!messages || messages.length === 0) {
      Logger.log(`Skipping thread ${threadId} again as it has no messages.`);
      continue;
    }

    Logger.log(`Processing thread ${threadId} (${i + 1}/${threadsToUpdate.length}, API Call #${apiCallCount + 1})...`);
    const combinedContent = combineMessages(messages);

    //if (apiCallCount > 0) {
    //  Utilities.sleep(API_CALL_DELAY_MS);
    //}

    let status = 'Error: Analysis Failed';
    try {
      status = analyzeWithGemini(combinedContent);
      Logger.log(`Gemini analysis for ${threadId} resulted in status: ${status}`);
    } catch (e) {
      Logger.log(`Error calling/analyzing Gemini API for thread ${threadId}: ${e}`);
      status = 'Error: Analysis Exception';
    }
    apiCallCount++;

    const reportDate = item.reportDate;
    const subject = thread.getFirstMessageSubject();
    const lastMessageDate = thread.getLastMessageDate();
    const threadLink = `https://mail.google.com/mail/u/0/#label/${GMAIL_LABEL.replace(/ /g, '+')}/${threadId}`;

    const rowData = {
      'Thread ID': threadId,
      'Subject': subject,
      'Report Date': reportDate,
      'Status': status,
      'Last Checked': new Date(),
      'Last Message Date': lastMessageDate,
      'Gmail Link': threadLink
    };

    try {
        if (item.isNew) {
          addNewRow(sheet, rowData);
          Logger.log(`Added analysis result for new thread ${threadId}`);
        } else {
          const updateData = {
             'Status': status,
             'Last Checked': rowData['Last Checked'],
             'Last Message Date': rowData['Last Message Date'],
          };
          updateSheetRow(sheet, item.row, updateData);
          Logger.log(`Updated analysis result for thread ${threadId} (row ${item.row})`);
        }
    } catch(e) {
        Logger.log(`Error updating sheet for thread ${threadId}: ${e}`);
    }
  } // End of threadsToUpdate loop

  // --- Sort Sheet Data ---
  try {
      Logger.log(`Sorting sheet by '${REPORT_DATE_COLUMN_NAME}' descending...`);
      sortSheetByDate(sheet, REPORT_DATE_COLUMN_NAME);
      Logger.log("Sheet sorted.");
  } catch (e) {
      Logger.log(`Error sorting sheet: ${e}`);
  }

  // --- Delete Old Records ---
  try {
    Logger.log(`Checking for records older than ${MONTHS_TO_KEEP} months to delete...`);
    // Use the same threshold date calculated earlier
    deleteOldRecords(sheet, REPORT_DATE_COLUMN_NAME, MONTHS_TO_KEEP);
  } catch (e) {
    Logger.log(`Error during old record deletion: ${e}`);
  }

  Logger.log(`Bug tracking process finished. Performed ${apiCallCount} API calls.`);
 } // <<< END OF main() FUNCTION >>>

 /**
 * Reads existing data from the sheet.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet object.
 * @return {Object} An object where keys are Thread IDs and values contain row, dates, and status.
 * @throws {Error} If essential header columns ('Thread ID', 'Report Date', 'Last Checked') are missing.
 */
 function getExistingSheetData(sheet) {
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues(); // Gets all data including headers
  const existingData = {};

  if (values.length < 1) return existingData; // Empty sheet

  const headers = values[0].map(h => String(h).trim()); // Get headers from the first row
  const threadIdCol = headers.indexOf('Thread ID');
  const reportDateCol = headers.indexOf(REPORT_DATE_COLUMN_NAME); // Index for report date
  const lastMsgDateCol = headers.indexOf('Last Message Date');
  const lastCheckedCol = headers.indexOf('Last Checked');
  const statusCol = headers.indexOf('Status'); // --- 追加: Status列のインデックス取得 ---

  // Ensure required columns exist - Throw error to stop execution if missing
  if (threadIdCol === -1) throw new Error("'Thread ID' column not found. Please add it.");
  if (reportDateCol === -1) throw new Error(`'${REPORT_DATE_COLUMN_NAME}' column not found. Please add it for sorting and deletion.`);
  if (lastCheckedCol === -1) throw new Error("'Last Checked' column not found. Please add it for re-check logic.");

  // Optional columns warning
  if (lastMsgDateCol === -1) Logger.log("Warning: 'Last Message Date' column not found.");
  if (statusCol === -1) Logger.log("Warning: 'Status' column not found. Cannot check existing status for Ignored logic."); // --- 追加 ---

  // Start from row 1 (index 1) to skip headers
  for (let i = 1; i < values.length; i++) {
    const threadId = String(values[i][threadIdCol]).trim();
    if (threadId) {
       const reportDate = values[i][reportDateCol] instanceof Date ? values[i][reportDateCol] : null;
       const lastMessageDate = (lastMsgDateCol !== -1 && values[i][lastMsgDateCol] instanceof Date) ? values[i][lastMsgDateCol] : null;
       const lastCheckedDate = (lastCheckedCol !== -1 && values[i][lastCheckedCol] instanceof Date) ? values[i][lastCheckedCol] : null;
       const status = (statusCol !== -1 && values[i][statusCol]) ? String(values[i][statusCol]).trim() : null; // --- 変更: Status取得、nullチェック追加 ---

       if (!reportDate) {
           Logger.log(`Warning: Invalid or missing date in '${REPORT_DATE_COLUMN_NAME}' column for thread ID ${threadId} at row ${i+1}. This row might sort/delete incorrectly.`);
       }

       existingData[threadId] = {
         row: i + 1, // Sheet rows are 1-based
         reportDate: reportDate,
         lastMessageDate: lastMessageDate,
         lastCheckedDate: lastCheckedDate,
         status: status // --- 追加: Statusを格納 ---
       };
    }
  }
  return existingData;
 }

 /**
 * Combines message bodies from a thread into a single string.
 * @param {Array<GoogleAppsScript.Gmail.GmailMessage>} messages Array of Gmail messages.
 * @return {string} Combined text content of the messages.
 */
 function combineMessages(messages) {
  let combined = "";
  messages.forEach((message, index) => {
    const date = message.getDate();
    const from = message.getFrom();
    const subject = message.getSubject();
    let body = message.getPlainBody();
    if (!body) {
        body = message.getBody()
                 .replace(/<style([\s\S]*?)<\/style>/gi, '')
                 .replace(/<script([\s\S]*?)<\/script>/gi, '')
                 .replace(/<[^>]*>/g, ' ')
                 .replace(/\s+/g, ' ')
                 .trim();
    }

    combined += `--- Message ${index + 1} ---\n`;
    combined += `Date: ${date}\n`;
    combined += `From: ${from}\n`;
    combined += `Subject: ${subject}\n`;
    combined += `Body:\n${body.substring(0, 3000)}\n\n`;
  });
  return combined;
 }

 /**
 * Calls the Gemini API to analyze the email content and determine the status.
 * @param {string} emailContent The combined content of the email thread.
 * @return {string} The determined status string.
 */
 function analyzeWithGemini(emailContent) {
  let prompt = GEMINI_PROMPT_TEMPLATE;
  try {
      if (emailContent.length > 200000) {
          Logger.log("Warning: Input emailContent is very large, truncating before API call.");
          emailContent = emailContent.substring(0, 200000) + "... [Truncated Internally]";
      }
      prompt = prompt.replace('{EMAIL_CONTENT}', emailContent);
  } catch (e) {
      Logger.log(`Error preparing prompt: ${e}. Using fallback content.`);
      return 'Error: Prompt Preparation Failed';
  }

  const MAX_PROMPT_LENGTH = 180000;
  if (prompt.length > MAX_PROMPT_LENGTH) {
     Logger.log(`Warning: Final prompt length (${prompt.length}) exceeds limit (${MAX_PROMPT_LENGTH}). Truncating.`);
     prompt = prompt.substring(0, MAX_PROMPT_LENGTH) + "\n... [Prompt Truncated]";
  }

  const payload = {
    contents: [{
      parts: [{
        text: prompt
      }]
    }],
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(GEMINI_API_ENDPOINT, options);
  const responseCode = response.getResponseCode();
  const responseBody = response.getContentText();

  if (responseCode === 200) {
    try {
        const jsonResponse = JSON.parse(responseBody);
        if (jsonResponse.candidates && jsonResponse.candidates.length > 0 &&
            jsonResponse.candidates[0].content && jsonResponse.candidates[0].content.parts &&
            jsonResponse.candidates[0].content.parts.length > 0 && jsonResponse.candidates[0].content.parts[0].text)
        {
          let textResponse = jsonResponse.candidates[0].content.parts[0].text;
          const match = textResponse.match(/Status:\s*(.+)/i);
          if (match && match[1]) {
             return match[1].trim();
          } else {
              Logger.log(`Could not parse status like 'Status: ...' from response: ${textResponse}`);
              return textResponse.trim().substring(0,150);
          }
        } else if (jsonResponse.candidates && jsonResponse.candidates.length > 0 && jsonResponse.candidates[0].finishReason) {
             Logger.log(`Gemini call finished with reason: ${jsonResponse.candidates[0].finishReason}.`);
             return `Error: Model Finish (${jsonResponse.candidates[0].finishReason})`;
        }
         else {
           Logger.log(`Unexpected Gemini response structure: ${responseBody}`);
           return 'Error: Unexpected API Response Structure';
        }
    } catch (e) {
      Logger.log(`Error parsing Gemini JSON response: ${e}\nResponse Body: ${responseBody}`);
      return 'Error: Parsing API Response Failed';
    }
  } else {
    let errorStatus = `Error: API Call Failed (${responseCode})`;
    Logger.log(`Error calling Gemini API: Response Code ${responseCode}. Response Body: ${responseBody}`);
    if (responseCode === 429) errorStatus = 'Error: API Rate Limit';
    else if (responseCode === 403) errorStatus = 'Error: API Permission Denied';
    else if (responseCode === 400) errorStatus = 'Error: API Bad Request';
    else if (responseCode >= 500) errorStatus = `Error: API Server Error (${responseCode})`;
    return errorStatus;
  }
 }

 /**
 * Adds a new row to the spreadsheet with the provided data.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet object.
 * @param {Object} rowData An object where keys are header names and values are cell values.
 */
 function addNewRow(sheet, rowData) {
  // This function now just prepares the data. Sorting happens later.
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(h => String(h).trim());
  const newRow = headers.map(header => rowData.hasOwnProperty(header) ? rowData[header] : "");
  sheet.appendRow(newRow); // Appends to the bottom temporarily
 }

 /**
 * Updates specific cells in an existing row in the spreadsheet.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet object.
 * @param {number} rowNumber The 1-based row number to update.
 * @param {Object} rowDataToUpdate An object with header names and new values.
 */
 function updateSheetRow(sheet, rowNumber, rowDataToUpdate) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(h => String(h).trim());
  headers.forEach((header, index) => {
    if (rowDataToUpdate.hasOwnProperty(header)) {
      const colIndex = index + 1;
      const value = rowDataToUpdate[header];
      try {
          sheet.getRange(rowNumber, colIndex).setValue(value);
      } catch (e) {
          Logger.log(`Error setting value '${value}' in row ${rowNumber}, col ${colIndex} (Header: ${header}): ${e}`);
      }
    }
  });
 }

 /**
 * Sorts the entire sheet (excluding header row) by a specified date column in descending order.
 * 指定された日付列に基づいて、シート全体（ヘッダー行を除く）を降順でソートします。
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet object to sort.
 * @param {string} dateColumnName The exact header name of the date column to sort by.
 */
 function sortSheetByDate(sheet, dateColumnName) {
    const lastRow = sheet.getLastRow();
    // Only sort if there is data beyond the header row
    if (lastRow < 2) {
        Logger.log("No data rows to sort.");
        return;
    }

    const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const dateColumnIndex = headerRow.findIndex(header => String(header).trim() === dateColumnName);

    if (dateColumnIndex === -1) {
        Logger.log(`Error: Column "${dateColumnName}" not found for sorting.`);
        throw new Error(`Sort column "${dateColumnName}" not found.`); // Stop if column missing
    }

    // Get the range to sort (all data rows)
    const dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());

    // Sort the range based on the date column index, descending
    // Ensure values in the date column are treated as Dates for proper sorting
    dataRange.sort({
        column: dateColumnIndex + 1, // Spreadsheet range columns are 1-based
        ascending: false // false for descending order (newest first)
    });

    // Optional: Add visual confirmation or log counts
    Logger.log(`Sorted ${lastRow - 1} data rows.`);
 }

 /**
 * Deletes rows from the sheet where the date in the specified column is older than a threshold.
 * 指定された列の日付がしきい値よりも古い行をシートから削除します。
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet object to modify.
 * @param {string} dateColumnName The header name of the date column to check.
 * @param {number} monthsThreshold The age threshold in months (e.g., 2 for older than 2 months).
 */
 function deleteOldRecords(sheet, dateColumnName, monthsThreshold) {
  Logger.log(`Starting deletion process for records older than ${monthsThreshold} months...`);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    Logger.log("No data rows to check for deletion.");
    return; // No data to delete
  }

  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues(); // Get all data including headers
  const headers = values[0].map(h => String(h).trim());
  const dateColumnIndex = headers.indexOf(dateColumnName);

  if (dateColumnIndex === -1) {
    Logger.log(`Error: Column "${dateColumnName}" not found for deletion check.`);
    // Optionally throw an error or return early
    // throw new Error(`Column "${dateColumnName}" not found for deletion check.`);
    return;
  }

  const now = new Date();
  const thresholdDate = new Date(now);
  // Calculate the date `monthsThreshold` months ago
  thresholdDate.setMonth(thresholdDate.getMonth() - monthsThreshold);
  // Set time to start of the day for consistent comparison (optional, depends on desired precision)
  thresholdDate.setHours(0, 0, 0, 0);

  Logger.log(`Threshold date for deletion: ${thresholdDate.toISOString()}`);

  const rowsToDelete = [];
  // Iterate backwards through the data rows (excluding header)
  for (let i = lastRow -1; i >= 1; i--) { // i is 0-based index for values array, maps to row i+1
    const reportDateValue = values[i][dateColumnIndex];

    // Check if the value is a valid Date object
    if (reportDateValue instanceof Date && !isNaN(reportDateValue.getTime())) {
      const reportDate = new Date(reportDateValue); // Create copy to avoid modifying original if needed
      reportDate.setHours(0,0,0,0); // Normalize time for comparison (optional)

      if (reportDate < thresholdDate) {
        // Add the actual row number (1-based) to the list
        rowsToDelete.push(i + 1);
        // Logger.log(`Marking row ${i + 1} for deletion (Date: ${reportDateValue.toISOString()})`);
      }
    } else {
       // Log rows with invalid or missing dates in the target column if needed
       // Logger.log(`Skipping row ${i + 1} due to invalid/missing date in column '${dateColumnName}'. Value: ${reportDateValue}`);
    }
  }

  if (rowsToDelete.length > 0) {
    Logger.log(`Found ${rowsToDelete.length} rows to delete.`);
    // Sort rows in descending order to avoid index shifting issues during deletion
    rowsToDelete.sort((a, b) => b - a);

    // Delete rows one by one, starting from the bottom
    rowsToDelete.forEach(rowNum => {
      try {
        sheet.deleteRow(rowNum);
        // Logger.log(`Deleted row ${rowNum}.`);
      } catch (e) {
        Logger.log(`Error deleting row ${rowNum}: ${e}`);
      }
    });
    Logger.log(`Finished deleting ${rowsToDelete.length} old records.`);
  } else {
    Logger.log("No records found older than the threshold.");
  }
 }

 // --- Optional: Trigger Management Functions ---

 function createTimeDrivenTrigger() {
  deleteTriggersByName('main'); // Avoid duplicates
  ScriptApp.newTrigger('main')
    .timeBased()
    .everyHours(1) // Adjust frequency as needed
    .create();
  Logger.log('Time-driven trigger created for main function (runs approx. every hour).');
  SpreadsheetApp.getUi().alert('Time-driven trigger created successfully! It will run approximately every hour.');
 }

 function deleteTriggersByName(functionName) {
    const triggers = ScriptApp.getProjectTriggers();
    let deletedCount = 0;
    triggers.forEach(trigger => {
        if (trigger.getHandlerFunction() === functionName) {
            try {
                ScriptApp.deleteTrigger(trigger);
                deletedCount++;
                Logger.log('Deleted trigger ID: ' + trigger.getUniqueId());
            } catch (e) {
                Logger.log(`Failed to delete trigger ${trigger.getUniqueId()}: ${e}`);
            }
        }
    });
    if (deletedCount > 0) Logger.log(`Deleted ${deletedCount} triggers for the '${functionName}' function.`);
    else Logger.log(`No triggers found for the '${functionName}' function.`);
    return deletedCount;
 }

 function deleteAllMainTriggers() {
    const count = deleteTriggersByName('main');
    SpreadsheetApp.getUi().alert(`Deleted ${count} triggers for the 'main' function.`);
 }