(googleスプレッドシート)n行おきに色付けする 他

前回の記事は以下の通りです。
googleのスプレッドシートで、A列、B列、C列を、順番に、新たな列に、(A1,B1,C1,A2,B2,C2・・・)と展開する方法を記載しています。

その他、スプレッドシートの編集で使用したものを記載しておきます。

1.スプレッドシートのメニューの「拡張機能」→「Apps Script」（エディタ）→「ファイル」表記の右の「＋」マークをクリックして、以下のコードで書き換えます。
2.フロッピーマークを押して、保存します。
3.（カーソルが編集したいシートにあることを確認後）apps scriptのエディターに戻り、実行ボタンを押します。
<注意>処理する行が多すぎるとタイムアウトになるので、その場合は、適当に区切って複数回行ってください。

<1> 特定の列について、各セルの文字数が閾値を超える場合に、フォントサイズを小さくする方法

function resizeTextInMergedCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var column = 4; // B列（2列目）
  var rowStart =1; // 開始行
  var totalRows = 123; // 最終行

  var minLength = 132; // 文字数の最小値
  var fontSize = 8.5; // 変更するフォントサイズ

  // B列の全行を対象に処理
  for (var i = rowStart; i <= totalRows; i++) {
    var cell = sheet.getRange(i, column);
    var mergedRanges = cell.getMergedRanges();

    // セルが結合されている場合
    if (mergedRanges.length > 0) {
      for (var j = 0; j < mergedRanges.length; j++) {
        var mergedRange = mergedRanges[j];
        var cellText = mergedRange.getValue().toString();
        
        if (cellText.length >= minLength) {
          Logger.log("Resizing text in merged cell range: " + mergedRange.getA1Notation());
          mergedRange.setFontSize(fontSize); // 文字サイズを8.5に設定
        }
      }
    }
  }
  
  Logger.log("Script completed.");
}

<2> 1行おきに<1>を実施したい場合

function resizeTextInColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var column = 1; // 対象の列（1はA列）
  var rowStart = 1; // 開始行
  var totalRows = 200; // シートの最終行
  
  var evenRows = false; // 偶数行を対象にする場合は true、奇数行を対象にする場合は false
  var minLength = 7; // 文字数の最小値（デフォルト：10）
  
  for (var i = rowStart; i <= totalRows; i++) {
    var cell = sheet.getRange(i, column);
    var cellText = cell.getValue().toString();
    
    //Logger.log("Row: " + i + ", Text: " + cellText + ", Length: " + cellText.length);
    
    if ((evenRows && i % 2 === 0 || !evenRows && i % 2 !== 0) && cellText.length >= minLength) {
      Logger.log("Resizing text in row " + i);
      cell.setFontSize(8); // 文字サイズを8に設定
    }
  }
  
  Logger.log("Script completed.");
}

<3> セルを縦に3つずつ結合する場合

ビジネスでは結合は推奨されていないので使う機会はないかもしれませんが、備忘録として。

function mergeCellsInPairs() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = 7; // 最終行を指定

  for (var i = 2; i <= lastRow; i += 3) { //2行目から開始、3行毎に結合
    var range = sheet.getRange(i, 4, 3, 1); // D列(4列目)で3行分の範囲を取得
    range.merge(); // セルを結合
  }
}

2列分結合する場合は、(i,4,3,2)とする。

<4>  文字の大きさを一行おきに変更する場合

function changeFontSize() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // A列の奇数行（A1, A3, A5, ...）に対してフォントサイズを12に設定
  for (var i = 1; i <= 12835; i += 2) {
    sheet.getRange(i, 1).setFontSize(8); // 1=A列,2=B列、8はフォントサイズ
  }
  
  // A列の偶数行（A2, A4, A6, ...）に対してフォントサイズを12に設定
  for (var i = 2; i <= 12835; i += 2) {
    sheet.getRange(i, 1).setFontSize(12); // 1=A列,2=B列
  }
}

<5> 文字の色を複数行おきに変更する場合

function changeTextColorEveryThreeRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  var verticalStep = 3; // 3行おきに設定する
  var textColor = '#FF0000'; // 変更する文字の色

  var startRow = 3; // 開始行（必要に応じて変更）
  var endRow = sheet.getLastRow(); // 終了行（シートの最終行まで）
  var lastColumn = sheet.getLastColumn(); // 最終列
  
  for (var row = startRow; row <= endRow; row += verticalStep) {
    var range = sheet.getRange(row, 1, 1, lastColumn);
    range.setFontColor(textColor); // 文字の色を変更
  }
}

列を指定する場合は以下

function changeTextColorEveryThreeRowsInColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  var verticalStep = 3; // 3行おきに設定する
  var textColor = '#985050'; // 変更する文字の色
  var targetColumn = 2; // B列（2列目）に対して変更を行う
  
  var startRow = 5; // 開始行（必要に応じて変更）
  var endRow = sheet.getLastRow(); // 終了行（シートの最終行まで）

  for (var row = startRow; row <= endRow; row += verticalStep) {
    var range = sheet.getRange(row, targetColumn);
    range.setFontColor(textColor); // 文字の色を変更
  }
}

<6> （3行おきに確認し）特定の文字に色付けする場合

