GAS 指定した行を最終行に並べ替えしたい。

Question

関数が少しわかる程度の初心者です。
どうか条件付の並べ替えについて教えてください。

前提・実現したいこと

スプレッドシートで案件の管理をしており、A列からBE列まで情報を記載してます。
案件によっては納期が変更になる事もあるのでその都度、メニューから並べ替えを選んで納期順にしています。
また、一時的に案件がストップしたものはA～Bセルをピンク色に塗りつぶし、最終行に手作業で並べ替えをしています。
私がやりたい事は、ボタンを押すと勝手に納期順に並べ替えをし、かつピンク色に塗りつぶした行は納期の日にち関係なく
最終行にもっていくという事を実現したいです。

【　例　】
A列：月
B列：納期
C列：案件名
D列：お客様名…等

###試した事、分からない事
自分で考えた結果
１　まずはピンク色の色コードを取得

function getBackColor(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheets()[0];
  var cell=sheet.getRange('A67')
  cell.setBackground('A67')
  
  Browser.msgBox("A67のカラーコードは「"+cell.getBackground()+"」");
}

２　１で取得したコードは「#ffccff」だったので
「#ffccff」だったら最終行に並べ替え
「#ffccff」以外だったらB列の納期別に並べ替え
という指示を書きたかったのですが、それをどのように書いていいのか分かりません。

　　

function sort(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet()[0];
  var lastRow=ss.getLastRow();
  var lastCol=ss.getLastColumn();
  const color='#ffccff';
  switch(color){
     case '#ffccff:
      

Comments

[macaron_xxx]

無理やり書けばGASで実行できなくはないのですが、背景色は1行ごとにAPIを叩いて見に行かないといけないです。
方向としては、仮行に色の情報を持たせて、ソートすることになるのですが、
例えばBF列に「STOP」と書けば条件付き書式でA,B列が色付きになる。という運用に変えると、ソートもより楽になるのですが、それはお望みではないですか？

[退会済みユーザー]

コメントありがとうございます。
「STOP」と書けば条件付書式でA,B列が色付きになるという運用でも問題ございません。
今は手作業で中断した案件の行（A列、B列）を塗りつぶしをしていたのを条件付書式に運用を変えるだけでソートが楽になるというのが初心者には見当もつかないのでお恥ずかしいですが・・・
そこからは、hiroshi0240さんがおっしゃっている事を使えば出来るという事なんでしょうか。

[hiroshi0240]

「STOP」などデータ上で情報を読み取れる列があれば、データ全体をシンプルに並び替え（「STOP」が書いてある列が第一優先、納期が第2優先でソート）するだけで実現できますよ。

[退会済みユーザー]

コメントありがとうございます。
冷静に考えればそうですよね・・・。
「色のついたものをソート」だけが自分の頭の中にあって、macaron_xxxさんがおっしゃった事を
ちゃんと理解できませんでした・・・。
細かな所までわざわざ教えて頂きありがとうございます！

Answer 1

パターンA:そのまま実装すると

function sort() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().sort([{column:2, ascending:true}]);
  const lastRow = sheet.getLastRow();
  const pink = "#ffccff";
  const rows = sheet.getRange(1,1,lastRow,1).getBackgrounds().map(function(e,i){ return {R:i+1, Col:e[0]}}).filter(function(e){ return e.Col == pink;}).map(function(e){return e.R}).reverse();
  const pinks = rows.length;
  rows.forEach(function(r) { sheet.moveRows(sheet.getRange(r, 1, 1, 5), lastRow+1);});
  sheet.getRange(lastRow - pinks + 1, pinks, 5).sort([{column:2, ascending:true}]);
}

~~ピンクのを後ろに下げたときに、その中でもソートがかかっていることにするのが厄介でした。~~ミュータブルなinsertPosをデクリメントしながらやれば、再ソートは不要でしたね。もう検証したくないので書き直しませんが。
必ずA列が着色されているとき、色をまとめてとることはできるので、ボトルネックになるのは、着色行を一つずつ移動させる部分です。着色行が十分に少なければ、実用できると思います。

パターンB:ソート可能なデータを付与できるとすると

推奨されているソート可能なデータを付与する例ですが、個人的にはチェックボックスをおすすめします。ワンクリックだし誤入力(全角半角)もない。

見た目はこう

コードはこう

function onEdit(e) {
    const sheet = e.range.getSheet();
    if(sheet.getName() !== "そのシートの名前") return;
    const c = e.range.getColumn();
    if(c !== 1) { return; }
    const r = e.range.getRow();
    const value = e.value;
    switch(value) {
        case "TRUE" :
            sheet.getRange(r, 2, 1, 2).setBackground("#ffccff");
            break;
        case "FALSE" :
        default :
            sheet.getRange(r, 2, 1, 2).setBackground(null);
            break;
    }
}
function sortByCheck() {
    const sheet = SpreadsheetApp.getActiveSheet();
    sheet.getDataRange().sort([{column:1, ascending:true}, {column:3, ascending:true}]);
}

こちらは、他人がチェックをつけたときに、コードで色がつくのかなーという疑問がありますが、個人的にG Suiteを持たなくなったので分かりません。
(もし運用上可能なら、色のことは忘れて、チェックがあることを延期と思えばいいような)
→条件付き書式なんですね。それは思いつかなかった。条件付き書式だとしたら、onEditは不要です)

