Zapierで技術書典のサークル向け購入通知メールからGoogle スプレッドシートに必要な情報を出力して集計する

はじめに

本日(2021/01/06)までの技術書典10にサークル参加しています。サークルとしては、技術書典のサイトの販売履歴ページにCSVなどのファイルに出力する機能がほしいと思っていますが、残念ながら、今のところ実装はされていないようです。
そこで、サークルの本が購入されると、サークル代表者に「[技術書典] あなたのファンが書籍「〇〇」を購入しました」というメールが来るのを利用し、Google スプレッドシートに自動的に購入情報を整理する仕組みを考えてみました。
私はGmailのメールアドレスを登録している為、Gmailのメール受信をトリガにして、 スプレッドシートに情報を書き出します。
現在IFTTTは、IFにGmailを設定することができない為、代替手段として、Zapierを使用します。
※Zapier を用いて Gmail の受信記録を Spreadsheet に転記するという記事を参考にしました。
ZapierもIFTTTと同様Googleアカウントがあればすぐに動作させることができます。

スプレッドシートに情報が書き出せれば、あとは表やグラフに整理できますので、そのことに関しても合わせて記述します。

注意事項

Zapierは、IFTTTよりも課金対象になる条件が緩い（無料版は100回/月のActionまで ※）為、大手サークルさんが利用する場合は、課金対象に注意してください。
また、Zapierサーバーにメール内容を解析させることになる為、セキュリティには注意が必要です。個人情報が筒抜けになる可能性がありますので、普段使用しているGmailアドレスとは別のアドレスを用意した方がよいかもしれません。本稿の内容を試す場合は自己責任でお願いします。

本記事内容は、技術書典の利用規約に反していないつもりですが、利用規約に反している等問題が発生した場合は速やかに記事を削除します。
技術書典運営様に本Zapierに関する動作について質問、要望等を出すのも運営様の迷惑になりますので、控えていただきますようお願いします。

※Zapierのアカウントを作成してから14日間は、1000回まで実行可能等のお試し期間があります。Get started with your free Zapier trial参照。

注意事項2(2021/07/23追記)

Zapの仕様に記載が見つからなかったのですが、Google Sheetの出力行は100行までのようです。それ以上追加しようとしたら、

のようなエラーが発生しました。ご注意ください。無課金のアカウントだからかもしれません。

Google スプレッドシートの設定１

「技術書典」というスプレッドシートを作成します。

Dataシート

シート1を「Data」に変更します。（このシート名はZapierで使用します）
下記のようにセルにそれぞれ入力します。

A1:Date(Gmail)
B1:Body(Gmail)
C1:Date
D1:BookName
E1:Type
F1:Value
G1:Value(送料込)

C列全体を表示形式 -> 数字 -> 日付
に変更します。
あとは好みで見た目を変更します。

Zapierの設定

Zapierにアクセスし、「Sign up with Google」でログインします。

左側のメニューにある「Make A ZAP」を押下します。
Zapと言うのがIFTTTにおけるアプレットのことで、「何か起きたら何かをする」するというルールのことになります。

Search Boxに「Gmail」と入力し、Gmailを選択します。

Trigger

Choose app & event

Trigger Eventを「New Email Matching Search」を選択し、Continueを押下します。

Choose account

Gmailアカウントを選択し、Save & Continueを押下します。

Set up trigger

サークルの通知メールだけを解析対象にする為、Search Stringに
[技術書典] あなたのファンが書籍「
を入力し、Save & Continueを押下します。

Test Trigger

Test triggerボタンを押下して、動作を試すことができます。
すでにメールがINBOXにあれば、認識したメール内容が表示されます。

Action

続きまして、どのように動作するかについて設定します。

Choose app & event

Google Sheetsを選択し、Action EventにCreate Spreadsheet Rowを選択します。
Continueを押下します。

Choose account

Google Sheets account

スプレッドシートのアカウントを選択し、Save & Continueを押下します。

Set up action

