teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップGoogle スプレッドシートに関する質問GASでのスプレッドシートの個別リスト作成について

編集履歴

回答編集履歴

9

コメントを受けて追加

2021/05/23 01:54

投稿

退会済みユーザー
answer CHANGED
@@ -1,9 +1,16 @@
1
1
  ソースは下記のようになります。(reply()関数)
2
-
2
+ **[05/23 10:40] getSchedTime()関数を追加、reply関数の後半を修正**
3
3
  ※フォームに紐付けたスプレッドシートのメニューの ファイル→「スプレッドシートの設定」で、
4
4
  タイムゾーンを「(GMT+0900) Tokyo」に設定しておくこと）
5
- ```js
5
+ ```diff
6
6
 
7
+ function getSchedTime(date, time) {
8
+ return new Date(
9
+ Utilities.formatDate(new Date(date), "JST", "yyyy-MM-dd") +
10
+ " " + Utilities.formatDate(new Date(time), "JST", "HH:mm") + "+09:00"
11
+ );
12
+ }
13
+
7
14
  function reply(data) {
8
15
  var postMsg = data.message.text;
9
16
  var replyToken = data.replyToken;
@@ -19,19 +26,23 @@
19
26
  var spreadsheet = SpreadsheetApp.openById("XXXXXXXXXX");
20
27
  var sheet = spreadsheet.getActiveSheet();
21
28
 
22
- var today =new Date(Utilities.formatDate(new Date(),"JST", "yyyy-MM-dd 00:00:00+09:00"))
29
+ var today = new Date(Utilities.formatDate(new Date(),"JST", "yyyy-MM-dd 00:00:00+09:00"))
23
30
  // スプレッドシートのデータを一括取得
24
- var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 4).getValues();
31
+ var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 7).getValues();
25
32
  // ユーザID が一致しかつ当日以降のものだけ抽出し、日付順に並び替え。
26
33
  var records = allRecords
27
- .filter(e => e[3] == userId && e[2] >= today)
34
+ .filter(e => e[3] == userId && e[2] >= today)
28
- .sort((a,b) => a[2] - b[2])
35
+ .sort((a, b) => getSchedTime(a[2], a[6]) - getSchedTime(b[2], b[6]));
29
-
36
+
30
37
  // 日付をフォーマットし、予定と一緒に配列化。
31
- var value = records.map(e =>
38
+ var value = records.map(e => {
32
- `・${e[1]}（${Utilities.formatDate(e[2], "JST", "yyyy/MM/dd")}）\n\n`);
39
+ var date = Utilities.formatDate(new Date(e[2]), "JST", "yyyy/MM/dd");
40
+ var time = Utilities.formatDate(new Date(e[6]), "JST", "HH:mm");
41
+ return `・${e[1]} (${date} ${time})\n\n`
42
+ });
33
43
 
34
- replyText = value.join("") + "忘れないようにしましょう！";
44
+ if (value.length > 0) replyText = value.join("") + "忘れないようにしましょう！";
45
+ else replyText = "予定がありません。";
35
46
  }
36
47
 
37
48
  【以下略】
@@ -77,15 +88,12 @@
77
88
 
78
89
  ```
79
90
  var records = allRecords
80
- .filter(e => e[3] == userId && e[2] > today)
91
+ .filter(e => e[3] == userId && e[2] >= today)
81
- .sort((a,b) => a[2] - b[2])
92
+ .sort(～～略～～)
93
+
82
94
  ```
83
- この「e => e[3] == userId」の部分が「D列が、その予定を呼び出したユーザーＩＤと等しいかどうか」を比較している部分になります。
95
+ この「e[3] == userId」の部分が「D列が、その予定を呼び出したユーザーＩＤと等しいかどうか」を比較している部分になります。
84
- 　
85
- 元のコードでは、1行1行スプレッドシートにアクセスしており、データが多くなるとスピードが遅くなりそうだったので、
86
- データをあらかじめ配列に一括格納し、filterとsortを使用して選択・並び替えています。
87
96
 
