スプレッドシートのminuteのセルから正しい値を取り出すことができない

Question

実現したいこと

特定の時刻になったらgmailを送るものをつくろうとしています。
特定の時刻が複数あるので、GASのトリガーではなくGoogleスプレッドシートでnow関数を用いています。
now関数からhourとminuteで時と分を表示するセルをそれぞれ用意しています。その二つの値が表で用意している時刻表群と等しいものがあれば、それ以降の、gmailを送るプログラムが動くようにしています。

発生している問題・分からないこと

実現したいことで説明した、minute関数を用いて分を表示しているセルの値を変数hourに"const"を用いて格納した際に、実際にセルで表示されている分の値とhourが異なってしまいます。これは、下のソースコードの"console.log"で確認しています。
"const"を"var"に変えてもセルの値とhourが異なっています。
minute関数になっていない定数のセルから数値を正しく読み取ることは可能でした。
minute関数となっているセルの値を「=(セルの番号)」でそのまま置換するセルを用意して、それをhourとして読み取る形にしても、minuteのセルと「=(セルの番号)」の2つは同じ値が表示されますが、hourとは値が異なってしまいます。
now関数があるスプレッドシートは、更新時と分ごとに値が更新されるようになっています。
上記のような場合で、値が異なってしまう理由を教えてください。

該当のソースコード

function sendConditionalEmails() {
  const spreadsheet = SpreadsheetApp.openById("1ujn3YC5CG0T_p4LpkNPq50Sq9D7BOnP74odvJsXk-e4");

  // timeシートからデータを取得
  const timeSheet = spreadsheet.getSheetByName("time");
  const month = timeSheet.getRange("B4").getValue();
  const day = timeSheet.getRange("C4").getValue();
  const hour = timeSheet.getRange("D4").getValue();
  const minute = timeSheet.getRange("E4").getValue();

  //　変数の値を確認
  console.log(month)
  console.log(day)
  console.log(hour)
  console.log(minute)

  // class_timeシートのデータを取得
  const classTimeSheet = spreadsheet.getSheetByName("class_time");
  const classTimeData = classTimeSheet.getDataRange().getValues();

  // hourと一致する行を検索
  let i = -1;
  for (let row = 1; row < classTimeData.length; row++) {
    if (classTimeData[row][1] === hour) { // B列の値と一致
      i = row + 1; // 行番号を取得 (1ベース)
      break;
    }
  }

  if (i === -1) {
    Logger.log("hourと一致する値が見つかりません。プログラムを終了します。");
    return;
  }

  // minuteと一致するか確認
  const classTimeMinute = classTimeSheet.getRange(i, 3).getValue(); // C列
  if (classTimeMinute !== minute) {
    Logger.log("minuteと一致しません。プログラムを終了します。");
    return;
  }

  // A列の値をperiodに代入
  const period = classTimeSheet.getRange(i, 1).getValue();

  // 繰り返し処理の準備
  const classPlanSheet = spreadsheet.getSheetByName("class_plan");
  const memberSheet = spreadsheet.getSheetByName("member");
  const mailSheet = spreadsheet.getSheetByName("mail");
  const classPlanData = classPlanSheet.getDataRange().getValues();
  const memberData = memberSheet.getDataRange().getValues();

  const noc = 5;
  let rep = 1;

  while (rep <= noc) {
    const planMonth = classPlanData[rep - 1][0]; // A列
    const planDay = classPlanData[rep - 1][1]; // B列
    const planPeriod = classPlanData[rep - 1][2]; // C列
    const planStatus = classPlanData[rep - 1][4]; // E列

    // 条件を満たさなければ次のrepに進む
    if (planMonth !== month || planDay !== day || planPeriod !== period || planStatus !== 0) {
      rep++;
      continue;
    }

    // 条件を満たした場合
    const id = classPlanData[rep - 1][3]; // D列
    i = -1;

    // memberシートでidを検索
    for (let row = 1; row < memberData.length; row++) {
      if (memberData[row][0] === id) { // A列の値と一致
        i = row + 1; // 行番号を取得 (1ベース)
        break;
      }
    }

    if (i === -1) {
      Logger.log(`memberシートにID ${id} が見つかりません。(rep: ${rep})`);
      rep++;
      continue;
    }

    // メール情報をセット
    const recipient = memberSheet.getRange(i, 4).getValue(); // D列
    mailSheet.getRange("B1").setValue(recipient);
    const subject = mailSheet.getRange("B2").getValue();
    let body = mailSheet.getRange("B3").getValue();

    // $1, $2, $4の置換
    const memberB = memberSheet.getRange(i, 2).getValue(); // B列
    const memberC = memberSheet.getRange(i, 3).getValue(); // C列
    body = body.replace("$1", memberB).replace("$2", memberC).replace("$4", period);

    // メール送信
    if (recipient && subject && body) {
      GmailApp.sendEmail(recipient, subject, body);
      Logger.log(`メールを送信しました。(rep: ${rep})`);
    } else {
      Logger.log("メール送信に必要な情報が不足しています。");
    }

    // mailシートのB1に"$4"をセット
    mailSheet.getRange("B1").setValue("$4");

    // repを1増やす
    rep++;
  }
}

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

「わからないことや発生している問題を教えてください」に書いた通りです。
また、検索してもminuteが原因で値が正しく」読み取れない理由はわかりませんでした。

補足

最後に、今回初めてGASに触れたため至らないところが多いとは思いますが、よろしくお願いします。

