回答編集履歴
4
修正
test
CHANGED
|
@@ -57,7 +57,7 @@
|
|
|
57
57
|
}
|
|
58
58
|
array2.push(array1);
|
|
59
59
|
}
|
|
60
|
-
s
|
|
60
|
+
shB.getRange((h * 35) + 3, 2, sheetB_Date_Flat.length, sheetB_Offer_Flat.length).setValues(array2);
|
|
61
61
|
}
|
|
62
62
|
}
|
|
63
63
|
```
|
3
変更
test
CHANGED
|
@@ -1,4 +1,4 @@
|
|
|
1
|
-
|
|
1
|
+
「setValue」を何度も行うことに時間がかかっていると思うのでこういった感じにしたら多少早くなると思います。
|
|
2
2
|
```ここに言語を入力
|
|
3
3
|
function GenerateDailyOfferReport() {
|
|
4
4
|
|
|
@@ -36,9 +36,6 @@
|
|
|
36
36
|
const ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
37
37
|
const sheetA = ss.getSheetByName("シートA").getDataRange().getValues();
|
|
38
38
|
const shB = ss.getSheetByName("シートB");
|
|
39
|
-
|
|
40
|
-
let array1 = [];
|
|
41
|
-
let array2 = [];
|
|
42
39
|
|
|
43
40
|
for(let h = 0; h <= 8; h++){
|
|
44
41
|
const sheetB_Date = shB.getRange((h * 35) + 3,1,1,31).getValues();
|
|
@@ -47,8 +44,11 @@
|
|
|
47
44
|
const sheetB_Date_Flat = sheetB_Date.flat();
|
|
48
45
|
const sheetB_Campaign_Flat = sheetB_Campaign.flat();
|
|
49
46
|
const sheetB_Offer_Flat = sheetB_Offer.flat();
|
|
47
|
+
|
|
48
|
+
const array2 = [];
|
|
50
49
|
|
|
51
50
|
for(let i = 0; i < sheetB_Date_Flat.length; i++){
|
|
51
|
+
const array1 = [];
|
|
52
52
|
for(let j = 0; j < sheetB_Offer_Flat.length; j++){
|
|
53
53
|
for(let k = 0; k < sheetB_Campaign_Flat.length; k++){
|
|
54
54
|
const arrayFilter = sheetA.filter(value => value[0] == String(sheetB_Date_Flat[i]) && value[1] == 900 && value[2] == sheetB_Offer_Flat[j] && value[3] == sheetB_Campaign_Flat[k]);
|
|
@@ -56,10 +56,8 @@
|
|
|
56
56
|
}
|
|
57
57
|
}
|
|
58
58
|
array2.push(array1);
|
|
59
|
-
array1 = [];
|
|
60
59
|
}
|
|
61
60
|
ss.getSheetByName("シートB").getRange((h * 35) + 3, 2, sheetB_Date_Flat.length, sheetB_Offer_Flat.length).setValues(array2);
|
|
62
|
-
array2 = [];
|
|
63
61
|
}
|
|
64
62
|
}
|
|
65
63
|
```
|
2
追加
test
CHANGED
|
@@ -29,3 +29,37 @@
|
|
|
29
29
|
ss.getSheetByName("シートB").getRange(3 , 2,sheetB_Date_Flat.length,sheetB_Offer_Flat.length).setValues(array2);
|
|
30
30
|
}
|
|
31
31
|
```
|
|
32
|
+
後、35行ずつ同じ動作するのならこんな感じでも
|
|
33
|
+
```ここに言語を入力
|
|
34
|
+
function GenerateDailyOfferReport() {
|
|
35
|
+
|
|
36
|
+
const ss = SpreadsheetApp.getActiveSpreadsheet();
|
|
37
|
+
const sheetA = ss.getSheetByName("シートA").getDataRange().getValues();
|
|
38
|
+
const shB = ss.getSheetByName("シートB");
|
|
39
|
+
|
|
40
|
+
let array1 = [];
|
|
41
|
+
let array2 = [];
|
|
42
|
+
|
|
43
|
+
for(let h = 0; h <= 8; h++){
|
|
44
|
+
const sheetB_Date = shB.getRange((h * 35) + 3,1,1,31).getValues();
|
|
45
|
+
const sheetB_Campaign = shB.getRange(1,3,1,1).getValues();
|
|
46
|
+
const sheetB_Offer = shB.getRange((h * 35) + 2,2,1,26).getValues();
|
|
47
|
+
const sheetB_Date_Flat = sheetB_Date.flat();
|
|
48
|
+
const sheetB_Campaign_Flat = sheetB_Campaign.flat();
|
|
49
|
+
const sheetB_Offer_Flat = sheetB_Offer.flat();
|
|
50
|
+
|
|
51
|
+
for(let i = 0; i < sheetB_Date_Flat.length; i++){
|
|
52
|
+
for(let j = 0; j < sheetB_Offer_Flat.length; j++){
|
|
53
|
+
for(let k = 0; k < sheetB_Campaign_Flat.length; k++){
|
|
54
|
+
const arrayFilter = sheetA.filter(value => value[0] == String(sheetB_Date_Flat[i]) && value[1] == 900 && value[2] == sheetB_Offer_Flat[j] && value[3] == sheetB_Campaign_Flat[k]);
|
|
55
|
+
array1.push(arrayFilter.length);
|
|
56
|
+
}
|
|
57
|
+
}
|
|
58
|
+
array2.push(array1);
|
|
59
|
+
array1 = [];
|
|
60
|
+
}
|
|
61
|
+
ss.getSheetByName("シートB").getRange((h * 35) + 3, 2, sheetB_Date_Flat.length, sheetB_Offer_Flat.length).setValues(array2);
|
|
62
|
+
array2 = [];
|
|
63
|
+
}
|
|
64
|
+
}
|
|
65
|
+
```
|
1
消し忘れ
test
CHANGED
|
@@ -27,6 +27,5 @@
|
|
|
27
27
|
}
|
|
28
28
|
// console.log(array2);
|
|
29
29
|
ss.getSheetByName("シートB").getRange(3 , 2,sheetB_Date_Flat.length,sheetB_Offer_Flat.length).setValues(array2);
|
|
30
|
-
array2=[];
|
|
31
30
|
}
|
|
32
31
|
```
|