88
- 予定の文章の組み立て部分もmapを利用してすっきりした形にしてみました。
89
97
 
90
98
  -----
91
99
 
@@ -95,23 +103,43 @@
95
103
  フォーム入力時に、useridを変えられたり削除されたりしてしまうと、上記の仕組みがうまく動きません。
96
104
  →これは、userid項目を隠すことで対応できますが、隠すにはフォームを自作する必要があり、難易度が高いです。
97
105
  現状、「useridを変えたり消したりすると動作しません」と注意喚起しておく必要があります。
98
-
99
106
  　
100
- ・今後実装できると面白そうなこと
101
- 　　登録済み予定の変更・削除
102
- 　　メモ機能
103
- など
104
-
105
107
  -----
106
108
  　
107
109
  　
108
- コメントでの依頼分：当日も含めて表示されるようにとのことだったので日付取得部分を下記のように変更しました。
110
+ コメントでの依頼分：当日も含めて表示&時刻追加とのことだったので日付取得部分を下記のように変更しました。
111
+ (回答前半に記載コードを参照)
109
112
  ```js
113
+ // getSchedTime()関数を追加
114
+ function getSchedTime(date, time) {
115
+ return new Date(
116
+ Utilities.formatDate(new Date(date), "JST", "yyyy-MM-dd") +
110
- var today =new Date(Utilities.formatDate(new Date(), "JST", "yyyy-MM-dd 00:00:00+09:00"))
117
+ " " + Utilities.formatDate(new Date(time), "JST", "HH:mm") + "+09:00"
118
+ );
119
+ }
120
+
121
+ //reply関数後半を変更
122
+ function reply(data) {
123
+ 【略】
124
+ ...
125
+ ...
111
- // スプレッドシートのデータを一括取得
126
+ // スプレッドシートのデータを一括取得 (時刻データがある７列目まで取得するように修正）
112
- var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 4).getValues();
127
+ var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 7).getValues();
113
- // ユーザID が一致しかつ当日以降（当日含む）の項目だけ抽出し、日付順に並び替え。
128
+ // ユーザID が一致しかつ当日以降のものだけ抽出し、日付順に並び替え。
114
129
  var records = allRecords
115
- .filter(e => e[3] == userId && e[2] >= today)
130
+ .filter(e => e[3] == userId && e[2] >= today)
116
- .sort((a,b) => a[2] - b[2])
131
+ .sort((a, b) => getSchedTime(a[2], a[6]) - getSchedTime(b[2], b[6]));
132
+
133
+ // 日付をフォーマットし、予定と一緒に配列化。
134
+ var value = records.map(e => {
135
+ var date = Utilities.formatDate(new Date(e[2]), "JST", "yyyy/MM/dd");
136
+ var time = Utilities.formatDate(new Date(e[6]), "JST", "HH:mm");
137
+ return `・${e[1]} (${date} ${time})\n\n`
138
+ });
139
+ if (value.length > 0) replyText = value.join("") + "忘れないようにしましょう！";
140
+ else replyText = "予定がありません。";
141
+ }
142
+
143
+ 【以下略】
144
+ ...
117
145
  ```

8

修正

2021/05/23 01:54

投稿

退会済みユーザー
answer CHANGED
@@ -24,7 +24,7 @@
24
24
  var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 4).getValues();
25
25
  // ユーザID が一致しかつ当日以降のものだけ抽出し、日付順に並び替え。
26
26
  var records = allRecords
27
- .filter(e => e[2] >= today)
27
+ .filter(e => e[3] == userId && e[2] >= today)
28
28
  .sort((a,b) => a[2] - b[2])
29
29
 
30
30
  // 日付をフォーマットし、予定と一緒に配列化。
@@ -112,6 +112,6 @@
112
112
  var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 4).getValues();
113
113
  // ユーザID が一致しかつ当日以降（当日含む）の項目だけ抽出し、日付順に並び替え。
114
114
  var records = allRecords