Drive：入力不要
Spreadsheet:「技術書典」を選択します。
Worksheet:「Data」を選択します。
Date(Gmail):「Date」を選択します。
Body(Gmail):「Body Plain」を選択します。
Date:

=DATEVALUE(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 3)&"/"&MATCH(INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 2),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)&"/"&INDEX(SPLIT(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2)), "[0-9]{1,2} [a-zA-Z]{3} [0-9]{2,4}"), " "), 0, 1))+TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),"[0-9:]{5,8}"))-VALUE(IF(COUNTIF(INDIRECT(ADDRESS(ROW(),COLUMN()-2)), "*-*"), "-", "")&TIMEVALUE(MID(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2)), "[+-][0-9]{4}"), 2, 2)&":0:0"))+TIMEVALUE("9:0:0")

を入力します。Date(Gmail)（A列）に入力される日時はUTCの日時になる為、C列に変換した日時を出力します。Zapier側に日時の記述をJapan/Tokyoに変更する設定はあったのですが、動作に変わりなかった為自前でスプレッドシートが認識できる日時文字列に変更する必要があります。
※時刻は、技術書典の履歴情報と照らし合わせると１秒程度遅延が発生しますが、１秒程度なら許容範囲だと思います。

BookName:

=CLEAN(SUBSTITUTE(INDEX(SPLIT(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),CHAR(10),TRUE,TRUE),12),"書籍名 : ", ""))

SUBSTITUTE関数で不要な文字列を削除します。なぜか改行ではないゴミがくっついてしまう為、CLEAN関数でゴミを除去しています。

Type:

=CLEAN(SUBSTITUTE(INDEX(SPLIT(INDIRECT(ADDRESS(ROW(),COLUMN()-3)),CHAR(10),TRUE,TRUE),13),"販売形態 : ", ""))

Value:

=VALUE(REGEXREPLACE(INDEX(SPLIT(INDIRECT(ADDRESS(ROW(),COLUMN()-4)),CHAR(10),TRUE,TRUE),14),"販売価格 : |\+送料|円|\s",""))

REGEXREPLACE関数で不要な文字列を削除します。

Value(送料込):

=IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2))="電子＋紙セット",INDIRECT(ADDRESS(ROW(),COLUMN()-1))-100,INDIRECT(ADDRESS(ROW(),COLUMN()-1)))

電子＋紙セットの場合は送料（100円）を引くよう設定します。

Save & Continueを押下します。
設定は以上です。

画面右上の

をONに変更するとZAPが有効になります。
メールが来るのを待ちましょう。

メールが来るとスプレッドシートに、

のように出力されます。

Google スプレッドシートの設定２

ここからは、取得したデータをスプレッドシート上でどう集計するか、という話です。

集計シート

「集計」シートを新たに作成します。

2行目:本のタイトルを記述します。
3行名:本の種類（電子＋紙セットまたは電子版）を記述します。
B列:日付を入力します。（範囲は任意）

セルC4などに頒布数を出力する関数を記述します。
１例として、セルC4には

=COUNTIFS(Data!$C:$C,">="&INT($B4),Data!$C:$C,"<"&INT($B4)+1,Data!$D:$D,"=" & C$2,Data!$E:$E, "=" & C$3)

と入力します。セルC2とセルC3で書籍対象を決定し、B4で日時を取得し、抽出しています。
他のセルはコピーでいけます。

グラフシート

新たにグラフシートを作成します。

挿入 -> グラフでグラフを追加します。
グラフをダブルクリックしてグラフエディタを表示し、設定タブを下記のように入力します。
※集計シートのセルB4～B15に2020/12/26～2021/01/06を入力した場合
グラフの種類：積み上げ横棒グラフ
積み上げ：標準
データ範囲：'集計'!B4:J15,'集計'!B4:B15
範囲を結合：上下
列Bを集計にチェック

カスタマイズタブ -> グラフの軸のタイトルに「技術書典10頒布数」と入力します。

すると、

のような積み上げグラフが表示されます。

売上合計

セルに

=SUM(Data!$G2:$G201)

のように入力すれば、売上（送料込み）の合計が表示されます。