google spreadsheet で色が付いたセルを探すスクリプト

投稿者:

google スプレッドシートを使っていて、色の付いたセルを探したいと思った。

ツールメニューからスクリプトを開き、以下のコードを貼りつける。

// return first index (1 origin) which cell is colored (not #ffffff)
// if not found return length + 1;
// topleftref: cell reference in string (cell('address', a1notation))
// length: number of columns
function coloredIndexOf(topleftref, length) {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var topleft = sheet.getRange(topleftref);
  var topleftRow = topleft.getRow();
  var topleftCol = topleft.getColumn();
  var index;
  for (index = 0; index < length; index++) {
    var range = sheet.getRange(topleftRow, topleftCol + index);
    if (range.getValue() && range.getBackground() != '#ffffff') {
      break;
    }
  }
  return index + 1;
}

例えば、F4 セルから後ろの 5 つのセルに値を入れてセルに色を付けた場合、=coloredIndexOf(cell(“address”,$F4), 5) のように関数を入力すると、一番左に色が付いているセルの番号(1から始まる)を返す。

ただ、セルの色を更新しても、表は再計算されないようだ・・・関数の引数はセル参照文字列と長さなので、セルの色が変わっても、関数の引数に変化がないためだろう。試行錯誤した挙句、例えば、A1セルに適当な文字列(レビジョン等)を入れて、=coloredIndexOf(cell(“address”,$F4), 5, $A$1) のように、して使ってみた。(セルの色を変える度にA1セルを手動で更新する必要がある)

以下のようなシフト表の「シフト」列を算出するために使った。シフト列に転記する時によく間違えるので。(アプリを使えばよいのかもしれないが・・・)

https://docs.google.com/spreadsheets/d/1MDPWS35L_vTRHm7XxeSVrAmWQaO0csL_eaRN8U-ufW4/edit?usp=sharing