115
- .filter(e => e[2] >= today)
115
+ .filter(e => e[3] == userId && e[2] >= today)
116
116
  .sort((a,b) => a[2] - b[2])
117
117
  ```

7

2021/05/22 11:49

投稿

退会済みユーザー
answer CHANGED
@@ -1,5 +1,7 @@
1
1
  ソースは下記のようになります。(reply()関数)
2
2
 
3
+ ※フォームに紐付けたスプレッドシートのメニューの ファイル→「スプレッドシートの設定」で、
4
+ タイムゾーンを「(GMT+0900) Tokyo」に設定しておくこと）
3
5
  ```js
4
6
 
5
7
  function reply(data) {

6

変更

2021/05/22 09:47

投稿

退会済みユーザー
answer CHANGED
@@ -1,4 +1,5 @@
1
1
  ソースは下記のようになります。(reply()関数)
2
+
2
3
  ```js
3
4
 
4
5
  function reply(data) {
@@ -16,13 +17,13 @@
16
17
  var spreadsheet = SpreadsheetApp.openById("XXXXXXXXXX");
17
18
  var sheet = spreadsheet.getActiveSheet();
18
19
 
19
- var today = new Date();
20
+ var today =new Date(Utilities.formatDate(new Date(),"JST", "yyyy-MM-dd 00:00:00+09:00"))
20
21
  // スプレッドシートのデータを一括取得
21
22
  var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 4).getValues();
22
23
  // ユーザID が一致しかつ当日以降のものだけ抽出し、日付順に並び替え。
23
24
  var records = allRecords
24
- .filter(e => e[3] == userId && e[2] > today)
25
+ .filter(e => e[2] >= today)
25
- .sort((a, b) => a[2] - b[2])
26
+ .sort((a,b) => a[2] - b[2])
26
27
 
27
28
  // 日付をフォーマットし、予定と一緒に配列化。
28
29
  var value = records.map(e =>
@@ -97,4 +98,18 @@
97
98
  ・今後実装できると面白そうなこと
98
99
  　　登録済み予定の変更・削除
99
100
  　　メモ機能
100
- など
101
+ など
102
+
103
+ -----
104
+ 　
105
+ 　
106
+ コメントでの依頼分：当日も含めて表示されるようにとのことだったので日付取得部分を下記のように変更しました。
107
+ ```js
108
+ var today =new Date(Utilities.formatDate(new Date(), "JST", "yyyy-MM-dd 00:00:00+09:00"))
109
+ // スプレッドシートのデータを一括取得
110
+ var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 4).getValues();
111
+ // ユーザID が一致しかつ当日以降（当日含む）の項目だけ抽出し、日付順に並び替え。
112
+ var records = allRecords
113
+ .filter(e => e[2] >= today)
114
+ .sort((a,b) => a[2] - b[2])
115
+ ```

5

修正

2021/05/22 09:33

投稿

退会済みユーザー
answer CHANGED
@@ -1,4 +1,4 @@
1
- ソースは下記のようになります。(reply()関数）
1
+ ソースは下記のようになります。(reply()関数)
2
2
  ```js
3
3
 
4
4
  function reply(data) {
@@ -15,19 +15,18 @@
15
15
  } else if (postMsg == "今後の予定") {
16
16
  var spreadsheet = SpreadsheetApp.openById("XXXXXXXXXX");
17
17
  var sheet = spreadsheet.getActiveSheet();
18
-
19
-
18
+
20
19
  var today = new Date();
21
20
  // スプレッドシートのデータを一括取得
22
21
  var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 4).getValues();
23
22
  // ユーザID が一致しかつ当日以降のものだけ抽出し、日付順に並び替え。
24
23
  var records = allRecords
25
24
  .filter(e => e[3] == userId && e[2] > today)
26
- .sort((a,b) => a[2] - b[2])
25
+ .sort((a, b) => a[2] - b[2])
27
26
 
28
27
  // 日付をフォーマットし、予定と一緒に配列化。
