予実管理とグラフでの可視化を自動化する予実管理AIエージェント

function importData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const menuSheet = ss.getSheetByName('menu');
  const fileId = menuSheet.getRange('A2').getValue();
  const sheetName = menuSheet.getRange('B2').getValue();
  
  try {
    const sourceSpreadsheet = SpreadsheetApp.openById(fileId);
    const sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
    
    if (!sourceSheet) {
      SpreadsheetApp.getUi().alert('対象シートが見つかりません');
      return;
    }
    
    const response = SpreadsheetApp.getUi().alert('インポートを開始しても良いですか？', SpreadsheetApp.getUi().ButtonSet.YES_NO);
    
    if (response !== SpreadsheetApp.getUi().Button.YES) {
      return;
    }
    
    // ステータス表示用のHTMLを作成
    const html = HtmlService.createHtmlOutput('<p>インポート実行中です</p>')
      .setWidth(300)
      .setHeight(80);
    
    SpreadsheetApp.getUi().showSidebar(html);
    
    const targetSheet = ss.getSheetByName('月次');
    const sourceData = sourceSheet.getRange('A2:AL' + sourceSheet.getLastRow()).getValues();
    const targetData = targetSheet.getRange('A2:AL' + targetSheet.getLastRow()).getValues();
    
    for (let i = 0; i < sourceData.length; i++) {
      if (sourceData[i][0] !== '') {
        const targetIndex = targetData.findIndex(row => row[0] === sourceData[i][0]);
        if (targetIndex !== -1) {
          for (let j = 2; j < sourceData[i].length; j++) {
            targetSheet.getRange(targetIndex + 2, j + 1).setValue(sourceData[i][j]);
          }
        }
      }
    }
    
    // サイドバーは自動的には閉じないので、完了後にアラートを表示
    SpreadsheetApp.getUi().alert('インポートが完了しました');
    
  } catch (error) {
    console.error('Error:', error);
    SpreadsheetApp.getUi().alert('エラーが発生しました: ' + error.message);
  }
}

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() === '月次') {
    updateLSPlanAndActual();
  }
}

function updateLSPlanAndActual() {
  updateLSPlan();
  updateLSActual();
}

function updateLSPlan() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var monthlySheet = ss.getSheetByName('月次');
  var lsPlanSheet = ss.getSheetByName('LS計画');

  // Get data from 月次 sheet
  var headers = monthlySheet.getRange('C1:AL1').getValues()[0];
  var data = monthlySheet.getRange('B2:AL').getValues();
  
  // Initialize an array to store the transposed and filtered data
  var result = [];

  // Add the first row with headers
  var firstRow = ['計画'];
  for (var j = 0; j < headers.length; j++) {
    if (/.*計画.*/.test(headers[j])) {
      firstRow.push(headers[j]);
    }
  }
  result.push(firstRow);

  // Add the data rows
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var newRow = [row[0]]; // Start with the value from column B (e.g., 2024年4月 計画)
    for (var j = 1; j < row.length; j++) { // Skip the first column (already added)
      if (/.*計画.*/.test(headers[j - 1])) {
        newRow.push(row[j]);
      }
    }
    result.push(newRow);
  }

  // Transpose result to match the structure of LS計画 sheet
  var transposedResult = transpose(result);

  // Clear the LS計画 sheet before setting new values
  lsPlanSheet.clear();

  // Set the transposed and filtered data to LS計画 sheet
  lsPlanSheet.getRange(1, 1, transposedResult.length, transposedResult[0].length).setValues(transposedResult);

  // Convert text dates to actual dates in LS計画 sheet
  var dateRange = lsPlanSheet.getRange('A2:A' + (transposedResult.length));
  var dateValues = dateRange.getValues();
  
  for (var i = 0; i < dateValues.length; i++) {
    var dateText = dateValues[i][0];
    var date = convertTextToDate(dateText, '計画');
    if (date) {
      dateValues[i][0] = date;
    }
  }
  
  dateRange.setValues(dateValues);
}

function updateLSActual() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var monthlySheet = ss.getSheetByName('月次');
  var lsActualSheet = ss.getSheetByName('LS実績');

  // Get data from 月次 sheet
  var headers = monthlySheet.getRange('C1:AL1').getValues()[0];
  var data = monthlySheet.getRange('B2:AL').getValues();
  
  // Initialize an array to store the transposed and filtered data
  var result = [];

  // Add the first row with headers
  var firstRow = ['実績'];
  for (var j = 0; j < headers.length; j++) {
    if (/.*実績.*/.test(headers[j])) {
      firstRow.push(headers[j]);
    }
  }
  result.push(firstRow);

  // Add the data rows
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var newRow = [row[0]]; // Start with the value from column B (e.g., 2024年4月 実績)
    for (var j = 1; j < row.length; j++) { // Skip the first column (already added)
      if (/.*実績.*/.test(headers[j - 1])) {
        newRow.push(row[j]);
      }
    }
    result.push(newRow);
  }

  // Transpose result to match the structure of LS実績 sheet
  var transposedResult = transpose(result);

  // Clear the LS実績 sheet before setting new values
  lsActualSheet.clear();

  // Set the transposed and filtered data to LS実績 sheet
  lsActualSheet.getRange(1, 1, transposedResult.length, transposedResult[0].length).setValues(transposedResult);

  // Add the header '実績' to cell A1
  lsActualSheet.getRange('A1').setValue('実績');

  // Convert text dates to actual dates in LS実績 sheet
  var dateRange = lsActualSheet.getRange('A2:A' + (transposedResult.length));
  var dateValues = dateRange.getValues();
  
  for (var i = 0; i < dateValues.length; i++) {
    var dateText = dateValues[i][0];
    var date = convertTextToDate(dateText, '実績');
    if (date) {
      dateValues[i][0] = date;
    }
  }
  
  dateRange.setValues(dateValues);
}

function transpose(matrix) {
  return matrix[0].map((col, i) => matrix.map(row => row[i]));
}

function convertTextToDate(dateText, type) {
  var datePattern = new RegExp('(\\d{4})年(\\d{1,2})月 ' + type);
  var match = datePattern.exec(dateText);
  if (match) {
    var year = parseInt(match[1], 10);
    var month = parseInt(match[2], 10) - 1; // JavaScript Date months are 0-based
    return new Date(year, month, 1);
  }
  return null;
}