Comments

[kotori_a]

文章だけだとわからないので、スプレッドシートの共有リンクを貼ってください。

> 、minute関数を用いて分を表示しているセルの値を変数hourに"const"を用いて格納した際に、実際にセルで表示されている分の値とhourが異なってしまいます。これは、下のソースコードの"console.log"で確認しています。
> minute関数となっているセルの値を「=(セルの番号)」でそのまま置換するセルを用意して、それをhourとして読み取る形にしても、minuteのセルと「=(セルの番号)」の2つは同じ値が表示されますが、hourとは値が異なってしまいます。

ここの意味が分かりません。なぜminuteの値をhourに入れなければいけないでしょうか？

[g4367]

対応していただきありがとうございます。
初めに話しておくと、minute関数のセルの値をhourに格納しているというのは自分の間違いです。
申し訳ございません。
正しくはスプレッドシートのminute関数のセルの値をGAS上の変数minuteに格納しています。
URLは少々お待ちください。
個人情報を入力してしまっているため少し編集します。

[g4367]

以下がURLです。上で説明しているプログラムと関係ないプログラムもあるため、ご注意ください。
https://docs.google.com/spreadsheets/d/1HLrqHnI2L3R49e9thmSGFduJbgP8REkijMDJLtHco1Q/edit?usp=sharing

[kotori_a]

スクリプトが紐ついているファイルと、スクリプトの２行目の openById() で指定しているファイルが異なるようですが、それでいいのですか？
もし、そのスクリプトが紐ついているファイルを指定したいならば、
2行目の const spreadsheet = SpreadsheetApp.openById(～);の行を
const spreadsheet = SpreadsheetApp.getActive();
に変更して実行してみて下さい。

[YAmaGNZ]

最終的にやりたいことというのは現在日時を取得したいということになるのでしょうか？
もしそうなのであればシートに現在日時を格納するセルを設けるのではなく
var d = new Date();
var mon = d.getMonth() + 1;
var d2 = d.getDate();
var h = d.getHours();
var min = d.getMinutes();
といった感じでGASで取得すればいいのではないでしょうか？

[g4367]

まずkotori_aさんの方に返信します。
コメントありがとうございます。
紐づいているプログラムが異なるのは、私のミスです。申し訳ございません。
ここで共有するためにここでプロジェクトをコピーしたあと、URLを変更し忘れたために起きてしましました。
そのうえで、指定されたとおりに修正し、実行してみましたが変数minuteの値がセルの値と異なってしまいます。
申し訳ございませんが、うまく動きません。

[kotori_a]

自分の環境ではうまく動きましたが、いろいろ検索すると、GASのバグ？のせいで、スプレッドシート上では更新されているはずの値が、プログラムで読み取れないことがあるみたいです。なので回答に解決策を記載しました。

[g4367]

YAmaGNZさんの方に返信します。
コメントありがとうございます。
GAS内で時間を読み取ってしまうという方法は自分の頭にはなく、大変参考になりました。ありがとうございます。
そのうえで、申し訳ございませんがセルの読み取りが更新されないのはバグであることを教えていただいたことが、自分の中により納得感があるため、こちらをベストアンサーに選ばせていただきたいと思います。
対応してくださって、本当にありがとうございました。

[g4367]

kotori_aさんの方に返信します。
対応ありがとうございます。
問題の原因を聞き、とても納得できました。
今後は、GASでセルの値を読み取ることはできるだけ避けたいと思いました。
あらためて、ありがとうございました。

[kotori_a]

解決されたのであればよかったです！

Answer 1

自分の環境では正常に動きましたが、ネットを検索したところ、たまにスクリプトが読み取るシート内容が更新されていないというバグが発生することがあるみたいです。
（SpreadsheetApp.flush() を使っても更新されないとのこと）

下記のように、シートから読み取るのではなく、Date()関数を使って、スクリプト内で直接、日付時刻を取得すれば解決すると思います。

※この場合、シートの方の表示更新は遅いため、シートの表示とプログラム実行時にコンソールに表示される時刻が若干異なる場合があります。
ただし、下のプログラムでは、「プログラムを実行した時点」の日付時刻を正しく取得しています。

function sendConditionalEmails() {
  const spreadsheet = SpreadsheetApp.getActive();

  // --------以下は削除---------
  // const timeSheet = spreadsheet.getSheetByName("time");
  // timeシートからデータを取得
  //const timeSheet = spreadsheet.getSheetByName("time");
  //const month = timeSheet.getRange("B4").getValue();
  //const day = timeSheet.getRange("C4").getValue();
  //const hour = timeSheet.getRange("D4").getValue();
  //const minute = timeSheet.getRange("E4").getValue();
  // -----------削除ここまで----------


  // 念のためFlushする
  SpreadsheetApp.flush();

  // スクリプトで直接、日付時刻を取得する。
  const [month, day, hour, minute] = Utilities.formatDate(new Date(), 'Asia/Tokyo','MM,dd,HH,mm').split(',').map(e=>parseInt(e));
  //　変数の値を確認
  console.log(month)
  console.log(day)
  console.log(hour)
  console.log(minute)

Comments

[g4367]

対応ありがとうございます。
問題の原因とそれに対する対処法のどちらも大変参考になりました。
したがって、ベストアンサーに選ばせていただきます。
他の対応してくださった方にもとても感謝しています。
みなさま、本当にありがとうございました。