三毛猫.キュート.ビジネス

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // メニューを追加
  ui.createMenu('Custom Menu')
    .addItem('Execute Tasks', 'myFunction')
    .addToUi();
}
function myFunction() {
  freezeRowsAndColumns();
  addColumnsAndCalculate();
  sortData();
  formatColumnHeadersAndData();
  copyMemoAndBackground();
  highlightNewEntries();

}
function freezeRowsAndColumns() {
  // アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // アクティブなシートを取得
  var sheet = ss.getActiveSheet();
  
  // 行を固定（この場合は3行目までを固定）
  sheet.setFrozenRows(2);
  
  // 列を固定（この場合は3列目までを固定）
  sheet.setFrozenColumns(3);
}

function addColumnsAndCalculate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();

  // ヘッダー行を取得
  var headers = sheet.getRange(2, 1, 1, lastColumn).getValues()[0];

  // 各カラムのインデックスを取得
  var liabilitiesCol = headers.indexOf('負債') + 1;
  var currentAssetsCol = headers.indexOf('流動資産') + 1;
  var marketValueCol = headers.indexOf('時価総額') + 1;

  if (liabilitiesCol === 0 || currentAssetsCol === 0 || marketValueCol === 0) {
    Logger.log("必要なカラムが見つかりません。");
    return;
  }

  // 新しいカラム見出しの追加
  sheet.insertColumnAfter(lastColumn);
  sheet.insertColumnAfter(lastColumn + 1);
  sheet.getRange(2, lastColumn + 1).setValue("流動資産-負債");
  sheet.getRange(2, lastColumn + 2).setValue("NC比率");

  // 各行に対して計算を実行
  for (var i = 3; i <= lastRow; i++) { var liabilities = sheet.getRange(i, liabilitiesCol).getValue(); var currentAssets = sheet.getRange(i, currentAssetsCol).getValue(); var marketValue = sheet.getRange(i, marketValueCol).getValue(); if (typeof liabilities === 'number' && typeof currentAssets === 'number' && typeof marketValue === 'number' && marketValue !== 0) { var netCurrentAssets = currentAssets - liabilities; var ncRatio = netCurrentAssets / marketValue; sheet.getRange(i, lastColumn + 1).setValue(netCurrentAssets); sheet.getRange(i, lastColumn + 2).setValue(ncRatio); sheet.getRange(i, lastColumn + 2).setNumberFormat("0.00"); // 小数点以下2桁のフォーマットを適用 } else { sheet.getRange(i, lastColumn + 1).setValue("#NUM!"); sheet.getRange(i, lastColumn + 2).setValue("#NUM!"); } } } function sortData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lastRow = sheet.getLastRow(); var lastColumn = sheet.getLastColumn(); // ヘッダー行を取得 var headers = sheet.getRange(2, 1, 1, lastColumn).getValues()[0]; // 各カラムのインデックスを取得 var ncRatioCol = headers.indexOf('NC比率') + 1; var perCol = headers.indexOf('PER(会予)') + 1; if (ncRatioCol === 0 || perCol === 0) { Logger.log("必要なカラムが見つかりません。"); return; } // ソート範囲を取得 var range = sheet.getRange(3, 1, lastRow - 2, lastColumn); // ソート実行 range.sort([{ column: ncRatioCol, ascending: false }, { column: perCol, ascending: true }]); } function formatColumnHeadersAndData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lastRow = sheet.getLastRow(); var lastColumn = sheet.getLastColumn(); // ヘッダー行を取得 var headers = sheet.getRange(2, 1, 1, lastColumn).getValues()[0]; // カラムのインデックスを取得 var marketValueCol = headers.indexOf('時価総額') + 1; var liabilitiesCol = headers.indexOf('負債') + 1; var currentAssetsCol = headers.indexOf('流動資産') + 1; var netCurrentAssetsCol = headers.indexOf('流動資産-負債') + 1; var ncRatioCol = headers.indexOf('NC比率') + 1; // 緑色にするカラム if (marketValueCol > 0) {
    sheet.getRange(2, marketValueCol, lastRow - 1, 1).setFontColor("green");
  }
  if (liabilitiesCol > 0) {
    sheet.getRange(2, liabilitiesCol, lastRow - 1, 1).setFontColor("green");
  }
  if (currentAssetsCol > 0) {
    sheet.getRange(2, currentAssetsCol, lastRow - 1, 1).setFontColor("green");
  }

  // 赤色にするカラム
  if (netCurrentAssetsCol > 0) {
    sheet.getRange(2, netCurrentAssetsCol, lastRow - 1, 1).setFontColor("red");
  }
  if (ncRatioCol > 0) {
    sheet.getRange(2, ncRatioCol, lastRow - 1, 1).setFontColor("red");
  }
}
function copyMemoAndBackground() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var comparisonSheetName = activeSheet.getRange("C1").getValue();
  var comparisonSheet = ss.getSheetByName(comparisonSheetName);
  
  if (!comparisonSheet) {
    Logger.log("比較するタブが見つかりません。");
    return;
  }

  var lastRowActive = activeSheet.getLastRow();
  var lastRowComparison = comparisonSheet.getLastRow();
  var lastColumnActive = activeSheet.getLastColumn();
  
  // アクティブなタブに「メモ」列を追加
  activeSheet.insertColumnAfter(lastColumnActive);
  activeSheet.getRange(2, lastColumnActive + 1).setValue("メモ");

  // アクティブなタブと比較タブの会社名の列を取得
  var activeCompanyNames = activeSheet.getRange(3, 1, lastRowActive - 2, 1).getValues().flat();
  var comparisonCompanyNames = comparisonSheet.getRange(3, 1, lastRowComparison - 2, 1).getValues().flat();

  for (var i = 0; i < activeCompanyNames.length; i++) { var activeCompanyName = activeCompanyNames[i]; var comparisonIndex = comparisonCompanyNames.indexOf(activeCompanyName); if (comparisonIndex > -1) {
      // 比較タブからメモをコピー
      var memo = comparisonSheet.getRange(comparisonIndex + 3, lastColumnActive + 1).getValue();
      activeSheet.getRange(i + 3, lastColumnActive + 1).setValue(memo);
      
      // 比較タブの背景色をコピー
      var bgColor = comparisonSheet.getRange(comparisonIndex + 3, 1, 1, comparisonSheet.getLastColumn()).getBackground();
      activeSheet.getRange(i + 3, 1, 1, activeSheet.getLastColumn()).setBackground(bgColor);
    }
  }
}
function highlightNewEntries() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var comparisonSheetName = activeSheet.getRange("C1").getValue();
  var comparisonSheet = ss.getSheetByName(comparisonSheetName);
  
  if (!comparisonSheet) {
    Logger.log("比較するタブが見つかりません。");
    return;
  }

  var lastRowActive = activeSheet.getLastRow();
  var lastRowComparison = comparisonSheet.getLastRow();
  
  // アクティブなタブと比較タブの会社名の列を取得
  var activeCompanyNames = activeSheet.getRange(3, 1, lastRowActive - 2, 1).getValues().flat();
  var comparisonCompanyNames = comparisonSheet.getRange(3, 1, lastRowComparison - 2, 1).getValues().flat();

  for (var i = 0; i < activeCompanyNames.length; i++) {
    var activeCompanyName = activeCompanyNames[i];
    var comparisonIndex = comparisonCompanyNames.indexOf(activeCompanyName);

    if (comparisonIndex === -1) {
      // 比較タブに存在しない会社名の行を太字にする
      activeSheet.getRange(i + 3, 1, 1, activeSheet.getLastColumn()).setFontWeight("bold");
    }
  }
}