別シートの情報と同一の場合、フラグを立てる処理について

Question

Googleフォームのアンケートに回答された内容を、スプレッドシートAに掲載しており、
別シート（スプレッドシートB）に管理表があります。

スプレッドシートAにアンケートが回答されたら、スプレッドシートBに、「完了」フラグを立てたいと思っておりますが、
同一メンバーおよび同一コースがある為、検索キーをアドレスとコースコードが同じ場合としたいと思っております。

以下の場合、スプレッドシートAのB2セル/F2セルとスプレッドシートBのC7セル/K7セルが同じなので、
スプレッドシートBのQ7セルへ「完了」というフラグを立てたいです。

なお、AとBは同じスプレッドシートとなり、シートが異なるのみです。

▼スプレッドシートA

▼スプレッドシートB

GASでの実装は可能でしょうか？
もしくは、GASを実装せずに、数式で対応する方法がありますでしょうか？

ご存じの方がいらっしゃいましたら、アドバイスいただけますとたすかります。
よろしくお願いします。

Answer 1

関数でいくならこんな感じ？

=IF(COUNTIFS('スプレッドシートA'!B:B,"="&$K7,'スプレッドシートA'!F:F,"="&$C7)>0,"完了","")

Comments

[papinianus]

すばらしい解決ですね。関数を覚えないといけないのかもしれない

質問者さん。こちらをご利用ください。

[mitsuba]

macaron_xxxさん、数式をお教えいただきまして、ありがとうございました。無事に反映されるようになりました。大変たすかりました。

Answer 2

シートAとシートBが同じ(一つの)スプレッドシート内の別シートだとして、下記のonChange()を値の変更時のトリガーとして設定すれば動作する気がする(未検証)

function onChange(e) {
  //https://teratail.com/questions/141563
  Logger.log("start");
  var datA = e.source.getSheetByName("シートA").getDataRange().getValues();
  var last = datA.length - 1;
  var mailaddr = datA[last][1];
  var course = datA[last][5];
  Logger.log("check:"+mailaddr+" "+course);
  var datB = e.source.getSheetByName("シートB").getDataRange().getValues();
  for(var i = 0; i < datB.length; i++) {
    Logger.log(datB[i]);
    if(datB[i][2] !== course) { continue; }
    if(datB[i][6] !== mailaddr) { continue; }
    datB[i][16] = "完了";
    break;
  }
  Logger.log("set");
  Logger.log(datB);
  e.source.getSheetByName("シートB").getDataRange().setValues(datB);
}

Comments

[mitsuba]

コードの作成をいただきまして、本当にありがとうございます。

さっそく試してみましたところ、以下のエラーが表示されてしまいました。
何か、他に設定等必要でしょうか？

▼エラー内容
　TypeError: undefined からプロパティ「source」を読み取れません。

▼エラー対象行
var datA = e.source.getSheetByName("シートA").getDataRange().getValues();

なお、シートAとシートBは、同じ(一つの)スプレッドシートとなります。
よろしくお願いします。

[papinianus]

確認ですが、スクリプトの実行ボタンやデバッグボタンからでは動作しません。
値の変更時のトリガーで実行していてそうなりますでしょうか？

[mitsuba]

大変失礼いたしました。

現在のプロジェクトトリガーを、「スプレッドシートから」「値の変更」に設定して、
再度、試してみましたところ、特にエラー等は表示されませんが、シートBのQ7セルに「完了」の文字は反映されない状況です。

対応方法に誤りがある様でしたら、ご指摘いただけます様、お願いします。

[papinianus]

formから送信される前提なので最終行のみを見る動作です。

ログを細かく出すようにしたので、startしているか、メールアドレスやコースが正しく取得できているか、ループがちゃんと回っているかなどご確認ください(どこで躓いているのか実データがないと分からないです)
また、ログを出すには、フォームからの送信ではなく、フォームで書き込みが起こるであろうシートを手動で編集(値の変更)する必要がありますので、ご注意ください。

[papinianus]

混乱させて申し訳ないです。macaron_xxxさんの解決策に乗りましょう。

[mitsuba]

papinianusさん　親身になり相談にのっていただき、ありがとうございました。スクリプトまで作成いただきｍさいたのに大変恐縮ですが、macaron_xxxさんにお教えいただきました数式で解決いたしました。本当にありがとうございました。

[papinianus]

いえいえこちらこそすみませんでした。質問した人に分かるように説明できないのは、完全にこちらの落ち度ですので。macaron_xxxさんはすごいですね