[GAS]noteの動きをリアルタイム集計するために通知メールをスプシに自動転記させてみた
夏頃から脳の霧が晴れるように少しづつクリアになり、10月頃には書籍と長文を書くことができるまで復活しました。理由は明確で「仕事を辞めるぞ！」と決めたことです。退職は精神と体調にいいので、クソな職場にいる人は早く辞めるといいよ。辞めなくてもいいから「辞めよ」って決めるだけでも心が晴れやかになり、精神が健やかになり、体の不調がみるみる回復します。
健やかさってどこにあるのだろうか？私はどんな風に今後の人生を過ごしたいのだろうか？と考えることがこの3か月多かったのですが「やろうと思っていることに取り組める」は結構大きな指標です。
いや、やるべきこと（論文とか論文とか論文とか）に全然手をつけていないのに何言ってるの？的な部分はあるのですが、考えること、新しいことを少し調べること、書籍を読むこと、元気だったらなんてことがないことが、健やかでない日々だとおざなりになります。
三宅香帆さんの『なぜ働いていると本が読めなくなるのか』は、そんな日々のモヤモヤした不安感と焦燥感をド直球で刺しにくるタイトルで中身はもちろん、もうタイトルだけで満点で、大ヒットなのも頷けます。
やり方は想像がついたが放置3年
例のごとく前置きが長いのですが、ずっと「noteの数値ちゃんと見たほうがいいよな～～～」とは思っていたのですが、noteの分析は本当に貧弱で…。かゆいところには全部手が届かず、必要な情報をとって集計するには手動コピペか結構がっつりプログラミングしなくちゃ…な感じ…。かつ情報を取ってくるにもnoteは公式API公開をしていないので結構「がっといってぐっと情報を抜いてくる」も一筋縄じゃいかないっつー感じなのです。
「メールでの通知をスプシにまとめる」方式であれば、Zapierかませばすぐできるだろうなぁ…と思って3年くらい放置して、やっと実装したあとに「え、これってもしかしなくてもGASでやればよかった？」みたいなことに気が付き、さらにnote側がしれっと毎月の販売データをCSVでDLさせてくれる仕様にやっっっっっっっっっっっっっっとなっていたので、それ使えばよくね…？とか紆余曲折したのがここ2週間くらいです。
Zapierでやっちゃったけどメール転記だけだしGASでよかったんか？という気がしてきた…が…まぁいいか。notionに格納でもよかったかも？だけど、結局文字列加工するならスプシに入れて…でいいな～となるなどする。
— ぱぴこ (@inucococo) December 4, 2024
1年分くらいの販売データDLが可能に…
note側が提供してくれていた販売データのDL場所はこちら。わかりにくくて、私は24年12月まで存在に気が付いてなかったです…。いつからあったんだろう、昔からかな…？？？ どうやら24年11月頃実装みたいです。
【公式FAQ】記事の販売履歴をCSV形式でエクスポートする
ダッシュボード > 販売履歴 > エクスポート設定 とボタン押下で進むとエキスポート画面に遷移します
対象年月をプルダウンで選び、『販売履歴のエクスポート』ボタンを押下すると、対象の販売履歴データのCSVファイルがDLできます
2024年12月現在、2023年10月分データまではDLが可能です
私は2019年から販売記録があるはずなので、データ保存期間があるのかなんなのかはまでは調べていません。てかいつの間にこんなデータDL機能ついたの？？？結構前？？？
複数ファイルのマージはPowerQueryが便利
ひとまず販売データがあるならそれ見るか～～～とDLしたわけです。正直、理想的には「期間指定で対象データを全部抜く」だったわけですが、そういう仕様ではなかった。くぅ。結果、同一フォーマットの複数月次ファイルが14個（23年10月～24年11月）あり、これを1個のリスト型データにしたいわけじゃないですか。
で、コピペは面倒だし、どうやったらいいんかしら～と雑にChatgptに聞いたらPythonでやればいいよ！って言われて、えー…って思って、ちょっと触ったけどエラー出るしだる…となった後に「あれ、普通にPowerQueryでよくね？？？」となってなったら秒でした。
DLしたファイルをひとつのフォルダに格納する（input_folder)
Excelの データ > データの取得 > ファイルから > フォルダーから を選択し、対象フォルダ（input_folder) を選択する
フォルダ内のCSVファイルが読み込まれるので、『結合』プルダウンの中にある『結合および読み込み』を選択する
各ファイルデータが読み込まれるので、変換コードなどを確認・選択してOKを押す
フォルダ配下に配置した全CSVファイルがMergeされたデータができる！
また、PowerQueryのいいところはまだあって…。
データ型の変換が容易
CSVファイルが増えても（今後24年12月データ以降の未来データや、23年10月以前の過去データが増えた場合も）、同一フォルダに格納してファイル側を更新すれば即反映される…ということ。
複数ファイルの結合、統合にめっちゃ便利ねぇ～～～となりました。詳しいことはわんさか記載してくれているブログがあるので読んでください。
でも日付・時間データの変換はうまくいかなくて、結局テキスト型で入ってきた日付・時間データを関数変換しました。
B2セルに日付データ（テキスト型）＝20240101003315
日付データ（テキスト型）から曜日を出したくて抽出…
=TEXT(DATE(LEFT(B2, 4), MID(B2, 5, 2), MID(B2, 7, 2)), "aaa")
で、販売データでちょいちょい見ていたら、やっぱりダッシュボード化までして自動更新でちょいちょい見たくなるのが人の性じゃないですか。
弊社のMSライセンスはBIもちゃんと使えるE5なんですが、まぁ会社のに突っ込むわけにはいかないし、ダッシュボードって無料とかのあるん…？googleって何かあるの…？みたいなところで止まっています（あるっぽい）
notionのチャート機能が出てから全然使ってなくて、どうせならExcel/スプシの内容をnotionに自動転記で食わせてチャートで…とかができるといいかもしれないです。
理想の状態はリアルタイムで情報格納・転記・チャート表示が自動になること
お分かりいただけるだろうか。
正直「月次で販売データをDLする」みたいなことすら手動でやりたくないわけ、こっちは。なんだけど、もうちょっと色々調べたり、スプシ側に入れる情報をうまくやってあげないとダメっぽいなぁって感じです。
当然Pythonやsherllで組むってことはできるわけですが、WEBページログインして特定ファイルをDLして格納…みたいなところをある程度手軽にやるならRPAかしら…とは思うのですが、どっちがいいんでしょうね。
詳しい人いたら教えてください！！！
本題：GASでメールを拾ってスプシに入れる
で、前述の通り、販売データDLできるならそれでいいか～？？？とも思ったのですが、一定のリアルタイム性のあるデータを追いたい…という話と、実績の月次データを見るのはやっぱり視点が変わるし、実績月次データだけっつーのもねぇ～みたいな気持ちなので、GAS初挑戦！みたいな感じでやってみた。
やろうとしていることは非常に一般的だったので、ぐぐればすぐ同じようなことをしている人にたどり着けました。Google周りは触っている方も多くて情報を探すのは苦労がなさそう…という印象でした。
さて、chatgptちゃんにも助けてもらいながら、できたコードがこちらです。
各コードの意味や内容は調べてね…なんすけど、noteの通知メールの情報を拾う部分にも結構癖があって「なんでお前は…」という気持ちにはさせられました。処理が終わった対象を「転記済」というGmailのラベルを付与することで対象外にする手法はほぼ共通だったので、パクっています。
function MailCopy() {
//********************************
// 処理に必要な設定情報
//********************************
// 検索条件に該当するスレッド一覧を取得
var SearchWord = 'from:noreply@note.com -label:転記済';
// 転記済のメールに設定するラベル名
var Label = GmailApp.getUserLabelByName('転記済');
// 転記するシート名
var SheetName = 'note log';
//********************************
// スプレッドシートの取得
//********************************
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SheetName);
// シートが存在しない場合のエラー処理
if (!sheet) {
throw new Error("シート名 '" + SheetName + "' が見つかりません。シート名を確認してください。");
}
//********************************
// メール情報取得処理
//********************************
var threads = GmailApp.search(SearchWord);
threads.forEach(function(thread) {
var messages = thread.getMessages();
messages.forEach(function(message) {
var SendDate = message.getDate(); // メール送信時間
var Subject = message.getSubject(); // メール件名
var Body = message.getBody(); // HTML形式の本文
// HTMLタグとCSS部分を削除
Body = Body.replace(/<style[\s\S]*?<\/style>/gi, ''); // <style>タグを削除
Body = Body.replace(/<@media[\s\S]*?\}/gi, ''); // メディアクエリ削除
Body = Body.replace(/<[^>]*>/g, ''); // その他のHTMLタグを削除
Body = Body.replace(/ /g, ' ').replace(/&/g, '&'); // 特殊文字を置換
// 不要な改行と空白行を削除
Body = Body.split(/\r?\n/).map(function(line) {
return line.trim(); // 各行の前後空白を削除
}).filter(function(line) {
return line !== ""; // 空行を削除
}).join("\n"); // 改行で結合
// 最終行を取得して転記
var lastRow = sheet.getLastRow() + 1;
sheet.getRange(lastRow, 1).setValue(SendDate); // 送信時間
sheet.getRange(lastRow, 2).setValue(Subject); // 件名
sheet.getRange(lastRow, 3).setValue(Body); // 本文
});
// スレッドに処理済みラベルを付ける
thread.addLabel(Label);
});
}
上記を動かすと、以下のようにスプシ列A~Cに<受信日時><メールタイトル><本文>の情報が入る仕様。黄色でマークしているD~J列は関数で文字列操作したりして抜き出したり、付加した情報になります。
noreply@note.com から届いた、<転記済>ラベルが付いていないメールの<受信日時><メールタイトル><本文>情報をスプシに入れる
スプシに入れる際には1行足して、上記の情報を格納する
スプシに入れたら、<転記済>ラベルをメールに付与し、二重登録がされないように処理する
これが一連の流れです。処理を捨てたり、微妙だったところは以下。
note側のメール通知と決済時間には数秒～数分のラグがある
本文情報に決済日時があり文字列操作で抜き出すことは可能だが、時間ラグを明確に気にしないといけないほど精緻で見る必要もないので受信日時管理でいっか～としている
note側の通知メールは全部拾って転記するため、購入情報のみがスプシに入るわけではない
スプシのフィルタ機能で除外が一番リーズナブルなので無視
メールタイトルで拾うにはややパターンが多く煩雑で…またnote側の仕様でタイトルが変更される可能性もあり、全部取得で調整するのがよさそうと判断
今は実装していないがちょっと改良しようと思っている部分は以下。
1行足した時に関数が引き継がれるようにコピーする処理
当然、やっている人はいるので、パクるか～～となる。
24.12.14 update！関数コピー処理追加版
さくっと実装しました。便利。問題なく動いています。
function MailCopy() {
//********************************
// 処理に必要な設定情報
//********************************
// 検索条件に該当するスレッド一覧を取得
var SearchWord = 'from:noreply@note.com -label:転記済';
// 転記済のメールに設定するラベル名
var Label = GmailApp.getUserLabelByName('転記済');
// 転記するシート名
var SheetName = 'note log';
//********************************
// スプレッドシートの取得
//********************************
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SheetName);
// シートが存在しない場合のエラー処理
if (!sheet) {
throw new Error("シート名 '" + SheetName + "' が見つかりません。シート名を確認してください。");
}
//********************************
// メール情報取得処理
//********************************
var threads = GmailApp.search(SearchWord);
threads.forEach(function(thread) {
var messages = thread.getMessages();
messages.forEach(function(message) {
var SendDate = message.getDate(); // メール送信時間
var Subject = message.getSubject(); // メール件名
var Body = message.getBody(); // HTML形式の本文
// HTMLタグとCSS部分を削除
Body = Body.replace(/<style[\s\S]*?<\/style>/gi, ''); // <style>タグを削除
Body = Body.replace(/<@media[\s\S]*?\}/gi, ''); // メディアクエリ削除
Body = Body.replace(/<[^>]*>/g, ''); // その他のHTMLタグを削除
Body = Body.replace(/ /g, ' ').replace(/&/g, '&'); // 特殊文字を置換
// 不要な改行と空白行を削除
Body = Body.split(/\r?\n/).map(function(line) {
return line.trim(); // 各行の前後空白を削除
}).filter(function(line) {
return line !== ""; // 空行を削除
}).join("\n"); // 改行で結合
// 最終行を取得して転記
var lastRow = sheet.getLastRow() + 1;
sheet.getRange(lastRow, 1).setValue(SendDate); // 送信時間
sheet.getRange(lastRow, 2).setValue(Subject); // 件名
sheet.getRange(lastRow, 3).setValue(Body); // 本文
//********************************
// D列からJ列の関数をコピー
//********************************
if (lastRow > 1) { // 2行目以降が対象
var sourceRange = sheet.getRange(lastRow - 1, 4, 1, 7); // 直前の行（D列～J列）
var targetRange = sheet.getRange(lastRow, 4, 1, 7); // 新規行（D列～J列）
sourceRange.copyTo(targetRange); // 関数をコピー
}
});
// スレッドに処理済みラベルを付ける
thread.addLabel(Label);
});
}
E~J列に入ってる関数をコピーしています。
実行は自動で
時間指定でGAS起動ができるように設定できるので、それで自動実行しています。「キックを意識しないで勝手に動く」的な部分があるかないかで自動化の楽さって全然変わるので、スケジューラーでのキックが容易なのはありがたいです。
面倒だったところや、悩ましい点
noteのメールが画像形式でくるため、余計な情報を除く処理を入れるなど文字列操作が必要なこと（Gatgptちゃんが一発処理してくれたが）
noteの通知メールの形式が同一フォーマットではなく、数パターンを処理する必要があり、関数側処理が煩雑になること
過去分通知データが一部欠損しており、結局3か年データとかは見れなかったこと
◆noteのメール形式と、文字列処理せずにスプシ転記した場合の状態
これは結構、え～待って～～～な状態になりました。HTMLのタグ情報や、無駄な改行、空白などを転機時のGAS側の処理で消して転記する形にしています。
通知メールのパターンが複数なのヤメテ
noteのメール通知を拾って加工…という形を取っているのですが、そのパターンが複数あって、絶妙に最悪なんですよね…。
私のnote構成が悪い（各種サービスが入ってしまっている）部分もあれど、note側でもう少しフォーマット統一で流すくらいのことはしてよう…と思いました。販売通知で必要なデータって同一のはずじゃん…。
特定のメールを拾ってまとめて…とする場合は、何かしら「固定値」の処理をするもので、固定値処理をする場合に楽なのがform形式の情報に入れてもらう…とかなんですけれど、受け側でコントロールできないところのフォーマットがバラだと、色々処理するのが面倒なのでウーーーーーとなりますね。DXとは標準化との闘い…。
Zapierなどのローコードが便利なところ
まぁ、選択してぽちぽちしたらOK！とという点。画像データで変なリンク情報が入る～みたいなところを、ツール側で結構よしなに処理してくれるあたりは楽ちんさがあります。
ただ、Power Automateでもそうでしたが、Zapierでも日付データがUTCで入ってくるので、FORMAT DATEかまして日本時間にしてあげる必要があります。そしてZapierは現在2ステップしか無料版では作れないので、変換をツール側で処理できなくて詰みました。
やり方解説通りですぐです。見たまま、そのまま。
いい感じのソフトウェア・アプリケーションの無料部分が渋くなっていくのはこの世の理なんですが、Zapierも例にもれずで。Freeから次のランクの課金ハードル、ちょっと高すぎませんか？？？となった。円安を考えても、1000円前後のライトPlanが必要では…？？？
2stepかつ、100タスクしか月に処理できない…となると普通に今回のような「ある程度の頻度で転記が発生する」みたいなやつだと危ないので使えないですね。
Googleの自由度が高い！の意味を理解
googleを使っている人が「GASでちょちょいってできて便利」的なことを言っていた意味はわかりました。Outlook（M365）だと、まぁshell scriptはあるものの、googleほどハードルが低くない感がありますし、結構基本的な処理でもPAかshell script…となりがちで「面倒」なのはその通り。
ずっとMS世界観だったので、なかなか業務で利用しないGAS領域まで触れる機会がなかったので、ちょこっとさわって「あ、なるほど～」となったのはよかったです。