Comments

[hiroshi0240]

チェックボックスなら、空白セルをFalseで扱う事が出来るので、私も賛成です。という事で+1

[退会済みユーザー]

皆様、初心者の私に色々教えて頂きありがとうございます。
チェックボックスであればpapinianusさんのおっしゃる通り誤入力等がなくなるので
良い案だと思います！
またご丁寧にコードまで教えて頂きありがとうございます！

Answer 2

私もmacaron_xxxさんのご意見に賛成（というか自分ならそうします）が、とりあえず、実現させたいであろうことに必要な情報を。
まずは、データ範囲全体の並び替えを行ったあとの処理と想定し、全行に対して背景色を見に行く必要があるので、for文などの繰り返し処理を書きます。
その中で、1行ごとに、ifやswitchのような条件分岐で、「#ffccff」だったら最終行に移動、それ以外は何もしない。と記載すればよいかと。
行の移動は　moveRowsを使えばできます。

Comments

[退会済みユーザー]

コメントと分かりやすくご説明頂きありがとうございます。
moveRowsは初めて知りましたので調べて自分で再度やってみます。

[macaron_xxx]

moveRowsよりもRangeのsortのほうがいいですよ。

[hiroshi0240]

sortって列してい必要じゃなかったでしたっけ？自分はsortって使い込んだことがないので、後学のために「Rangeのsortのほうがいい」の具体的な方法とその背景をお教えいただけますか？（もしかして全行の背景情報を配列に格納したものを使ってsortできる？だとしたらそのほうが早いかも）

[macaron_xxx]

私の方針はソート順を列に持たせて、Range.sortです。
moveRowsをループで回すと、シートの編集やなどと相まって、ソートが保証されないから＋処理速度の問題です。

[hiroshi0240]

なるほど、そういうことであれば回答の冒頭に記載している通り私も列にデータをもたせる方が良いと思います。moveRowsは単に質問の「背景色情報から並び替えをする方法」にたいする回答です。

[退会済みユーザー]

アドバイス頂きましたが、もう少し教えてください。
hiroshi0240さんがおっしゃった下記の発言ですが、

『「STOP」などデータ上で情報を読み取れる列があれば、データ全体をシンプルに並び替え（「STOP」が書いてある列が第一優先、納期が第2優先でソート）するだけで実現できますよ。』

私はこれを「STOP」をキーにsortで並べ替えをして最後尾に持っていくと解釈しましたが
あっておりますでしょうか？
あっているなら、この「STOP」をキーに並べ替えるコードの書き方が分かりません。
sort ( { column : 55 , ascending : true }); で書いても
「STOP」と書いたものが一番上に並べ替えられてしまいます。
※trueをfalseに変えても結果は同じでした。

この「STOP」という文字列をキーに並べ替える書き方があれば教えてください。
※ネットで探しましたが、見つける事が出来ませんでした。

[hiroshi0240]

sortって空白セルは対応できないんですね。stop以外の空白セルにダミーでスペースを入力するか、配列データ（getValuesした値）として以下の様にソートすればできるかと思います。
対象配列＝data
data.sort(function(a,b){
var A =a[1];//stopが入力されている列数-１（2列目なら１）
var B =b[1];//stopが入力されている列数-１（2列目なら１）
if(A != "stop" && B == "stop") return -1;
if(A == "stop" && B != "stop") return 1;
return 0;
　});

[papinianus]

配列をソートするというのは、data = getRange().getValues()して、getRange().setValues(data)するという意味ですよね？そうなると、色は追従しないように思いますがいかがでしょうか？
→条件付き書式にするのでしたね。すみません。見落としてました。