質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.79%

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

受付中

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 611

shiomikento

score 12

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

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

知識

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

該当のソースコード

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • papinianus

    2019/05/03 00:26

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

    キャンセル

回答 3

+1

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/30 09:12

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

    キャンセル

  • 2019/04/30 13:11

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

    キャンセル

  • 2019/05/01 16:04

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

    キャンセル

0

背景の取得でどう頑張っても、原理的に、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();});
}

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/30 09:09

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

    キャンセル

  • 2019/04/30 09:12

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

    キャンセル

  • 2019/05/03 00:25 編集

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

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

    キャンセル

0

仮定

  • 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();
}

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.79%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る