【GAS】フィルタをかけて表示された内容を取得し、行毎に書き換え、1回のメソッド呼び出しで反映させたい

Question

実現したいこと

GASで、スプレッドシートにフィルタをかけて表示された内容を取得し、行毎に書き換え、1回のメソッド呼び出しで反映させたい。
・フィルタをかけて表示された内容を取得:出来た
・フィルタをかけて表示された内容を行毎に書き換え:出来たのか不明
・書き換えた内容を1回のメソッド呼び出しでスプレッドシートに反映:やり方が判らない

具体的には、
・スプレッドシートの列は「メールアドレス」「メール送信済フラグ」
1.「メール送信済フラグ」が空白の行をフィルタして取得
2.行毎にメール送信し、送信したら「メール送信済フラグ」に値を書き込む
3.この処理を毎分繰り返す

発生している問題

スプレッドシートには、フォームから入力された内容が増えていくので、
メール送信済みの行は取得せず、最小限のデータのやり取りで済ませたい。
最小限のデータのやり取りにする書き方が判らない。

ソースコード

// 1分毎のトリガーに設定しておく
function onTimerEveryMinute() {
 const ColAddress = 1;
 const ColRemind = 2;
 const SheetID = 'testSheetID';
 const SheetName = 'フォームの回答 1';
 const RowStart = 2; // 行:データ開始位置(見出し行が1)

 // フィルタ準備(誰かが変えてたら掛け直される)
 const Sheet = SpreadsheetApp.openById(SheetID).getSheetByName(SheetName);
 var Filter = Sheet.getFilter();
 if (null != Filter) {
  Filter.remove();
 }
 Filter = Sheet.getRange(RowStart - 1,　1,　Sheet.getLastRow(),　Sheet.getLastColumn()).createFilter();

 // 値が空白(メール未送信)でフィルタ
 var criteria = SpreadsheetApp.newFilterCriteria().whenCellEmpty();
 criteria.build();
 Filter.setColumnFilterCriteria(ColRemind, criteria);

 // メール送信
 var values = Sheet.getDataRange().getValues().filter(function(_, i) {return !Sheet.isRowHiddenByFilter(i + 1)});
 for(var j = RowStart - 1; j < values.length; j++) {
  GmailApp.sendEmail(values[j][ColAddress - 1], 'testMailSubject', 'このメールはGoogle Apps Scriptによる自動送信です。', {name: 'testMailName'});
  // メール送信済フラグを立てる
  values[j][ColRemind - 1] = 1; // 出来たのか不明
 }
 // ループ内でフラグ立てて、ループ外で一気に書き込みたいが、やり方が判らない

 // メール送信したらデフォルトのフィルタ状態に復帰
 if (null != criteria) {
  Filter.removeColumnFilterCriteria(ColRemind); // remindフィルタ解除
 }
}

読んだteratailのページ

・スプレッドシートのデータをフィルタで非表示にした行を除外して取得したい
https://teratail.com/questions/228915
・【GAS】フィルタをかけて表示される行の行番号を知りたい
https://teratail.com/questions/297477

Comments

[sawa]

>最小限のデータのやり取りで済ませたい
処理速度を気にされてるのであれば、全部データ取得したあと配列に対してfilterかけた方がよさそうですが、なにかフィルタ表示を使う理由はありますか？

今書かれているコードも Sheet.getDataRange().getValues() で取得した全データを絞り込んでるだけなので、特に理由が無いなら取得した配列で処理をしていけば良いかと。

[aafxaa]

コメントありがとうございます。反応が遅くなりまして申し訳ございません。

実はGASのコードを書くのは今回が初で、
Sheet.getDataRange().getValues().filter()の行はコピペでした。
ご指摘の通り、データを絞り込めればいいので、
「見た目」をフィルタする必要はありません。
よって「メール送信」前後の処理は不要になりますね。

処理速度の件も、GASには実行時間制限が有るらしいと聞きかじった程度なので、
データ量の見積をして問題無さそうならシンプルな処理に書き直そうと思います。

