質問編集履歴
3
コードの追記
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -6,40 +6,79 @@
|
|
|
6
6
|
|
|
7
7
|
### 前提
|
|
8
8
|
|
|
9
|
-
|
|
9
|
+
APIで取得したデータを使い、配列内のオブジェクトから特定のkeyの値のみの配列を作る、ということをしていました。
|
|
10
|
+
```GAS
|
|
11
|
+
function myFunction() {
|
|
12
|
+
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
|
|
13
|
+
var sheet = spreadsheet.getSheetByName('テスト');
|
|
14
|
+
var values = sheet.getDataRange().getValues();
|
|
15
|
+
var LastRow = sheet.getLastRow();
|
|
16
|
+
|
|
17
|
+
for(let i =1; i<LastRow; i++){
|
|
18
|
+
var ApiUrl = 'https://apiurl' + values[i][0]
|
|
19
|
+
var ApiCell = sheet.getRange(i+1,2);
|
|
20
|
+
var res = UrlFetchApp.fetch(ApiUrl).getContentText();
|
|
21
|
+
var res_json = JSON.parse(res);
|
|
22
|
+
var res_json_result1 = res_json.result;
|
|
23
|
+
var res_json_result2 = res_json_result1.items;
|
|
24
|
+
var res_json_result3 = res_json_result2[0];
|
|
25
|
+
var p_code = res_json_result3.p_code;
|
|
26
|
+
var user = res_json_result3.username;
|
|
27
|
+
var date = res_json_result3.date;
|
|
28
|
+
var StartDate = date.start;
|
|
29
|
+
var info = res_json_result3.iteminfo;
|
|
30
|
+
var Data1Obj = info.data1;
|
|
31
|
+
var Data1 = Data1Obj.map((obj)=>obj.name);
|
|
32
|
+
var Data2Obj = info.data2;
|
|
33
|
+
var Data2 = Data2Obj.map((obj)=>obj.name);
|
|
34
|
+
var Data3Obj = info.data3;
|
|
35
|
+
var Data3 = Data3Obj.map((obj)=>obj.name);
|
|
36
|
+
var Data4Obj = info.data4;
|
|
37
|
+
var Data4 = Data4Obj.map((obj)=>obj.name);
|
|
38
|
+
|
|
39
|
+
console.log(Data1)
|
|
40
|
+
console.log(Data2)
|
|
41
|
+
console.log(Data3)
|
|
42
|
+
console.log(Data4)
|
|
43
|
+
}
|
|
44
|
+
}
|
|
45
|
+
|
|
46
|
+
```
|
|
47
|
+
取得したデータの全体像console.jog(res_json)が以下です。
|
|
48
|
+
|
|
10
49
|
```ここに言語を入力
|
|
50
|
+
{ request:
|
|
11
|
-
{
|
|
51
|
+
{ parameters:
|
|
52
|
+
{ api_id: 'apiid',
|
|
53
|
+
username: 'USERNAME',
|
|
54
|
+
email: 'xyz@example.jp',
|
|
55
|
+
output: 'json' } },
|
|
56
|
+
result:
|
|
57
|
+
{ status: 200,
|
|
58
|
+
result_count: 1,
|
|
59
|
+
items: [ { p_code: 'xxx',
|
|
60
|
+
p_name: '',
|
|
61
|
+
username: 'USERNAME',
|
|
62
|
+
email: 'xyz@example.jp',
|
|
63
|
+
date: { start: '2022-11-5', finish: '2022-11-11' },
|
|
64
|
+
iteminfo: { data1:
|
|
12
|
-
[ { id: 1111, name: '北海道' },
|
|
65
|
+
[ { id: 1111, name: '北海道' },
|
|
13
|
-
{ id: 1112, name: '青森' },
|
|
66
|
+
{ id: 1112, name: '青森' },
|
|
14
|
-
{ id: 1113, name: '岩手' },
|
|
67
|
+
{ id: 1113, name: '岩手' },
|
|
15
|
-
{ id: 1114, name: '秋田' },
|
|
68
|
+
{ id: 1114, name: '秋田' },
|
|
16
|
-
{ id: 1115, name: '福島' },
|
|
69
|
+
{ id: 1115, name: '福島' },
|
|
17
|
-
{ id: 1116, name: '山形' },
|
|
70
|
+
{ id: 1116, name: '山形' },
|
|
18
|
-
{ id: 1117, name: '新潟' } ],
|
|
71
|
+
{ id: 1117, name: '新潟' } ],
|
|
19
|
-
data2: [ { id: 4600, name: '北' } ],
|
|
72
|
+
data2: [ { id: 4600, name: '北' } ],
|
|
20
|
-
data3:
|
|
73
|
+
data3:
|
|
21
|
-
[ { id: 1111018, name: '北海道', ruby: 'ほっかいどう' },
|
|
74
|
+
[ { id: 1111018, name: '北海道', ruby: 'ほっかいどう' },
|
|
22
|
-
{ id: 1112018, name: '青森', ruby: 'あおもり' },
|
|
75
|
+
{ id: 1112018, name: '青森', ruby: 'あおもり' },
|
|
23
|
-
{ id: 1113018, name: '岩手', ruby: 'いわて' },
|
|
76
|
+
{ id: 1113018, name: '岩手', ruby: 'いわて' },
|
|
24
|
-
{ id: 1114018, name: '秋田', ruby: 'あきた' } ],
|
|
77
|
+
{ id: 1114018, name: '秋田', ruby: 'あきた' } ],
|
|
25
|
-
data4: [ { id: 7020, name: '東北' } ] }
|
|
78
|
+
data4: [ { id: 7020, name: '東北' } ] }
|
|
26
79
|
```
|
|
27
|
-
上記データを var info とし、配列内の各オブジェクトから、nameの値の配列を作るため
|
|
28
|
-
```GAS
|
|
29
|
-
var Data1Obj = info.data1;
|
|
30
|
-
var Data1 = Data1Obj.map((obj)=>obj.name);
|
|
31
|
-
var Data2Obj = info.data2;
|
|
32
|
-
var Data2 = Data2Obj.map((obj)=>obj.name);
|
|
33
|
-
var Data3Obj = info.data3;
|
|
34
|
-
var Data3 = Data3Obj.map((obj)=>obj.name);
|
|
35
|
-
var Data4Obj = info.data4;
|
|
36
|
-
var Data4 = Data4Obj.map((obj)=>obj.name);
|
|
37
80
|
|
|
38
|
-
|
|
81
|
+
|
|
39
|
-
console.log(Data2)
|
|
40
|
-
console.log(Data3)
|
|
41
|
-
console.log(Data4)
|
|
42
|
-
```
|
|
43
82
|
上記のようにしたところ、ログがそれぞれ
|
|
44
83
|
[ '北海道', '青森', '岩手', '秋田', '福島', '山形', '新潟' ]
|
|
45
84
|
[ '北' ]
|
|
@@ -61,19 +100,41 @@
|
|
|
61
100
|
### 該当のソースコード
|
|
62
101
|
|
|
63
102
|
```GAS
|
|
103
|
+
function myFunction() {
|
|
104
|
+
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
|
|
105
|
+
var sheet = spreadsheet.getSheetByName('テスト');
|
|
106
|
+
var values = sheet.getDataRange().getValues();
|
|
64
|
-
var
|
|
107
|
+
var LastRow = sheet.getLastRow();
|
|
65
|
-
var Data1 = Data1Obj.map((obj)=>obj.name);
|
|
66
|
-
var Data2Obj = info.data2;
|
|
67
|
-
var Data2 = Data2Obj.map((obj)=>obj.name);
|
|
68
|
-
var Data3Obj = info.data3;
|
|
69
|
-
var Data3 = Data3Obj.map((obj)=>obj.name);
|
|
70
|
-
var Data4Obj = info.data4;
|
|
71
|
-
var Data4 = Data4Obj.map((obj)=>obj.name);
|
|
72
108
|
|
|
109
|
+
for(let i =1; i<LastRow; i++){
|
|
110
|
+
var ApiUrl = 'https://apiurl' + values[i][0]
|
|
111
|
+
var ApiCell = sheet.getRange(i+1,2);
|
|
112
|
+
var res = UrlFetchApp.fetch(ApiUrl).getContentText();
|
|
113
|
+
var res_json = JSON.parse(res);
|
|
114
|
+
var res_json_result1 = res_json.result;
|
|
115
|
+
var res_json_result2 = res_json_result1.items;
|
|
116
|
+
var res_json_result3 = res_json_result2[0];
|
|
117
|
+
var p_code = res_json_result3.p_code;
|
|
118
|
+
var user = res_json_result3.username;
|
|
119
|
+
var date = res_json_result3.date;
|
|
120
|
+
var StartDate = date.start;
|
|
121
|
+
var info = res_json_result3.iteminfo;
|
|
122
|
+
var Data1Obj = info.data1;
|
|
123
|
+
var Data1 = Data1Obj.map((obj)=>obj.name);
|
|
124
|
+
var Data2Obj = info.data2;
|
|
125
|
+
var Data2 = Data2Obj.map((obj)=>obj.name);
|
|
126
|
+
var Data3Obj = info.data3;
|
|
127
|
+
var Data3 = Data3Obj.map((obj)=>obj.name);
|
|
128
|
+
var Data4Obj = info.data4;
|
|
129
|
+
var Data4 = Data4Obj.map((obj)=>obj.name);
|
|
130
|
+
|
|
73
|
-
console.log(Data1)
|
|
131
|
+
console.log(Data1)
|
|
74
|
-
console.log(Data2)
|
|
132
|
+
console.log(Data2)
|
|
75
|
-
console.log(Data3)
|
|
133
|
+
console.log(Data3)
|
|
76
|
-
console.log(Data4)
|
|
134
|
+
console.log(Data4)
|
|
135
|
+
}
|
|
136
|
+
}
|
|
137
|
+
|
|
77
138
|
```
|
|
78
139
|
|
|
79
140
|
### 試したこと
|
2
)の修正
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -27,13 +27,13 @@
|
|
|
27
27
|
上記データを var info とし、配列内の各オブジェクトから、nameの値の配列を作るため
|
|
28
28
|
```GAS
|
|
29
29
|
var Data1Obj = info.data1;
|
|
30
|
-
var Data1 = Data1Obj.map((obj)
|
|
30
|
+
var Data1 = Data1Obj.map((obj)=>obj.name);
|
|
31
31
|
var Data2Obj = info.data2;
|
|
32
|
-
var Data2 = Data2Obj.map((obj)
|
|
32
|
+
var Data2 = Data2Obj.map((obj)=>obj.name);
|
|
33
33
|
var Data3Obj = info.data3;
|
|
34
|
-
var Data3 = Data3Obj.map((obj)
|
|
34
|
+
var Data3 = Data3Obj.map((obj)=>obj.name);
|
|
35
35
|
var Data4Obj = info.data4;
|
|
36
|
-
var Data4 = Data4Obj.map((obj)
|
|
36
|
+
var Data4 = Data4Obj.map((obj)=>obj.name);
|
|
37
37
|
|
|
38
38
|
console.log(Data1)
|
|
39
39
|
console.log(Data2)
|
|
@@ -62,13 +62,13 @@
|
|
|
62
62
|
|
|
63
63
|
```GAS
|
|
64
64
|
var Data1Obj = info.data1;
|
|
65
|
-
var Data1 = Data1Obj.map((obj)
|
|
65
|
+
var Data1 = Data1Obj.map((obj)=>obj.name);
|
|
66
66
|
var Data2Obj = info.data2;
|
|
67
|
-
var Data2 = Data2Obj.map((obj)
|
|
67
|
+
var Data2 = Data2Obj.map((obj)=>obj.name);
|
|
68
68
|
var Data3Obj = info.data3;
|
|
69
|
-
var Data3 = Data3Obj.map((obj)
|
|
69
|
+
var Data3 = Data3Obj.map((obj)=>obj.name);
|
|
70
70
|
var Data4Obj = info.data4;
|
|
71
|
-
var Data4 = Data4Obj.map((obj)
|
|
71
|
+
var Data4 = Data4Obj.map((obj)=>obj.name);
|
|
72
72
|
|
|
73
73
|
console.log(Data1)
|
|
74
74
|
console.log(Data2)
|
1
誤字修正
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -78,7 +78,7 @@
|
|
|
78
78
|
|
|
79
79
|
### 試したこと
|
|
80
80
|
|
|
81
|
-
|
|
81
|
+
合っているかわからないのですが、エラーになっている部分を
|
|
82
82
|
```GAS
|
|
83
83
|
var Data3 = Data3Obj.find((item) => item.name);
|
|
84
84
|
```
|