29
28
  var value = records.map(e =>
30
- `・${Utilities.formatDate(e[2], "JST", "yyyy/MM/dd")}（${e[1]}）\n\n`);
29
+ `・${e[1]}（${Utilities.formatDate(e[2], "JST", "yyyy/MM/dd")}）\n\n`);
31
30
 
32
31
  replyText = value.join("") + "忘れないようにしましょう！";
33
32
  }

4

修正

2021/05/21 16:16

投稿

退会済みユーザー
answer CHANGED
@@ -27,7 +27,7 @@
27
27
 
28
28
  // 日付をフォーマットし、予定と一緒に配列化。
29
29
  var value = records.map(e =>
30
- `・${Utilities.formatDate(e[2], "JST", "yyyy/MM/dd")}（${e[3]}）\n\n`);
30
+ `・${Utilities.formatDate(e[2], "JST", "yyyy/MM/dd")}（${e[1]}）\n\n`);
31
31
 
32
32
  replyText = value.join("") + "忘れないようにしましょう！";
33
33
  }

3

修正

2021/05/21 16:10

投稿

退会済みユーザー
answer CHANGED
@@ -51,6 +51,9 @@
51
51
  LINEのユーザIDがあらかじめ記入されたフォームを呼び出すことが可能になります。
52
52
  (下記の部分です）
53
53
  ```javascript
54
+ 略
55
+ var userId = data.source.userId;
56
+ 略
54
57
  if (postMsg == "登録") {
55
58
  // entry.zzzzzzはユーザーIDのフォームフィールドの番号。
56
59
  replyText =

2

修正

2021/05/21 16:00

投稿

退会済みユーザー
answer CHANGED
@@ -11,7 +11,7 @@
11
11
  // entry.zzzzzzはユーザーIDのフォームフィールドの番号。
12
12
  replyText =
13
13
  "予定の登録はこちら\n" +
14
- `https://docs.google.com/forms/d/1XXXXXXXXXX/viewform?entry.zzzzzz=${userId}`;
14
+ `https://docs.google.com/forms/d/1XXXXXXXXXX/viewform?usp=pp_url&entry.zzzzzz=${userId}`;
15
15
  } else if (postMsg == "今後の予定") {
16
16
  var spreadsheet = SpreadsheetApp.openById("XXXXXXXXXX");
17
17
  var sheet = spreadsheet.getActiveSheet();
@@ -55,7 +55,7 @@
55
55
  // entry.zzzzzzはユーザーIDのフォームフィールドの番号。
56
56
  replyText =
57
57
  "予定の登録はこちら\n" +
58
- `https://docs.google.com/forms/d/1XXXXXXXXXX/viewform?entry.zzzzzz=${userId}`;
58
+ `https://docs.google.com/forms/d/1XXXXXXXXXX/viewform?usp=pp_url&entry.zzzzzz=${userId}`;
59
59
  }
60
60
  略
61
61
  ```

1

修正

2021/05/21 15:59

投稿

退会済みユーザー
answer CHANGED
@@ -22,7 +22,7 @@
22
22
  var allRecords = sheet.getRange(2, 1, sheet.getLastRow()-1, 4).getValues();
23
23
  // ユーザID が一致しかつ当日以降のものだけ抽出し、日付順に並び替え。
24
24
  var records = allRecords
25
- .filter(e => e[3] == userId && e[3] > today)
25
+ .filter(e => e[3] == userId && e[2] > today)
26
26
  .sort((a,b) => a[2] - b[2])
27
27
 
28
28
  // 日付をフォーマットし、予定と一緒に配列化。
@@ -72,7 +72,7 @@
72
72
 
73
73
  ```
74
74
  var records = allRecords
75
- .filter(e => e[3] == userId && e[3] > today)
75
+ .filter(e => e[3] == userId && e[2] > today)
76
76
  .sort((a,b) => a[2] - b[2])
77
77
  ```
78
78
  この「e => e[3] == userId」の部分が「D列が、その予定を呼び出したユーザーＩＤと等しいかどうか」を比較している部分になります。