調査不足の質問にコメントくださいましてありがとうございました。
勉強になりました。

Answer 1

遅くなりました。シンプルにこんな感じになりました。

const ColRemind = 23;
const ColStatus = 4;
const RngMRemindRcvd = '$A$3'
const RowStart = 3;                                               // 行:データ開始位置(見出し行が2)

const SheetID = 'testSheetID';
const SheetName = 'フォームの回答 1';
const SheetNameMStat= '状態マスタ';
const SheetNameMRemind= 'Remindマスタ';

const Sheet = SpreadsheetApp.openById(SheetID).getSheetByName(SheetName);
const SheetMStat = SpreadsheetApp.openById(SheetID).getSheetByName(SheetNameMStat);
const SheetMRemind = SpreadsheetApp.openById(SheetID).getSheetByName(SheetNameMRemind);

function onEdit(e) {                                              // スプレッドシート編集トリガに設定
  const ColRcvdDateTime = 6;
  const RngMstatRcvd = '$A$1'

  const Rng = e.range;
  if (RowStart <= Rng.getRow() && ColRcvdDateTime == Rng.getColumn() && !e.oldvalue) {// データ行に受取日時が入力されたら受取済にする
    Sheet.getRange(Rng.getRow(), ColStatus).setValue(SheetMStat.getRange(RngMstatRcvd).getValue());
    Sheet.getRange(Rng.getRow(), ColRemind).setValue(SheetMRemind.getRange(RngMRemindRcvd).getValue());
  }
}

function onTimerEveryMinute() {                                   // 1分毎のトリガーに設定しておく
  const ColAdrs = 2;
  const ColAdrsManager = 24;
  const ColCustomer = 3;
  const ColDeadlineDate = 20;
  const ColDeadlineTime = 21;
  const ColOrderDateTime = 1;
  const ColOrderId = 10;
  const ColOrderName = 11;
  const ColScheduledDate = 16;
  const ColScheduledTime = 17;
  const PrmFmt = 'yyyy/MM/dd HH:mm';
  const PrmTimeZone = 'Asia/Tokyo';
  const RngMRemindSent = '$A$1'
  const RngMRemindNoAdrs = '$A$2'

  // メール送信
  const NowJst = Utilities.formatDate(new Date(), PrmTimeZone, PrmFmt);
  var values = Sheet.getDataRange().getValues();
  for(var j = RowStart - 1; j < values.length; j++) { // 見出し行はスキップ
    if(values[j][ColRemind - 1]) {  // 何らかのフラグが立ってたらスキップ
      continue;
    }

    if(values[j][ColStatus - 1]) {  // 状態値が設定されていたらスキップ(onEditの保険)
      // ここに来るということは、!values[j][ColRemind - 1]である
      Sheet.getRange(j + 1, ColRemind).setValue(SheetMRemind.getRange(RngMRemindRcvd).getValue());
      continue;
    }

    if(!values[j][ColAdrs - 1]) { // メールアドレスが無いと送信できないのでスキップ
      // ここに来るということは、!values[j][ColRemind - 1]である
      Sheet.getRange(j + 1, ColRemind).setValue(SheetMRemind.getRange(RngMRemindNoAdrs).getValue());
      continue;
    }

    if(!values[j][ColDeadlineDate - 1]) { // 期日が決まっていなければ催促出来ないのでスキップ
      // ここに来るということは、!values[j][ColRemind - 1]である
      continue;
    }

    if (values[j][ColScheduledDate - 1] + ' ' + values[j][ColScheduledTime - 1] < NowJst) {
      GmailApp.sendEmail(values[j][ColAdrs - 1]
                        , 'testMailSubject'
                        , 'このメールはGoogle Apps Scriptによる自動送信です。'
                        , {name: 'testMailName'});
      Sheet.getRange(j + 1, ColRemind).setValue(SheetMRemind.getRange(RngMRemindSent).getValue());
    }
  }
}