【GAS】複数セルで日付を取得して、○日前というのを一括で処理してGSSに記載したい

前提

対応日を1つずつ取得して、取得した日付の5営業日前（土日にかぶった場合は金曜日の日付）を
C列に記載するものを書いていたんですが、個数が多くなり、処理時間を短くしたいです。

実現したいこと

B4:Bの数字を一括で取得して、5日前という処理を一括で対応して、土日になった場合は、個別で処理をするという対応にしたいです

発生している問題・エラーメッセージ

function syuryobi_medal() {

  var dayOfWeekStr = ["日", "月", "火", "水", "木", "金", "土"];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート2");
  var columnBVals = sheet2.getRange('B:B').getValues(); // B列値を配列で取得
  var lastRow = columnBVals.filter(String).length;
  var now = new Date();

    now.setDate(now.getDate());
    var d5 = Utilities.formatDate(now, 'Asia/Tokyo', 'yyyy-MM-dd ');
    sheet2.getRange(1, 2).setValue(d5)

for (var i = 4; i <= lastRow; i++) {

    var sheetDate = sheet2.getRange(i, 2); // 日付のセルを読み込む
    var dt_s = sheet2.getRange(i, 2).getValue();
    var sheetDayOfWeek = dayOfWeekStr[sheetDate.getValue().getDay()];

    var Friday = dayOfWeekStr[5]
    var Thursday = dayOfWeekStr[4]
    
    var dt = new Date(dt_s);

    //5営業日前
    if (sheetDayOfWeek === Friday) {
      var dt_5 = dt.setDate(dt.getDate() - 7);
      var s5 = Utilities.formatDate(dt, 'Asia/Tokyo', 'yyyy-MM-dd ');
      sheet2.getRange(i, 3).setValue(s5)
    }
    else if (sheetDayOfWeek === Thursday) {
      var dt_5 = dt.setDate(dt.getDate() - 6);
      var s5 = Utilities.formatDate(dt, 'Asia/Tokyo', 'yyyy-MM-dd ');
      sheet2.getRange(i, 3).setValue(s5)
    }
    else {
      var dt_5 = dt.setDate(dt.getDate() - 5);
      var s5 = Utilities.formatDate(dt, 'Asia/Tokyo', 'yyyy-MM-dd ');
      sheet2.getRange(i, 3).setValue(s5)
    }
  }

}

該当のソースコード

ソースコード

試したこと

mapで書いたりしましたが、うまく動きませんでした。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

行動規範の内容に同意します

回答2件

処理方法の違い

質問者様の処理方法
- B列の日付を一括で取得
- 1つずつ5営業日前を算出
- 1つずつC列に書き込み
私のコードの処理方法
- B列の日付を一括で取得
- 1つずつ5営業日前を算出し、2次元配列に格納
- 一括でC列に書き込み

コード例

土曜日と日曜日をお休みとしています。
B列の日付が木曜日の場合は6日、金曜日の場合は7日、それ以外は5日を引くと、土日に被らない5営業日前を算出できます。

javascript
1function write5DaysBefore() {
2  const ss = SpreadsheetApp.getActiveSpreadsheet();
3  const sheet = ss.getActiveSheet();
4
5  // B列の日付を一括で取得
6  const values = sheet.getRange('B4:B').getValues().filter(([dt]) => dt);
7
8  // -5営業日の新しい配列を生成
9  const newValues = values.map(([dt]) => {
10    const day = dt.getDay(); // 対応日の曜日を取得
11    const sub = day === 4 ? 6 : day === 5 ? 7 : 5; // 何日引くかの日数の算定。木曜日なら6日、金曜日なら7日、それ以外は5日とする
12    const date = new Date(dt).setDate(dt.getDate() - sub); // 対応日から5or6or7日引く
13
14    return [new Date(date)]; // 算出された日付を配列にいれて戻す（2次元配列になるようにするため）
15  });
16
17  // C列に一括で値を書き込む
18  sheet.getRange(4, 3, newValues.length, 1).setValues(newValues);
19}

投稿2023/01/14 08:11

編集2023/01/14 08:28

Cocode

総合スコア2314

ai39

2023/01/14 09:29

無事動かすことができました！ただ処理速度が他の方のほうが早く、、大変申し訳無いのですが、、他の方をベストアンサーにさせていただきます…！ご回答いただいてとても助かりました！前回に引き続きありがとうございました！

行動規範の内容に同意します

ベストアンサー

変更可能な変数 dt にDateオブジェクトが入っているとして、

dtの日付を5日前にしてから、その（５日前に変更後の）日付が土曜か日曜であるときは、さらに最も近い過去の金曜の日付にする。

という処理は以下のように書けます。

javascript
1dt = new Date(dt - 5 * 24*60*60*1000); // 5日前の日付にして、
2if (dt.getDay() % 6 === 0) { // それが土曜か日曜の場合には、
3    dt.setDate(dt.getDate() - (dt.getDay()+2)%7);  // 最も近い過去の金曜の日付にする。
4}

参考までに上記のコードの使用例を挙げます。
たとえば下記のスプレッドシート

のA列にある10個の日付に対して、冒頭の処理を行った日付をB列に出力するコードは以下のように書けます。

javascript
1function myFunction() {
2  const sheet = SpreadsheetApp.getActiveSheet();
3
4  const dates = sheet.getRange(1, 1, 10, 1).getValues();
5
6  const dates2 = dates.map(([dt]) => { 
7    dt = new Date(dt - 5 * 24*60*60*1000); // 5日前の日付にして、
8    if (dt.getDay() % 6 === 0) { // それが土曜か日曜の場合には、
9      dt.setDate(dt.getDate() - (dt.getDay()+2)%7);  // 最も近い過去の金曜の日付にする。
10    }
11    return [dt];
12  });
13
14  sheet.getRange(1, 2, 10, 1).setValues(dates2);
15}