回答編集履歴
3
a
test
CHANGED
|
@@ -109,3 +109,63 @@
|
|
|
109
109
|
}
|
|
110
110
|
|
|
111
111
|
```
|
|
112
|
+
|
|
113
|
+
|
|
114
|
+
|
|
115
|
+
自己満足
|
|
116
|
+
|
|
117
|
+
```javascript
|
|
118
|
+
|
|
119
|
+
function AutoOrder_MainProtest2() {
|
|
120
|
+
|
|
121
|
+
const infoSheetName = 'info';
|
|
122
|
+
|
|
123
|
+
const colA_classify = 0;
|
|
124
|
+
|
|
125
|
+
const colH_state = 7;
|
|
126
|
+
|
|
127
|
+
const colF_startdate = 5;
|
|
128
|
+
|
|
129
|
+
const notYet = '未発注';
|
|
130
|
+
|
|
131
|
+
const span = 6;
|
|
132
|
+
|
|
133
|
+
|
|
134
|
+
|
|
135
|
+
const dat = SpreadsheetApp.getActive().getSheetByName(infoSheetName).getDataRange().getValues().filter(function(e) { return e[colH_state] === notYet ; });
|
|
136
|
+
|
|
137
|
+
dat.forEach(function (e) {
|
|
138
|
+
|
|
139
|
+
var startdate = e[colF_startdate];
|
|
140
|
+
|
|
141
|
+
var enddate = getNdaysAfter(startdate, span);
|
|
142
|
+
|
|
143
|
+
var startdateAsString = "開始日：" + formatDate(startdate);
|
|
144
|
+
|
|
145
|
+
var enddateAsString = "終了日:" + formatDate(enddate);
|
|
146
|
+
|
|
147
|
+
});
|
|
148
|
+
|
|
149
|
+
}
|
|
150
|
+
|
|
151
|
+
function getNdaysAfter(date, num) {
|
|
152
|
+
|
|
153
|
+
const ret = new Date(date.getFullYear(), date.getMonth(), date.getDate());
|
|
154
|
+
|
|
155
|
+
ret.setDate(ret.getDate() + num);
|
|
156
|
+
|
|
157
|
+
return ret;
|
|
158
|
+
|
|
159
|
+
}
|
|
160
|
+
|
|
161
|
+
function formatDate(date) {
|
|
162
|
+
|
|
163
|
+
const formattedDate = Utilities.formatDate(date, 'Asia/Tokyo', 'MM/dd');
|
|
164
|
+
|
|
165
|
+
const day = ["日", "月", "火", "水", "木", "金", "土"][date.getDay()];
|
|
166
|
+
|
|
167
|
+
return formattedDate + "(" + day + ")";
|
|
168
|
+
|
|
169
|
+
}
|
|
170
|
+
|
|
171
|
+
```
|
2
a
test
CHANGED
|
@@ -30,7 +30,7 @@
|
|
|
30
30
|
|
|
31
31
|
var ret = CountDay(num, StartDate);
|
|
32
32
|
|
|
33
|
-
|
|
33
|
+
StartDate = ret[0];
|
|
34
34
|
|
|
35
35
|
var EndDate = ret[1];
|
|
36
36
|
|
1
a
test
CHANGED
|
@@ -5,3 +5,107 @@
|
|
|
5
5
|
|
|
6
6
|
|
|
7
7
|
従って、`var StartDate = Utilities.formatDate(StartDate, 'Asia/Tokyo', 'MM/dd');` および `var EndDate = Utilities.formatDate(EndDate, 'Asia/Tokyo', 'MM/dd');` と小文字にすれば意図した日付になりそうです。
|
|
8
|
+
|
|
9
|
+
|
|
10
|
+
|
|
11
|
+
```javascript
|
|
12
|
+
|
|
13
|
+
function AutoOrder_MainProtest2() {
|
|
14
|
+
|
|
15
|
+
const Book = SpreadsheetApp.getActive();
|
|
16
|
+
|
|
17
|
+
const InfoSheet = Book.getSheetByName("info"); //発注詳細を格納するシート
|
|
18
|
+
|
|
19
|
+
const ClassValue = InfoSheet.getRange("A:A").getValues(); //発注の分類
|
|
20
|
+
|
|
21
|
+
const OrderStatus = InfoSheet.getRange("H:H").getValues(); //発注の状態(未発注,発注済み)
|
|
22
|
+
|
|
23
|
+
|
|
24
|
+
|
|
25
|
+
for (var num = 0; num < ClassValue.length; num++) {
|
|
26
|
+
|
|
27
|
+
if (OrderStatus[num] == "未発注") { //未発注のみ処理を行う
|
|
28
|
+
|
|
29
|
+
var StartDate = new Date(InfoSheet.getRange(num + 1, 6).getValue()); //一括処理を行うため、F列は開始日が記入されている
|
|
30
|
+
|
|
31
|
+
var ret = CountDay(num, StartDate);
|
|
32
|
+
|
|
33
|
+
var StartDate = ret[0];
|
|
34
|
+
|
|
35
|
+
var EndDate = ret[1];
|
|
36
|
+
|
|
37
|
+
}
|
|
38
|
+
|
|
39
|
+
}
|
|
40
|
+
|
|
41
|
+
}
|
|
42
|
+
|
|
43
|
+
function CountDay(num, StartDate) {
|
|
44
|
+
|
|
45
|
+
const Month_TypeA = [1, 3, 5, 7, 8, 10, 12]; //31日の月
|
|
46
|
+
|
|
47
|
+
const Month_TypeB = [4, 6, 9, 11]; //30日の月
|
|
48
|
+
|
|
49
|
+
const Month_TypeC = [2]; //2月は29日
|
|
50
|
+
|
|
51
|
+
const YobiList = ["日", "月", "火", "水", "木", "金", "土"]; //getDay()で得た値を曜日に変換するためのリスト
|
|
52
|
+
|
|
53
|
+
|
|
54
|
+
|
|
55
|
+
var StartDay = StartDate.getDate();
|
|
56
|
+
|
|
57
|
+
var year = StartDate.getFullYear();
|
|
58
|
+
|
|
59
|
+
var month = StartDate.getMonth() + 1; //最初のMonthは0のため、+1
|
|
60
|
+
|
|
61
|
+
var StartYobi = StartDate.getDay();
|
|
62
|
+
|
|
63
|
+
var StartDate = Utilities.formatDate(StartDate, 'Asia/Tokyo', 'MM/dd');
|
|
64
|
+
|
|
65
|
+
var EndDay = StartDay + 6;
|
|
66
|
+
|
|
67
|
+
|
|
68
|
+
|
|
69
|
+
if (Month_TypeA.indexOf(month) >= 0 && EndDay > 31) {
|
|
70
|
+
|
|
71
|
+
var EndDay = EndDay - 31;
|
|
72
|
+
|
|
73
|
+
var month = month + 1;
|
|
74
|
+
|
|
75
|
+
if (month == 13) {
|
|
76
|
+
|
|
77
|
+
var year = year + 1; //年越しの場合
|
|
78
|
+
|
|
79
|
+
}
|
|
80
|
+
|
|
81
|
+
} else if (Month_TypeB.indexOf(month) >= 0 && EndDay > 30) {
|
|
82
|
+
|
|
83
|
+
var EndDay = EndDay - 30;
|
|
84
|
+
|
|
85
|
+
var month = month + 1;
|
|
86
|
+
|
|
87
|
+
} else if (Month_TypeC.indexOf(month) >= 0 && EndDay > 29) {
|
|
88
|
+
|
|
89
|
+
var EndDay = EndDay - 29;
|
|
90
|
+
|
|
91
|
+
var month = month + 1;
|
|
92
|
+
|
|
93
|
+
}
|
|
94
|
+
|
|
95
|
+
var EndDate = new Date(year, month - 1, EndDay);
|
|
96
|
+
|
|
97
|
+
var EndYobi = EndDate.getDay();
|
|
98
|
+
|
|
99
|
+
var EndDate = Utilities.formatDate(EndDate, 'Asia/Tokyo', 'MM/dd');
|
|
100
|
+
|
|
101
|
+
|
|
102
|
+
|
|
103
|
+
Logger.log("開始日:" + StartDate + "(" + YobiList[StartYobi] + ")");
|
|
104
|
+
|
|
105
|
+
Logger.log("終了日:" + EndDate + "(" + YobiList[EndYobi] + ")");
|
|
106
|
+
|
|
107
|
+
return [StartDate, EndDate];
|
|
108
|
+
|
|
109
|
+
}
|
|
110
|
+
|
|
111
|
+
```
|