スプレッドシートで「キャンセル」と入ったら、データを削除or差し引きしたい（関数orGAS）

Question

前提・実現したいこと

GoogleフォームとGoogleスプレッドシートで発注の管理をしています。
フォームでキャンセルにチェックして送信したら、以前発注したデータを削除、もしくは差し引きして発注が無かった状態にしたいです。

下記図の黄色く塗りつぶしているところがキャンセル入力してデータを取り消したい行になります。

▼シート名「シート1」：フォームの回答のシート
![]

◆制約
①F列（単価）から金額の合計を別のシートにSUMIFで算出しています
②D列（お弁当の種類を選択）から発注する個数を別のシートにCOUNTIFで算出しています

試したこと、考えた案

①B列に「キャンセル」と入ったら、F列の単価が*-1することはIF関数等で何とかできそうと思うのですが、D列のお弁当の種類を取り消す方法が思いついていません。

②GASで「removeDuplicates()」で重複している行を削除しようと思いましたが、1行しか削除されず、目的とは違いました。

関数での解決でもGASでの解決でも構いません。
何かいいアドバイスございましたら教えてくださるとありがたいです。

Answer 1

解釈があっているかわかりませんが、GASを使った方法を提示してみます。
キャンセルされた場合、シートに表示されている一番近い同じ名前の人を、以前に注文したアイテムとして判断しました。
２種類のスクリプトを作ってみましたので、シートのバックアップをとり試してみてください。

D列のお弁当の種類を消します、F列の単価を０にします、列の色を黄色にします。

function editCancelItems() {
    let file = SpreadsheetApp.openById("シートのIDを記入してください。");
    let sheet = file.getSheetByName("シート1");

    let values = sheet.getRange("B2:C").getValues();
    let values_canceled = values
        .map(function (v, i) {
            if (v[0] === "キャンセル") {
                var index = i + 2;
                return {
                    cancel_name: v[1],
                    cancel_index: index,
                };
            } else {
                return false;
            }
        })
        .filter(function (v, i) {
            return v;
        });
    //console.log(values_canceled);
    values_canceled.forEach(function (a, i) {
        values.forEach(function (v, i) {
            var index = i + 2;
            if (v[0] !== "キャンセル" && v[1] === a.cancel_name && index < a.cancel_index) {
                a.duplicate_index = index;
            }
        });
    });
    //console.log(values_canceled);
    let remove_index = values_canceled
        .map(function (a, i) {
            return [a.cancel_index, a.duplicate_index];
        })
        .flat();
    remove_index.sort((a, b) => {
        return b - a;
    });
    remove_index = Array.from(new Set(remove_index));
    console.log(remove_index);
    remove_index.forEach(function (index, i) {
        sheet.getRange(`D${index}`).setValue(``);
        sheet.getRange(`F${index}`).setValue(0);
        sheet.getRange(`${index}:${index}`).setBackground(`yellow`);
    });
}

列を削除するバージョンです。

function removeCancelItems() {
    let file = SpreadsheetApp.openById("シートのIDを記入してください。");
    let sheet = file.getSheetByName("シート1");

    let values = sheet.getRange("B2:C").getValues();
    let values_canceled = values
        .map(function (v, i) {
            if (v[0] === "キャンセル") {
                var index = i + 2;
                return {
                    cancel_name: v[1],
                    cancel_index: index,
                };
            } else {
                return false;
            }
        })
        .filter(function (v, i) {
            return v;
        });
    //console.log(values_canceled);
    values_canceled.forEach(function (a, i) {
        values.forEach(function (v, i) {
            var index = i + 2;
            if (v[0] !== "キャンセル" && v[1] === a.cancel_name && index < a.cancel_index) {
                a.duplicate_index = index;
            }
        });
    });
    //console.log(values_canceled);
    let remove_index = values_canceled
        .map(function (a, i) {
            return [a.cancel_index, a.duplicate_index];
        })
        .flat();
    remove_index.sort((a, b) => {
        return b - a;
    });
    remove_index = Array.from(new Set(remove_index));
    console.log(remove_index);
    remove_index.forEach(function (index, i) {
        //Utilities.sleep(5000);
        sheet.deleteRow(index);
    });
}

Comments

[r_suzuki_SK]

回答ありがとうございます。
前に回答してくださった方で自分なりに作ってみて解決してしまいましたが、GASも勉強したいと思っていましたので、とてもありがたいです！
詳しく2パターンの提示ありがとうございます。
こちらのパターンでも作成してみて勉強させていただきます。

ありがとうございました！

Answer 2

【スクリプトを使わず、関数だけを使用する方法】

ここではフォームからの回答を集約しているシートの名前が、「シート1」という名前であると仮定します。

シート1に新たに「個数」という名前の列を追加し、
その列の2行目に下記の式を入力します。（下図のH列）
（注意）「個数」の列はスプレッドシートだけに追加すればよく、アンケートに追加する必要はありません。

=ARRAYFORMULA(IF(D2:D<>"", IF(B2:B="キャンセル", -1, 1), ""))

これは
「お弁当の種類(D列)を選択済みかつキャンセル列(B列)が空白でないならば 1、
お弁当の種類を選択済みかつキャンセル列に"キャンセル"という文字列がある場合は -1、
お弁当の種類列が空白の場合（回答されていない行）は 空白」
という意味です。

次に、お弁当の種類ごとの数を集計するシートに、下記のような式を入れます。

=QUERY(QUERY('シート1'!A2:H, "SELECT D, SUM(H) GROUP BY D LABEL SUM(H) ''"), "WHERE Col1 <> ''")

これでお弁当の種類ごとに、キャンセルを除いた個数が集計表示されます。

QUERYが2つある理由：
後半のQUERYだけだと、Hが空白となっている行も集計表示されてしまいます。
そこで、後半のQUERYで抽出した結果のうち1列目が空白のものを前半のQUERYで除外して表示しています。

Comments

[r_suzuki_SK]

回答ありがとうございます、qnoirさんのおかげで無事解決できました。
個数でカウントすれば発注個数がカウントできますね！
また＜＞がノットイコールだと初めて知りました。

とても勉強になりました、ありがとうございました。