スプレットシート、背景色の応じて保護

Question

###灰色に色分けした古いデータをマクロで保護していきたいです。

現在仕事で使用しているデータをスプレットシートで管理しています。
複数の人が同時にアップしているので、確定した古いデータを保護していきたいと考えています。
現在保護したい部分を色分けし、古い保護したいデータは全て自動で灰色になるようにしてあります。
手作業では大変なのでマクロで灰色の部分を選択し、保護するようにしたいです。

知識

最近マクロの使い方を覚えたばかりなので、分かりやすく解説を入れて教えていただけると助かります。

該当のソースコード

var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('H5:H8').activate();　この部分がセルの位置が毎回変わるので灰色指定としたいです。
var protection = spreadsheet.getRange('H5:H8').protect();
protection.removeEditors(['メールアドレス', ]);
};

Comments

[papinianus]

後出しされた仕様変更について2点質問がありますので、そこも踏まえて、質問を改めてくださるようお願いします。
Z列ですが、灰色にならない状態では未来の日付が入っているのでしょうか、それとも空欄ですか？
I列からY列は保護して良いのでしょうか、ダメなのでしょうか？

Answer 1

背景色自体は、Range指定.getBackgroundColor()で取れますが、これだと確認したい範囲毎にAPIを叩くことになるので、あまりオススメはしません。そもそも自動で色を変えているので有れば、その処理と同時に保護すれば良いかと思いますがいかがでしょうか？

Comments

[papinianus]

色をかえるときに保護する、という考えかたはとても正しいと思います。ただ、個人的に、条件付き書式とか、色をコードでかえてないんじゃないかという懸念があります。
ただ、自動化できているってことは、判定できるルールがあるはずなので、(条件書式だとしても)色もプロテクトもかかるコードに変えることは可能だと思いますし、そうするのが正解だと思いますので+1

[shiomikento]

コメントありがとうございます。色自体はコードではなく条件付き書式で変更をしています。簡単に説明をすると一つの行にお客様の様々な詳細を入れており、Z行にお客様からの支払いが行われた日付を入力しています。Ｚ行に昨日以前の日付が入力された場合にA～H、Zのみを保護し、変更できないようにしたいという感じです。条件付き書式で昨日以前の日付が入った場合、A～H、ZをZの日付を元に灰色に変更しています。なので色別で保護と考えたのですがそもそもZ行の日付で保護をしたほうがスムーズでしょうか？その場合のやり方も教えてもらえると助かります。

[shiomikento]

因みにZ行に日付を入れて終わったものは下のほうに移動していますので（上にまだ終わっていない複数の行があるので、どこの行より下という決まりはない）保護したいセルは連続しています。

[hiroshi0240]

下の方に移動する理由が、単にリストを見易くする為で有れば、保護済みの別のシートに移動させるだけの方がスマートかと思いますが。

[hiroshi0240]

やりたい事の全体像が、特定列が編集された時の値によって、その行を最終行に移動し、色を変え、編集ロックすると言うので有れば、OnEdit(e)のパラメーターで編集内容を判断し、条件一致時に、行移動&色変更&編集ロックが良いと思います。
色変更については、条件付き書式が良いと言う事なら、それだけ別にしても良いかとは思いますが。少なくとも処理実行のトリガー判断は背景色にしない方法を取った方が良いと思います。

Answer 2

仮定

• Z列は空欄ではない(非保護行には、未来の日付が入っている)
• A-Z列全てを保護する(I-Y列もZ列に過去の日付が入っていたら編集不可である)

前者はソートを容易化するのに寄与する(ソートが1行でできる)。仮に空白であるとするとソートはかなり厄介になる
後者は保護の付け外し回数を容易化するのに寄与する(外すのと付けるので2回ですむ)。対応は容易なので適当に書いてみてください

function onEdit(e) {
  const c = e.range.getColumn();
  if(c !== 26) return;
  const sheet = e.range.getSheet();
  const dataRange = sheet.getDataRange();
  dataRange.sort([{column:26, ascending:false}]);
  const today = new Date();
  const todayOfZeroHour = new Date(today.getFullYear(), today.getMonth(), today.getDate());
  const pos = dataRange.getValues().filter(function(e) { return e[25] >= todayOfZeroHour;}).length;
  const lastR = sheet.getLastRow();
  sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function(e){ e.remove();});
  sheet.getRange(pos + 1, 1, lastR - pos, 26).protect();
}

Answer 3

背景の取得でどう頑張っても、原理的に、1セルずつロックになるので、実行時間的に使いものにならないでしょうね。
ロジックで頑張って、連続セルはまとめてロックとかにすればいいのかもしれないですが、連続セルをまとめるロジックはそこそこ難しいので、ちょっと私はご遠慮します。

function q186804() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function(e){ e.remove();});
  const gray = "#999999";
  const grays = sheet.getDataRange().getBackgrounds().map(function(eo, io){ return eo.map(function(ei, ii){ return {R:io+1,C:ii+1,Col:ei};});}).reduce(function(a,c){ return a.concat(c);},[]).filter(function(e){return e.Col === gray;}) ;
  grays.forEach(function(e) {sheet.getRange(e.R,e.C).protect();});
}

Comments

[shiomikento]

コメントありがとうございます。色自体はコードではなく条件付き書式で変更をしています。簡単に説明をすると一つの行にお客様の様々な詳細を入れており、Z行にお客様からの支払いが行われた日付を入力しています。Ｚ行に昨日以前の日付が入力された場合にA～H、Zのみを保護し、変更できないようにしたいという感じです。条件付き書式で昨日以前の日付が入った場合、A～H、ZをZの日付を元に灰色に変更しています。なので色別で保護と考えたのですがそもそもZ行の日付で保護をしたほうがスムーズでしょうか？その場合のやり方も教えてもらえると助かります。

[shiomikento]

因みにZ行に日付を入れて終わったものは下のほうに移動していますので（上にまだ終わっていない複数の行があるので、どこの行より下という決まりはない）保護したいセルは連続しています。

[papinianus]

まず、私の回答はグレーの色を正しく指定すれば動作するはずですが、試していただけていないのでしょうか？(グレーを正しく指定すれば、となっているのもグレーの色の指定が質問で明確化なされていないことに起因します)
試していただけていない場合、ご自身の求める回答であるか否かはどのように誰が判定するのでしょうか？
試していただけている場合、どこがどうダメか具体的にお願いします。この2件は仕様変更であって、この回答に対するコメントにはあたらないので、この2件をうけての回答修正はありません。

後出しで出された仕様変更については、質問に追記してください。ここを他の回答者(候補)が見る保証はありません。