Google Apps Scriptを使ってメールの☆レポートを集計してみた

毎日、メールで届く☆レポードのメールから☆を付けた人を抽出し、どれだけ☆を付けてくれたかをスプレッドシートに出力するGoogle Apps Scriptを作ってみました。

☆レポート抽出プログラム作成

準備するもの

1. Googleアカウント
2. Gメールに☆レポートのメールを受信している
3. スプレッドシート

あくまでもGメールからの抽出を想定しているので、ヤフーメールの場合は、Outlookやサンダーバードなどのメーラー経由で、Gメールに☆レポートのメールをコピーしてください。なお、未読のメールのものから抽出する仕様となっているので、既読の場合は、集計されません。

作業手順

Gメールに☆レポートというラベルを作成し、そこに☆レポートのメールを移します。

スプレッドシートを開き、一行目に順にレポート日、ハンドルネーム、☆を設定し、ヘッダー行とします。

スプレッドのメニューから「拡張機能」　→　「Apps Script」を選択します。

↓のコードを貼り付けます。

1. function importHatenaStars() {
2.   const labelName = '☆レポート';
3.   const sheetId = 'スプレッドシートIDに書き換え必要';
4.   const sheetName = 'シート1';
5.   const sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
6.  
7.   const threads = GmailApp.search('label:' + labelName + ' is:unread');
8.  
9.   threads.forEach(thread => {
10.     const messages = thread.getMessages();
11.     messages.forEach(message => {
12.       const body = message.getPlainBody();
13.       const date = message.getDate();
14.       const formattedDate = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
15.  
16.       // 行単位で分割
17.       const lines = body.split(/\r?\n/);
18.  
19.       for (let i = 0; i < lines.length; i++) {
20.         if (lines[i].trim().startsWith('■')) {
21.           let j = i + 2;
22.           while (j < lines.length && lines[j].trim() !== '') {
23.             const line = lines[j].trim();
24.  
25.             // 「(id:...) ☆」の形式を拾う
26.             const match = line.match(/^(.*?)\(id:.*?\)\s+(.+)$/);
27.             if (match) {
28.               const name = match[1].trim();
29.               const starsText = match[2].trim();
30.  
31.               // 「☆数字」or「☆☆☆」のどちらにも対応
32.               let starsCount = 0;
33.  
34.               // まず「☆数字」にマッチするか調べる
35.               const numeric = starsText.match(/☆\s*(\d+)/);
36.               if (numeric) {
37.                 starsCount = parseInt(numeric[1], 10);
38.               } else {
39.                 // 残った文字列から ☆ 記号を全部数える
40.                 const starMarks = (starsText.match(/☆/g) || []).length;
41.                 starsCount = starMarks;
42.               }
43.  
44.               sheet.appendRow([formattedDate, name, starsCount]);
45.             }
46.             j++;
47.           }
48.         }
49.       }
50.       message.markRead();
51.     });
52.   });
53. }
54.  

3行目の

const sheetId = 'スプレッドシートIDに書き換え必要';

については、スプレッドシートを開いているブラウザのアドレスバーのURLのスプレッドシートのID部分で書き換えてください。

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxx/edit?gid=0#gid=0

↑は例ですが、xxxxxxxxxxxxxxxxxxxxxxの部分がスプレッドシートのIDとなるので、そこをコピーして貼り付けてください。シート名を変更の場合は、

const sheetName = 'シート1';

ここも変更したシート名に変更が必要になります。変更後は、Ctrl ＋ Sで保存します。保存後は、上のメニューから「▷ 実行」を押すと、スクリプトの実行が出来ます。初回実行時は、実行確認が表示されるので、

blog.take-it-easy.site

に従ってスクリプトの実行を許可します。

実行完了すると、↑の図のように、日付、ハンドルネーム、☆を付けた数をスプレッドシートに出力します。記事毎に☆を付けられるので、同じ日に同じハンドルネームの人が重複して出力されますが、これは今後の改善点としたいと思います。

注意点

原因は多々あると思いますが、☆の総数は、実際とは少し差があります。おそらくは、☆を取り消した後、また、☆を付けた場合、その日の☆として計上されるのではないかと思いますが、それによって、☆レポートの☆の数と総数が合わないのではと考えていますが、近似値ではあります。あとは、特別な☆は今は計上してませんので、普通の☆のみです。

日毎の集計

新たなシートを作成し、A1セルに

=QUERY('シート1'!A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A ORDER BY A ASC", 1)

とすると、日毎の集計が出来ます。並び順は古い日付からです。

人毎の集計

新たなシートを作成し、A1セルに

=QUERY('シート1'!A:C, "SELECT B, SUM(C) WHERE B IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC", 1)

とすると、人毎の集計が出来ます。並び順は、☆を多く付けた人の順となります。☆を多く付けている方には当然ではありますが、必ずお返しをするようにしましょう。（笑）

まとめ

今回は、Google Apps Scriptを使って、☆レポートのメールから☆を付けた人を抽出し、スプレッドシートで一覧への出力と、集計を実行しました。基本的にプログラムはchatgptに書かせています。仕様を明確に伝えれば、ほぼ正しく動作するプログラムを書いてはくれますが、バグもあるので、そこは補ってあげる必要があります。バグがあった場合は、出力された結果と想定する動作をchatgptに伝えれば修正してくれますので、上手い事chatgptを活用出来れば、プログラムを書く事もそんなに苦にはならないと思いますが、chatgptが書いてくれたプログラムを理解する能力もある程度は必要です。