function changeSpecificTextColors() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  var verticalStep = 1; // 3行おきに設定する
  var defaultColor = '#000000'; // デフォルトの文字の色（黒）
  var highlightColor = '#032cf8'; // ハイライトする文字の色（トマト色）
  
  // ハイライトする文字列のリスト
  var highlightStrings = ["(類義語)", "(同義語)","test"];
  var targetColumn = 5; // B列（2列目）に対して変更を行う
  
  var startRow = 5; // 開始行
  var endRow = 20; // 終了行
  
  for (var row = startRow; row <= endRow; row += verticalStep) {
    var range = sheet.getRange(row, targetColumn);
    var cellValue = range.getValue();
    var richTextBuilder = SpreadsheetApp.newRichTextValue().setText(cellValue);
    
    var textStyles = [];
    
    // 指定文字列をハイライト
    highlightStrings.forEach(function(str) {
      var startIndex = 0;
      while ((startIndex = cellValue.indexOf(str, startIndex)) !== -1) {
        var endIndex = startIndex + str.length;
        textStyles.push({
          start: startIndex,
          end: endIndex,
          color: highlightColor
        });
        startIndex = endIndex; // 次の検索開始位置
      }
    });

    // デフォルトの文字色設定
    for (var i = 0; i < cellValue.length; i++) {
      var textColor = defaultColor;
      textStyles.forEach(function(style) {
        if (i >= style.start && i < style.end) {
          textColor = style.color;
        }
      });
      richTextBuilder.setTextStyle(i, i + 1, SpreadsheetApp.newTextStyle().setForegroundColor(textColor).build());
    }

    var richTextValue = richTextBuilder.build();
    range.setRichTextValue(richTextValue);
  }
}

<7>  罫線（横線のみ）をn行おきにひく

function addHorizontalBordersEveryNRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  var targetColumn = 3; // C列（3列目）
  var verticalStep = 3; // 3行おきに設定
  var startRow = 4; // 開始行（4行目）指定行の下の枠線
  var endRow = 200; // 
 //var endRow = sheet.getLastRow(); // 終了行（シートの最終行まで）

  // 横罫線を引くための範囲を指定
  for (var row = startRow; row <= endRow; row += verticalStep) {
    // B列に対して横罫線を引く
    var range = sheet.getRange(row, targetColumn, 1, 1);
    
    // 罫線を引く前に、対象範囲の罫線を保存しておく
    var currentBorders = getBorders(range);
    
    // 罫線を引く
    range.setBorder(null, null, true, null, null, null, 'black', SpreadsheetApp.BorderStyle.SOLID);
    
    // 罫線の上書き
    restoreBorders(range, currentBorders);
  }
}

// 指定した範囲の罫線情報を取得する関数
function getBorders(range) {
  var borders = range.getBorder();
  return {
    top: borders.top,
    bottom: borders.bottom,
    left: borders.left,
    right: borders.right,
    vertical: borders.vertical,
    horizontal: borders.horizontal
  };
}

// 罫線情報を再設定する関数
function restoreBorders(range, borders) {
  range.setBorder(
    borders.top,      // top
    borders.left,     // left
    borders.bottom,   // bottom
    borders.right,    // right
    borders.vertical, // vertical
    borders.horizontal, // horizontal
    null, // color (null means no change)
    null  // style (null means no change)
  );
}

<8> 上揃え・下揃えを、同列内の奇数行・偶数行ごと（または３行毎）に指定する

function formatTextAlignment() {
  // シートを取得（現在のアクティブシートを使用）
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // デフォルトの列はB列とします（列番号は2）
  var column = 2; // 1=A列,2=B列

  // 最終行を取得
  var lastRow = sheet.getLastRow();
  //var lastRow = 100; //100行目まで実施したいときは上の行をコメントアウトしてこれを使う。

  // 1行目から最終行まで2行おきに処理
  for (var i = 1; i <= lastRow; i += 2) {
    // B列のセルを取得してフォーマットを変更
    sheet.getRange(i, column).setVerticalAlignment("top");       //
    sheet.getRange(i + 1, column).setVerticalAlignment("bottom"); // 
    //sheet.getRange(i + 2, column).setVerticalAlignment("bottom"); //
  }

  /*
  // 1行目から最終行まで3行おきに処理したい場合
  for (var i = 1; i <= lastRow; i += 3) {
    // A列のセルを取得してフォーマットを変更
    sheet.getRange(i, column).setVerticalAlignment("top");       // 上ぞろえ
    sheet.getRange(i + 1, column).setVerticalAlignment("middle"); // 中央ぞろえ
    sheet.getRange(i + 2, column).setVerticalAlignment("bottom"); // 下ぞろえ
  }
  */
}

<9> n行おきに、指定した複数列を結合する

function mergeCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startRow = 5; // 開始行
  var endRow = 250; // 終了行
  var step = 3; // ステップの数

  for (var i = startRow; i <= endRow; i += step) {
    //var range1 = sheet.getRange('C' + i);
    //var range2 = sheet.getRange('D' + i);
    //var range3 = sheet.getRange('E' + i);
    sheet.getRange('C' + i + ':E'+i).merge();
  } //C-Eを結合、step(=3)行毎に
}

どなたかのお役に立てれば幸いです。

<動かないとき>
・参照範囲、指定した数字を確認する
・参照先のセルが空欄か確認する
・簡潔なデータで確認してみる
・アクティブシートになっているか確認する
・別のシートで試してみる

以上